Running multiple instances on a [large] server
One of the teams here is planning to run anything from 8 to 16 database instances [no indication on sizing yet, but gut-feel SGAs are 200MB to 1GB and DB sizes 500MB to 40GB] on a large server, something like a Sun E6800 or an equivalent HP or Fujitsu server. 1. How many of you do run, and are comfortable running, multiple databases on the same server, whether it is partitioned or not ? 2. Do you create a seperate ORACLE_HOME for each instance ? 3. Do you just buy the Oracle DB CPU license on the total number of CPUs on the server ? My positioning is a. We might not be able to create 8 partitions but partition such that we have a max of 2 or 3 instances in one partition. Hopefully, I can dynamically change CPU partitioning to reallocate CPU to another group of instances. b. Each instance should have it's own ORACLE_HOME. [Disk space is not a constraint]. c. Just add up the number of CPUs on the server, across all partitions, and buy a CPU license. Also, a CPU license is much preferable to Named-User as some of these databases would host Portals for 1,000+ end-users. Hemant K Chitale http://hkchital.tripod.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Hemant K Chitale INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Oracle qry
Hi All, I've the following qry : select distinct(decode(b.sys_id,'TRDENG',a.cust_bas_no,a.cosmos_base_no)) baseno ,a.br_cod,pty_nam,bank_name from piar_fr_psd a, psd b, bank_br c where a.psd_id=b.psd_id and a.psd_serial_num = b.psd_serial_no and b.bank_id = c.bank_id and (((sys_id = 'TRDENG') and (a.cust_bas_no is not null)) or ((sys_id 'TRDENG') and (a.cosmos_base_no is not null))) o/p : baseno br_cod pty_nam bank_name 110 x1 y1 110 x111 y1 110 x1334 y1 220 x212 y2 220 x213 y2 For a baseno,br_cod combination I need only one row to be displayed. i.e any one pty_nam should be displayed . o/p : baseno br_cod pty_nam bank_name 110 x1 y1 220 x213 y2 How can I achieve this. Thanks Manoj -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Database vs File System
We have got a requirement to keep all the documents on repository. We are looking on the possibility of keeping documents on Database or filesysytem. Could you please let me know your views which one I should go for. Regards, Dhanvir This email and any attached to it are confidential and intended only for the individual or entity to which it is addressed. If you are not the intended recipient, please let us know by telephoning or emailing the sender. You should also delete the email and any attachment from your systems and should not copy the email or any attachment or disclose their content to any other person or entity. The views expressed here are not necessarily those of Churchill Insurance Group plc or its affiliates or subsidiaries. Thank you. Churchill Insurance Group plc. Company Registration Number - 2280426. England. Registered Office: Churchill Court, Westmoreland Road, Bromley, Kent BR1 1DP. -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Rathi Dhanvir INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: Running multiple instances on a [large] server
I have run/am running multiple oracle databases on a single server on AIX/Linux/W2K/NT although not with sga's in the GB area.Just make sure you get away with your I/O requirements We only had seperate homes for seperate oracle versions, so all 817 databases were in one home and 8.0.5 databases were grouped in one home etc.etc. I did not have to deal with licensing so can't give you a comment there. -Original Message- Sent: dinsdag 11 maart 2003 9:49 To: Multiple recipients of list ORACLE-L One of the teams here is planning to run anything from 8 to 16 database instances [no indication on sizing yet, but gut-feel SGAs are 200MB to 1GB and DB sizes 500MB to 40GB] on a large server, something like a Sun E6800 or an equivalent HP or Fujitsu server. 1. How many of you do run, and are comfortable running, multiple databases on the same server, whether it is partitioned or not ? 2. Do you create a seperate ORACLE_HOME for each instance ? 3. Do you just buy the Oracle DB CPU license on the total number of CPUs on the server ? My positioning is a. We might not be able to create 8 partitions but partition such that we have a max of 2 or 3 instances in one partition. Hopefully, I can dynamically change CPU partitioning to reallocate CPU to another group of instances. b. Each instance should have it's own ORACLE_HOME. [Disk space is not a constraint]. c. Just add up the number of CPUs on the server, across all partitions, and buy a CPU license. Also, a CPU license is much preferable to Named-User as some of these databases would host Portals for 1,000+ end-users. Hemant K Chitale http://hkchital.tripod.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Hemant K Chitale INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Jack van Zanen INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Snapshots
Could anybody of the list give me the correct explanation of the below ?mechanism provided by ORACLE for table replication - Snapshots and SNAPSHOT LOGs Thanks and Regards, Santosh
RE: Oracle qry
Select * from ( select distinct(decode(b.sys_id,'TRDENG',a.cust_bas_no,a.cosmos_base_no)) baseno ,a.br_cod,pty_nam,bank_name, rank() over (partition by baseno ,a.br_cod order by pty_nam) as rk from piar_fr_psd a, psd b, bank_br c where a.psd_id=b.psd_id and a.psd_serial_num = b.psd_serial_no and b.bank_id = c.bank_id and (((sys_id = 'TRDENG') and (a.cust_bas_no is not null)) or ((sys_id 'TRDENG') and (a.cosmos_base_no is not null))) ) where rk = 1; Kamal. -Original Message- [EMAIL PROTECTED] Sent: 11 March 2003 10:56 To: Multiple recipients of list ORACLE-L Hi All, I've the following qry : select distinct(decode(b.sys_id,'TRDENG',a.cust_bas_no,a.cosmos_base_no)) baseno ,a.br_cod,pty_nam,bank_name from piar_fr_psd a, psd b, bank_br c where a.psd_id=b.psd_id and a.psd_serial_num = b.psd_serial_no and b.bank_id = c.bank_id and (((sys_id = 'TRDENG') and (a.cust_bas_no is not null)) or ((sys_id 'TRDENG') and (a.cosmos_base_no is not null))) o/p : baseno br_cod pty_nam bank_name 110 x1 y1 110 x111 y1 110 x1334 y1 220 x212 y2 220 x213 y2 For a baseno,br_cod combination I need only one row to be displayed. i.e any one pty_nam should be displayed . o/p : baseno br_cod pty_nam bank_name 110 x1 y1 220 x213 y2 How can I achieve this. Thanks Manoj -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). * Disclaimer This message (including any attachments) contains confidential information intended for a specific individual and purpose, and is protected by law. If you are not the intended recipient, you should delete this message and are hereby notified that any disclosure, copying, or distribution of this message, or the taking of any action based on it, is strictly prohibited. * Visit us at http://www.mahindrabt.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Kamaljeet Singh INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: LMT monitoring
Fresh tablespace Created specifically to create a testcase for an iTAR. -Original Message- Sent: Monday, March 10, 2003 4:30 PM To: Multiple recipients of list ORACLE-L Was this a fresh tablespace or were there swiss cheese holes available to fill? Ron Thomas Hypercom, Inc [EMAIL PROTECTED] Each new user of a new system uncovers a new class of bugs. -- Kernighan [EMAIL PROTECTED] t.comTo: [EMAIL PROTECTED] Sent by: cc: [EMAIL PROTECTED] Subject: RE: LMT monitoring 03/10/2003 02:02 PM Please respond to ORACLE-L As mydata load continues, the saga continues. The simplistic algorithm does not hold Can anyone explain these results? PARTITION_NAME EXTENT_ID BYTES/1024 BYTES/1024/1024 -- -- -- --- FINS_FM_DATA_CLOSED_200207 84 6144 6 FINS_FM_DATA_CLOSED_200207 85 5120 5 FINS_FM_DATA_CLOSED_200207 86 6144 6 FINS_FM_DATA_CLOSED_200207 87 5120 5 FINS_FM_DATA_CLOSED_200207 88 5120 5 FINS_FM_DATA_CLOSED_200207 89 4096 4 FINS_FM_DATA_CLOSED_200207 90 5120 5 FINS_FM_DATA_CLOSED_200207 91 4096 4 FINS_FM_DATA_CLOSED_200207 92 4096 4 FINS_FM_DATA_CLOSED_200207 93 4096 4 FINS_FM_DATA_CLOSED_200207 94 4096 4 FINS_FM_DATA_CLOSED_200207 95 3072 3 FINS_FM_DATA_CLOSED_200207 96 4096 4 FINS_FM_DATA_CLOSED_200207 97 3072 3 FINS_FM_DATA_CLOSED_200207 98 3072 3 FINS_FM_DATA_CLOSED_200207 99 3072 3 FINS_FM_DATA_CLOSED_200207100 3072 3 FINS_FM_DATA_CLOSED_200207101 3072 3 FINS_FM_DATA_CLOSED_200207102 3072 3 FINS_FM_DATA_CLOSED_200207103 3072 3 FINS_FM_DATA_CLOSED_200207104 3072 3 FINS_FM_DATA_CLOSED_200207105 3072 3 FINS_FM_DATA_CLOSED_200207106 3072 3 FINS_FM_DATA_CLOSED_200207107 3072 3 FINS_FM_DATA_CLOSED_200207108 3072 3 FINS_FM_DATA_CLOSED_200207109 3072 3 FINS_FM_DATA_CLOSED_200207110 3072 3 FINS_FM_DATA_CLOSED_200207111 2048 2 FINS_FM_DATA_CLOSED_200207112 2048 2 FINS_FM_DATA_CLOSED_200207113 2048 2 FINS_FM_DATA_CLOSED_200207114 2048 2 FINS_FM_DATA_CLOSED_200207115 2048 2 -Original Message- Sent: Monday, March 10, 2003 3:36 PM To: '[EMAIL PROTECTED]' According to a good email from Dan Fink (which I've since checked to 83 extents), the size of the extents is based soley on extent counts #extents next extent size 1-1564k 16-79 1m 80-1998m 200-64m I would guess that in most cases estimating the next extent size to be 8m would be sufficient for space monitoring purposes Kevin -Original Message- Sent: Monday, March 10, 2003 2:40 PM To: Multiple recipients of list ORACLE-L Kevin For Raj's purposes, is it possible to estimate a range? I'm thinking he really just needs an estimate to see if he is getting close. Dennis Williams DBA, 40%OCP, 100% DBA Lifetouch, Inc. [EMAIL PROTECTED] -Original Message- Sent: Monday, March 10, 2003 12:40 PM To: Multiple recipients of list ORACLE-L There are three (3) types of LMTs (yes, three!) UNIFORM Extent sizes Every extent created in the tablespace will be the same size, no matter the storage parameters specified. AUTOALLOCATE (System managed) The system will decide the next extent size at creation. This is based on a large number of things. (I think the phase of the moon and the number of sun-spots on 03-03-1942 are included in this calculation) The min extent size is 64K USER Allocated This is only available for tablespaces that were converted from dictionary managed tablespaces. As it would seem, the user determines the space allocation -- the space allocation is the same as for dictionary managed
RE: LMT monitoring
The version is 8.1.7.1.0 The report is via this query: SELECT partition_name, extent_id, bytes/1024, bytes/1024/1024 FROM dba_extents WHERE segment_name = 'FORMATTER_DATA_HISTORY' ANDowner = 'KEVIN' ORDER BY 1, 2 The file is not autoextent. The table is populated by sqlldr direct path. The tablespace is locally managed with system allocated extents TABLESPACE_NAMEEXTENT_MAN ALLOCATIO -- -- - KEVIN_TS LOCAL SYSTEM Kevin -Original Message- Sent: Monday, March 10, 2003 5:21 PM To: Multiple recipients of list ORACLE-L Which version of Oracle ? How are you getting the report ? Is the file autoextent - if so at what unit ? How are you filling the table ? Is the tablespace ASS Managed ? Regards Jonathan Lewis http://www.jlcomp.demon.co.uk Now available One-day tutorials: Cost Based Optimisation Trouble-shooting and Tuning Indexing Strategies (see http://www.jlcomp.demon.co.uk/tutorial.html ) UK___March 19th UK___April 8th UK___April 22nd USA_(FL)_May 2nd Next dates for the 3-day seminar: (see http://www.jlcomp.demon.co.uk/seminar.html ) UK_(Manchester)_May USA_(CA, TX)_August The Co-operative Oracle Users' FAQ http://www.jlcomp.demon.co.uk/faq/ind_faq.html - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: 10 March 2003 21:02 As mydata load continues, the saga continues. The simplistic algorithm does not hold Can anyone explain these results? PARTITION_NAME EXTENT_ID BYTES/1024 BYTES/1024/1024 -- -- -- --- FINS_FM_DATA_CLOSED_200207 84 6144 6 FINS_FM_DATA_CLOSED_200207 85 5120 5 FINS_FM_DATA_CLOSED_200207 86 6144 6 FINS_FM_DATA_CLOSED_200207 87 5120 5 FINS_FM_DATA_CLOSED_200207 88 5120 5 -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Jonathan Lewis INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Toepke, Kevin M INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: Snapshots
Read this, http://download-west.oracle.com/docs/cd/A87860_01/doc/server.817/a76965/c31repli.htm#12888 Regards, Kamaljeet Singh (NCDB ASG) MBT, 52 BarrackSquare, Martlesham. IP5 3RF. Off. 01473 667170 Mob. 077 5368 5370 [EMAIL PROTECTED] -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of Santosh Varma Sent: 11 March 2003 11:35 To: Multiple recipients of list ORACLE-L Subject: Snapshots Could anybody of the list give me the correct explanation of the below ?mechanism provided by ORACLE for table replication - Snapshots and SNAPSHOT LOGs Thanks and Regards, Santosh * Disclaimer This message (including any attachments) contains confidential information intended for a specific individual and purpose, and is protected by law. If you are not the intended recipient, you should delete this message and are hereby notified that any disclosure, copying, or distribution of this message, or the taking of any action based on it, is strictly prohibited. * Visit us at http://www.mahindrabt.com
Re: Oracle Development Suite for Linux?
On Mon, Mar 10, 2003 at 08:28:50PM -0800, Charles Hart wrote: I installed 9.2 on redhat 8.0 with great luck. I installed on a 700 PC with 756 megs of memory. I was using forms 6i running on NT client and could not tell when I was pointed at this instance verus one running on a HP machine. The document I found that was helpful in the install was http://otn.oracle.com/tech/linux/pdf/installtips_final.pdf I believe the real problems where with 8i on a 2.4 kernel. There is a miss-match is the glibc versions used for the kernel and oracle. What a mess. This seemed to work on 8.1.7-rh7.1 combination. rh7.1 is the last free version in the support matrix if that matters. http://www.tldp.org/HOWTO/Oracle8-on-RH7X-HOWTO-3.html On an error with a 9.0 install: Error when invoking /9.0/plsql/lib/ins_plsql.mk Doc ID: 197301.995 From: Alex Andriyashchenko [EMAIL PROTECTED] Reply-To: [EMAIL PROTECTED] To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Subject: Oracle Development Suite for Linux? Date: Mon, 10 Mar 2003 12:24:01 -0800 Hello List, Has somebody had any luck or problems to install Oracle development suite v9.0.2 under RH Linux? How good it is there now? I found that previous versions run much better under Windows. Thank you for your help. -- Best regards, Alex mailto:[EMAIL PROTECTED] __ Do You Yahoo!? Everything you'll ever need on one web page from News and Sport to Email and Music Charts http://uk.my.yahoo.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Alex Andriyashchenko INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). _ The new MSN 8: smart spam protection and 2 months FREE* http://join.msn.com/?page=features/junkmail -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Charles Hart INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- === Ray Stell [EMAIL PROTECTED] (540) 231-4109 KE4TJC28^D -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Ray Stell INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: Oracle position on hints
Title: RE: Oracle position on hints Hi, We recently upgraded from 7.3.4 to 8.7 (management plans on getting to that Y2K problem shortly ;-) We had an SQL statement that really needed a hint in 7.3.4. After upgrading to 8.7, I removed the hint and it runs much faster without it. I spend some of my spare time testing SQL with hints removed now. Jerry Whittle ASIFICS DBA NCI Information Systems Inc. [EMAIL PROTECTED] 618-622-4145 -Original Message- From: Mark Richard [SMTP:[EMAIL PROTECTED] snip 1) You are limiting functionality when the database is upgraded - I have seen several examples where Oracle went from 7 to 8 and noone looks at every SQL statement to reevaluate the validity of every hint. snip
RE: ``SQL*Net message to client`` Wait event with no SQL*Net
I think you're right Tim. It was kinda the point I so clumsily tried to make. There really is no such thing as a direct connection per se. Everything connects using SqlNet. -Original Message- Sent: Monday, March 10, 2003 6:19 PM To: Multiple recipients of list ORACLE-L But it still goes through the SQL*Net code layer on both the client and server sides. It's just that the SQL*Net code layer has the ability to spawn the server process from the client (i.e. pipe or bequeath) as well as contacting the TNS Listener. Regardless of how the server and client are attached, it is all going through the SQL*Net layer and thus gets SQL*Net wait-events... - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Monday, March 10, 2003 2:19 PM I'm not sure we're hitting the nail on this one. Just a sqlplus user/password locally should not use the listener because it works when the listener is stopped. [EMAIL PROTECTED] 3/10 1:31p Thanks Lisa, but I already checked that. I used to think this as well. But according to Rachel and Tom the listener is apparently used even if the @sid is not present. Glad I wasn't the only one laboring under that misapprehension :) Jay -Original Message- Sent: Monday, March 10, 2003 1:54 PM To: [EMAIL PROTECTED]; Miller, Jay connection Jay, if the connection was made with @ORACLE_SID (sqlplus user/[EMAIL PROTECTED]) then the listener is used. If the connection was made with the sid defined in the environment variable ORACLE_SID and without @ (sqlplus user/pw) the listener is not used. List please correct me if I'm wrong. Lisa Koivu Oracle Database Monkey Mama Fairfield Resorts, Inc. 5259 Coconut Creek Parkway Ft. Lauderdale, FL, USA 33063 Office: 954-935-4117 Fax:954-935-3639 Cell:954-683-4459 -Original Message- Sent: Monday, March 10, 2003 1:04 PM To: Multiple recipients of list ORACLE-L connection A developer/analyst was running 4 reports on our datawarehouse, connecting locally using SQL Plus from a telnet session on the Unix box. He then started 4 queries, spooling the output to the unix server (again, local). He was curious as to why the sessions were both taking so long and why they alternated between showing as Active and Inactive in v$session. When I checked v$session_wait for his sessions I saw that they were a mix of SQL*Net message from client and SQL*Net message to client Since he's not connecting through the listener, why would this wait event show up? There should be no network activity at all (I double checked that the SQL is not using any database links). Any ideas? Oracle 8.1.6.2 Solaris 2.6 Thanks, Jay Miller -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Darrell Landrum INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Tim Gorman INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services
Tablespaces - datafiles
All - I am having a problem with the datafiles in a temporary tablespace. I need to move and rename three different datafiles in the tablespace. I am able to take them offline - no problem. I cna make the changes at the OS level. I am running on Unix. But I can't get the changes to show up in the OEM inorder to bring them back on-line. Do I need to remove all users from this tablespace before making these changes? The tablespace is temporary so does that make a difference? Any suggestions? LeRoy -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: LeRoy Kemnitz INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: Quick question on cursor resource use
-Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] Behalf Of DENNIS WILLIAMS Sent: Monday, March 10, 2003 15:45 To: Multiple recipients of list ORACLE-L Subject: RE: Quick question on cursor resource use Fuzzy What is prompting you to increase OPEN_CURSORS? Is your application currently receiving an error from running out of cursors? Yes, it's returning ORA-01000 - having looked at the SQL, it's perfectly understandable why - a whole bunch of concurrently submitted SQL, with lots of recursive SQL spawned by Oracle to support it. So I'm not complaining about getting the error. I just want some ideas about the resource hit if I up this to 500, 1000, 50? The Oracle architecture stuff was silent on the subject. Ciao Fuzzy :-) -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Grant Allen INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: Oracle position on hints 9.2.0.X
After spending 2 ½ days trying hints, init parameters, re-writing the query, a completely useless TAR, etc. to get a query that runs in 1 second on 8.1.6.X to go faster than 1 minute on 9.2.0.2 I found a new to 9.2.0.X dynamic init parameter optimizer_dynamic_sampling, if I understand it correctly this parameter forces the optimizer to try harder to get an efficient execution plan. Check the FM there are some interesting things that each level causes the optimizer to do. The default is optimizer_dynamic_sampling=1 I've tried optimizer_dynamic_sampling = 5 7. The query in question has several joins across database links. In 8.1.6 the 10046 trace shows 68 I/O's to the remote database in 9.2.0.2 with optimizer_dynamic_sampling =1 10046 shows 1.4 million I/O's to the remote database. With optimizer_dynamic_sampling = 5 the I/O's are back to 68. Check this parameter it saved us from re-writing a bunch of sql... ...JIM... [EMAIL PROTECTED] 3/11/03 7:33:56 AM Hi, We recently upgraded from 7.3.4 to 8.7 (management plans on getting to that Y2K problem shortly ;-) We had an SQL statement that really needed a hint in 7.3.4. After upgrading to 8.7, I removed the hint and it runs much faster without it. I spend some of my spare time testing SQL with hints removed now. Jerry Whittle ASIFICS DBA NCI Information Systems Inc. [EMAIL PROTECTED] 618-622-4145 -Original Message- From: Mark Richard [SMTP:[EMAIL PROTECTED] snip 1) You are limiting functionality when the database is upgraded - I have seen several examples where Oracle went from 7 to 8 and noone looks at every SQL statement to reevaluate the validity of every hint. snip -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: James Howerton INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: Running multiple instances on a [large] server
Hi, On one of the 7 development box (aix 4.3.3) we have 27 instances Oracle 8172. All using the same oracle_home. I can't say it's the fastest response time ;-) As for the licensing we have a mix of CPU and user licences. Stephane Paquette Administrateur de bases de donnees Database Administrator Standard Life www.standardlife.ca Tél. (514) 925-7187 [EMAIL PROTECTED] mailto:[EMAIL PROTECTED] -Original Message- Chitale Sent: Tuesday, March 11, 2003 3:49 AM To: Multiple recipients of list ORACLE-L One of the teams here is planning to run anything from 8 to 16 database instances [no indication on sizing yet, but gut-feel SGAs are 200MB to 1GB and DB sizes 500MB to 40GB] on a large server, something like a Sun E6800 or an equivalent HP or Fujitsu server. 1. How many of you do run, and are comfortable running, multiple databases on the same server, whether it is partitioned or not ? 2. Do you create a seperate ORACLE_HOME for each instance ? 3. Do you just buy the Oracle DB CPU license on the total number of CPUs on the server ? My positioning is a. We might not be able to create 8 partitions but partition such that we have a max of 2 or 3 instances in one partition. Hopefully, I can dynamically change CPU partitioning to reallocate CPU to another group of instances. b. Each instance should have it's own ORACLE_HOME. [Disk space is not a constraint]. c. Just add up the number of CPUs on the server, across all partitions, and buy a CPU license. Also, a CPU license is much preferable to Named-User as some of these databases would host Portals for 1,000+ end-users. Hemant K Chitale http://hkchital.tripod.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Hemant K Chitale INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Stephane Paquette INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Re: Running multiple instances on a [large] server
I can't remember the last time that I saw a server (other than WinNT) running a single instance. My experience is that it's quite normal to run multiple instances on a single server. One Oracle home per version of Oracle. I'm not sure what the point of a separate Oracle home per instance would be. On the licensing issue --- that's Oracle's call. We have a site license so it's not an issue for me. Hemant K Chitale hkchitalTo: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] @singnet.com.sg cc: Sent by: rootSubject: Running multiple instances on a [large] server 03/11/2003 03:48 AM Please respond to ORACLE-L One of the teams here is planning to run anything from 8 to 16 database instances [no indication on sizing yet, but gut-feel SGAs are 200MB to 1GB and DB sizes 500MB to 40GB] on a large server, something like a Sun E6800 or an equivalent HP or Fujitsu server. 1. How many of you do run, and are comfortable running, multiple databases on the same server, whether it is partitioned or not ? 2. Do you create a seperate ORACLE_HOME for each instance ? 3. Do you just buy the Oracle DB CPU license on the total number of CPUs on the server ? My positioning is a. We might not be able to create 8 partitions but partition such that we have a max of 2 or 3 instances in one partition. Hopefully, I can dynamically change CPU partitioning to reallocate CPU to another group of instances. b. Each instance should have it's own ORACLE_HOME. [Disk space is not a constraint]. c. Just add up the number of CPUs on the server, across all partitions, and buy a CPU license. Also, a CPU license is much preferable to Named-User as some of these databases would host Portals for 1,000+ end-users. Hemant K Chitale http://hkchital.tripod.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Hemant K Chitale INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Thomas Day INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: Running multiple instances on a [large] server
Title: RE: Running multiple instances on a [large] server On our development RAC servers we have 24 instances ... on each side, performance is okay. Raj - Rajendra dot Jamadagni at espn dot com Any views expressed here are strictly personal. QOTD: Any clod can have facts, having an opinion is an art !! -Original Message- From: Stephane Paquette [mailto:[EMAIL PROTECTED]] Sent: Tuesday, March 11, 2003 9:19 AM To: Multiple recipients of list ORACLE-L Subject: RE: Running multiple instances on a [large] server Hi, On one of the 7 development box (aix 4.3.3) we have 27 instances Oracle 8172. All using the same oracle_home. I can't say it's the fastest response time ;-) As for the licensing we have a mix of CPU and user licences. Stephane Paquette Administrateur de bases de donnees Database Administrator Standard Life www.standardlife.ca Tél. (514) 925-7187 [EMAIL PROTECTED] mailto:[EMAIL PROTECTED] -Original Message- Chitale Sent: Tuesday, March 11, 2003 3:49 AM To: Multiple recipients of list ORACLE-L One of the teams here is planning to run anything from 8 to 16 database instances [no indication on sizing yet, but gut-feel SGAs are 200MB to 1GB and DB sizes 500MB to 40GB] on a large server, something like a Sun E6800 or an equivalent HP or Fujitsu server. 1. How many of you do run, and are comfortable running, multiple databases on the same server, whether it is partitioned or not ? 2. Do you create a seperate ORACLE_HOME for each instance ? 3. Do you just buy the Oracle DB CPU license on the total number of CPUs on the server ? My positioning is a. We might not be able to create 8 partitions but partition such that we have a max of 2 or 3 instances in one partition. Hopefully, I can dynamically change CPU partitioning to reallocate CPU to another group of instances. b. Each instance should have it's own ORACLE_HOME. [Disk space is not a constraint]. c. Just add up the number of CPUs on the server, across all partitions, and buy a CPU license. Also, a CPU license is much preferable to Named-User as some of these databases would host Portals for 1,000+ end-users. Hemant K Chitale http://hkchital.tripod.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Hemant K Chitale INET: [EMAIL PROTECTED] Fat City Network Services -- 858-538-5051 http://www.fatcity.com San Diego, California -- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Stephane Paquette INET: [EMAIL PROTECTED] Fat City Network Services -- 858-538-5051 http://www.fatcity.com San Diego, California -- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). *This e-mail message is confidential, intended only for the named recipient(s) above and may contain information that is privileged, attorney work product or exempt from disclosure under applicable law. If you have received this message in error, or are not the named recipient(s), please immediately notify corporate MIS at (860) 766-2000 and delete this e-mail message from your computer, Thank you.*1
Analyzing indexes
I need to determine whether or not a couple of indexes need to be rebuilt. The problem is the indexes are quite large and on a 24x7 high volume database. If I try to run an analyze validate structure to gather the data I need to make that decision, it sets a lock on the table for about an hour which I can't afford to do. There is no slow time when I can do this and management has said before they're not going to spring for the partitioning option to break the indexes up into managable pieces. Is there some other way I can get the information needed to determine if an index needs to be rebuilt or not without setting a lock on the table? We are on Oracle 8.1.7. -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Chuck Hamilton INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: Tablespaces - datafiles
Tom: You are absolutely right. What I do is to have a nightly script to check the available disk space (using df -k and awk) and total sum of dba_temp_files to make sure there always enough disk space for the LMT temp tablespace. Guang -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] Behalf Of Mercadante, Thomas F Sent: Tuesday, March 11, 2003 9:19 AM To: Multiple recipients of list ORACLE-L Subject: RE: Tablespaces - datafiles . Now here is the kicker. Let's say you have a disk that is 9 gig is size. You can create 10-1 Gig Temp data files on that disk. Since Oracle does not create the files full-sized, there is nothing to stop this from happening. Sometime later, as the TEMP tablespace gets used, the files grow until eventually the disk fills up, and a sql query crashes with an obscure disk io error. Oracle is trying to expand the TEMP datafiles to the size it's been told they should be. But there is no physical space left on disk. Documentation in 817 does not mention this. But 92 doc's are up to date. nice surprise, eh? Tom Mercadante Oracle Certified Professional -Original Message- Sent: Tuesday, March 11, 2003 8:39 AM To: Multiple recipients of list ORACLE-L All - I am having a problem with the datafiles in a temporary tablespace. I need to move and rename three different datafiles in the tablespace. I am able to take them offline - no problem. I cna make the changes at the OS level. I am running on Unix. But I can't get the changes to show up in the OEM inorder to bring them back on-line. Do I need to remove all users from this tablespace before making these changes? The tablespace is temporary so does that make a difference? Any suggestions? LeRoy -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: LeRoy Kemnitz INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Mercadante, Thomas F INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: gmei INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: LMT monitoring
Title: RE: LMT monitoring Connor, What on earth you are doing on this list immediately after your Wedding? Which cruise liner has internet access?? I think Disney has ... ps: Thanks for the algorithm, let me implement and see how good my data dictionary holds up. Raj - Rajendra dot Jamadagni at espn dot com Any views expressed here are strictly personal. QOTD: Any clod can have facts, having an opinion is an art !! -Original Message- From: Connor McDonald [mailto:[EMAIL PROTECTED]] Sent: Tuesday, March 11, 2003 9:24 AM To: Multiple recipients of list ORACLE-L Subject: Re: LMT monitoring Not that this helps Raj much, but the algorithm does vary if the initial size of the segment is large, along the lines of: case when initial_extent 1m then case when extents 16 then next = 64k, when extents 80 then next = 1m, when extents 200 then next = 8m, else next = 64m when initial_extent = 1m then case when extents 64 then next = 1m, when extents 184 then next = 8m, else next = 64m ) and a large thank you to all those who passed on best wishes for my wedding. It was a great day enjoyed by all. Cheers Connor --- Daniel W. Fink [EMAIL PROTECTED] wrote: From my testing, I have found the following autoallocate alogrithm. The first 16 extents are 64k in size. The subsequent allocation method is the next 63 extents of 1m, the next 120 extents of 8m and all additional extents at 64m. I have tested this with segments in excess of 100 gigabytes and I did not find a new extent size. The first 3 sizes are documented by Oracle, the last one I found by testing and have verified from other research, though the author/website escapes me at the current time. -- Daniel W. Fink http://www.optimaldba.com IOUG-A Live! April 27 - May 1, 2003 Orlando, FL Sunday, April 27 8:30am - 4:30pm - Problem Solving with Oracle 9i SQL Wednesday, May 1 1:00pm - 2:00pm - Automatic Undo Internals Jamadagni, Rajendra wrote: Rachel, in case of auto allocate, oracle used 4 or 5 (experts don't even agree on if it is 4 or 5) fixed sizes (64k ...) and based on number of existing extents it will choose when an extent of next size should be allocated. The problem is there is no set formula (or I haven't seen one agreed upon by Oracle ... the answer from Oracle is always fuzzy about this). That's why, I don't know if the next extent of my table will be 64K or 1M ... if someone knows a formula, I can write a quick script and things would be easy ... but due to lack of formula, everything is a hypothesis .. In case of dictionary managed, you have next extent size and pct increase and you can predict what the next extent would be. This is also true if you use uniformed extents in LMT. But it isn't easy in LMT and auto allocate. It is probably as predictable as expecting a straight like from a drunken monkey with a crayon. Raj - Rajendra dot Jamadagni at espn dot com Any views expressed here are strictly personal. QOTD: Any clod can have facts, having an opinion is an art !! This e-mail message is confidential, intended only for the named recipient(s) above and may contain information that is privileged, attorney work product or exempt from disclosure under applicable law. If you have received this message in error, or are not the named recipient(s), please immediately notify corporate MIS at (860) 766-2000 and delete this e-mail message from your computer, Thank you.*2 = Connor McDonald web: http://www.oracledba.co.uk web: http://www.oaktable.net email: [EMAIL PROTECTED] GIVE a man a fish and he will eat for a day. But TEACH him how to fish, and...he will sit in a boat and drink beer all day __ Do You Yahoo!? Everything you'll ever need on one web page from News and Sport to Email and Music Charts http://uk.my.yahoo.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: =?iso-8859-1?q?Connor=20McDonald?= INET: [EMAIL PROTECTED] Fat City Network Services -- 858-538-5051 http://www.fatcity.com San Diego, California -- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: Tablespaces - datafiles
LeRoy, I just struggled with this last week. You can't move Temporary Data files. You need to drop and recreate the TEMP tablespace - creating the data files in the correct directories. On a side note - here is an interesting feature. When Oracle creates files for the TEMP tablespace, it does not create the files full sized like it does for normal data files. It creates them smaller for speed purposes (it creates the TEMP tablespace very fast) and will allow the TEMP data files to grow as needed. Now here is the kicker. Let's say you have a disk that is 9 gig is size. You can create 10-1 Gig Temp data files on that disk. Since Oracle does not create the files full-sized, there is nothing to stop this from happening. Sometime later, as the TEMP tablespace gets used, the files grow until eventually the disk fills up, and a sql query crashes with an obscure disk io error. Oracle is trying to expand the TEMP datafiles to the size it's been told they should be. But there is no physical space left on disk. Documentation in 817 does not mention this. But 92 doc's are up to date. nice surprise, eh? Tom Mercadante Oracle Certified Professional -Original Message- Sent: Tuesday, March 11, 2003 8:39 AM To: Multiple recipients of list ORACLE-L All - I am having a problem with the datafiles in a temporary tablespace. I need to move and rename three different datafiles in the tablespace. I am able to take them offline - no problem. I cna make the changes at the OS level. I am running on Unix. But I can't get the changes to show up in the OEM inorder to bring them back on-line. Do I need to remove all users from this tablespace before making these changes? The tablespace is temporary so does that make a difference? Any suggestions? LeRoy -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: LeRoy Kemnitz INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Mercadante, Thomas F INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
perl 5.8 access to oracle
Just installed Perl 5.8. Ran ppm3 to install the DBD package andPPM couldn'tfind it. I looked at activeperl site, it stated dbd for Oracle failed. I have now installed perl 5.6 and can find dbd/dbi files. With Perl 5.8, are you supposed to use something other than dbd/dbi to access an Oracle database? Jeffrey BeckstromDatabase AdministratorGreater Cleveland Regional Transit Authority1240 W. 6th StreetCleveland, Ohio 44113(216) 781-4204
RE: Running multiple instances on a [large] server
We are running 11 instances on WinNT with no problems! At one point, we had 26 instances. they are all development instances, so volumn and load are low. but they all share one Oracle Home. Tom Mercadante Oracle Certified Professional -Original Message- Sent: Tuesday, March 11, 2003 9:19 AM To: Multiple recipients of list ORACLE-L I can't remember the last time that I saw a server (other than WinNT) running a single instance. My experience is that it's quite normal to run multiple instances on a single server. One Oracle home per version of Oracle. I'm not sure what the point of a separate Oracle home per instance would be. On the licensing issue --- that's Oracle's call. We have a site license so it's not an issue for me. Hemant K Chitale hkchitalTo: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] @singnet.com.sg cc: Sent by: rootSubject: Running multiple instances on a [large] server 03/11/2003 03:48 AM Please respond to ORACLE-L One of the teams here is planning to run anything from 8 to 16 database instances [no indication on sizing yet, but gut-feel SGAs are 200MB to 1GB and DB sizes 500MB to 40GB] on a large server, something like a Sun E6800 or an equivalent HP or Fujitsu server. 1. How many of you do run, and are comfortable running, multiple databases on the same server, whether it is partitioned or not ? 2. Do you create a seperate ORACLE_HOME for each instance ? 3. Do you just buy the Oracle DB CPU license on the total number of CPUs on the server ? My positioning is a. We might not be able to create 8 partitions but partition such that we have a max of 2 or 3 instances in one partition. Hopefully, I can dynamically change CPU partitioning to reallocate CPU to another group of instances. b. Each instance should have it's own ORACLE_HOME. [Disk space is not a constraint]. c. Just add up the number of CPUs on the server, across all partitions, and buy a CPU license. Also, a CPU license is much preferable to Named-User as some of these databases would host Portals for 1,000+ end-users. Hemant K Chitale http://hkchital.tripod.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Hemant K Chitale INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Thomas Day INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Mercadante, Thomas F INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Re: LMT monitoring
Not that this helps Raj much, but the algorithm does vary if the initial size of the segment is large, along the lines of: case when initial_extent 1m then case when extents 16 then next = 64k, when extents 80 then next = 1m, when extents 200 then next = 8m, else next = 64m when initial_extent = 1m then case when extents 64 then next = 1m, when extents 184 then next = 8m, else next = 64m ) and a large thank you to all those who passed on best wishes for my wedding. It was a great day enjoyed by all. Cheers Connor --- Daniel W. Fink [EMAIL PROTECTED] wrote: From my testing, I have found the following autoallocate alogrithm. The first 16 extents are 64k in size. The subsequent allocation method is the next 63 extents of 1m, the next 120 extents of 8m and all additional extents at 64m. I have tested this with segments in excess of 100 gigabytes and I did not find a new extent size. The first 3 sizes are documented by Oracle, the last one I found by testing and have verified from other research, though the author/website escapes me at the current time. -- Daniel W. Fink http://www.optimaldba.com IOUG-A Live! April 27 - May 1, 2003 Orlando, FL Sunday, April 27 8:30am - 4:30pm - Problem Solving with Oracle 9i SQL Wednesday, May 1 1:00pm - 2:00pm - Automatic Undo Internals Jamadagni, Rajendra wrote: Rachel, in case of auto allocate, oracle used 4 or 5 (experts don't even agree on if it is 4 or 5) fixed sizes (64k ...) and based on number of existing extents it will choose when an extent of next size should be allocated. The problem is there is no set formula (or I haven't seen one agreed upon by Oracle ... the answer from Oracle is always fuzzy about this). That's why, I don't know if the next extent of my table will be 64K or 1M ... if someone knows a formula, I can write a quick script and things would be easy ... but due to lack of formula, everything is a hypothesis .. In case of dictionary managed, you have next extent size and pct increase and you can predict what the next extent would be. This is also true if you use uniformed extents in LMT. But it isn't easy in LMT and auto allocate. It is probably as predictable as expecting a straight like from a drunken monkey with a crayon. Raj - Rajendra dot Jamadagni at espn dot com Any views expressed here are strictly personal. QOTD: Any clod can have facts, having an opinion is an art !! This e-mail message is confidential, intended only for the named recipient(s) above and may contain information that is privileged, attorney work product or exempt from disclosure under applicable law. If you have received this message in error, or are not the named recipient(s), please immediately notify corporate MIS at (860) 766-2000 and delete this e-mail message from your computer, Thank you.*2 = Connor McDonald web: http://www.oracledba.co.uk web: http://www.oaktable.net email: [EMAIL PROTECTED] GIVE a man a fish and he will eat for a day. But TEACH him how to fish, and...he will sit in a boat and drink beer all day __ Do You Yahoo!? Everything you'll ever need on one web page from News and Sport to Email and Music Charts http://uk.my.yahoo.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: =?iso-8859-1?q?Connor=20McDonald?= INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: Database vs File System
Rathi Here are some considerations: 1. How many documents / how much storage? 2. Do you need to manipulate the documents while they are stored? 3. Consider Oracle ifs http://www.orafaq.org/faqifs.htm 4. What are your performance requirements? Dennis Williams DBA, 40%OCP, 100% DBA Lifetouch, Inc. [EMAIL PROTECTED] -Original Message- Sent: Tuesday, March 11, 2003 3:59 AM To: Multiple recipients of list ORACLE-L We have got a requirement to keep all the documents on repository. We are looking on the possibility of keeping documents on Database or filesysytem. Could you please let me know your views which one I should go for. Regards, Dhanvir This email and any attached to it are confidential and intended only for the individual or entity to which it is addressed. If you are not the intended recipient, please let us know by telephoning or emailing the sender. You should also delete the email and any attachment from your systems and should not copy the email or any attachment or disclose their content to any other person or entity. The views expressed here are not necessarily those of Churchill Insurance Group plc or its affiliates or subsidiaries. Thank you. Churchill Insurance Group plc. Company Registration Number - 2280426. England. Registered Office: Churchill Court, Westmoreland Road, Bromley, Kent BR1 1DP. -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Rathi Dhanvir INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: DENNIS WILLIAMS INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Re:RE: Running multiple instances on a [large] server
We also run several instances on just about every server we have. Most, but not all share the same Oracle_home. I only create new ones for newer versions of Oracle, but for each database I prefer to use seperate mount points such as: /test/system /test/rbs /test/temp /prod1/system /prod1/rbs /prod1/temp /prod2/system etc... Keeps one from stepping on one's own _. (You fill in the blanks.) The biggest problem I think you'll face is physical memory. Have too little with too big an SGA you start swapping. Have smaller SGA's and wait IO can become a problem. Simply put, you just can't have too much memory. Typically we license a server for whatever it's being used for, so yes we do have user and cpu licenses. Kind of a pain keeping track. Dick Goulet Reply Separator Author: Stephane Paquette [EMAIL PROTECTED] Date: 3/11/2003 6:19 AM Hi, On one of the 7 development box (aix 4.3.3) we have 27 instances Oracle 8172. All using the same oracle_home. I can't say it's the fastest response time ;-) As for the licensing we have a mix of CPU and user licences. Stephane Paquette Administrateur de bases de donnees Database Administrator Standard Life www.standardlife.ca Tél. (514) 925-7187 [EMAIL PROTECTED] mailto:[EMAIL PROTECTED] -Original Message- Chitale Sent: Tuesday, March 11, 2003 3:49 AM To: Multiple recipients of list ORACLE-L One of the teams here is planning to run anything from 8 to 16 database instances [no indication on sizing yet, but gut-feel SGAs are 200MB to 1GB and DB sizes 500MB to 40GB] on a large server, something like a Sun E6800 or an equivalent HP or Fujitsu server. 1. How many of you do run, and are comfortable running, multiple databases on the same server, whether it is partitioned or not ? 2. Do you create a seperate ORACLE_HOME for each instance ? 3. Do you just buy the Oracle DB CPU license on the total number of CPUs on the server ? My positioning is a. We might not be able to create 8 partitions but partition such that we have a max of 2 or 3 instances in one partition. Hopefully, I can dynamically change CPU partitioning to reallocate CPU to another group of instances. b. Each instance should have it's own ORACLE_HOME. [Disk space is not a constraint]. c. Just add up the number of CPUs on the server, across all partitions, and buy a CPU license. Also, a CPU license is much preferable to Named-User as some of these databases would host Portals for 1,000+ end-users. Hemant K Chitale http://hkchital.tripod.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Hemant K Chitale INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Stephane Paquette INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Row chaining
Title: RE: Running multiple instances on a [large] server I see some values 0 for chaint_cnt in dba_tables . How do I know if this is chained rows or migrated rows ? Any hits . Thanks, ak
Re[2]: Running multiple instances on a [large] server
Thomas, Now that's an interesting comment. I can't remember ever seeing more than one instance on an NT machine either except when we were playing around boy did that crash in a big hurry (NT 4.0 Oracle 8.0). On the other hand I've set up Linux with 4 instances (same piece of hardware) and had no problems. Got to love the Penguin. Dick Goulet Reply Separator Author: Thomas Day [EMAIL PROTECTED] Date: 3/11/2003 6:19 AM I can't remember the last time that I saw a server (other than WinNT) running a single instance. My experience is that it's quite normal to run multiple instances on a single server. One Oracle home per version of Oracle. I'm not sure what the point of a separate Oracle home per instance would be. On the licensing issue --- that's Oracle's call. We have a site license so it's not an issue for me. Hemant K Chitale hkchitalTo: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] @singnet.com.sg cc: Sent by: rootSubject: Running multiple instances on a [large] server 03/11/2003 03:48 AM Please respond to ORACLE-L One of the teams here is planning to run anything from 8 to 16 database instances [no indication on sizing yet, but gut-feel SGAs are 200MB to 1GB and DB sizes 500MB to 40GB] on a large server, something like a Sun E6800 or an equivalent HP or Fujitsu server. 1. How many of you do run, and are comfortable running, multiple databases on the same server, whether it is partitioned or not ? 2. Do you create a seperate ORACLE_HOME for each instance ? 3. Do you just buy the Oracle DB CPU license on the total number of CPUs on the server ? My positioning is a. We might not be able to create 8 partitions but partition such that we have a max of 2 or 3 instances in one partition. Hopefully, I can dynamically change CPU partitioning to reallocate CPU to another group of instances. b. Each instance should have it's own ORACLE_HOME. [Disk space is not a constraint]. c. Just add up the number of CPUs on the server, across all partitions, and buy a CPU license. Also, a CPU license is much preferable to Named-User as some of these databases would host Portals for 1,000+ end-users. Hemant K Chitale http://hkchital.tripod.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Hemant K Chitale INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Thomas Day INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing
RE: Tablespaces - datafiles
You can do 1. Create a new temporary tablespace (say temp1) 2. Assign all user's TEMPORARY_TABLESPACE to temp1 3. Drop the old temporary tablespace, remove it's OS files. 4. Re-create the temporary tablespace using new OS file names 5. Re-Assign all user's TEMPORARY_TABLESPACE to this new temporary tablespace 6. Drop temp1 and remove it's OS files HTH. Guang -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] Behalf Of LeRoy Kemnitz Sent: Tuesday, March 11, 2003 8:39 AM To: Multiple recipients of list ORACLE-L Subject: Tablespaces - datafiles All - I am having a problem with the datafiles in a temporary tablespace. I need to move and rename three different datafiles in the tablespace. I am able to take them offline - no problem. I cna make the changes at the OS level. I am running on Unix. But I can't get the changes to show up in the OEM inorder to bring them back on-line. Do I need to remove all users from this tablespace before making these changes? The tablespace is temporary so does that make a difference? Any suggestions? LeRoy -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: LeRoy Kemnitz INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: gmei INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Re: Database vs File System
If you have millions of documents it may be better to have them stored in the db. If you have terabytes of documents that are pretty much static/read-only then you may want to have a separate db for the images and one for your data. [EMAIL PROTECTED] 03/11/03 04:58AM We have got a requirement to keep all the documents on repository. We arelooking on the possibility of keeping documents on Database or filesysytem. Could you please let me know your views which one I should go for.Regards,Dhanvir This email and any attached to it are confidential and intended only for theindividual or entity to which it is addressed. If you are not the intended recipient,please let us know by telephoning or emailing the sender. You should also delete the email andany attachment from your systems and should not copy the email or any attachment ordisclose their content to any other person or entity. The views expressed here are not necessarilythose of Churchill Insurance Group plc or its affiliates or subsidiaries. Thank you. Churchill Insurance Group plc. Company Registration Number - 2280426.England. Registered Office: Churchill Court, Westmoreland Road, Bromley, Kent BR11DP. -- Please see the official ORACLE-L FAQ: http://www.orafaq.net-- Author: Rathi Dhanvir INET: [EMAIL PROTECTED]Fat City Network Services -- 858-538-5051 http://www.fatcity.comSan Diego, California -- Mailing list and web hosting services-To REMOVE yourself from this mailing list, send an E-Mail messageto: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and inthe message BODY, include a line containing: UNSUB ORACLE-L(or the name of mailing list you want to be removed from). You mayalso send the HELP command for other information (like subscribing).
RE: Running multiple instances on a [large] server
Hemant What you are considering is certainly feasible. Consider how compatible these applications are, whether they have similar requirements in terms of uptime. Are their performance requirements compatible? One factor to consider is future upgrade paths of the applications. We seem to run into situations where one application needs a new Oracle version, which means a new O.S. version, but another application cannot upgrade at this time. Just be aware you are chaining these applications together by doing this. I have only run that many Oracle instances on test, but would not hesitate to do that on production. I create an Oracle home for each Oracle version. This was discussed recently on this list, although more of the discussion related to having separate Unix userids for each instance, something I do not do. Oracle licensing is based on the number of CPUs, for the CPU licensing option. Dennis Williams DBA, 40%OCP, 100% DBA Lifetouch, Inc. [EMAIL PROTECTED] -Original Message- Sent: Tuesday, March 11, 2003 2:49 AM To: Multiple recipients of list ORACLE-L One of the teams here is planning to run anything from 8 to 16 database instances [no indication on sizing yet, but gut-feel SGAs are 200MB to 1GB and DB sizes 500MB to 40GB] on a large server, something like a Sun E6800 or an equivalent HP or Fujitsu server. 1. How many of you do run, and are comfortable running, multiple databases on the same server, whether it is partitioned or not ? 2. Do you create a seperate ORACLE_HOME for each instance ? 3. Do you just buy the Oracle DB CPU license on the total number of CPUs on the server ? My positioning is a. We might not be able to create 8 partitions but partition such that we have a max of 2 or 3 instances in one partition. Hopefully, I can dynamically change CPU partitioning to reallocate CPU to another group of instances. b. Each instance should have it's own ORACLE_HOME. [Disk space is not a constraint]. c. Just add up the number of CPUs on the server, across all partitions, and buy a CPU license. Also, a CPU license is much preferable to Named-User as some of these databases would host Portals for 1,000+ end-users. Hemant K Chitale http://hkchital.tripod.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Hemant K Chitale INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: DENNIS WILLIAMS INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
POLL: Database to DBA ratio
I'm trying to build a case for management that we need additional DBAs so I'd like to take a quick poll if I may. What is the ratio of Oracle databases to DBAs in your shop? This includes development and production databases. At our shop it's 33:1. -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Chuck Hamilton INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: Hot backups
I tried it both ways and got the same result. According to my notes from the Oracle Backup and Recovery course when you apply your redo logs, the control file gets rolled forward along with the data files. I believe that the only time you would get into trouble here is if you had made structural changes after the control file was backed up. This should not be an issue in my application. Peter Schauss Northrop Grumman Corporation -Original Message- Sent: Monday, March 10, 2003 4:08 PM To: Multiple recipients of list ORACLE-L Peter - Shouldn't you backup the controlfile AFTER completing tablespace backups? Dennis Williams DBA, 40%OCP, 100% DBA Lifetouch, Inc. [EMAIL PROTECTED] -Original Message- Sent: Monday, March 10, 2003 2:37 PM To: Multiple recipients of list ORACLE-L I am implementing hot backups on a small development database using the following approach: 1. From sqlplus: backup controlfile to 'filename'; 2. For each tablespace - sqlplus: alter tablespace name begin backup; - shell: copy files for that tablespace - sqlplus: alter tablespace name end backup; - sqlplus: alter system switch logfile; 3. tar and gzip the tablespace copies and backup control file; As a test, I am restoring the files to another system by doing the following: 1. Copied the init.ora file from the backed up database. 2. Restore the copied tablespaces and control files. 3. Copy the archived redo logs to the directory specified in the init.ora file. 4. Note the number on the most recent redo log. 5. sqlplus internal startup mount recover database until cancel using backup controlfile; (hit return when prompted for the next file until the last redo log noted in step 4 has been processed, then type CANCEL in response to the prompt for the next file.) alter database open reset logs; The above steps work, but I am wondering is there a better way to handle the control files so that I do not have to use the until cancel option. Thanks, Peter Schauss Northrop Grumman Corporation -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Schauss, Peter INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: DENNIS WILLIAMS INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Schauss, Peter INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: Analyzing indexes
Title: RE: Analyzing indexes Chuck, Do you think these indexes are corrupt? Validate structure doesn't give you statistics like Compute Statistics or Estimate Statistics does. Jerry Whittle ASIFICS DBA NCI Information Systems Inc. [EMAIL PROTECTED] 618-622-4145 -Original Message- From: Chuck Hamilton [SMTP:[EMAIL PROTECTED] I need to determine whether or not a couple of indexes need to be rebuilt. The problem is the indexes are quite large and on a 24x7 high volume database. If I try to run an analyze validate structure to gather the data I need to make that decision, it sets a lock on the table for about an hour which I can't afford to do. There is no slow time when I can do this and management has said before they're not going to spring for the partitioning option to break the indexes up into managable pieces. Is there some other way I can get the information needed to determine if an index needs to be rebuilt or not without setting a lock on the table? We are on Oracle 8.1.7.
RE: RE: Running multiple instances on a [large] server
Hemant Dick makes a good point about physical memory. The problem with a lot of instances is that they all have fixed amounts of memory (except in 9i, up to SGA_MAX_SIZE), and if one instance needs more memory you can't reallocate unless you bounce it to add memory and maybe bounce several others to reduce their memory. If you can consolidate instances, then they will be dynamically sharing memory. Probably not the answer management wants to hear, though. Dennis Williams DBA, 40%OCP, 100% DBA Lifetouch, Inc. [EMAIL PROTECTED] -Original Message- Sent: Tuesday, March 11, 2003 8:50 AM To: Multiple recipients of list ORACLE-L We also run several instances on just about every server we have. Most, but not all share the same Oracle_home. I only create new ones for newer versions of Oracle, but for each database I prefer to use seperate mount points such as: /test/system /test/rbs /test/temp /prod1/system /prod1/rbs /prod1/temp /prod2/system etc... Keeps one from stepping on one's own _. (You fill in the blanks.) The biggest problem I think you'll face is physical memory. Have too little with too big an SGA you start swapping. Have smaller SGA's and wait IO can become a problem. Simply put, you just can't have too much memory. Typically we license a server for whatever it's being used for, so yes we do have user and cpu licenses. Kind of a pain keeping track. Dick Goulet Reply Separator Author: Stephane Paquette [EMAIL PROTECTED] Date: 3/11/2003 6:19 AM Hi, On one of the 7 development box (aix 4.3.3) we have 27 instances Oracle 8172. All using the same oracle_home. I can't say it's the fastest response time ;-) As for the licensing we have a mix of CPU and user licences. Stephane Paquette Administrateur de bases de donnees Database Administrator Standard Life www.standardlife.ca Tél. (514) 925-7187 [EMAIL PROTECTED] mailto:[EMAIL PROTECTED] -Original Message- Chitale Sent: Tuesday, March 11, 2003 3:49 AM To: Multiple recipients of list ORACLE-L One of the teams here is planning to run anything from 8 to 16 database instances [no indication on sizing yet, but gut-feel SGAs are 200MB to 1GB and DB sizes 500MB to 40GB] on a large server, something like a Sun E6800 or an equivalent HP or Fujitsu server. 1. How many of you do run, and are comfortable running, multiple databases on the same server, whether it is partitioned or not ? 2. Do you create a seperate ORACLE_HOME for each instance ? 3. Do you just buy the Oracle DB CPU license on the total number of CPUs on the server ? My positioning is a. We might not be able to create 8 partitions but partition such that we have a max of 2 or 3 instances in one partition. Hopefully, I can dynamically change CPU partitioning to reallocate CPU to another group of instances. b. Each instance should have it's own ORACLE_HOME. [Disk space is not a constraint]. c. Just add up the number of CPUs on the server, across all partitions, and buy a CPU license. Also, a CPU license is much preferable to Named-User as some of these databases would host Portals for 1,000+ end-users. Hemant K Chitale http://hkchital.tripod.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Hemant K Chitale INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Stephane Paquette INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message
Re: Recovery problem using BMC Backtrack and tempfile
We've run into this with Backtrack 3.3, which is also supposed to support 8.1.7.x. If I recall correctly, the temp tablespace is there, but offline. We usually have the create scripts for the database in question, so we just drop the tablespace and recreate it. We usually create it as locally managed permanent, then alter it offline and drop it, then recreate it as temporary and reuse the fully allocated files to get around the annoying tempfile issue of 'not really' allocating the space. Stephane Paquette [EMAIL PROTECTED] T dardlife.ca To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent by: cc: [EMAIL PROTECTED] bcc: Subject: Recovery problem using BMC Backtrack and tempfile 03/10/03 03:54 PM Please respond to ORACLE-L Hi, Oracle 8.1.7.2 and BMC Backtrack 3.4.1 The database uses tempfile for the temporary tablespace. All backups are done with Backtrack. I've been asked to copy a db from a server to another one. So I used dtorestore to restore all datafiles to the new server. Backtrack doesn't know about the tempfile, it is not in the dbprofile. I rebuilt the controfile and put in comment the part ALTER TABLESPACE TEMP ADD TEMPFILE ... Once the db opened, I've seen that the temp tablespace was there but not attached to any file... Anybody using Backtrack have seen that ? Version 341 is supposed to handle Oracle 817 . I'm opening a tar at BMC . Stephane Paquette Administrateur de bases de donnees Database Administrator Standard Life www.standardlife.ca Tél. (514) 925-7187 [EMAIL PROTECTED] mailto:[EMAIL PROTECTED] -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Stephane Paquette INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
AFTER database logon trigger keeps sessions open
Hi All,
I have a after database logon trigger on server/instance 1. All it does is
insert a record into instance on server2 via a database link.
This creates a session on server2. So every user who logs on to server1 a
session is created on server2. The session does not
go away. Does anyone know why it does not after the user logs on?
Below is after logon trigger
DECLARE
-- str varchar2(200);
CURSOR temp_rec IS
SELECT user AS user_name,
NVL(UPPER(SUBSTR(osuser,1,30)),'SERVER') AS os_user_name,
RTRIM(NVL(SUBSTR(machine,instr(machine,'\') +1,12),'SERVER'),CHR(0))
AS machine_name,
sid AS session_id,
serial# AS serial_no,
SYSDATE AS logon_time,
SYS_CONTEXT('userenv','ip_address') AS ip_address,
NVL(SUBSTR(program,1,64),'INTERNAL') AS program_name,
i.instance_name AS instance_name,
i.host_name AS host_name,
i.version AS version
FROM v$session s,v$instance i
WHERE s.username = user
AND s.logon_time = (SELECT MAX(x.logon_time)
FROM v$session x
WHERE x.username = user);
BEGIN
FOR rec IN temp_rec LOOP
INSERT INTO [EMAIL PROTECTED]
(user_name,
os_user_name,
machine_name,
session_id,
serial_no,
logon_time,
ip_address,
program_name,
instance_name,
host_name,
version)
VALUES (rec.user_name,
rec.os_user_name,
rec.machine_name,
rec.session_id,
rec.serial_no,
rec.logon_time,
rec.ip_address,
rec.program_name,
rec.instance_name,
rec.host_name,
rec.version);
END LOOP;
EXCEPTION
WHEN OTHERS THEN
NULL;
END;
--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author:
INET: [EMAIL PROTECTED]
Fat City Network Services-- 858-538-5051 http://www.fatcity.com
San Diego, California-- Mailing list and web hosting services
-
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from). You may
also send the HELP command for other information (like subscribing).
Re: Re[2]: Running multiple instances on a [large] server
At RMOUG last week, I heard that someone (Chip Briggs I think) had 5 (count 'em 5) isntances of Oracle running on his PC. I don't think it was Linux and it definitely wasn't production anything Rachel --- [EMAIL PROTECTED] wrote: Thomas, Now that's an interesting comment. I can't remember ever seeing more than one instance on an NT machine either except when we were playing around boy did that crash in a big hurry (NT 4.0 Oracle 8.0). On the other hand I've set up Linux with 4 instances (same piece of hardware) and had no problems. Got to love the Penguin. Dick Goulet Reply Separator Author: Thomas Day [EMAIL PROTECTED] Date: 3/11/2003 6:19 AM I can't remember the last time that I saw a server (other than WinNT) running a single instance. My experience is that it's quite normal to run multiple instances on a single server. One Oracle home per version of Oracle. I'm not sure what the point of a separate Oracle home per instance would be. On the licensing issue --- that's Oracle's call. We have a site license so it's not an issue for me. Hemant K Chitale hkchitalTo: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] @singnet.com.sg cc: Sent by: rootSubject: Running multiple instances on a [large] server 03/11/2003 03:48 AM Please respond to ORACLE-L One of the teams here is planning to run anything from 8 to 16 database instances [no indication on sizing yet, but gut-feel SGAs are 200MB to 1GB and DB sizes 500MB to 40GB] on a large server, something like a Sun E6800 or an equivalent HP or Fujitsu server. 1. How many of you do run, and are comfortable running, multiple databases on the same server, whether it is partitioned or not ? 2. Do you create a seperate ORACLE_HOME for each instance ? 3. Do you just buy the Oracle DB CPU license on the total number of CPUs on the server ? My positioning is a. We might not be able to create 8 partitions but partition such that we have a max of 2 or 3 instances in one partition. Hopefully, I can dynamically change CPU partitioning to reallocate CPU to another group of instances. b. Each instance should have it's own ORACLE_HOME. [Disk space is not a constraint]. c. Just add up the number of CPUs on the server, across all partitions, and buy a CPU license. Also, a CPU license is much preferable to Named-User as some of these databases would host Portals for 1,000+ end-users. Hemant K Chitale http://hkchital.tripod.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Hemant K Chitale INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Thomas Day INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051
RE: Row chaining
ak Smart-alek answer: Apply one of the methods to eliminate migrated rows, and if the problem doesn't go away, you know you have some chained rows ;-) Chained rows are a little difficult to diagnose. Look at the value for avg_row_len - is it near the db_block_size? I haven't tried this, but if you really want to go to the trouble, you could create a table named CHAINED_ROWS, run ANALYZE . . . LIST CHAINED ROWS. The create a SQL statement that will execute the VSIZE function on each column and sum the values. Then run this statement on each rowid in CHAINED_ROWS. Now you see the reason for my initial suggestion. I would suggest that you not get too paranoid about getting CHAINED_ROWS to zero. But if your wait statistics starts to show table fetch continued row as significant, you definitely need to fix the problem. Dennis Williams DBA, 40%OCP, 100% DBA Lifetouch, Inc. [EMAIL PROTECTED] -Original Message- Sent: Tuesday, March 11, 2003 9:14 AM To: Multiple recipients of list ORACLE-L I see some values 0 for chaint_cnt in dba_tables . How do I know if this is chained rows or migrated rows ? Any hits . Thanks, ak -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: DENNIS WILLIAMS INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Re: POLL: Database to DBA ratio
Chuck, At this location we have a total of 5 databases and 1 DBA. Time off without an electronic teather is a thing of the past. My last location was 4 production databases with 1 DBA and a backup DBA from the development arena of 2 development databases and 1 DBA. Ron [EMAIL PROTECTED] 03/11/03 09:59AM I'm trying to build a case for management that we need additional DBAs so I'd like to take a quick poll if I may. What is the ratio of Oracle databases to DBAs in your shop? This includes development and production databases. At our shop it's 33:1. -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Chuck Hamilton INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Ron Rogers INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Re: Row chaining
AK, hi, row chaining is because the big row(row length is bigger than the block size), while row migration is because of small pctfree and updated rows. So, just look at the length of the rows. If chained rows, no way(unless you use larger block size and the row does not contains really long column like long/long row data type), if migrated rows, you can move the table to repair the migrated rows. Good luck Regards zhu chao msn:[EMAIL PROTECTED] www.cnoug.org(China Oracle User Group) === 2003-03-11 07:14:00 ,you wrote£º=== RE: Running multiple instances on a [large] serverI see some values 0 for chaint_cnt in dba_tables . How do I know if this is chained rows or migrated rows ? Any hits . Thanks, ak = = = = = = = = = = = = = = = = = = = = -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: chao_ping INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: Database vs File System
Classic case of doing a proper requirements analysis. It will cover issues already raised, but will place those suggestions into the correct context of your business objectives. There is no cook-book answer - the answer will be unique to your installation and your environment. If it happens to be the same as someone else's environment - put it down to co-incidence (unless you have validated their requiremnts analysis against your own!) peter edinburgh -Original Message- Sent: 11 March 2003 09:59 To: Multiple recipients of list ORACLE-L We have got a requirement to keep all the documents on repository. We are looking on the possibility of keeping documents on Database or filesysytem. Could you please let me know your views which one I should go for. Regards, Dhanvir This email and any attached to it are confidential and intended only for the individual or entity to which it is addressed. If you are not the intended recipient, please let us know by telephoning or emailing the sender. You should also delete the email and any attachment from your systems and should not copy the email or any attachment or disclose their content to any other person or entity. The views expressed here are not necessarily those of Churchill Insurance Group plc or its affiliates or subsidiaries. Thank you. Churchill Insurance Group plc. Company Registration Number - 2280426. England. Registered Office: Churchill Court, Westmoreland Road, Bromley, Kent BR1 1DP. -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Rathi Dhanvir INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). * This e-mail message, and any files transmitted with it, are confidential and intended solely for the use of the addressee. If this message was not addressed to you, you have received it in error and any copying, distribution or other use of any part of it is strictly prohibited. Any views or opinions presented are solely those of the sender and do not necessarily represent those of the British Geological Survey. The security of e-mail communication cannot be guaranteed and the BGS accepts no liability for claims arising as a result of the use of this medium to transmit messages from or to the BGS. The BGS cannot accept any responsibility for viruses, so please scan all attachments.http://www.bgs.ac.uk * -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Robson, Peter INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: POLL: Database to DBA ratio
3:1 Production, Development, my personal test DB : Me Craig Healey -Original Message- From: Chuck Hamilton [mailto:[EMAIL PROTECTED] Sent: 11 March 2003 14:59 To: Multiple recipients of list ORACLE-L Subject: POLL: Database to DBA ratio I'm trying to build a case for management that we need additional DBAs so I'd like to take a quick poll if I may. What is the ratio of Oracle databases to DBAs in your shop? This includes development and production databases. At our shop it's 33:1. ** This email and any files transmitted with it are confidential and intended solely for the use of the individual or entity to whom they are addressed and may contain confidential and/or privileged material. Any review, retransmission, dissemination or other use of, or taking of any action in reliance upon, this information by persons or entities other than the intended recipient is prohibited. Statements and opinions expressed in this e-mail may not represent those of the company. If you have received this email in error please notify [EMAIL PROTECTED] This footnote also confirms that this email message has been swept by MIMEsweeper for the presence of computer viruses (www.mimesweeper.com) *** -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Craig Healey INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: LMT monitoring
Title: RE: LMT monitoring Here is my interpretation of algorithm suggested by Conner, (I'll get to others too) /* CASE WHEN initial_extent 1m THEN CASE WHEN EXTENTS 16 THEN NEXT = 64k, WHEN EXTENTS 80 THEN NEXT = 1m, WHEN EXTENTS 200 THEN NEXT = 8m, ELSE NEXT = 64m WHEN initial_extent = 1m THEN CASE WHEN EXTENTS 64 THEN NEXT = 1m, WHEN EXTENTS 184 THEN NEXT = 8m, ELSE NEXT = 64m ) */ WITH main_qry AS ( SELECT a.owner owner , a.segment_name seg, b.init init, a.extent_id ext#, TO_CHAR(a.bytes) bytes FROM DBa_EXTENTS a, (SELECT owner, segment_name, bytes init FROM DBa_EXTENTS WHERE extent_id = 0) b WHERE b.owner = a.owner AND b.segment_name = a.segment_name ORDER BY a.owner, a.segment_name, a.extent_id) SELECT owner, seg, ext#, CASE WHEN init (1*1024*1024) THEN CASE WHEN ext# = 0 THEN bytes ELSE CASE WHEN (ext# BETWEEN 1 AND 15) AND (bytes = (64*1024)) THEN bytes ELSE CASE WHEN (ext# BETWEEN 16 AND 80) AND (bytes = (1024*1024)) THEN bytes ELSE CASE WHEN (ext# BETWEEN 81 AND 200) AND (bytes = (8*1024*1024)) THEN bytes ELSE CASE WHEN (ext# 200) AND (bytes = (64*1024*1024)) THEN bytes ELSE bytes || '#' END END END END END ELSE CASE WHEN ext# = 0 THEN bytes ELSE CASE WHEN (ext# BETWEEN 1 AND 64) AND (bytes = (1024*1024)) THEN bytes ELSE CASE WHEN (ext# BETWEEN 65 AND 184) AND (bytes = (8*1024*1024)) THEN bytes ELSE CASE WHEN (ext# 184) AND (bytes = (64*1024*1024)) THEN bytes ELSE bytes || '#' END END END END END verify FROM main_qry ORDER BY owner, seg, ext# / I know it is not optimal, but it gives me what I need. But here it the thing, even this is not right ... SELECT owner, segment_name, bytes, stragg(extent_id) FROM DB$EXTENTS WHERE owner = 'TCS' AND segment_name LIKE 'ACTUAL_IMP%' GROUP BY owner, segment_name, bytes ORDER BY owner,segment_name, bytes / OWNER SEGMENT_NAME BYTES STRAGG(EXTENT_ID) - - -- -- TCS ACTUAL_IMPRESSIONS 1048576 1,2,4,3,5,7,9,11,13,22,21,20,19,18,17,16,15,14,39,38,37,36,35,34,33,32,31,49,48,47,46,45,44,43,42,41,40,30,29,28,27,26,25,24,23,12,10,8,6 TCS ACTUAL_IMPRESSIONS 8388608 0,55,57,74,73,72,71,70,69,68,67,66,82,81,80,79,78,77,76,75,65,64,63,62,61,60,59,58,56,54,50,51,53,52 Last columns is just a comma delimited list of extent numbers (works like sum() for varchar2 strings, I can't get them sorted !!) Raj - Rajendra dot Jamadagni at espn dot com Any views expressed here are strictly personal. QOTD: Any clod can have facts, having an opinion is an art !! -Original Message- From: Rachel Carmichael [mailto:[EMAIL PROTECTED]] Sent: Tuesday, March 11, 2003 11:14 AM To: Jamadagni, Rajendra Subject: RE: LMT monitoring Raj, I know for a fact that Carnival does... as I was online during my cruise in February! Rachel --- Jamadagni, Rajendra [EMAIL PROTECTED] wrote: Connor, What on earth you are doing on this list immediately after your Wedding? Which cruise liner has internet access?? I think Disney has ... ps: Thanks for the algorithm, let me implement and see how good my data dictionary holds up. Raj - Rajendra dot Jamadagni at espn dot com Any views expressed here are strictly personal. QOTD: Any clod can have facts, having an opinion is an art !! -Original Message- Sent: Tuesday, March 11, 2003 9:24 AM To: Multiple recipients of list ORACLE-L Not that this helps Raj much, but the algorithm does vary if the initial size of the segment is large, along the lines of: case when initial_extent 1m then case when extents 16 then next = 64k, when extents 80 then next = 1m, when extents 200 then next = 8m, else next = 64m when initial_extent = 1m then case when extents 64 then next = 1m, when extents 184 then next = 8m, else next = 64m ) and a large thank you to all those who passed on best wishes for my wedding. It was a great day enjoyed by all. Cheers Connor --- Daniel W. Fink [EMAIL PROTECTED] wrote: From my testing, I have found the following autoallocate alogrithm. The first 16 extents are 64k in size. The subsequent allocation method is the next 63 extents of 1m, the next 120 extents of 8m and all additional extents at 64m. I have tested this with segments in excess of 100 gigabytes and I did not find a new extent size. The first 3 sizes are documented by Oracle, the last one I found by testing and have verified from other research, though the author/website escapes me at the current time. -- Daniel W. Fink http://www.optimaldba.com IOUG-A Live! April 27 - May 1, 2003 Orlando, FL Sunday, April 27 8:30am - 4:30pm - Problem Solving with Oracle 9i SQL Wednesday, May 1 1:00pm - 2:00pm - Automatic Undo Internals Jamadagni, Rajendra wrote: Rachel, in case of auto allocate, oracle used 4 or 5
RE: AFTER database logon trigger keeps sessions open
You can use: execute immediate 'alter session close database link
utilities_itport02_dblink';
After you're done with the insertion
Regards,
Waleed
-Original Message-
Sent: Tuesday, March 11, 2003 10:54 AM
To: Multiple recipients of list ORACLE-L
Hi All,
I have a after database logon trigger on server/instance 1. All it does is
insert a record into instance on server2 via a database link.
This creates a session on server2. So every user who logs on to server1 a
session is created on server2. The session does not
go away. Does anyone know why it does not after the user logs on?
Below is after logon trigger
DECLARE
-- str varchar2(200);
CURSOR temp_rec IS
SELECT user AS user_name,
NVL(UPPER(SUBSTR(osuser,1,30)),'SERVER') AS os_user_name,
RTRIM(NVL(SUBSTR(machine,instr(machine,'\') +1,12),'SERVER'),CHR(0))
AS machine_name,
sid AS session_id,
serial# AS serial_no,
SYSDATE AS logon_time,
SYS_CONTEXT('userenv','ip_address') AS ip_address,
NVL(SUBSTR(program,1,64),'INTERNAL') AS program_name,
i.instance_name AS instance_name,
i.host_name AS host_name,
i.version AS version
FROM v$session s,v$instance i
WHERE s.username = user
AND s.logon_time = (SELECT MAX(x.logon_time)
FROM v$session x
WHERE x.username = user);
BEGIN
FOR rec IN temp_rec LOOP
INSERT INTO [EMAIL PROTECTED]
(user_name,
os_user_name,
machine_name,
session_id,
serial_no,
logon_time,
ip_address,
program_name,
instance_name,
host_name,
version)
VALUES (rec.user_name,
rec.os_user_name,
rec.machine_name,
rec.session_id,
rec.serial_no,
rec.logon_time,
rec.ip_address,
rec.program_name,
rec.instance_name,
rec.host_name,
rec.version);
END LOOP;
EXCEPTION
WHEN OTHERS THEN
NULL;
END;
--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author:
INET: [EMAIL PROTECTED]
Fat City Network Services-- 858-538-5051 http://www.fatcity.com
San Diego, California-- Mailing list and web hosting services
-
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from). You may
also send the HELP command for other information (like subscribing).
--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author: Khedr, Waleed
INET: [EMAIL PROTECTED]
Fat City Network Services-- 858-538-5051 http://www.fatcity.com
San Diego, California-- Mailing list and web hosting services
-
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from). You may
also send the HELP command for other information (like subscribing).
RE: need help with dynamic sql
Title: RE: need help with dynamic sql
if you at-least know the source and destination tables will have same number of columns and data types ... you could just do something like
insert into target.table
select from source.table
Raj
-
Rajendra dot Jamadagni at espn dot com
Any views expressed here are strictly personal.
QOTD: Any clod can have facts, having an opinion is an art !!
-Original Message-
From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]]
Sent: Tuesday, March 11, 2003 10:24 AM
To: Multiple recipients of list ORACLE-L
Subject: need help with dynamic sql
g_colName is a string of column names that I build earlier in the algorithm. My problem is how do I do the 'INTO' part of this cursor, since I do not know how many or of what type my columns are. The query is on the fly and totally dynamic.
Anyway to do this without using DBMS_SQL package?
OPEN cur_values FOR 'SELECT '||g_colName ||
' FROM ' || p_sourceSchema||'.'||v_tableName ||
' WHERE PARENT_RESOURCE_ID BETWEEM :1 AND :2'
USING p_startResourceID, p_endResourceID;
LOOP
FETCH cur_values
INTO v_values;
EXIT WHEN cur_values%NOTFOUND;
EXECUTE IMMEDIATE 'INSERT INTO '||p_targetSchema||'.'||v_tableName||
'('||g_colName||')'||
'(VALUES)';
--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author: [EMAIL PROTECTED]
INET: [EMAIL PROTECTED]
Fat City Network Services -- 858-538-5051 http://www.fatcity.com
San Diego, California -- Mailing list and web hosting services
-
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from). You may
also send the HELP command for other information (like subscribing).
*This e-mail
message is confidential, intended only for the named recipient(s) above and may
contain information that is privileged, attorney work product or exempt from
disclosure under applicable law. If you have received this message in error, or are
not the named recipient(s), please immediately notify corporate MIS at (860) 766-2000
and delete this e-mail message from your computer, Thank
you.*1
RE: need help with dynamic sql
Not with execute immediate. Use dbms_sql
I have done this.
For example and help
http://asktom.oracle.com/pls/ask/f?p=4950:8:104566430235951855::NO::F495
0_P8_DISPLAYID,F4950_P8_CRITERIA:584023239495,
Regards,
Kamaljeet Singh (NCDB ASG)
MBT, 52 Barrack Square, Martlesham. IP5 3RF.
Off. 01473 667170
Mob. 077 5368 5370
[EMAIL PROTECTED]
-Original Message-
[EMAIL PROTECTED]
Sent: 11 March 2003 16:29
To: Multiple recipients of list ORACLE-L
g_colName is a string of column names that I build earlier in the
algorithm. My problem is how do I do the 'INTO' part of this cursor,
since I do not know how many or of what type my columns are. The query
is on the fly and totally dynamic.
Anyway to do this without using DBMS_SQL package?
OPEN cur_values FOR 'SELECT '||g_colName ||
' FROM ' || p_sourceSchema||'.'||v_tableName
||
' WHERE PARENT_RESOURCE_ID BETWEEM :1 AND
:2'
USING p_startResourceID, p_endResourceID;
LOOP
FETCH cur_values
INTO v_values;
EXIT WHEN cur_values%NOTFOUND;
EXECUTE IMMEDIATE 'INSERT INTO
'||p_targetSchema||'.'||v_tableName||
'('||g_colName||')'||
'(VALUES)';
--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author: [EMAIL PROTECTED]
INET: [EMAIL PROTECTED]
Fat City Network Services-- 858-538-5051 http://www.fatcity.com
San Diego, California-- Mailing list and web hosting services
-
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from). You may
also send the HELP command for other information (like subscribing).
*
Disclaimer
This message (including any attachments) contains
confidential information intended for a specific
individual and purpose, and is protected by law.
If you are not the intended recipient, you should
delete this message and are hereby notified that
any disclosure, copying, or distribution of this
message, or the taking of any action based on it,
is strictly prohibited.
*
Visit us at http://www.mahindrabt.com
--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author: Kamaljeet Singh
INET: [EMAIL PROTECTED]
Fat City Network Services-- 858-538-5051 http://www.fatcity.com
San Diego, California-- Mailing list and web hosting services
-
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from). You may
also send the HELP command for other information (like subscribing).
Re: Re[2]: Running multiple instances on a [large] server
I do have multiple databases on a Win2K server. No problems except that MicroSoft just loves to write the SGA out to the swap file (about 630M of SGA, 1.3G of RAM and disk i/o out the wazzoo). I don't know if there was some specific reason that we never ran more than one instance on WinNT 4.0 but we never did. On WinNT it was one instance, one server. dgoulet @vicr.comTo: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent by: rootcc: Subject: Re[2]: Running multiple instances on a [large] server 03/11/2003 09:54 AM Please respond to ORACLE-L Thomas, Now that's an interesting comment. I can't remember ever seeing more than one instance on an NT machine either except when we were playing around boy did that crash in a big hurry (NT 4.0 Oracle 8.0). On the other hand I've set up Linux with 4 instances (same piece of hardware) and had no problems. Got to love the Penguin. Dick Goulet Reply Separator Author: Thomas Day [EMAIL PROTECTED] Date: 3/11/2003 6:19 AM I can't remember the last time that I saw a server (other than WinNT) running a single instance. My experience is that it's quite normal to run multiple instances on a single server. One Oracle home per version of Oracle. I'm not sure what the point of a separate Oracle home per instance would be. On the licensing issue --- that's Oracle's call. We have a site license so it's not an issue for me. Hemant K Chitale hkchitalTo: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] @singnet.com.sg cc: Sent by: rootSubject: Running multiple instances on a [large] server 03/11/2003 03:48 AM Please respond to ORACLE-L One of the teams here is planning to run anything from 8 to 16 database instances [no indication on sizing yet, but gut-feel SGAs are 200MB to 1GB and DB sizes 500MB to 40GB] on a large server, something like a Sun E6800 or an equivalent HP or Fujitsu server. 1. How many of you do run, and are comfortable running, multiple databases on the same server, whether it is partitioned or not ? 2. Do you create a seperate ORACLE_HOME for each instance ? 3. Do you just buy the Oracle DB CPU license on the total number of CPUs on the server ? My positioning is a. We might not be able to create 8 partitions but partition such that we have a max of 2 or 3 instances in one partition. Hopefully, I can dynamically change CPU partitioning to reallocate CPU to another group of instances. b. Each instance should have it's own ORACLE_HOME. [Disk space is not a constraint]. c. Just add up the number of CPUs on the server, across all partitions, and buy a CPU license. Also, a CPU license is much preferable to Named-User as some of these databases would host Portals for 1,000+ end-users. Hemant K Chitale http://hkchital.tripod.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Hemant K Chitale INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing:
RE: POLL: Database to DBA ratio
33 DBA's to one database? That's pretty danged good! :) Seriously, this is a question that comes up every so often within Oracle as well, as clients ask Oracle employees what should be expected. There's no simple answer to it. As you might expect, the answer is it depends. :) Depends on (a non-exhaustive list): 1. Requirement for 24x7 DBA coverage 2. Complexity of database 3. Type of applications using the database 4. Database type (OLTP, DW etc.) 5. Number of tiers (two tier, three tier etc.) 6. Amount of input required into development of apps (tuning support and so on) And lots more as well. Pete Controlling developers is like herding cats. Kevin Loney, Oracle DBA Handbook Oh no, it's not. It's much harder than that! Bruce Pihlamae, long-term Oracle DBA -Original Message- Hamilton Sent: Tuesday, March 11, 2003 6:59 AM To: Multiple recipients of list ORACLE-L I'm trying to build a case for management that we need additional DBAs so I'd like to take a quick poll if I may. What is the ratio of Oracle databases to DBAs in your shop? This includes development and production databases. At our shop it's 33:1. -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Chuck Hamilton INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Pete Sharman INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Re: POLL: Database to DBA ratio
We have 10 development databases that I support directly plus 5 implementation/testing databases that I support as second level and one production snapshot datamart. 16:1 However, there are also DBAs who support each project. That would make the ratio closer to 4:1 or 3:1. Chuck Hamilton chuckh To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] @softhome.net cc: Sent by: rootSubject: POLL: Database to DBA ratio 03/11/2003 09:59 AM Please respond to ORACLE-L I'm trying to build a case for management that we need additional DBAs so I'd like to take a quick poll if I may. What is the ratio of Oracle databases to DBAs in your shop? This includes development and production databases. At our shop it's 33:1. -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Chuck Hamilton INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Thomas Day INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Slow running jobs
Hi, We're having slow response on some batch jobs in our production system. A straight SQL trace shows intermittant slow response times on selects against a variety of tables. What is odd is: the statistics are current for the tables, frequently the results of a compute. The same cursor with different bind variables will randomly vary from 200 microseconds to 3.6 seconds, randomly. This occurs on multiple tables in multiple tablespaces. What is this normally indicative of? The other thing I did was run a 10046 level 8 trace for about 20 seconds. I saw 22 log file sync's in that period in the trace file. Any thoughts welcome. Russ
Re: Quick question on cursor resource use
I wouldn't hesitate to set it to 500 or a even 1000 if your appliation really needs this many. But if you think about 10's of thousands, I would start really looking af the application to understand if it were written correctly. The major memory is not allocated by setting the parameter, but it will be allocated when you actually start opening and using cursors. (I have also seen people increasing it only because the application was buggy and didn't close cursors after use. This is not a good idea) /Bjrn. Grant Allen wrote: -Original Message- WILLIAMS Sent: Monday, March 10, 2003 15:45 To: Multiple recipients of list ORACLE-L Fuzzy What is prompting you to increase OPEN_CURSORS? Is your application currently receiving an error from running out of cursors? Yes, it's returning ORA-01000 - having looked at the SQL, it's perfectly understandable why - a whole bunch of concurrently submitted SQL, with lots of recursive SQL spawned by Oracle to support it. So I'm not complaining about getting the error. I just want some ideas about the resource hit if I up this to 500, 1000, 50? The Oracle architecture stuff was silent on the subject. Ciao Fuzzy :-) -- Bjrn Engsig, Miracle A/S Member of Oak Table Network [EMAIL PROTECTED] - http://MiracleAS.dk
RE: Rollback Segments on 8.1.7.4
I will have to check your question on 'fewer sorts'. As far as changes to the SQL statements, there were 1 or 2 sql statements changed in the application because they no longer worked efficiently with the change to 8.1.7. But, on the whole (lets say 98%+ of the time) no changes were made. -Original Message- Sent: Monday, March 10, 2003 5:39 PM To: Multiple recipients of list ORACLE-L Did any of the rollback segment storage parameters (i.e. INITIAL, NEXT, PCTINCREASE, MINEXTENTS, MAXEXTENTS, OPTIMAL) or the number of rollback segments change between 8.0.5 and 8.1.7? Changes to any of these variables could impact the space available for storing inactive undo blocks for read-consistency purposes. If the answer is of course not, then here comes the harder question: did the SQL statements change between 8.0.5 and 8.1.7? Not just outright different SQL statements, but even the same SQL statements running with different execution plans? For example, changing explain plans so that SORT-MERGE join is no longer use could have significant impact on ORA-01555, as forcing sorts are one way to minimize SNAPSHOT TOO OLD. Having queries run faster is another way to minimize ORA-01555, so getting rid of SORT-MERGE joins will likely help, but if the performance improvements aren't good enough, then the conversion to HASH or NESTED LOOP joins may allow more ORA-01555 to occur. Have you noticed fewer sorts occurring since 8.0.5? Just some ideas... - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Monday, March 10, 2003 2:59 PM Evening; Has anyone noticed any difference on the way Oracle uses Rollback Segments on 8.1.7.4 versus 8.0.5 ?? Ever since upgrading to 8.1.7.4 from 8.0.5 on our Solaris servers we have noticed more SNAPSHOT TOO OLD errors than we used to. Are there any suggestions as to rollback tuning that has to be done with that kind of version move ?? Any help would be appreciated. Thanks Kevin -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Kevin Lange INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Tim Gorman INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Kevin Lange INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Disable / enable constraints
I need to truncate and import data into several schemas. The tables have lots of constraints. I can produce a script to disable and enable the contriants but I would like to know more about the constraint_type field in dba_constraints and what are all the SYS_ contraints? Should I disable all constraints for a schema before the import or only certain types? Thanks! Ron If you are not the intended recipient of this e-mail message, any use, distribution or copying of the message is prohibited. Please let me know immediately by return e-mail if you have received this message by mistake, then delete the e-mail message. Thank you. -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Smith, Ron L. INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
can't create database
Title: RE: LMT monitoring can't create a database with oracle 8.1.7.the installation went fine, but when i start dbassist on the 2% of thedatabase creation it tels me: "not connected to oracle". any ideas?
RE: Running multiple instances on a [large] server
Title: RE: Running multiple instances on a [large] server We run upto 22 instances on large SUN boxes... Each environment has their own oracle home, own file system, and in some cases-their own UNIX account for the environment.. One loses a little space for the multiple oracle homes, and it does add some overhead for the operations.. But when we need to migrate a database to a new server;we can shutdown the DB-umount,mount on the new system, and crank it up. We have been in a situation where the machine resources choked, and we were able to move a couple of databases with less than a 3 minute hit for an outage. Would have been less if the operations guys(SA's) had things tee'd up, and ready to hit it with the Driver too. greg -Original Message- From: Hemant K Chitale [mailto:[EMAIL PROTECTED]] Sent: Tuesday, March 11, 2003 3:49 AM To: Multiple recipients of list ORACLE-L Subject: Running multiple instances on a [large] server One of the teams here is planning to run anything from 8 to 16 database instances [no indication on sizing yet, but gut-feel SGAs are 200MB to 1GB and DB sizes 500MB to 40GB] on a large server, something like a Sun E6800 or an equivalent HP or Fujitsu server. 1. How many of you do run, and are comfortable running, multiple databases on the same server, whether it is partitioned or not ? 2. Do you create a seperate ORACLE_HOME for each instance ? 3. Do you just buy the Oracle DB CPU license on the total number of CPUs on the server ? My positioning is a. We might not be able to create 8 partitions but partition such that we have a max of 2 or 3 instances in one partition. Hopefully, I can dynamically change CPU partitioning to reallocate CPU to another group of instances. b. Each instance should have it's own ORACLE_HOME. [Disk space is not a constraint]. c. Just add up the number of CPUs on the server, across all partitions, and buy a CPU license. Also, a CPU license is much preferable to Named-User as some of these databases would host Portals for 1,000+ end-users. Hemant K Chitale http://hkchital.tripod.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Hemant K Chitale INET: [EMAIL PROTECTED] Fat City Network Services -- 858-538-5051 http://www.fatcity.com San Diego, California -- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: POLL: Database to DBA ratio
27 databases to 1 dba, mixed dev, test, and prod. [EMAIL PROTECTED] 03/11/03 11:29AM 3:1 Production, Development, my personal test DB : MeCraig Healey -Original Message- href="">mailto:[EMAIL PROTECTED] Sent: 11 March 2003 14:59 To: Multiple recipients of list ORACLE-L build a case for management that we need additional DBAs so I'd like to take a quick poll if I may. What is the ratio of Oracle databases to DBAs in your shop? This includes development and production databases. At our shop it's 33:1. **This email and any files transmitted with it are confidential and intended solelyfor the use of the individual or entity to whom they are addressed and may containconfidential and/or privileged material. Any review, retransmission, disseminationor other use of, or taking of any action in reliance upon, this information by persons or entities other than the intended recipient is prohibited. Statementsand opinions expressed in this e-mail may not represent those of the company. If you have received this email in error please notify [EMAIL PROTECTED] This footnote also confirms that this email message has been swept by MIMEsweeperfor the presence of computer viruses (www.mimesweeper.com)***-- Please see the official ORACLE-L FAQ: http://www.orafaq.net-- Author: Craig Healey INET: [EMAIL PROTECTED]Fat City Network Services -- 858-538-5051 http://www.fatcity.comSan Diego, California -- Mailing list and web hosting services-To REMOVE yourself from this mailing list, send an E-Mail messageto: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and inthe message BODY, include a line containing: UNSUB ORACLE-L(or the name of mailing list you want to be removed from). You mayalso send the HELP command for other information (like subscribing).
RE: AFTER database logon trigger keeps sessions open
Title: RE: AFTER database logon trigger keeps sessions open
Try putting a
dbms_session.close_database_link('utilities_itport02_dblink');
after the insert. also aren't we missing a commit?? I'd also make this a autonomous transaction ...
Raj
-
Rajendra dot Jamadagni at espn dot com
Any views expressed here are strictly personal.
QOTD: Any clod can have facts, having an opinion is an art !!
-Original Message-
From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]]
Sent: Tuesday, March 11, 2003 10:54 AM
To: Multiple recipients of list ORACLE-L
Subject: AFTER database logon trigger keeps sessions open
Hi All,
I have a after database logon trigger on server/instance 1. All it does is
insert a record into instance on server2 via a database link.
This creates a session on server2. So every user who logs on to server1 a
session is created on server2. The session does not
go away. Does anyone know why it does not after the user logs on?
Below is after logon trigger
DECLARE
-- str varchar2(200);
CURSOR temp_rec IS
SELECT user AS user_name,
NVL(UPPER(SUBSTR(osuser,1,30)),'SERVER') AS os_user_name,
RTRIM(NVL(SUBSTR(machine,instr(machine,'\') +1,12),'SERVER'),CHR(0))
AS machine_name,
sid AS session_id,
serial# AS serial_no,
SYSDATE AS logon_time,
SYS_CONTEXT('userenv','ip_address') AS ip_address,
NVL(SUBSTR(program,1,64),'INTERNAL') AS program_name,
i.instance_name AS instance_name,
i.host_name AS host_name,
i.version AS version
FROM v$session s,v$instance i
WHERE s.username = user
AND s.logon_time = (SELECT MAX(x.logon_time)
FROM v$session x
WHERE x.username = user);
BEGIN
FOR rec IN temp_rec LOOP
INSERT INTO [EMAIL PROTECTED]
(user_name,
os_user_name,
machine_name,
session_id,
serial_no,
logon_time,
ip_address,
program_name,
instance_name,
host_name,
version)
VALUES (rec.user_name,
rec.os_user_name,
rec.machine_name,
rec.session_id,
rec.serial_no,
rec.logon_time,
rec.ip_address,
rec.program_name,
rec.instance_name,
rec.host_name,
rec.version);
END LOOP;
EXCEPTION
WHEN OTHERS THEN
NULL;
END;
--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author:
INET: [EMAIL PROTECTED]
Fat City Network Services -- 858-538-5051 http://www.fatcity.com
San Diego, California -- Mailing list and web hosting services
-
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from). You may
also send the HELP command for other information (like subscribing).
This e-mail
message is confidential, intended only for the named recipient(s) above and may
contain information that is privileged, attorney work product or exempt from
disclosure under applicable law. If you have received this message in error, or are
not the named recipient(s), please immediately notify corporate MIS at (860) 766-2000
and delete this e-mail message from your computer, Thank
you.*2
RE: Rollback Segments on 8.1.7.4
Dan; Everything remained the same on the DB except the version. No changes made. -Original Message- Sent: Monday, March 10, 2003 5:34 PM To: Multiple recipients of list ORACLE-L Kevin, The ORA-01555 errors are almost certainly symptoms, not the actual problem. It has been a few years since I tested an 8.0.x db, but I do not recall seeing any changes in rollback segments from 7.3 to 8.1. Have you converted from Dictionary to Locally managed RBS tablespaces? When you upgraded, did you change any of the settings, like OPTIMAL, for the rollback segments? Were they dropped and recreated or upgraded inline? -- Daniel W. Fink http://www.optimaldba.com IOUG-A Live! April 27 - May 1, 2003 Orlando, FL Sunday, April 27 8:30am - 4:30pm - Problem Solving with Oracle 9i SQL Wednesday, May 1 1:00pm - 2:00pm - Automatic Undo Internals Kevin Lange wrote: Evening; Has anyone noticed any difference on the way Oracle uses Rollback Segments on 8.1.7.4 versus 8.0.5 ?? Ever since upgrading to 8.1.7.4 from 8.0.5 on our Solaris servers we have noticed more SNAPSHOT TOO OLD errors than we used to. Are there any suggestions as to rollback tuning that has to be done with that kind of version move ?? Any help would be appreciated. Thanks Kevin -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Daniel W. Fink INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Kevin Lange INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
NTFS on red hat
i have precompiled the cernel with ntfs write support. now how can i mount my ntfs partitions to try it?
Re: Oracle Development Suite for Linux?
RS On Mon, Mar 10, 2003 at 08:28:50PM -0800, Charles Hart wrote: I installed 9.2 on redhat 8.0 with great luck. I installed on a 700 PC with 756 megs of memory. I was using forms 6i running on NT client and could not tell when I was pointed at this instance verus one running on a HP machine. The document I found that was helpful in the install was http://otn.oracle.com/tech/linux/pdf/installtips_final.pdf RS I believe the real problems where with 8i on a 2.4 kernel. There is a RS miss-match is the glibc versions used for the kernel and oracle. What RS a mess. This seemed to work on 8.1.7-rh7.1 combination. rh7.1 is the RS last free version in the support matrix if that matters. RS http://www.tldp.org/HOWTO/Oracle8-on-RH7X-HOWTO-3.html RS On an error with a 9.0 install: RS Error when invoking /9.0/plsql/lib/ins_plsql.mk RS Doc ID: 197301.995 OK Lads, there are good responses. Thank you. Just clarify something... I have installed Oracle Database Server 9.2.0.1 on RH 7.3 without any major problems as well. Now I look at Application Server and Development Tools (such as Forms and Reports). I see 2 options for me: Linux (preferable) or Windows. It will be test installation - performance is not an issue. The main call for me is time I spent to get it work. Is Linux installation is more time consumed than Windows now (before it took more time and involved more complex procedure to get it working under Linux)? -- Best regards, Alexmailto:[EMAIL PROTECTED] __ Do You Yahoo!? Everything you'll ever need on one web page from News and Sport to Email and Music Charts http://uk.my.yahoo.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Alex Andriyashchenko INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: Hot backups
How about trying these: SVRMGR set autorecovery on Autorecovery ON SVRMGR recover database using backup controlfile; Ping Northrop Grumman IT -Original Message- Sent: Tuesday, March 11, 2003 10:34 AM To: Multiple recipients of list ORACLE-L I tried it both ways and got the same result. According to my notes from the Oracle Backup and Recovery course when you apply your redo logs, the control file gets rolled forward along with the data files. I believe that the only time you would get into trouble here is if you had made structural changes after the control file was backed up. This should not be an issue in my application. Peter Schauss Northrop Grumman Corporation -Original Message- Sent: Monday, March 10, 2003 4:08 PM To: Multiple recipients of list ORACLE-L Peter - Shouldn't you backup the controlfile AFTER completing tablespace backups? Dennis Williams DBA, 40%OCP, 100% DBA Lifetouch, Inc. [EMAIL PROTECTED] -Original Message- Sent: Monday, March 10, 2003 2:37 PM To: Multiple recipients of list ORACLE-L I am implementing hot backups on a small development database using the following approach: 1. From sqlplus: backup controlfile to 'filename'; 2. For each tablespace - sqlplus: alter tablespace name begin backup; - shell: copy files for that tablespace - sqlplus: alter tablespace name end backup; - sqlplus: alter system switch logfile; 3. tar and gzip the tablespace copies and backup control file; As a test, I am restoring the files to another system by doing the following: 1. Copied the init.ora file from the backed up database. 2. Restore the copied tablespaces and control files. 3. Copy the archived redo logs to the directory specified in the init.ora file. 4. Note the number on the most recent redo log. 5. sqlplus internal startup mount recover database until cancel using backup controlfile; (hit return when prompted for the next file until the last redo log noted in step 4 has been processed, then type CANCEL in response to the prompt for the next file.) alter database open reset logs; The above steps work, but I am wondering is there a better way to handle the control files so that I do not have to use the until cancel option. Thanks, Peter Schauss Northrop Grumman Corporation -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Schauss, Peter INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: DENNIS WILLIAMS INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Schauss, Peter INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Dong, Ping - Raleigh, NC INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: POLL: Database to DBA ratio
Chuck I think this is a slippery argument. It may appeal to managers but can lead to bad decisions long-term. Does anyone remember years ago when managers would measure COBOL programmers LOC (lines of code)? Just measure how many lines of code each programmer writes each day and vola! you know which are your best programmers -- right? But I would match 5 production instances against a single test instance with a group of cranky developers who are normalization bigots. I maintain about 15 instances by myself, so by your standards I'm a real slacker. I've tried to group applications into common instances, for ease of maintenance and better use of system memory. Maybe I should change that policy so I can boost my numbers ;-) Dennis Williams DBA, 40%OCP, 100% DBA Lifetouch, Inc. [EMAIL PROTECTED] -Original Message- Sent: Tuesday, March 11, 2003 8:59 AM To: Multiple recipients of list ORACLE-L I'm trying to build a case for management that we need additional DBAs so I'd like to take a quick poll if I may. What is the ratio of Oracle databases to DBAs in your shop? This includes development and production databases. At our shop it's 33:1. -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Chuck Hamilton INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: DENNIS WILLIAMS INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: POLL: Database to DBA ratio
[EMAIL PROTECTED] 03/11/03 09:59AM I'm trying to build a case for management that we need additional DBAs so I'd like to take a quick poll if I may. What is the ratio of Oracle databases to DBAs in your shop? This includes development and production databases. At our shop it's 33:1. It fluctates pretty wildly for us depending on the RD effort at any one point in time. But it's not unusual for us to have 50:3, though the 50 are of mixed types (Oracle, DB2, SQLServer etc.) Ciao Fuzzy :-) -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Grant Allen INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Re: Oracle position on hints
Seems like we are starting Yet Another Bind Discussion! Anyway, the peeking into bind variables is basically done when a hard parse is done. Hence, if multiple sessions execute the same shared SQL statement, only the first one will actually do the peek and hence, the optimization for all executions in all sessions will be as this first one. In my opinion, cursor_sharing and bind variable peeking should be useless features. However, I realize there are some incorrectly written applications out there that can have marginal need for them. /Bjrn. [EMAIL PROTECTED] wrote: From the performance tuning guide: The CBO peeks at the values of user-defined bind variables on the first invocation of a cursor. This feature lets the optimizer determine the selectivity of any WHERE clause condition, as well as if literals have been used instead of bind variables. On subsequent invocations of the cursor, no peeking takes place, and the cursor is shared, based on the standard cursor-sharing criteria, even if subsequent invocations use different bind values. CBO only peeks the bind variable once, so that wouldn't help much for star transforms and histograms. Jared "Jesse, Rich" [EMAIL PROTECTED] 03/10/2003 01:39 PM To: "'[EMAIL PROTECTED]'" [EMAIL PROTECTED] cc: "'[EMAIL PROTECTED]'" [EMAIL PROTECTED] Subject:RE: Oracle position on hints I thought that went out the window with 9i because it can snoop at the bind variables?? Rich Rich JesseSystem/Database Administrator [EMAIL PROTECTED] Quad/Tech International, Sussex, WI USA -Original Message- Sent: Monday, March 10, 2003 12:09 PM To: Multiple recipients of list ORACLE-L Well, I wouldn't say you should *always* use bind variables. Many situations in a database warehouse preclude that. Potentially long running queries may need to have literals to help the CBO make the right choice. Star transforms don't work with bind variables, and histograms can't be used with bind variables. Jared "Nicoll, Iain" [EMAIL PROTECTED] Sent by: [EMAIL PROTECTED] 03/08/2003 06:23 AM Please respond to ORACLE-L To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] cc: Subject:RE: Oracle position on hints Given the attitude of most dba's that you should always use bind variables where possible I can't see how you'd cope with skewed data without them. Most developers should know a databases's data better than the optimiser and certainly when building queries it's always worthwhile seeing where data is being most effectively filtered. There are lots of mature systems out there where the data characteristics are unlikely to change much and for most in-house developers you're never going to have to think about portability but always about performance. Iain Nicoll -Original Message- Sent: 07 March 2003 16:04 To: Multiple recipients of list ORACLE-L Hi, Does Oracle have an official position on hints ? Will they go away as the optimiser is becoming bettre or they are there to stay ? TIA Stephane Paquette Administrateur de bases de donnees Database Administrator Standard Life www.standardlife.ca Tl. (514) 925-7187 [EMAIL PROTECTED] mailto:[EMAIL PROTECTED] -- Bjrn Engsig, Miracle A/S Member of Oak Table Network [EMAIL PROTECTED] - http://MiracleAS.dk
RE: POLL: Database to DBA ratio
6:2 six DBAs to one development + one testing VLDB. Good luck. -Original Message- Sent: Tuesday, March 11, 2003 11:29 AM To: Multiple recipients of list ORACLE-L 3:1 Production, Development, my personal test DB : Me Craig Healey -Original Message- From: Chuck Hamilton [mailto:[EMAIL PROTECTED] Sent: 11 March 2003 14:59 To: Multiple recipients of list ORACLE-L Subject: POLL: Database to DBA ratio I'm trying to build a case for management that we need additional DBAs so I'd like to take a quick poll if I may. What is the ratio of Oracle databases to DBAs in your shop? This includes development and production databases. At our shop it's 33:1. ** This email and any files transmitted with it are confidential and intended solely for the use of the individual or entity to whom they are addressed and may contain confidential and/or privileged material. Any review, retransmission, dissemination or other use of, or taking of any action in reliance upon, this information by persons or entities other than the intended recipient is prohibited. Statements and opinions expressed in this e-mail may not represent those of the company. If you have received this email in error please notify [EMAIL PROTECTED] This footnote also confirms that this email message has been swept by MIMEsweeper for the presence of computer viruses (www.mimesweeper.com) *** -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Craig Healey INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Dong, Ping - Raleigh, NC INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Re: Row chaining
Tom Kyte has an excellent method of finding which are chained and which are migrated published in a recent issue of Oracle magazine. I don't know which one, possibly the latest, but will check when I get back in the office. In the meantime, the information may be on his web site. A good starting point... http://asktom.oracle.com/pls/ask/f?p=4950:8:104855126678244125::NO::F4950_P8_DISPLAYID,F4950_P8_CRITERIA:358341515662, If you have Oracle magazine, though, he answers your specific question. [EMAIL PROTECTED] 3/11 10:40a AK, hi, row chaining is because the big row(row length is bigger than the block size), while row migration is because of small pctfree and updated rows. So, just look at the length of the rows. If chained rows, no way(unless you use larger block size and the row does not contains really long column like long/long row data type), if migrated rows, you can move the table to repair the migrated rows. Good luck Regards zhu chao msn:[EMAIL PROTECTED] www.cnoug.org(China Oracle User Group) === 2003-03-11 07:14:00 ,you wroteú*=== RE: Running multiple instances on a [large] serverI see some values 0 for chaint_cnt in dba_tables . How do I know if this is chained rows or migrated rows ? Any hits . Thanks, ak = = = = = = = = = = = = = = = = = = = = -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: chao_ping INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Darrell Landrum INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: POLL: Database to DBA ratio
volume of database structural changes (new tables, changes to existing ones) is another to add to that list --- Pete Sharman [EMAIL PROTECTED] wrote: 33 DBA's to one database? That's pretty danged good! :) Seriously, this is a question that comes up every so often within Oracle as well, as clients ask Oracle employees what should be expected. There's no simple answer to it. As you might expect, the answer is it depends. :) Depends on (a non-exhaustive list): 1.Requirement for 24x7 DBA coverage 2.Complexity of database 3.Type of applications using the database 4.Database type (OLTP, DW etc.) 5.Number of tiers (two tier, three tier etc.) 6.Amount of input required into development of apps (tuning support and so on) And lots more as well. Pete Controlling developers is like herding cats. Kevin Loney, Oracle DBA Handbook Oh no, it's not. It's much harder than that! Bruce Pihlamae, long-term Oracle DBA -Original Message- Hamilton Sent: Tuesday, March 11, 2003 6:59 AM To: Multiple recipients of list ORACLE-L I'm trying to build a case for management that we need additional DBAs so I'd like to take a quick poll if I may. What is the ratio of Oracle databases to DBAs in your shop? This includes development and production databases. At our shop it's 33:1. -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Chuck Hamilton INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Pete Sharman INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). __ Do you Yahoo!? Yahoo! Web Hosting - establish your business online http://webhosting.yahoo.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Rachel Carmichael INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: can't create database
Title: RE: LMT monitoring I had the same problem,You have to generate the script with dbassist then run the script. -Original Message-From: Milen Pankov [mailto:[EMAIL PROTECTED]Sent: Tuesday, March 11, 2003 9:00 AMTo: Multiple recipients of list ORACLE-LSubject: can't create database can't create a database with oracle 8.1.7.the installation went fine, but when i start dbassist on the 2% of thedatabase creation it tels me:"not connected to oracle".any ideas? === Confidentiality Statement === The information contained in this message and any attachments is intended only for the use of the individual or entity to which it is addressed, and may contain information that is PRIVILEGED, CONFIDENTIAL and exempt from disclosure under applicable law. If you have received this message in error, you are prohibited from copying, distributing, or using the information. Please contact the sender immediately by return e-mail and delete the original message from your system. = End Confidentiality Statement =
RE: can't create database
Title: RE: LMT monitoring Is this on Linux? If yes, then it sounds like you need to install the glibc stubs patch. If no, then I don't know what the problem might be. -Original Message-From: Milen Pankov [mailto:[EMAIL PROTECTED]Sent: Tuesday, March 11, 2003 10:00 AMTo: Multiple recipients of list ORACLE-LSubject: can't create database can't create a database with oracle 8.1.7.the installation went fine, but when i start dbassist on the 2% of thedatabase creation it tels me:"not connected to oracle".any ideas?
Can't run full exports due to SMON wait
Title: Can't run full exports due to SMON wait Running 8.1.7.4 on RS6000/AiX 4.3.3. We noticed this weekend that all our full exports performed via sqlbacktrack for a given database were hanging. Killing the exports and restarting does not work. Checking waits, it looks as though the full exports start hanging on buffer busy waits, on the same file/block as SMON, which in turn has been stuck it appears, the entire weekend: SID EVENT P1TEXT P1 P2TEXT P2 P3TEXT P3 - --- --- -- - --- 5 db file scattered read file# 2 block# 69025 blocks 8 SID Username EVENT WAIT_TIME STATE SECONDS_IN_WAIT - --- --- 5 db file scattered read 0 WAITING 246544 SID O/S User MACHINE Program - --- - 5 oracle indysdb02 [EMAIL PROTECTED] (SMON) SQL_TEXT -- select i.obj#, i.flags, u.name, o.name from sys.obj$ o, sys.user$ u, sys.ind$ i where (bitand(i.flags, 256) = 256 or bitand(i.flags, 512) = 512) and (not((i.type# = 9) and bitand(i.flags,8) = 8)) and o.obj#=i.obj# and o.owner# = u.user# Before we resort to a bounce, I'm hoping that someone here may have seen this before, or at least, have some idea as to what's going on with SMON. TIA. Jeffery D Thomas DBA Thomson Information Services Thomson, Inc. Email: [EMAIL PROTECTED] Indy DBA Master Documentation available at: http://gkmqp.tce.com/tis_dba Select 'Indy DBA' then 'DBA Web Pages' --- As of February 12, 2003 Thomson unified its email addresses on a worldwide basis. Please note my new email address: [EMAIL PROTECTED] Thomson is the leader in solutions and technologies for the entertainment and media industries and serves its customers under its four strategic brands: Technicolor, Grass Valley, RCA and THOMSON. More about Thomson: http://www.thomson.net/videochain
RE: Disable / enable constraints
SYS_ contraints are the ones that when you created them you did not give a name. Oracle will automatically assign a name like 'SYS_1234567'. You can drop tables in the schema before import. Import will automatically create tables if it does not exist. HTH. Guang -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] Behalf Of Smith, Ron L. Sent: Tuesday, March 11, 2003 11:50 AM To: Multiple recipients of list ORACLE-L Subject: Disable / enable constraints I need to truncate and import data into several schemas. The tables have lots of constraints. I can produce a script to disable and enable the contriants but I would like to know more about the constraint_type field in dba_constraints and what are all the SYS_ contraints? Should I disable all constraints for a schema before the import or only certain types? Thanks! Ron If you are not the intended recipient of this e-mail message, any use, distribution or copying of the message is prohibited. Please let me know immediately by return e-mail if you have received this message by mistake, then delete the e-mail message. Thank you. -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Smith, Ron L. INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: gmei INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Re:POLL: Database to DBA ratio
Chuck, We've got near as many instances as you (40 at last count) but in three different locations(2 in Mass although there 10 miles apart and one in CA). Consequently there are three DBA's. You also might want to take a head count of what I refer to as screaming mimesis (end users who whenever anything wrong happens must be experiencing the end of the world). One dba per 5 of these appears to be the right balance! *-) Dick Goulet Reply Separator Author: Chuck Hamilton [EMAIL PROTECTED] Date: 3/11/2003 6:59 AM I'm trying to build a case for management that we need additional DBAs so I'd like to take a quick poll if I may. What is the ratio of Oracle databases to DBAs in your shop? This includes development and production databases. At our shop it's 33:1. -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Chuck Hamilton INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: Oracle Developer Kit 9iV2 on Solaris - Refined
Title: RE: Oracle Developer Kit 9iV2 on Solaris - Refined Actually what are best practices for where you put this stuff - apparently some of it can be either on 9ias or on the RDBMS. If you have an application that will be using both - which is best practice? Are there issues related to security risk, manageability, priviledges, etc.? -Original Message- From: Stankus, Paula G Sent: Tuesday, March 11, 2003 1:01 PM To: '[EMAIL PROTECTED]' Subject: RE: Oracle Developer Kit 9iV2 on Solaris As a standard we install web servers separate from RDBMS servers (i.e. on separate hosts). 9iV2 comes with Development Kit. Is any of that needed on the RDBMS server? Would I want any of that installed on the web server host on Solaris if my developers are comfortable with Windows - there is a Windows version of the same - or do some components need to be installed on a webserver? Is there a good overview of the Developer Kit? Okay, I will look on OTN. However, if any quick answers would like to know. Thanks, Paula
RE: Oracle position on hints
I doubt there are "some incorrectly applications", I think that is a rule rather than an exception. Raj - Rajendra dot Jamadagni at espn dot com Any views expressed here are strictly personal. QOTD: Any clod can have facts, having an opinion is an art !! -Original Message-From: Bjørn Engsig [mailto:[EMAIL PROTECTED]Sent: Tuesday, March 11, 2003 12:00 PMTo: Multiple recipients of list ORACLE-LSubject: Re: Oracle position on hintsSeems like we are starting Yet Another Bind Discussion!Anyway, the peeking into bind variables is basically done when a hard parse is done. Hence, if multiple sessions execute the same shared SQL statement, only the first one will actually do the peek and hence, the optimization for all executions in all sessions will be as this first one.In my opinion, cursor_sharing and bind variable peeking should be useless features. However, I realize there are some incorrectly written applications out there that can have marginal need for them./Bjørn. This e-mail message is confidential, intended only for the named recipient(s) above and may contain information that is privileged, attorney work product or exempt from disclosure under applicable law. If you have received this message in error, or are not the named recipient(s), please immediately notify corporate MIS at (860) 766-2000 and delete this e-mail message from your computer, Thank you.*2
RE: Oracle Developer Kit 9iV2 on Solaris
Title: RE: Oracle Developer Kit 9iV2 on Solaris As a standard we install web servers separate from RDBMS servers (i.e. on separate hosts). 9iV2 comes with Development Kit. Is any of that needed on the RDBMS server? Would I want any of that installed on the web server host on Solaris if my developers are comfortable with Windows - there is a Windows version of the same - or do some components need to be installed on a webserver? Is there a good overview of the Developer Kit? Okay, I will look on OTN. However, if any quick answers would like to know. Thanks, Paula
RE: POLL: Database to DBA ratio
I currently have 5 development databases and 1 beta production database that I administer, so that's 6:1, but I'm also the Sys Admin for these servers as well. These are also very small databases. robin -Original Message- Sent: Tuesday, March 11, 2003 11:44 AM To: Multiple recipients of list ORACLE-L We have 10 development databases that I support directly plus 5 implementation/testing databases that I support as second level and one production snapshot datamart. 16:1 However, there are also DBAs who support each project. That would make the ratio closer to 4:1 or 3:1. Chuck Hamilton chuckh To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] @softhome.net cc: Sent by: rootSubject: POLL: Database to DBA ratio 03/11/2003 09:59 AM Please respond to ORACLE-L I'm trying to build a case for management that we need additional DBAs so I'd like to take a quick poll if I may. What is the ratio of Oracle databases to DBAs in your shop? This includes development and production databases. At our shop it's 33:1. -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Chuck Hamilton INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Thomas Day INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Robin Ilardi INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: Disable / enable constraints
Ron, SYS_ constraints are NOT NULL constraints. They are also those constraints placed on tables by you that you have not named. This is why we should always name our constraints! The Constraint_Type field is decoded as follows: C = Check Constraint P = Primary Key R = Relational (Foreign Key) constraint U = Unique Constraint There are probably others documented in the Oracle docs someplace. Hope this helps! Tom Mercadante Oracle Certified Professional -Original Message- Sent: Tuesday, March 11, 2003 11:50 AM To: Multiple recipients of list ORACLE-L I need to truncate and import data into several schemas. The tables have lots of constraints. I can produce a script to disable and enable the contriants but I would like to know more about the constraint_type field in dba_constraints and what are all the SYS_ contraints? Should I disable all constraints for a schema before the import or only certain types? Thanks! Ron If you are not the intended recipient of this e-mail message, any use, distribution or copying of the message is prohibited. Please let me know immediately by return e-mail if you have received this message by mistake, then delete the e-mail message. Thank you. -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Smith, Ron L. INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Mercadante, Thomas F INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: POLL: Database to DBA ratio
28 Oracle (dev, test, qa prod) 3 SQL Server (arent I lucky? gag) 31:1 Todd Carlson -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of Gene Sais Sent: Tuesday, March 11, 2003 11:00 AM To: Multiple recipients of list ORACLE-L Subject: RE: POLL: Database to DBA ratio 27 databases to 1 dba, mixed dev, test, and prod. [EMAIL PROTECTED] 03/11/03 11:29AM 3:1 Production, Development, my personal test DB : Me Craig Healey -Original Message- href="" PROTECTED]mailto:[EMAIL PROTECTED] Sent: 11 March 2003 14:59 To: Multiple recipients of list ORACLE-L build a case for management that we need additional DBAs so I'd like to take a quick poll if I may. What is the ratio of Oracle databases to DBAs in your shop? This includes development and production databases. At our shop it's 33:1. ** This email and any files transmitted with it are confidential and intended solely for the use of the individual or entity to whom they are addressed and may contain confidential and/or privileged material. Any review, retransmission, dissemination or other use of, or taking of any action in reliance upon, this information by persons or entities other than the intended recipient is prohibited. Statements and opinions expressed in this e-mail may not represent those of the company. If you have received this email in error please notify [EMAIL PROTECTED] This footnote also confirms that this email message has been swept by MIMEsweeper for the presence of computer viruses (www.mimesweeper.com) *** -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Craig Healey INET: [EMAIL PROTECTED] Fat City Network Services -- 858-538-5051 http://www.fatcity.com San Diego, California -- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Re: can't create database
On Tue, Mar 11, 2003 at 09:00:09AM -0800, Milen Pankov wrote: RE: LMT monitoringcan't create a database with oracle 8.1.7. the installation went fine, but when i start dbassist on the 2% of the database creation it tels me: not connected to oracle. any ideas? -- look in the alert log === Ray Stell [EMAIL PROTECTED] (540) 231-4109 KE4TJC28^D -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Ray Stell INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: POLL: Database to DBA ratio
Last place I was at was 35:1 but we had top notch guys and and organized environment..The kinds of databases you have.. what kinds of work being done on them etc. the high availability and service level agreement and change control requirments. Now we have mega-sensitive instances.. needing Now we have 3:1 Many of them have 3-way upgrades each year and we only have 8 months to do it in.. Saying all that is to say...its not apples to apples.. What we do- is, we map all the ongoing projects and resource out the dba's and when new projects come...we have a tool to communicate the resource requirements to management... something they understand when you say You want that project.. we need more resources... or what other project do you want to cancel. If you don't use a tool like this its easy to have managment pushing you into making all kinds of bad decisions and become heavily understaffed. Brian -Original Message- Sent: Tuesday, March 11, 2003 12:14 PM To: Multiple recipients of list ORACLE-L 6:2 six DBAs to one development + one testing VLDB. Good luck. -Original Message- Sent: Tuesday, March 11, 2003 11:29 AM To: Multiple recipients of list ORACLE-L 3:1 Production, Development, my personal test DB : Me Craig Healey -Original Message- From: Chuck Hamilton [mailto:[EMAIL PROTECTED] Sent: 11 March 2003 14:59 To: Multiple recipients of list ORACLE-L Subject: POLL: Database to DBA ratio I'm trying to build a case for management that we need additional DBAs so I'd like to take a quick poll if I may. What is the ratio of Oracle databases to DBAs in your shop? This includes development and production databases. At our shop it's 33:1. ** This email and any files transmitted with it are confidential and intended solely for the use of the individual or entity to whom they are addressed and may contain confidential and/or privileged material. Any review, retransmission, dissemination or other use of, or taking of any action in reliance upon, this information by persons or entities other than the intended recipient is prohibited. Statements and opinions expressed in this e-mail may not represent those of the company. If you have received this email in error please notify [EMAIL PROTECTED] This footnote also confirms that this email message has been swept by MIMEsweeper for the presence of computer viruses (www.mimesweeper.com) *** -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Craig Healey INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Dong, Ping - Raleigh, NC INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Spears, Brian INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: POLL: Database to DBA ratio
On the Oracle side there's 2 of us (myself and one other) supporting 20 databases, so that makes us 10:1. However, in addition to this there is also the IBM mainframe/Software AG Adabas side where it is my privilege to support the 5 legacy database environments by myself. Jim Damiano -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: James Damiano INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Oracle Database audit...
Title: Oracle Database audit... This may or may not be of value... Under the Security: Possibly users with the oracle account password on the machine? This would obviously allow people to conn / as sysdba. Maybe? :) Have a good one!
Re: Analyzing indexes
RE: Analyzing indexesNo but validate structure populates the index_stats view which is the only way I know of to get the index height, leaf rows, deleted leaf rows, and pct. of used space which is what I normally use in determining if an index needs to be rebuilt or not. Is there another way? - Original Message - To: Multiple recipients of list ORACLE-L Sent: Tuesday, March 11, 2003 10:49 AM Chuck, Do you think these indexes are corrupt? Validate structure doesn't give you statistics like Compute Statistics or Estimate Statistics does. Jerry Whittle ASIFICS DBA NCI Information Systems Inc. [EMAIL PROTECTED] 618-622-4145 -Original Message- I need to determine whether or not a couple of indexes need to be rebuilt. The problem is the indexes are quite large and on a 24x7 high volume database. If I try to run an analyze validate structure to gather the data I need to make that decision, it sets a lock on the table for about an hour which I can't afford to do. There is no slow time when I can do this and management has said before they're not going to spring for the partitioning option to break the indexes up into managable pieces. Is there some other way I can get the information needed to determine if an index needs to be rebuilt or not without setting a lock on the table? We are on Oracle 8.1.7. -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Chuck Hamilton INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: POLL: Database to DBA ratio
We currently have twenty-one databases, thirteen production and eight which are either test or development. Of the thirteen production databases five need to be up on a 24 X 7 basis; physics experiments and or accelerator monitoring depend on them. Nine of the production databases are Oracle back ends for third-party products including Peoplesoft and Remedy. All the above databases all administered by one DBA. We do have Peoplesoft Administrators, Remedy Administrators, System Administrators to take some of the load off, but I am responsible for the installation, upgrade, maintenance, tuning, and backup of all databases and Oracle software. This includes Internet Developer Suite and Internet Application Server, although as to the latter, I'm in the process of unloading it. This all works quite well most of the time. A problem can arise when I must devote my attention to fixing a pressing problem. In which case the reading of some reports on the non-24x7 databases may be postponed and the database then pages me about trouble which I could of fixed proactively had I read the reports. Larry E. talks of having a single database with third party certifications to consider and with multiple applications requiring 24 X 7 service this is impossible. We do of course have to negotiate down times for even the 24 X 7 applications, but their operational schedules make it impossible to have them all down at once. I do get vacations. A cell phone and a wide-area pager is a necessity, and I don't necessarily have to be at work from eight to five. Ian MacGregor Stanford Linear Accelerator Center [EMAIL PROTECTED] -Original Message- Sent: Tuesday, March 11, 2003 8:24 AM To: Multiple recipients of list ORACLE-L Chuck, At this location we have a total of 5 databases and 1 DBA. Time off without an electronic teather is a thing of the past. My last location was 4 production databases with 1 DBA and a backup DBA from the development arena of 2 development databases and 1 DBA. Ron [EMAIL PROTECTED] 03/11/03 09:59AM I'm trying to build a case for management that we need additional DBAs so I'd like to take a quick poll if I may. What is the ratio of Oracle databases to DBAs in your shop? This includes development and production databases. At our shop it's 33:1. -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Chuck Hamilton INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Ron Rogers INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: MacGregor, Ian A. INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Re: Running multiple instances on a [large] server
Do all share the same address space or does each instance get it's own copy of Oracle.exe and it's own 2g address space? - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Tuesday, March 11, 2003 10:04 AM We are running 11 instances on WinNT with no problems! At one point, we had 26 instances. they are all development instances, so volumn and load are low. but they all share one Oracle Home. Tom Mercadante Oracle Certified Professional -Original Message- Sent: Tuesday, March 11, 2003 9:19 AM To: Multiple recipients of list ORACLE-L I can't remember the last time that I saw a server (other than WinNT) running a single instance. My experience is that it's quite normal to run multiple instances on a single server. One Oracle home per version of Oracle. I'm not sure what the point of a separate Oracle home per instance would be. On the licensing issue --- that's Oracle's call. We have a site license so it's not an issue for me. Hemant K Chitale hkchitalTo: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] @singnet.com.sg cc: Sent by: rootSubject: Running multiple instances on a [large] server 03/11/2003 03:48 AM Please respond to ORACLE-L One of the teams here is planning to run anything from 8 to 16 database instances [no indication on sizing yet, but gut-feel SGAs are 200MB to 1GB and DB sizes 500MB to 40GB] on a large server, something like a Sun E6800 or an equivalent HP or Fujitsu server. 1. How many of you do run, and are comfortable running, multiple databases on the same server, whether it is partitioned or not ? 2. Do you create a seperate ORACLE_HOME for each instance ? 3. Do you just buy the Oracle DB CPU license on the total number of CPUs on the server ? My positioning is a. We might not be able to create 8 partitions but partition such that we have a max of 2 or 3 instances in one partition. Hopefully, I can dynamically change CPU partitioning to reallocate CPU to another group of instances. b. Each instance should have it's own ORACLE_HOME. [Disk space is not a constraint]. c. Just add up the number of CPUs on the server, across all partitions, and buy a CPU license. Also, a CPU license is much preferable to Named-User as some of these databases would host Portals for 1,000+ end-users. Hemant K Chitale http://hkchital.tripod.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Hemant K Chitale INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Thomas Day INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Mercadante, Thomas F INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Chuck Hamilton INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To
Re: RE: need help with dynamic sql
thats the problem, I do not know for sure that the tables are identical in each
schema. Sorry, I left that out. I have to check for that before I even get to that
point. If there are 'NOT NULL' columns in the destination table, then I throw insert
to an error table, if not then I build the comma delimited string of columns.
From: Jamadagni, Rajendra [EMAIL PROTECTED]
Date: 2003/03/11 Tue AM 11:29:03 EST
To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED]
Subject: RE: need help with dynamic sql
if you at-least know the source and destination tables will have same number
of columns and data types ... you could just do something like
insert into target.table
select from source.table
Raj
-
Rajendra dot Jamadagni at espn dot com
Any views expressed here are strictly personal.
QOTD: Any clod can have facts, having an opinion is an art !!
-Original Message-
Sent: Tuesday, March 11, 2003 10:24 AM
To: Multiple recipients of list ORACLE-L
g_colName is a string of column names that I build earlier in the algorithm.
My problem is how do I do the 'INTO' part of this cursor, since I do not
know how many or of what type my columns are. The query is on the fly and
totally dynamic.
Anyway to do this without using DBMS_SQL package?
OPEN cur_values FOR 'SELECT '||g_colName ||
' FROM ' || p_sourceSchema||'.'||v_tableName ||
' WHERE PARENT_RESOURCE_ID BETWEEM :1 AND :2'
USING p_startResourceID, p_endResourceID;
LOOP
FETCH cur_values
INTO v_values;
EXIT WHEN cur_values%NOTFOUND;
EXECUTE IMMEDIATE 'INSERT INTO
'||p_targetSchema||'.'||v_tableName||
'('||g_colName||')'||
'(VALUES)';
--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author: [EMAIL PROTECTED]
INET: [EMAIL PROTECTED]
Fat City Network Services-- 858-538-5051 http://www.fatcity.com
San Diego, California-- Mailing list and web hosting services
-
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from). You may
also send the HELP command for other information (like subscribing).
*This e-mail
message is confidential, intended only for the named recipient(s) above and may
contain information that is privileged, attorney work product or exempt from
disclosure under applicable law. If you have received this message in error, or are
not the named recipient(s), please immediately notify corporate MIS at (860)
766-2000 and delete this e-mail message from your computer, Thank
you.*1
Title: RE: need help with dynamic sql
if you at-least know the source and destination tables will have same number of columns and data types ... you could just do something like
insert into target.table
select from source.table
Raj
-
Rajendra dot Jamadagni at espn dot com
Any views expressed here are strictly personal.
QOTD: Any clod can have facts, having an opinion is an art !!
-Original Message-
From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]]
Sent: Tuesday, March 11, 2003 10:24 AM
To: Multiple recipients of list ORACLE-L
Subject: need help with dynamic sql
g_colName is a string of column names that I build earlier in the algorithm. My problem is how do I do the 'INTO' part of this cursor, since I do not know how many or of what type my columns are. The query is on the fly and totally dynamic.
Anyway to do this without using DBMS_SQL package?
OPEN cur_values FOR 'SELECT '||g_colName ||
' FROM ' || p_sourceSchema||'.'||v_tableName ||
' WHERE PARENT_RESOURCE_ID BETWEEM :1 AND :2'
USING p_startResourceID, p_endResourceID;
LOOP
FETCH cur_values
INTO v_values;
EXIT WHEN cur_values%NOTFOUND;
EXECUTE IMMEDIATE 'INSERT INTO '||p_targetSchema||'.'||v_tableName||
'('||g_colName||')'||
'(VALUES)';
--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author: [EMAIL PROTECTED]
INET: [EMAIL PROTECTED]
Fat City Network Services -- 858-538-5051 http://www.fatcity.com
San Diego, California -- Mailing list and web hosting services
-
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling
RE: Oracle Database audit...
Title: Oracle Database audit... Oops. This wasn't meant to go to the list. sorry -Original Message- From: Chris Stephens [mailto:[EMAIL PROTECTED] Sent: Tuesday, March 11, 2003 12:20 PM To: Multiple recipients of list ORACLE-L Subject: Oracle Database audit... This may or may not be of value... Under the Security: Possibly users with the oracle account password on the machine? This would obviously allow people to conn / as sysdba. Maybe? :) Have a good one!
Re: Running multiple instances on a [large] server
RE: Running multiple instances on a [large] serverBy environment do you mean instance? If so, couldn't you accomplish the same thing with one oracle home on each server for each version installed? That's what we do in our clustered environments. Each node has an oracle home for each version of Oracle that it might need to run. Failover here takes about the same amount of time but much less disk space. Last time I checked a single oracle home was running about 3g. - Original Message - To: Multiple recipients of list ORACLE-L Sent: Tuesday, March 11, 2003 12:10 PM We run upto 22 instances on large SUN boxes... Each environment has their own oracle home, own file system, and in some cases-their own UNIX account for the environment.. One loses a little space for the multiple oracle homes, and it does add some overhead for the operations.. But when we need to migrate a database to a new server;we can shutdown the DB-umount,mount on the new system, and crank it up. We have been in a situation where the machine resources choked, and we were able to move a couple of databases with less than a 3 minute hit for an outage. Would have been less if the operations guys(SA's) had things tee'd up, and ready to hit it with the Driver too. greg -Original Message- Sent: Tuesday, March 11, 2003 3:49 AM To: Multiple recipients of list ORACLE-L One of the teams here is planning to run anything from 8 to 16 database instances [no indication on sizing yet, but gut-feel SGAs are 200MB to 1GB and DB sizes 500MB to 40GB] on a large server, something like a Sun E6800 or an equivalent HP or Fujitsu server. 1. How many of you do run, and are comfortable running, multiple databases on the same server, whether it is partitioned or not ? 2. Do you create a seperate ORACLE_HOME for each instance ? 3. Do you just buy the Oracle DB CPU license on the total number of CPUs on the server ? My positioning is a. We might not be able to create 8 partitions but partition such that we have a max of 2 or 3 instances in one partition. Hopefully, I can dynamically change CPU partitioning to reallocate CPU to another group of instances. b. Each instance should have it's own ORACLE_HOME. [Disk space is not a constraint]. c. Just add up the number of CPUs on the server, across all partitions, and buy a CPU license. Also, a CPU license is much preferable to Named-User as some of these databases would host Portals for 1,000+ end-users. Hemant K Chitale http://hkchital.tripod.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Hemant K Chitale INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Chuck Hamilton INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Re: POLL: Database to DBA ratio
Consider yourself lucky. We've got a 71:1 ratio here. Chuck Hamilton [EMAIL PROTECTED]T To: Multiple recipients of list ORACLE-L Sent by: [EMAIL PROTECTED] [EMAIL PROTECTED] cc: bcc: Subject: POLL: 03/11/03 09:59 AMDatabase to DBA ratio Please respond to ORACLE-L I'm trying to build a case for management that we need additional DBAs so I'd like to take a quick poll if I may. What is the ratio of Oracle databases to DBAs in your shop? This includes development and production databases. At our shop it's 33:1. -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Chuck Hamilton INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Re: can't create database
I had the same error message on NT with Oracle 9.2 recently. The first problem was that the init_sid.ora file was created a directory different from where the db create scripts were looking for it. The second problem was that one of the init parameters was for Enterprise Edition and I was installing Standard Edition. Both problems resulted in the message of 'not connected to oracle'. Come to think of it, I don't think I've ever had a version of the DB Creation wizard work without getting some error. -- Alan Davey [EMAIL PROTECTED] 718-482-4200 x106 On 3/11/2003 12:44 PM, Stahlke, Mark [EMAIL PROTECTED] wrote: RE: LMT monitoring Is this on Linux? If yes, then it sounds like you need to install the glibc stubs patch. If no, then I don't know what the problem might be. -Original Message- From: Milen Pankov [mailto:[EMAIL PROTECTED] Sent: Tuesday, March 11, 2003 10:00 AM To: Multiple recipients of list ORACLE-L Subject: can't create database can't create a database with oracle 8.1.7. the installation went fine, but when i start dbassist on the 2% of the database creation it tels me: not connected to oracle. any ideas? -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Alan Davey INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: POLL: Database to DBA ratio
A simple num of databases to DBA ratio is quite meaningless. You need to take into consideration of at least the following: o Size of the database, transaction volume. What kind of up time your database needs to provide. o Complexity of the database and application which run on top of it. Number of objects, tables, stored procedures. Is there replication, partitioning, object types, RAC/OPS, etc involved? o Does the DBA also do some SA work, or development work or design etc. o How frequent is your development cycle. I am sure others will have lots of things to attribute. But you can't just say I have NNN number of databases and therefore need NN number of DBAs. Richard Ji -Original Message- Sent: Tuesday, March 11, 2003 6:59 AM To: Multiple recipients of list ORACLE-L I'm trying to build a case for management that we need additional DBAs so I'd like to take a quick poll if I may. What is the ratio of Oracle databases to DBAs in your shop? This includes development and production databases. At our shop it's 33:1. -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Chuck Hamilton INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Richard Ji INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Re: Row chaining
I have written two programs to deal with this: 1) a plsql procedure that shows all the LIO for a chained row system wide. This works like sar: ser serveroutput on execute oraperf.analyze_lio(10, 1); 2) an object row chainer analyzer, this will find which SQL statements and what objects have LIOs for row chaining and how much. Program 1 will be available from Oraperf.com Anjo. On Tuesday 11 March 2003 07:49, you wrote: ak Smart-alek answer: Apply one of the methods to eliminate migrated rows, and if the problem doesn't go away, you know you have some chained rows ;-) Chained rows are a little difficult to diagnose. Look at the value for avg_row_len - is it near the db_block_size? I haven't tried this, but if you really want to go to the trouble, you could create a table named CHAINED_ROWS, run ANALYZE . . . LIST CHAINED ROWS. The create a SQL statement that will execute the VSIZE function on each column and sum the values. Then run this statement on each rowid in CHAINED_ROWS. Now you see the reason for my initial suggestion. I would suggest that you not get too paranoid about getting CHAINED_ROWS to zero. But if your wait statistics starts to show table fetch continued row as significant, you definitely need to fix the problem. Dennis Williams DBA, 40%OCP, 100% DBA Lifetouch, Inc. [EMAIL PROTECTED] -Original Message- Sent: Tuesday, March 11, 2003 9:14 AM To: Multiple recipients of list ORACLE-L I see some values 0 for chaint_cnt in dba_tables . How do I know if this is chained rows or migrated rows ? Any hits . Thanks, ak -- Anjo Kolk http://www.oraperf.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Anjo Kolk INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Re: POLL: Database to DBA ratio
Chuck, I support 17 development, 2 production oracle databases, 3 DB2/MVS databases and 1 SQL Server. A coworker supports only 1 application and that is in UAT/Dev environment but it is a highly active application in the multi-Terabyte range (UDB). We have external prod. support for some of our apps (IBM). I think how many a DBA can support will depend upon a number of things such as (by no means a complete list): - Production Support and/or App. Dev support - Frequency of change to an application. Some stay in a constant state of change. - Complexity of system (ERP, n-tier, custom, vendor) - Procedures/processes/technology to allow easier support of multiple systems. David From: Chuck Hamilton [EMAIL PROTECTED] Reply-To: [EMAIL PROTECTED] To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Subject: POLL: Database to DBA ratio Date: Tue, 11 Mar 2003 06:59:21 -0800 I'm trying to build a case for management that we need additional DBAs so I'd like to take a quick poll if I may. What is the ratio of Oracle databases to DBAs in your shop? This includes development and production databases. At our shop it's 33:1. -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Chuck Hamilton INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). _ Add photos to your e-mail with MSN 8. Get 2 months FREE*. http://join.msn.com/?page=features/featuredemail -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: david davis INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: POLL: Database to DBA ratio
139 remote production servers/instances (30 are large to VLDB, the rest small), 5 development instances, 1 9iAS test instance, 3 dba's. Mostly reporting with batch ETL jobs run nightly. Scott Shafer San Antonio, TX 210.581.6217 -Original Message- From: Robin Ilardi [SMTP:[EMAIL PROTECTED] Sent: Tuesday, March 11, 2003 12:10 PM To: Multiple recipients of list ORACLE-L Subject: RE: POLL: Database to DBA ratio I currently have 5 development databases and 1 beta production database that I administer, so that's 6:1, but I'm also the Sys Admin for these servers as well. These are also very small databases. robin -Original Message- Sent: Tuesday, March 11, 2003 11:44 AM To: Multiple recipients of list ORACLE-L We have 10 development databases that I support directly plus 5 implementation/testing databases that I support as second level and one production snapshot datamart. 16:1 However, there are also DBAs who support each project. That would make the ratio closer to 4:1 or 3:1. Chuck Hamilton chuckh To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] @softhome.net cc: Sent by: rootSubject: POLL: Database to DBA ratio 03/11/2003 09:59 AM Please respond to ORACLE-L I'm trying to build a case for management that we need additional DBAs so I'd like to take a quick poll if I may. What is the ratio of Oracle databases to DBAs in your shop? This includes development and production databases. At our shop it's 33:1. -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Chuck Hamilton INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Thomas Day INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Robin Ilardi INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Re: POLL: Database to DBA ratio
31:1 will be 40:1 by the end of the year. I have a second guy in training as the second DBA. 3:1 oracle app servers 23 24X7 medical databases of one type or annother 8 Dev/Test databases 1 production app server 2 dev/test app servers ...JIM... [EMAIL PROTECTED] 3/11/03 8:59:20 AM I'm trying to build a case for management that we need additional DBAs so I'd like to take a quick poll if I may. What is the ratio of Oracle databases to DBAs in your shop? This includes development and production databases. At our shop it's 33:1. -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Chuck Hamilton INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: James Howerton INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Re: perl 5.8 access to oracle
Get DBI and DBD::Oracle from www.xmlproj.com/PPM, courtesy of Ilya Sterin. Jared Jeffrey Beckstrom [EMAIL PROTECTED] Sent by: [EMAIL PROTECTED] 03/11/2003 06:34 AM Please respond to ORACLE-L To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] cc: Subject:perl 5.8 access to oracle Just installed Perl 5.8. Ran ppm3 to install the DBD package and PPM couldn't find it. I looked at activeperl site, it stated dbd for Oracle failed. I have now installed perl 5.6 and can find dbd/dbi files. With Perl 5.8, are you supposed to use something other than dbd/dbi to access an Oracle database? Jeffrey Beckstrom Database Administrator Greater Cleveland Regional Transit Authority 1240 W. 6th Street Cleveland, Ohio 44113 (216) 781-4204 -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Re: POLL: Database to DBA ratio
Chuck, That's not really a good measure. It really depends on a number of things. * number of users * number of apps * size of db's * quality of apps ( some require more work than others ) * amount of new development * lots more I can't think of at the moment... Best thing to do is document the advantages of additional staff. If it can save the company $$, they will go for it. The savings will come in from less downtime, apps get put in production sooner, ... Jared Chuck Hamilton [EMAIL PROTECTED] Sent by: [EMAIL PROTECTED] 03/11/2003 06:59 AM Please respond to ORACLE-L To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] cc: Subject:POLL: Database to DBA ratio I'm trying to build a case for management that we need additional DBAs so I'd like to take a quick poll if I may. What is the ratio of Oracle databases to DBAs in your shop? This includes development and production databases. At our shop it's 33:1. -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Chuck Hamilton INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Re: Analyzing indexes
Chuck, I've been convinced that rebuilding indexes is a waste of time. In fact, it can cost you time, as rebuilding indexes can kill your peformance while the indexes again seek their 'level'. Check into at asktom.oracle.com. There's some good examples. jared Chuck Hamilton [EMAIL PROTECTED] Sent by: [EMAIL PROTECTED] 03/11/2003 06:30 AM Please respond to ORACLE-L To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] cc: Subject:Analyzing indexes I need to determine whether or not a couple of indexes need to be rebuilt. The problem is the indexes are quite large and on a 24x7 high volume database. If I try to run an analyze validate structure to gather the data I need to make that decision, it sets a lock on the table for about an hour which I can't afford to do. There is no slow time when I can do this and management has said before they're not going to spring for the partitioning option to break the indexes up into managable pieces. Is there some other way I can get the information needed to determine if an index needs to be rebuilt or not without setting a lock on the table? We are on Oracle 8.1.7. -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Chuck Hamilton INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
