AW: Why it picks the same PFile for both the DBs
Before starting svrmgr set the oracle_sid to test or prod In command window: SET ORACLE_SID=TEST HTH Volker Schoen E-Mail: mailto:[EMAIL PROTECTED] http://www.inplan.de -Ursprüngliche Nachricht- Von: Naveen Nahata [mailto:[EMAIL PROTECTED]] Gesendet: Montag, 2. September 2002 19:08 An: Multiple recipients of list ORACLE-L Betreff: Why it picks the same PFile for both the DBs Hi All, I know the problem is on NT machine, whereas most of the people in the list are working on UNIX based systems. Still any pointers will be appreciated. I have a system with 2 databases, both running on the same server. DB - 8.0.5. OS - Windows NT When i connect thru server manager to TEST, and then issue STARTUP, it starts up the PROD database using the initPROD.ora file. On querying V$instance it shows INSTANCE_NAME = TEST On querying V$database it shows NAME=PROD. So the TEST instance is mounting PROD DB using initPROD.ora When i connect to PROD and STARTUP, it starts PROD using initPROD.ora On querying V$instance it shows INSTANCE_NAME = PROD On querying V$database it shows NAME=PROD. So the PROD instance is mounting the PROD DB using initPROD.ora Why is it picking up only initPROD.ora? The following registry parameters are set... ORA_TEST_PFILE - Pointing to initTEST.ora ORA_PROD_PFILE - Pointing to initPROD.ora ORA_TEST_PWFILE - Pointing to PWDTEST.ora ORA_PROD_PWFILE - Pointing to PWDPROD.ora ORACLE_SID - PROD Though I can start the DBs easily using STARTUP pfile = 'respective_pfile', but that is not the thing. The thing is that I'm missing something, and also that I cannot start it directly through the services. What am i possibly missing?? Regards, Naveen Naveen Nahata Associate IS Email: [EMAIL PROTECTED] MindTree Consulting Pvt. Ltd. #42, Block 'A', 27th Cross, 2nd Stage, Banashankari, Bangalore - 560070 Ph: 91 (80) 6711777/6712777 Ext. 1614 Fax: 91 (80) 6714000 Web: http://www.mindtree.com/ -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Naveen Nahata INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists 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.com -- Author: INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists 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: Why it picks the same PFile for both the DBs
Hi Volker, That is OK. I can even start with STARTUP PFILE = 'appropriate_pfile'; But in this case I am unable to start it using NT Services. That is the problem. How do I get both the DBs to start by starting the services. I don't know why suddenly the problem has started coming. All registry parameters seem OK. I'm clueless. I know I'm missing something very obvious, but what is it? Regards Naveen -Original Message- Sent: Tuesday, September 03, 2002 3:23 PM To: Multiple recipients of list ORACLE-L Before starting svrmgr set the oracle_sid to test or prod In command window: SET ORACLE_SID=TEST HTH Volker Schoen E-Mail: mailto:[EMAIL PROTECTED] http://www.inplan.de -Ursprüngliche Nachricht- Von: Naveen Nahata [mailto:[EMAIL PROTECTED]] Gesendet: Montag, 2. September 2002 19:08 An: Multiple recipients of list ORACLE-L Betreff: Why it picks the same PFile for both the DBs Hi All, I know the problem is on NT machine, whereas most of the people in the list are working on UNIX based systems. Still any pointers will be appreciated. I have a system with 2 databases, both running on the same server. DB - 8.0.5. OS - Windows NT When i connect thru server manager to TEST, and then issue STARTUP, it starts up the PROD database using the initPROD.ora file. On querying V$instance it shows INSTANCE_NAME = TEST On querying V$database it shows NAME=PROD. So the TEST instance is mounting PROD DB using initPROD.ora When i connect to PROD and STARTUP, it starts PROD using initPROD.ora On querying V$instance it shows INSTANCE_NAME = PROD On querying V$database it shows NAME=PROD. So the PROD instance is mounting the PROD DB using initPROD.ora Why is it picking up only initPROD.ora? The following registry parameters are set... ORA_TEST_PFILE - Pointing to initTEST.ora ORA_PROD_PFILE - Pointing to initPROD.ora ORA_TEST_PWFILE - Pointing to PWDTEST.ora ORA_PROD_PWFILE - Pointing to PWDPROD.ora ORACLE_SID - PROD Though I can start the DBs easily using STARTUP pfile = 'respective_pfile', but that is not the thing. The thing is that I'm missing something, and also that I cannot start it directly through the services. What am i possibly missing?? Regards, Naveen Naveen Nahata Associate IS Email: [EMAIL PROTECTED] MindTree Consulting Pvt. Ltd. #42, Block 'A', 27th Cross, 2nd Stage, Banashankari, Bangalore - 560070 Ph: 91 (80) 6711777/6712777 Ext. 1614 Fax: 91 (80) 6714000 Web: http://www.mindtree.com/ -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Naveen Nahata INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists 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.com -- Author: INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists 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.com -- Author: Naveen Nahata INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists 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:
System Admin, thought you'd like at least one more :) joe RO-Hosur wrote: Please don't sent any more mail to this ID Please take it as a request Please ignore my name Mail ID : [EMAIL PROTECTED] Thanks System Admin -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Joe Testa INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists 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: Why it picks the same PFile for both the DBs
Have you tried to recreate your TEST service using :- Oradim -delete -sid TEST Oradim -new -sid TEST -intpwd password -startmode auto -pfile=d:\directory\testini.ora Lewis Bishop --- Barclays Enable/ISS/OPTS - Oracle OCP Database Consultant Phone - 020 8298 3418 Mobile - 07950 380857 Email - [EMAIL PROTECTED] -Original Message- Sent: 03 September 2002 11:23 To: Multiple recipients of list ORACLE-L This header confirms that this email message has been swept for the presence of computer viruses. Corporate IT THE WOOLWICH -- Hi Volker, That is OK. I can even start with STARTUP PFILE = 'appropriate_pfile'; But in this case I am unable to start it using NT Services. That is the problem. How do I get both the DBs to start by starting the services. I don't know why suddenly the problem has started coming. All registry parameters seem OK. I'm clueless. I know I'm missing something very obvious, but what is it? Regards Naveen -Original Message- Sent: Tuesday, September 03, 2002 3:23 PM To: Multiple recipients of list ORACLE-L Before starting svrmgr set the oracle_sid to test or prod In command window: SET ORACLE_SID=TEST HTH Volker Schoen E-Mail: mailto:[EMAIL PROTECTED] http://www.inplan.de -Ursprüngliche Nachricht- Von: Naveen Nahata [mailto:[EMAIL PROTECTED]] Gesendet: Montag, 2. September 2002 19:08 An: Multiple recipients of list ORACLE-L Betreff: Why it picks the same PFile for both the DBs Hi All, I know the problem is on NT machine, whereas most of the people in the list are working on UNIX based systems. Still any pointers will be appreciated. I have a system with 2 databases, both running on the same server. DB - 8.0.5. OS - Windows NT When i connect thru server manager to TEST, and then issue STARTUP, it starts up the PROD database using the initPROD.ora file. On querying V$instance it shows INSTANCE_NAME = TEST On querying V$database it shows NAME=PROD. So the TEST instance is mounting PROD DB using initPROD.ora When i connect to PROD and STARTUP, it starts PROD using initPROD.ora On querying V$instance it shows INSTANCE_NAME = PROD On querying V$database it shows NAME=PROD. So the PROD instance is mounting the PROD DB using initPROD.ora Why is it picking up only initPROD.ora? The following registry parameters are set... ORA_TEST_PFILE - Pointing to initTEST.ora ORA_PROD_PFILE - Pointing to initPROD.ora ORA_TEST_PWFILE - Pointing to PWDTEST.ora ORA_PROD_PWFILE - Pointing to PWDPROD.ora ORACLE_SID - PROD Though I can start the DBs easily using STARTUP pfile = 'respective_pfile', but that is not the thing. The thing is that I'm missing something, and also that I cannot start it directly through the services. What am i possibly missing?? Regards, Naveen Naveen Nahata Associate IS Email: [EMAIL PROTECTED] MindTree Consulting Pvt. Ltd. #42, Block 'A', 27th Cross, 2nd Stage, Banashankari, Bangalore - 560070 Ph: 91 (80) 6711777/6712777 Ext. 1614 Fax: 91 (80) 6714000 Web: http://www.mindtree.com/ -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Naveen Nahata INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists 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.com -- Author: INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists 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.com -- Author: Naveen Nahata INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists 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
AW: Why it picks the same PFile for both the DBs
Did you check the registry settings for HKLM\Software\Oracle and HKLM\System\currentcontrolset\services? Volker Schoen E-Mail: mailto:[EMAIL PROTECTED] http://www.inplan.de -Ursprüngliche Nachricht- Von: Naveen Nahata [mailto:[EMAIL PROTECTED]] Gesendet: Dienstag, 3. September 2002 12:23 An: Multiple recipients of list ORACLE-L Betreff: RE: Why it picks the same PFile for both the DBs Hi Volker, That is OK. I can even start with STARTUP PFILE = 'appropriate_pfile'; But in this case I am unable to start it using NT Services. That is the problem. How do I get both the DBs to start by starting the services. I don't know why suddenly the problem has started coming. All registry parameters seem OK. I'm clueless. I know I'm missing something very obvious, but what is it? Regards Naveen -Original Message- Sent: Tuesday, September 03, 2002 3:23 PM To: Multiple recipients of list ORACLE-L Before starting svrmgr set the oracle_sid to test or prod In command window: SET ORACLE_SID=TEST HTH Volker Schoen E-Mail: mailto:[EMAIL PROTECTED] http://www.inplan.de -Ursprüngliche Nachricht- Von: Naveen Nahata [mailto:[EMAIL PROTECTED]] Gesendet: Montag, 2. September 2002 19:08 An: Multiple recipients of list ORACLE-L Betreff: Why it picks the same PFile for both the DBs Hi All, I know the problem is on NT machine, whereas most of the people in the list are working on UNIX based systems. Still any pointers will be appreciated. I have a system with 2 databases, both running on the same server. DB - 8.0.5. OS - Windows NT When i connect thru server manager to TEST, and then issue STARTUP, it starts up the PROD database using the initPROD.ora file. On querying V$instance it shows INSTANCE_NAME = TEST On querying V$database it shows NAME=PROD. So the TEST instance is mounting PROD DB using initPROD.ora When i connect to PROD and STARTUP, it starts PROD using initPROD.ora On querying V$instance it shows INSTANCE_NAME = PROD On querying V$database it shows NAME=PROD. So the PROD instance is mounting the PROD DB using initPROD.ora Why is it picking up only initPROD.ora? The following registry parameters are set... ORA_TEST_PFILE - Pointing to initTEST.ora ORA_PROD_PFILE - Pointing to initPROD.ora ORA_TEST_PWFILE - Pointing to PWDTEST.ora ORA_PROD_PWFILE - Pointing to PWDPROD.ora ORACLE_SID - PROD Though I can start the DBs easily using STARTUP pfile = 'respective_pfile', but that is not the thing. The thing is that I'm missing something, and also that I cannot start it directly through the services. What am i possibly missing?? Regards, Naveen Naveen Nahata Associate IS Email: [EMAIL PROTECTED] MindTree Consulting Pvt. Ltd. #42, Block 'A', 27th Cross, 2nd Stage, Banashankari, Bangalore - 560070 Ph: 91 (80) 6711777/6712777 Ext. 1614 Fax: 91 (80) 6714000 Web: http://www.mindtree.com/ -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Naveen Nahata INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists 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.com -- Author: INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists 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.com -- Author: Naveen Nahata INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists 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.com -- Author: INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego,
RE: General Replication question
Well, the flood of responses (not) to this topic probably answers one of the points raised! While endorsing all that Dennis has stated, I would just like to add something. Most crucially, replication is an exercise in logic, which fundamentally depends on getting your database design correct on both (or all) instances. If one site has an indadequately defined model, then sure as fate, replication will uncover the weakness sooner or later in the form of corrupt data or a failed replication transaction. Which provides a useful side benefit, by the way. We have been running replication for 15 years. In-house system. Slowly and incrementally improved over the years. Why replicate? Because we had such a poor wan, that transactions across it were highly problematic. Easier to have a couple of instances, and replicate between them each night. Now we have three big sites, and murmurs between them in the dead of night ensure everything is maintained synchronous... The point about checking that replication has worked in very important. I spent a lot of time building up an ever-increasingly complex array of exception reports. No emails in the morning - all's well. Hey, but replication is great for carrying out major data migrations! peter edinburgh -Original Message- From: DENNIS WILLIAMS [mailto:[EMAIL PROTECTED]] Sent: 26 August 2002 19:19 To: Multiple recipients of list ORACLE-L Subject: RE: General Replication question Ed - We have flirted with the replication thing here for some time. I have had the same questions as you, trying to take classes, for example. I don't think replication is widely used, but there are plenty of sites out there. snip * 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.com -- Author: Robson, Peter INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists 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).
off-topic -UNIX question
Hello all, I get a process id by calling getProcessId()..I want to know from program whether that processId exists. Any way to find out how ? Thanks and regards, Santosh
RMAN-20035 error
HI ALL, I am using RMAN with the catalog on a NT Box and 2 targets databases on Netware boxes. First a made a whole backup on one database (it was OK), after I changed the target database on the command line of RMAN. I got this error : RMAN-03002: failure during compilation of command RMAN-03013: command type: backup RMAN-03014: implicit resync of recovery catalog failed RMAN-06004: ORACLE error from recovery catalog database: RMAN-20035: invalid high recid. Consulting Metalink they propose to make a RECOVER UNTIL CANCEL and open the database with open resetlogs option. But I could'nt do that at this moment. the databases are running well. Is there any other solution to go over this RMAN error without making a recovery of the database. Thank you Kamel Benlatreche -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: GL2Z/ INF DBA BENLATRECHE INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists 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: Why it picks the same PFile for both the DBs
Lewis, Ya that is the only logical thing left to do. Haven't done it as yet because all the registry parameters seemed fine and ORADIM does nothing more than creating a few registry entries. But ORADIM knows more than me what registry parameters to set, so i'll give it a shot. Regards, Naveen -Original Message- Sent: Tuesday, September 03, 2002 4:33 PM To: Multiple recipients of list ORACLE-L Have you tried to recreate your TEST service using :- Oradim -delete -sid TEST Oradim -new -sid TEST -intpwd password -startmode auto -pfile=d:\directory\testini.ora Lewis Bishop --- Barclays Enable/ISS/OPTS - Oracle OCP Database Consultant Phone - 020 8298 3418 Mobile - 07950 380857 Email - [EMAIL PROTECTED] -Original Message- Sent: 03 September 2002 11:23 To: Multiple recipients of list ORACLE-L This header confirms that this email message has been swept for the presence of computer viruses. Corporate IT THE WOOLWICH -- Hi Volker, That is OK. I can even start with STARTUP PFILE = 'appropriate_pfile'; But in this case I am unable to start it using NT Services. That is the problem. How do I get both the DBs to start by starting the services. I don't know why suddenly the problem has started coming. All registry parameters seem OK. I'm clueless. I know I'm missing something very obvious, but what is it? Regards Naveen -Original Message- Sent: Tuesday, September 03, 2002 3:23 PM To: Multiple recipients of list ORACLE-L Before starting svrmgr set the oracle_sid to test or prod In command window: SET ORACLE_SID=TEST HTH Volker Schoen E-Mail: mailto:[EMAIL PROTECTED] http://www.inplan.de -Ursprüngliche Nachricht- Von: Naveen Nahata [mailto:[EMAIL PROTECTED]] Gesendet: Montag, 2. September 2002 19:08 An: Multiple recipients of list ORACLE-L Betreff: Why it picks the same PFile for both the DBs Hi All, I know the problem is on NT machine, whereas most of the people in the list are working on UNIX based systems. Still any pointers will be appreciated. I have a system with 2 databases, both running on the same server. DB - 8.0.5. OS - Windows NT When i connect thru server manager to TEST, and then issue STARTUP, it starts up the PROD database using the initPROD.ora file. On querying V$instance it shows INSTANCE_NAME = TEST On querying V$database it shows NAME=PROD. So the TEST instance is mounting PROD DB using initPROD.ora When i connect to PROD and STARTUP, it starts PROD using initPROD.ora On querying V$instance it shows INSTANCE_NAME = PROD On querying V$database it shows NAME=PROD. So the PROD instance is mounting the PROD DB using initPROD.ora Why is it picking up only initPROD.ora? The following registry parameters are set... ORA_TEST_PFILE - Pointing to initTEST.ora ORA_PROD_PFILE - Pointing to initPROD.ora ORA_TEST_PWFILE - Pointing to PWDTEST.ora ORA_PROD_PWFILE - Pointing to PWDPROD.ora ORACLE_SID - PROD Though I can start the DBs easily using STARTUP pfile = 'respective_pfile', but that is not the thing. The thing is that I'm missing something, and also that I cannot start it directly through the services. What am i possibly missing?? Regards, Naveen Naveen Nahata Associate IS Email: [EMAIL PROTECTED] MindTree Consulting Pvt. Ltd. #42, Block 'A', 27th Cross, 2nd Stage, Banashankari, Bangalore - 560070 Ph: 91 (80) 6711777/6712777 Ext. 1614 Fax: 91 (80) 6714000 Web: http://www.mindtree.com/ -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Naveen Nahata INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists 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.com -- Author: INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists 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.com -- Author: Naveen Nahata INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051
RE:
-Please don't sent any more mail to this ID -Please take it as a request -Please ignore my name There is no excape. Leaving the list is futile. Your efforts will be crushed. You will be assimilated into the Borg. You shall be called one of none. The Borg -Original Message- Sent: Tuesday, September 03, 2002 1:08 AM To: Multiple recipients of list ORACLE-L Subject: Please don't sent any more mail to this ID Please take it as a request Please ignore my name Mail ID : [EMAIL PROTECTED] Thanks System Admin -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: RO-Hosur INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists 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.com -- Author: Farnsworth, Dave INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists 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: Why it picks the same PFile for both the DBs
Forgot to mention in the previous post. Rather DELETE and then CREATE, I think the best will be to edit the INSTANCE using ORADIM, isn't it? Naveen -Original Message- Sent: Tuesday, September 03, 2002 4:33 PM To: Multiple recipients of list ORACLE-L Have you tried to recreate your TEST service using :- Oradim -delete -sid TEST Oradim -new -sid TEST -intpwd password -startmode auto -pfile=d:\directory\testini.ora Lewis Bishop --- Barclays Enable/ISS/OPTS - Oracle OCP Database Consultant Phone - 020 8298 3418 Mobile - 07950 380857 Email - [EMAIL PROTECTED] -Original Message- Sent: 03 September 2002 11:23 To: Multiple recipients of list ORACLE-L This header confirms that this email message has been swept for the presence of computer viruses. Corporate IT THE WOOLWICH -- Hi Volker, That is OK. I can even start with STARTUP PFILE = 'appropriate_pfile'; But in this case I am unable to start it using NT Services. That is the problem. How do I get both the DBs to start by starting the services. I don't know why suddenly the problem has started coming. All registry parameters seem OK. I'm clueless. I know I'm missing something very obvious, but what is it? Regards Naveen -Original Message- Sent: Tuesday, September 03, 2002 3:23 PM To: Multiple recipients of list ORACLE-L Before starting svrmgr set the oracle_sid to test or prod In command window: SET ORACLE_SID=TEST HTH Volker Schoen E-Mail: mailto:[EMAIL PROTECTED] http://www.inplan.de -Ursprüngliche Nachricht- Von: Naveen Nahata [mailto:[EMAIL PROTECTED]] Gesendet: Montag, 2. September 2002 19:08 An: Multiple recipients of list ORACLE-L Betreff: Why it picks the same PFile for both the DBs Hi All, I know the problem is on NT machine, whereas most of the people in the list are working on UNIX based systems. Still any pointers will be appreciated. I have a system with 2 databases, both running on the same server. DB - 8.0.5. OS - Windows NT When i connect thru server manager to TEST, and then issue STARTUP, it starts up the PROD database using the initPROD.ora file. On querying V$instance it shows INSTANCE_NAME = TEST On querying V$database it shows NAME=PROD. So the TEST instance is mounting PROD DB using initPROD.ora When i connect to PROD and STARTUP, it starts PROD using initPROD.ora On querying V$instance it shows INSTANCE_NAME = PROD On querying V$database it shows NAME=PROD. So the PROD instance is mounting the PROD DB using initPROD.ora Why is it picking up only initPROD.ora? The following registry parameters are set... ORA_TEST_PFILE - Pointing to initTEST.ora ORA_PROD_PFILE - Pointing to initPROD.ora ORA_TEST_PWFILE - Pointing to PWDTEST.ora ORA_PROD_PWFILE - Pointing to PWDPROD.ora ORACLE_SID - PROD Though I can start the DBs easily using STARTUP pfile = 'respective_pfile', but that is not the thing. The thing is that I'm missing something, and also that I cannot start it directly through the services. What am i possibly missing?? Regards, Naveen Naveen Nahata Associate IS Email: [EMAIL PROTECTED] MindTree Consulting Pvt. Ltd. #42, Block 'A', 27th Cross, 2nd Stage, Banashankari, Bangalore - 560070 Ph: 91 (80) 6711777/6712777 Ext. 1614 Fax: 91 (80) 6714000 Web: http://www.mindtree.com/ -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Naveen Nahata INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists 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.com -- Author: INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists 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.com -- Author: Naveen Nahata INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To
Re: off-topic -UNIX question
Hello
Processes can test the validity of PID's by signalling the PID
with a ZERO signal number. To signal the process you use the
kill() system call. When a zero signal number is used error checking
is done but no signal is sent so that you don't have to worry
about what the process is going to do with a untrapped or
ill-defined interrupt handler
eg.
#include stdio.h
#include signal.h
main(int argc, char *argv[])
{
int pidno = atoi(argv[1]);
if(kill(pidno,0) == 0)
printf(pid number %d exists\n,pidno);
else
printf(pid number %d does not exist (or does not belong to
you)\n,pidno);
exit(0);
}
The above code will check the pid passed as the first command
line argument to the program. Note that you can't signal arbitrary
processes unless you are the superuser or the process is owned by
your userid.
HTH
Jeff Herrick
On Tue, 3 Sep 2002, Santosh Varma wrote:
Hello all,
I get a process id by calling getProcessId()..I want to know from
program whether that processId exists.
Any way to find out how ?
Thanks and regards,
Santosh
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: Jeff Herrick
INET: [EMAIL PROTECTED]
Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051
San Diego, California-- Public Internet access / Mailing Lists
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: off-topic -UNIX question
in the shell enter echo $$. didn't indicate in what application. kr mr [EMAIL PROTECTED] 09/03/02 13:29 PM Hello all, I get a process id by calling getProcessId()..I want to know from program whether that processId exists. Any way to find out how ? Thanks and regards, Santosh -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Markus Reger INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists 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: Disabling indexes - temporarily
Thanks a lot to all who have contributed their experiences and ideas for thsi problem. I have to look into the application and business details whether this is feasible or not. Have to talk to our tech head. Marul. - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Monday, September 02, 2002 6:48 PM Iain, thats a fantastic idea. Naveen -Original Message- Sent: Monday, September 02, 2002 5:28 PM To: Multiple recipients of list ORACLE-L Could you have a trigger which before insert, inserts into another empty table with exactly same layout but rejects the insert on the main table. Then disables the trigger and adds these at a non-busy stage and reenables the trigger. Would be a whole lot quicker if it's possible. -Original Message- Sent: Monday, September 02, 2002 11:38 AM To: Multiple recipients of list ORACLE-L Thanks Naveen, Lets forget about the statistics and performance, but I have such type of requirenment than is there any way out ? Marul. - Original Message - To: Multiple recipients of list ORACLE-L mailto:[EMAIL PROTECTED] Sent: Saturday, August 31, 2002 11:58 PM Firstly, you are only inserting 100-400 records daily, which is not a big deal. Even if there was a way to stop the indexes from getting updated, it won't increase the performance by a noticable amount. Secondly, there is no way(as far as i know) to make the indexes READ-ONLY with the table in READ-WRITE mode. Thirdly, rebuilding 20 indexes on a table with 1 million record will take a long time, in comparison updation by 100-400 records is nothing. It neither feasible nor advisable. Naveen -Original Message- Sent: Saturday, August 31, 2002 11:08 PM To: Multiple recipients of list ORACLE-L Hi all, Need to know if the following is possible in Oracle(any version):- I have a table of around (a) 30 Columns (b) 20 out of 30 are indexed (c) around 1 million (1,000,000) records. Most of the time there will be heavy reads (select queries) on this table except for some 100-400 records to be inserted in a day. The newly inserted records will not be selected by the queries for the next 24 hours (this is based on some business logic), thats for sure. Now the problem is when ever a record(s) is inserted the entire bunch of indexes is updated/rebuild by the Oracle which considerably slows down the throughput of the system during that period of time (until all indexes are updated). Can we have a solution whereby indexes should not be updated when a record(s) is inserted, because I know that these records will not be the part of the query for the next 24 hrs. The indexes will be re-built manually/scheduled during the off-peak hours once a day. In this way, the next day, new records inserted a day before will be ready to be fetched by the queries. Note- I can't put my indexes offline not for a single minute during peak hours. Any clues? TIA, Marul. -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Nicoll, Iain \(Calanais\) INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists 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.com -- Author: Naveen Nahata INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists 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.com -- Author: Marul Mehta INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists 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
RE: Why it picks the same PFile for both the DBs
Personally, I never bother with edit... oradim is pretty simplistic... that's just me though... Lewis Bishop --- Barclays Enable/ISS/OPTS - Oracle OCP Database Consultant Phone - 020 8298 3418 Mobile - 07950 380857 Email - [EMAIL PROTECTED] -Original Message- Sent: 03 September 2002 13:28 To: Multiple recipients of list ORACLE-L This header confirms that this email message has been swept for the presence of computer viruses. Corporate IT THE WOOLWICH -- Forgot to mention in the previous post. Rather DELETE and then CREATE, I think the best will be to edit the INSTANCE using ORADIM, isn't it? Naveen -Original Message- Sent: Tuesday, September 03, 2002 4:33 PM To: Multiple recipients of list ORACLE-L Have you tried to recreate your TEST service using :- Oradim -delete -sid TEST Oradim -new -sid TEST -intpwd password -startmode auto -pfile=d:\directory\testini.ora Lewis Bishop --- Barclays Enable/ISS/OPTS - Oracle OCP Database Consultant Phone - 020 8298 3418 Mobile - 07950 380857 Email - [EMAIL PROTECTED] -Original Message- Sent: 03 September 2002 11:23 To: Multiple recipients of list ORACLE-L This header confirms that this email message has been swept for the presence of computer viruses. Corporate IT THE WOOLWICH -- Hi Volker, That is OK. I can even start with STARTUP PFILE = 'appropriate_pfile'; But in this case I am unable to start it using NT Services. That is the problem. How do I get both the DBs to start by starting the services. I don't know why suddenly the problem has started coming. All registry parameters seem OK. I'm clueless. I know I'm missing something very obvious, but what is it? Regards Naveen -Original Message- Sent: Tuesday, September 03, 2002 3:23 PM To: Multiple recipients of list ORACLE-L Before starting svrmgr set the oracle_sid to test or prod In command window: SET ORACLE_SID=TEST HTH Volker Schoen E-Mail: mailto:[EMAIL PROTECTED] http://www.inplan.de -Ursprüngliche Nachricht- Von: Naveen Nahata [mailto:[EMAIL PROTECTED]] Gesendet: Montag, 2. September 2002 19:08 An: Multiple recipients of list ORACLE-L Betreff: Why it picks the same PFile for both the DBs Hi All, I know the problem is on NT machine, whereas most of the people in the list are working on UNIX based systems. Still any pointers will be appreciated. I have a system with 2 databases, both running on the same server. DB - 8.0.5. OS - Windows NT When i connect thru server manager to TEST, and then issue STARTUP, it starts up the PROD database using the initPROD.ora file. On querying V$instance it shows INSTANCE_NAME = TEST On querying V$database it shows NAME=PROD. So the TEST instance is mounting PROD DB using initPROD.ora When i connect to PROD and STARTUP, it starts PROD using initPROD.ora On querying V$instance it shows INSTANCE_NAME = PROD On querying V$database it shows NAME=PROD. So the PROD instance is mounting the PROD DB using initPROD.ora Why is it picking up only initPROD.ora? The following registry parameters are set... ORA_TEST_PFILE - Pointing to initTEST.ora ORA_PROD_PFILE - Pointing to initPROD.ora ORA_TEST_PWFILE - Pointing to PWDTEST.ora ORA_PROD_PWFILE - Pointing to PWDPROD.ora ORACLE_SID - PROD Though I can start the DBs easily using STARTUP pfile = 'respective_pfile', but that is not the thing. The thing is that I'm missing something, and also that I cannot start it directly through the services. What am i possibly missing?? Regards, Naveen Naveen Nahata Associate IS Email: [EMAIL PROTECTED] MindTree Consulting Pvt. Ltd. #42, Block 'A', 27th Cross, 2nd Stage, Banashankari, Bangalore - 560070 Ph: 91 (80) 6711777/6712777 Ext. 1614 Fax: 91 (80) 6714000 Web: http://www.mindtree.com/ -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Naveen Nahata INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists 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.com -- Author: INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of
RE: Why it picks the same PFile for both the DBs
Naveen, I would try edit first. If it doesn't work, then delete and create. It is no big deal - delete does not touch the database files at all - just the windows directory. Tom Mercadante Oracle Certified Professional -Original Message- Sent: Tuesday, September 03, 2002 8:28 AM To: Multiple recipients of list ORACLE-L Forgot to mention in the previous post. Rather DELETE and then CREATE, I think the best will be to edit the INSTANCE using ORADIM, isn't it? Naveen -Original Message- Sent: Tuesday, September 03, 2002 4:33 PM To: Multiple recipients of list ORACLE-L Have you tried to recreate your TEST service using :- Oradim -delete -sid TEST Oradim -new -sid TEST -intpwd password -startmode auto -pfile=d:\directory\testini.ora Lewis Bishop --- Barclays Enable/ISS/OPTS - Oracle OCP Database Consultant Phone - 020 8298 3418 Mobile - 07950 380857 Email - [EMAIL PROTECTED] -Original Message- Sent: 03 September 2002 11:23 To: Multiple recipients of list ORACLE-L This header confirms that this email message has been swept for the presence of computer viruses. Corporate IT THE WOOLWICH -- Hi Volker, That is OK. I can even start with STARTUP PFILE = 'appropriate_pfile'; But in this case I am unable to start it using NT Services. That is the problem. How do I get both the DBs to start by starting the services. I don't know why suddenly the problem has started coming. All registry parameters seem OK. I'm clueless. I know I'm missing something very obvious, but what is it? Regards Naveen -Original Message- Sent: Tuesday, September 03, 2002 3:23 PM To: Multiple recipients of list ORACLE-L Before starting svrmgr set the oracle_sid to test or prod In command window: SET ORACLE_SID=TEST HTH Volker Schoen E-Mail: mailto:[EMAIL PROTECTED] http://www.inplan.de -Ursprüngliche Nachricht- Von: Naveen Nahata [mailto:[EMAIL PROTECTED]] Gesendet: Montag, 2. September 2002 19:08 An: Multiple recipients of list ORACLE-L Betreff: Why it picks the same PFile for both the DBs Hi All, I know the problem is on NT machine, whereas most of the people in the list are working on UNIX based systems. Still any pointers will be appreciated. I have a system with 2 databases, both running on the same server. DB - 8.0.5. OS - Windows NT When i connect thru server manager to TEST, and then issue STARTUP, it starts up the PROD database using the initPROD.ora file. On querying V$instance it shows INSTANCE_NAME = TEST On querying V$database it shows NAME=PROD. So the TEST instance is mounting PROD DB using initPROD.ora When i connect to PROD and STARTUP, it starts PROD using initPROD.ora On querying V$instance it shows INSTANCE_NAME = PROD On querying V$database it shows NAME=PROD. So the PROD instance is mounting the PROD DB using initPROD.ora Why is it picking up only initPROD.ora? The following registry parameters are set... ORA_TEST_PFILE - Pointing to initTEST.ora ORA_PROD_PFILE - Pointing to initPROD.ora ORA_TEST_PWFILE - Pointing to PWDTEST.ora ORA_PROD_PWFILE - Pointing to PWDPROD.ora ORACLE_SID - PROD Though I can start the DBs easily using STARTUP pfile = 'respective_pfile', but that is not the thing. The thing is that I'm missing something, and also that I cannot start it directly through the services. What am i possibly missing?? Regards, Naveen Naveen Nahata Associate IS Email: [EMAIL PROTECTED] MindTree Consulting Pvt. Ltd. #42, Block 'A', 27th Cross, 2nd Stage, Banashankari, Bangalore - 560070 Ph: 91 (80) 6711777/6712777 Ext. 1614 Fax: 91 (80) 6714000 Web: http://www.mindtree.com/ -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Naveen Nahata INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists 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.com -- Author: INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists 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
Re: Velpuri bkup script-syntax error
Been a while since I used them, but I had to go through the file and remove some non-printable characters. You can do this pretty quickly with any number of text utilities. Do yourself a favor and use RMAN for backups. You can script any variety of backups fairly easily once you work your way through the manual. Glenn On Monday 02 September 2002 10:38 pm, you wrote: Did anyone else run into the error dbbackup_begin: syntax error at line 521 'end of file' unexpected after trying to use the Velpuri backup scripts for Unix? Where was the problem at and how did you resolve it? I downloaded the scripts from the Osborne website and this is the error that keeps occuring. Before trying to set up some other scripts, I wanted to give the Velpuri scripts one more shot. Thanks. -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Glenn Stauffer INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists 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: Disabling indexes - temporarily
If adding 400 records slows down the system, I guess it is time to take a hard look at your set-up and see where the problems are occurring by measuring and analyzing the waits in the database. How did you establish that balancing indexes causes the delay? No matter what kind of solution you implement, I'd still look at the waits that you see during these inserts, that will explain the problem to you. To me, 400 rows causing problem hints at a bigger problem than one visible. Raj __ Rajendra Jamadagni MIS, ESPN Inc. Rajendra dot Jamadagni at ESPN dot com Any opinion expressed here is personal and doesn't reflect that of ESPN Inc. QOTD: Any clod can have facts, but 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
Re: SQL*Plus in 10i
Concerning gqlplus I spoke to the author about this issue and he is supposed to be addressing it. I still find it useful and hope he fixes this problem soon. -D- On Fri, 30 Aug 2002 12:58:26 -0800 [EMAIL PROTECTED] wrote: Just tried it. Took 5 minutes to break it with this: set sqlprompt 'umachine - who_var@instance_var SQL ' Jared Orr, Steve [EMAIL PROTECTED] Sent by: [EMAIL PROTECTED] 08/30/2002 01:28 PM Please respond to ORACLE-L To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] cc: Subject:RE: SQL*Plus in 10i BTW: Anybody's using gqlplus? Whoa, I've been looking for something like this. Is it any good? -- Dwayne Cox Oracle Database Administrator Info Tech, Inc. 5700 SW 34th Street, Suite 1235 Gainesville, FL 32608 email: [EMAIL PROTECTED] phone: 352.381.4521 fax: 352.381. -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Dwayne Cox INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists 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: off-topic -UNIX question
-- Santosh Varma [EMAIL PROTECTED] Hello all, I get a process id by calling getProcessId()..I want to know from program whether that processId exists. Any way to find out how ? Depends on the O/s. Simplest way is to grep the output of ps. On systems with a /proc file system (e.g., linux, Solaris) you can look for /proc/$id. -- Steven Lembark 2930 W. Palmer Workhorse Computing Chicago, IL 60647 +1 800 762 1582 -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Steven Lembark INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists 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: woo hoo
Now that it is public, Mazel Tov! -Ari -Original Message- Carmichael Sent: Saturday, August 31, 2002 6:53 AM To: Multiple recipients of list ORACLE-L sigh, this is what comes of answering mail when I am tired... this was meant to go private. Not that I didn't want to share this information, but it is definitely OFF TOPIC. My apologies --- Rachel Carmichael [EMAIL PROTECTED] wrote: MY SISTER IS PREGNANT!!! Early days yet, and she will be very very very careful for a long time yet but she's pregnant! Rachel __ Do You Yahoo!? Yahoo! Finance - Get real-time stock quotes http://finance.yahoo.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Rachel Carmichael INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists 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! Finance - Get real-time stock quotes http://finance.yahoo.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Rachel Carmichael INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists 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.com -- Author: Ari Kaplan INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists 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: Why it picks the same PFile for both the DBs
Ya, Thanx everyone for the ideas. I think the problem must be the creation of password files, because after that you have to register the password file with the instance using ORADIM. The problem was not big but what and why is more important to me now Thanx Naveen -Original Message- Sent: Tuesday, September 03, 2002 6:58 PM To: Multiple recipients of list ORACLE-L Naveen, I would try edit first. If it doesn't work, then delete and create. It is no big deal - delete does not touch the database files at all - just the windows directory. Tom Mercadante Oracle Certified Professional -Original Message- Sent: Tuesday, September 03, 2002 8:28 AM To: Multiple recipients of list ORACLE-L Forgot to mention in the previous post. Rather DELETE and then CREATE, I think the best will be to edit the INSTANCE using ORADIM, isn't it? Naveen -Original Message- Sent: Tuesday, September 03, 2002 4:33 PM To: Multiple recipients of list ORACLE-L Have you tried to recreate your TEST service using :- Oradim -delete -sid TEST Oradim -new -sid TEST -intpwd password -startmode auto -pfile=d:\directory\testini.ora Lewis Bishop --- Barclays Enable/ISS/OPTS - Oracle OCP Database Consultant Phone - 020 8298 3418 Mobile - 07950 380857 Email - [EMAIL PROTECTED] -Original Message- Sent: 03 September 2002 11:23 To: Multiple recipients of list ORACLE-L This header confirms that this email message has been swept for the presence of computer viruses. Corporate IT THE WOOLWICH -- Hi Volker, That is OK. I can even start with STARTUP PFILE = 'appropriate_pfile'; But in this case I am unable to start it using NT Services. That is the problem. How do I get both the DBs to start by starting the services. I don't know why suddenly the problem has started coming. All registry parameters seem OK. I'm clueless. I know I'm missing something very obvious, but what is it? Regards Naveen -Original Message- Sent: Tuesday, September 03, 2002 3:23 PM To: Multiple recipients of list ORACLE-L Before starting svrmgr set the oracle_sid to test or prod In command window: SET ORACLE_SID=TEST HTH Volker Schoen E-Mail: mailto:[EMAIL PROTECTED] http://www.inplan.de -Ursprüngliche Nachricht- Von: Naveen Nahata [mailto:[EMAIL PROTECTED]] Gesendet: Montag, 2. September 2002 19:08 An: Multiple recipients of list ORACLE-L Betreff: Why it picks the same PFile for both the DBs Hi All, I know the problem is on NT machine, whereas most of the people in the list are working on UNIX based systems. Still any pointers will be appreciated. I have a system with 2 databases, both running on the same server. DB - 8.0.5. OS - Windows NT When i connect thru server manager to TEST, and then issue STARTUP, it starts up the PROD database using the initPROD.ora file. On querying V$instance it shows INSTANCE_NAME = TEST On querying V$database it shows NAME=PROD. So the TEST instance is mounting PROD DB using initPROD.ora When i connect to PROD and STARTUP, it starts PROD using initPROD.ora On querying V$instance it shows INSTANCE_NAME = PROD On querying V$database it shows NAME=PROD. So the PROD instance is mounting the PROD DB using initPROD.ora Why is it picking up only initPROD.ora? The following registry parameters are set... ORA_TEST_PFILE - Pointing to initTEST.ora ORA_PROD_PFILE - Pointing to initPROD.ora ORA_TEST_PWFILE - Pointing to PWDTEST.ora ORA_PROD_PWFILE - Pointing to PWDPROD.ora ORACLE_SID - PROD Though I can start the DBs easily using STARTUP pfile = 'respective_pfile', but that is not the thing. The thing is that I'm missing something, and also that I cannot start it directly through the services. What am i possibly missing?? Regards, Naveen Naveen Nahata Associate IS Email: [EMAIL PROTECTED] MindTree Consulting Pvt. Ltd. #42, Block 'A', 27th Cross, 2nd Stage, Banashankari, Bangalore - 560070 Ph: 91 (80) 6711777/6712777 Ext. 1614 Fax: 91 (80) 6714000 Web: http://www.mindtree.com/ -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Naveen Nahata INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists 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.com -- Author: INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists
RE: Why it picks the same PFile for both the DBs
Naveen, Ther password file has nothing to do with ORADIM. ORADIM is used to create, modify or delete NT services for maintaining the Oracle instance on the NT box. I'm not sure what you mean by register the password file...using ORADIM. ORADIM can be used to create the service using a specific password, but it is not needed at all. Tom Mercadante Oracle Certified Professional -Original Message- Sent: Tuesday, September 03, 2002 10:58 AM To: Multiple recipients of list ORACLE-L Ya, Thanx everyone for the ideas. I think the problem must be the creation of password files, because after that you have to register the password file with the instance using ORADIM. The problem was not big but what and why is more important to me now Thanx Naveen -Original Message- Sent: Tuesday, September 03, 2002 6:58 PM To: Multiple recipients of list ORACLE-L Naveen, I would try edit first. If it doesn't work, then delete and create. It is no big deal - delete does not touch the database files at all - just the windows directory. Tom Mercadante Oracle Certified Professional -Original Message- Sent: Tuesday, September 03, 2002 8:28 AM To: Multiple recipients of list ORACLE-L Forgot to mention in the previous post. Rather DELETE and then CREATE, I think the best will be to edit the INSTANCE using ORADIM, isn't it? Naveen -Original Message- Sent: Tuesday, September 03, 2002 4:33 PM To: Multiple recipients of list ORACLE-L Have you tried to recreate your TEST service using :- Oradim -delete -sid TEST Oradim -new -sid TEST -intpwd password -startmode auto -pfile=d:\directory\testini.ora Lewis Bishop --- Barclays Enable/ISS/OPTS - Oracle OCP Database Consultant Phone - 020 8298 3418 Mobile - 07950 380857 Email - [EMAIL PROTECTED] -Original Message- Sent: 03 September 2002 11:23 To: Multiple recipients of list ORACLE-L This header confirms that this email message has been swept for the presence of computer viruses. Corporate IT THE WOOLWICH -- Hi Volker, That is OK. I can even start with STARTUP PFILE = 'appropriate_pfile'; But in this case I am unable to start it using NT Services. That is the problem. How do I get both the DBs to start by starting the services. I don't know why suddenly the problem has started coming. All registry parameters seem OK. I'm clueless. I know I'm missing something very obvious, but what is it? Regards Naveen -Original Message- Sent: Tuesday, September 03, 2002 3:23 PM To: Multiple recipients of list ORACLE-L Before starting svrmgr set the oracle_sid to test or prod In command window: SET ORACLE_SID=TEST HTH Volker Schoen E-Mail: mailto:[EMAIL PROTECTED] http://www.inplan.de -Ursprüngliche Nachricht- Von: Naveen Nahata [mailto:[EMAIL PROTECTED]] Gesendet: Montag, 2. September 2002 19:08 An: Multiple recipients of list ORACLE-L Betreff: Why it picks the same PFile for both the DBs Hi All, I know the problem is on NT machine, whereas most of the people in the list are working on UNIX based systems. Still any pointers will be appreciated. I have a system with 2 databases, both running on the same server. DB - 8.0.5. OS - Windows NT When i connect thru server manager to TEST, and then issue STARTUP, it starts up the PROD database using the initPROD.ora file. On querying V$instance it shows INSTANCE_NAME = TEST On querying V$database it shows NAME=PROD. So the TEST instance is mounting PROD DB using initPROD.ora When i connect to PROD and STARTUP, it starts PROD using initPROD.ora On querying V$instance it shows INSTANCE_NAME = PROD On querying V$database it shows NAME=PROD. So the PROD instance is mounting the PROD DB using initPROD.ora Why is it picking up only initPROD.ora? The following registry parameters are set... ORA_TEST_PFILE - Pointing to initTEST.ora ORA_PROD_PFILE - Pointing to initPROD.ora ORA_TEST_PWFILE - Pointing to PWDTEST.ora ORA_PROD_PWFILE - Pointing to PWDPROD.ora ORACLE_SID - PROD Though I can start the DBs easily using STARTUP pfile = 'respective_pfile', but that is not the thing. The thing is that I'm missing something, and also that I cannot start it directly through the services. What am i possibly missing?? Regards, Naveen Naveen Nahata Associate IS Email: [EMAIL PROTECTED] MindTree Consulting Pvt. Ltd. #42, Block 'A', 27th Cross, 2nd Stage, Banashankari, Bangalore - 560070 Ph: 91 (80) 6711777/6712777 Ext. 1614 Fax: 91 (80) 6714000 Web: http://www.mindtree.com/ -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Naveen Nahata INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL
Re: off-topic -UNIX question
hello
hey - this is a great and really comprehensive explanation.
[EMAIL PROTECTED] 09/03/02 14:36 PM
Hello
Processes can test the validity of PID's by signalling the PID
with a ZERO signal number. To signal the process you use the
kill() system call. When a zero signal number is used error checking
is done but no signal is sent so that you don't have to worry
about what the process is going to do with a untrapped or
ill-defined interrupt handler
eg.
#include stdio.h
#include signal.h
main(int argc, char *argv[])
{
int pidno = atoi(argv[1]);
if(kill(pidno,0) == 0)
printf(pid number %d exists\n,pidno);
else
printf(pid number %d does not exist (or does not belong to
you)\n,pidno);
exit(0);
}
The above code will check the pid passed as the first command
line argument to the program. Note that you can't signal arbitrary
processes unless you are the superuser or the process is owned by
your userid.
HTH
Jeff Herrick
On Tue, 3 Sep 2002, Santosh Varma wrote:
Hello all,
I get a process id by calling getProcessId()..I want to know from
program whether that processId exists.
Any way to find out how ?
Thanks and regards,
Santosh
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: Jeff Herrick
INET: [EMAIL PROTECTED]
Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051
San Diego, California-- Public Internet access / Mailing Lists
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.com
--
Author: Markus Reger
INET: [EMAIL PROTECTED]
Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051
San Diego, California-- Public Internet access / Mailing Lists
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).
automatic segment space management
time for me to ask the experts again. My data warehouse will be 9.2, with all locally managed tablespaces. We will be following what I have taken to calling the Goldilocks principle -- that of small, medium and large tablespace extent sizes, with variations in that we will separate indexes and data, and will have even more separation for our fact tables into partitioned tables and tablespaces. However, now comes the time for me to work out storage clauses. And a quick read through the docs leaves me wondering if I should just turn on automatic segment-space management and not worry about setting PCTFREE, PCTUSED and FREELIST parameters. I can't find any real information or bugs on MetaLink either. Does anyone have any experience, good OR bad, with using this feature? If you are doing data warehouse work, what are good values for the parameters if I DO use them? One fact table is likely to be highly updated (customer info) as we collect more and more specific information from customers. The rest will be, as you would expect from a DW, mostly inserts. Help? Thanks! Rachel __ Do You Yahoo!? Yahoo! Finance - Get real-time stock quotes http://finance.yahoo.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Rachel Carmichael INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists 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[2]: woo hoo
Rachel, Good news should not be kept quite. And kids are a pile of fun, especially when they aren't yours. 8-) Dick (GrandPa) Goulet Reply Separator Author: Rachel Carmichael [EMAIL PROTECTED] Date: 8/31/2002 3:53 AM sigh, this is what comes of answering mail when I am tired... this was meant to go private. Not that I didn't want to share this information, but it is definitely OFF TOPIC. My apologies --- Rachel Carmichael [EMAIL PROTECTED] wrote: MY SISTER IS PREGNANT!!! Early days yet, and she will be very very very careful for a long time yet but she's pregnant! Rachel __ Do You Yahoo!? Yahoo! Finance - Get real-time stock quotes http://finance.yahoo.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Rachel Carmichael INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists 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! Finance - Get real-time stock quotes http://finance.yahoo.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Rachel Carmichael INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists 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.com -- Author: INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists 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: automatic segment space management
Rachel - I have been using LMTs with uniform extents (Oracle-recommended variation) for a couple of years now with Oracle 8.1.6, and now 9.2. The Oracle-recommended extent sizes are 128k, 4m, and 128m. As far as PCTFREE, et. al., these are at the table level, so my opinion would be that the guidelines for these are unchanged from the pre-LMT days. The key issue with the highly updated customer table would be whether the size of the row is changing. If you can keep the row size constant, then you won't wind up with chained rows. The biggest issue facing you is whether you turn AUTOEXTEND on. I did that and have encountered relatively few problems. Well, one problem. I had tables set with large NEXT extents to minimize extents, and when one extended boy did my sys admin get excited. I changed that. A bigger issue in building your data warehouse is whether you can use the partitioning option. Most of our queries were taking more than 2 minutes and I was able to partition and bring that down below 10 seconds. The users were pretty excited. Dennis Williams DBA Lifetouch, Inc. [EMAIL PROTECTED] -Original Message- Sent: Tuesday, September 03, 2002 10:49 AM To: Multiple recipients of list ORACLE-L time for me to ask the experts again. My data warehouse will be 9.2, with all locally managed tablespaces. We will be following what I have taken to calling the Goldilocks principle -- that of small, medium and large tablespace extent sizes, with variations in that we will separate indexes and data, and will have even more separation for our fact tables into partitioned tables and tablespaces. However, now comes the time for me to work out storage clauses. And a quick read through the docs leaves me wondering if I should just turn on automatic segment-space management and not worry about setting PCTFREE, PCTUSED and FREELIST parameters. I can't find any real information or bugs on MetaLink either. Does anyone have any experience, good OR bad, with using this feature? If you are doing data warehouse work, what are good values for the parameters if I DO use them? One fact table is likely to be highly updated (customer info) as we collect more and more specific information from customers. The rest will be, as you would expect from a DW, mostly inserts. Help? Thanks! Rachel __ Do You Yahoo!? Yahoo! Finance - Get real-time stock quotes http://finance.yahoo.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Rachel Carmichael INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists 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.com -- Author: DENNIS WILLIAMS INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists 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).
query for detect redundant index
HI all, Do you've any pl/sql for detect redundant index? thanks, Adriano Freire
Great SQL Tuning Book
I stumbled on this new book the other day and bought it. I think it is terrific. We DBAs are often asked to help tune SQL statements. This book offers lots of ideas for fixing SQL statements. Most thorough explanation of how the rule-based and cost-based optimizers work that I have encountered. Even provides a chart of the most common causes of bad SQL for each optimizer, and solutions for these problems. A valuable resource for every Oracle DBA. http://www.oreilly.com/catalog/orsqltunpr/ http://www.oreilly.com/catalog/orsqltunpr/ http://search.barnesandnoble.com/booksearch/isbnInquiry.asp?userid=1G60ZMKA1 J http://search.barnesandnoble.com/booksearch/isbnInquiry.asp?userid=1G60ZMKA 1Jisbn=0596002688 isbn=0596002688 Oracle SQL Tuning Pocket Reference Mark Gurry (Co-authored Oracle Performance Tuning way back in 1993 and updated since) O'Reilly $12.95 U.S. (at this price you might buy copies for some of your developers) Edited by Jonathan Gennick who is kind enough to answer questions on this list now and then. Dennis Williams DBA Lifetouch, Inc. [EMAIL PROTECTED] -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: DENNIS WILLIAMS INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists 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: automatic segment space management
We used this method (I wish I had come up with the Goldilocks code name...) in 8.0.4 on a couple of data warehouses. The bottom line is that we had 1 space related failure (application code filled up the error log) in over 9 months of operation. Which so impressed management that they decided to cut the support budget (not enough outages/calls to help desk). There were over 150 tablespaces per database so we could perform I/O balancing, object segregation, etc. We did not follow the SAFE (3 and only 3 extent sizes in the database). Rather we used 3 extent sizes per tablespace with medium a multiple of small and large a multiple of medium. PCTFREE - set to 10 (no updates, but provided a little space just in case this changed) PCTUSED - set to 80 (same reason as above) FREELIST - set to 5 (originally designed so only 2 processes would ever concurrently insert data) Sounds like you are on the right track. -Original Message- Sent: Tuesday, September 03, 2002 9:49 AM To: Multiple recipients of list ORACLE-L time for me to ask the experts again. My data warehouse will be 9.2, with all locally managed tablespaces. We will be following what I have taken to calling the Goldilocks principle -- that of small, medium and large tablespace extent sizes, with variations in that we will separate indexes and data, and will have even more separation for our fact tables into partitioned tables and tablespaces. However, now comes the time for me to work out storage clauses. And a quick read through the docs leaves me wondering if I should just turn on automatic segment-space management and not worry about setting PCTFREE, PCTUSED and FREELIST parameters. I can't find any real information or bugs on MetaLink either. Does anyone have any experience, good OR bad, with using this feature? If you are doing data warehouse work, what are good values for the parameters if I DO use them? One fact table is likely to be highly updated (customer info) as we collect more and more specific information from customers. The rest will be, as you would expect from a DW, mostly inserts. Help? Thanks! Rachel __ Do You Yahoo!? Yahoo! Finance - Get real-time stock quotes http://finance.yahoo.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Rachel Carmichael INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists 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.com -- Author: Fink, Dan INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists 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: automatic segment space management
Dennis, who creates tables in your DB? If devs do, don't you worry that one could accidentally create a 512MB table in your 128K TS, instead of a 512KB one? I really would like to implement LMTs here, and am doing so in certain restricted instances where I'm the only one who creates the objects for that TS, but I'm a little leery of letting it go to the developers, even though I don't use AUTOEXTEND. Thx! Rich Jesse System/Database Administrator [EMAIL PROTECTED] Quad/Tech International, Sussex, WI USA -Original Message- From: DENNIS WILLIAMS [mailto:[EMAIL PROTECTED]] Sent: Tuesday, September 03, 2002 12:42 PM To: Multiple recipients of list ORACLE-L Subject: RE: automatic segment space management Rachel - I have been using LMTs with uniform extents (Oracle-recommended variation) for a couple of years now with Oracle 8.1.6, and now 9.2. The Oracle-recommended extent sizes are 128k, 4m, and 128m. As far as PCTFREE, et. al., these are at the table level, so my opinion would be that the guidelines for these are unchanged from the pre-LMT days. The key issue with the highly updated customer table would be whether the size of the row is changing. If you can keep the row size constant, then you won't wind up with chained rows. The biggest issue facing you is whether you turn AUTOEXTEND on. I did that and have encountered relatively few problems. Well, one problem. I had tables set with large NEXT extents to minimize extents, and when one extended boy did my sys admin get excited. I changed that. A bigger issue in building your data warehouse is whether you can use the partitioning option. Most of our queries were taking more than 2 minutes and I was able to partition and bring that down below 10 seconds. The users were pretty excited. Dennis Williams DBA Lifetouch, Inc. [EMAIL PROTECTED] -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Jesse, Rich INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists 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).
Adding Datafile to Primary Database
Hi, We are having an Oracle 8.1.7 database with Dataguard 2.6 on Sun Solaris. Just wanted to check, if I am required to add a datafile to the Standby Datbase Manually, if I add a datafile to ROLLBACK Segment Tablespace in the Primary database. Thanks for your time and help. CP __ The NEW Netscape 7.0 browser is now available. Upgrade now! http://channels.netscape.com/ns/browsers/download.jsp Get your own FREE, personal Netscape Mail account today at http://webmail.netscape.com/ -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists 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: automatic segment space management
Rachel, You did not say if you would be using the UNIFORM option for the LMT's. If you allow the system to choose the initial sizing there can be a lot of wasted space as the table size grows. The system will choose sizing options that you most likely would not choose. I can't find my reference to the sizes that are chosen but 16K 64K 1M and 4M sound correct. The extends will increase as the total size of the table increases. I find it easier to manage the sizes of the tables in the tablespace with uniform extent sizes that are manageable and mist likely to be filled in a reasonable time frame. For small static tables I used a multiple of the block size and tried to group similarly used table in the same tablespace. The tables that are continually growing daily I partitioned by date range and allowed the tablespace to autoextend. There is very little wasted space and the tablespaces are usually 100 % full until the next extent is needed. I accepted the default PCTFREE, PCTUSED and FREELIST parameters and I haven't seen and problems yet. I hope this helps, Ron ROR mª¿ªm [EMAIL PROTECTED] 09/03/02 11:48AM time for me to ask the experts again. My data warehouse will be 9.2, with all locally managed tablespaces. We will be following what I have taken to calling the Goldilocks principle -- that of small, medium and large tablespace extent sizes, with variations in that we will separate indexes and data, and will have even more separation for our fact tables into partitioned tables and tablespaces. However, now comes the time for me to work out storage clauses. And a quick read through the docs leaves me wondering if I should just turn on automatic segment-space management and not worry about setting PCTFREE, PCTUSED and FREELIST parameters. I can't find any real information or bugs on MetaLink either. Does anyone have any experience, good OR bad, with using this feature? If you are doing data warehouse work, what are good values for the parameters if I DO use them? One fact table is likely to be highly updated (customer info) as we collect more and more specific information from customers. The rest will be, as you would expect from a DW, mostly inserts. Help? Thanks! Rachel __ Do You Yahoo!? Yahoo! Finance - Get real-time stock quotes http://finance.yahoo.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Rachel Carmichael INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists 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.com -- Author: Ron Rogers INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists 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: Adding Datafile to Primary Database
If you add a file to a primary, you must add it to the standby, reguardless of what object had the file. -Original Message- Sent: Tuesday, September 03, 2002 1:04 PM To: Multiple recipients of list ORACLE-L Hi, We are having an Oracle 8.1.7 database with Dataguard 2.6 on Sun Solaris. Just wanted to check, if I am required to add a datafile to the Standby Datbase Manually, if I add a datafile to ROLLBACK Segment Tablespace in the Primary database. Thanks for your time and help. CP __ The NEW Netscape 7.0 browser is now available. Upgrade now! http://channels.netscape.com/ns/browsers/download.jsp Get your own FREE, personal Netscape Mail account today at http://webmail.netscape.com/ -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists 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.com -- Author: Kevin Lange INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists 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: automatic segment space management
We may end up reworking the extent sizes, right now they are smaller than those but we are still in stage one, haven't gone live yet (and I don't even want to think about what a pain it will be to change things when we do go live). Data load test coming up soon, so I'll have a better idea of what I need to change things to, if I need to change them. There is no way the customer row will remain the same size unless I change all the varchar fields to char. We have VERY sparse data as yet and expect to be able to entice customers to fill in the blanks which will cause rows to grow. We are allocating WAY more space than we need at the moment and will be closely monitoring growth (once a day data loads) and I can always turn on autoextend if I need it. But then again, I am not the DBA who will be responsible for the production site, at least not for any space issues on the production DW. Rachel --- DENNIS WILLIAMS [EMAIL PROTECTED] wrote: Rachel - I have been using LMTs with uniform extents (Oracle-recommended variation) for a couple of years now with Oracle 8.1.6, and now 9.2. The Oracle-recommended extent sizes are 128k, 4m, and 128m. As far as PCTFREE, et. al., these are at the table level, so my opinion would be that the guidelines for these are unchanged from the pre-LMT days. The key issue with the highly updated customer table would be whether the size of the row is changing. If you can keep the row size constant, then you won't wind up with chained rows. The biggest issue facing you is whether you turn AUTOEXTEND on. I did that and have encountered relatively few problems. Well, one problem. I had tables set with large NEXT extents to minimize extents, and when one extended boy did my sys admin get excited. I changed that. A bigger issue in building your data warehouse is whether you can use the partitioning option. Most of our queries were taking more than 2 minutes and I was able to partition and bring that down below 10 seconds. The users were pretty excited. Dennis Williams DBA Lifetouch, Inc. [EMAIL PROTECTED] -Original Message- Sent: Tuesday, September 03, 2002 10:49 AM To: Multiple recipients of list ORACLE-L time for me to ask the experts again. My data warehouse will be 9.2, with all locally managed tablespaces. We will be following what I have taken to calling the Goldilocks principle -- that of small, medium and large tablespace extent sizes, with variations in that we will separate indexes and data, and will have even more separation for our fact tables into partitioned tables and tablespaces. However, now comes the time for me to work out storage clauses. And a quick read through the docs leaves me wondering if I should just turn on automatic segment-space management and not worry about setting PCTFREE, PCTUSED and FREELIST parameters. I can't find any real information or bugs on MetaLink either. Does anyone have any experience, good OR bad, with using this feature? If you are doing data warehouse work, what are good values for the parameters if I DO use them? One fact table is likely to be highly updated (customer info) as we collect more and more specific information from customers. The rest will be, as you would expect from a DW, mostly inserts. Help? Thanks! Rachel __ Do You Yahoo!? Yahoo! Finance - Get real-time stock quotes http://finance.yahoo.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Rachel Carmichael INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists 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.com -- Author: DENNIS WILLIAMS INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists 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! Finance - Get real-time stock quotes http://finance.yahoo.com -- Please see the official
Re: Great SQL Tuning Book
Mark Gurry. that kinda says it all. :) --- DENNIS WILLIAMS [EMAIL PROTECTED] wrote: I stumbled on this new book the other day and bought it. I think it is terrific. We DBAs are often asked to help tune SQL statements. This book offers lots of ideas for fixing SQL statements. Most thorough explanation of how the rule-based and cost-based optimizers work that I have encountered. Even provides a chart of the most common causes of bad SQL for each optimizer, and solutions for these problems. A valuable resource for every Oracle DBA. http://www.oreilly.com/catalog/orsqltunpr/ http://www.oreilly.com/catalog/orsqltunpr/ http://search.barnesandnoble.com/booksearch/isbnInquiry.asp?userid=1G60ZMKA1 J http://search.barnesandnoble.com/booksearch/isbnInquiry.asp?userid=1G60ZMKA 1Jisbn=0596002688 isbn=0596002688 Oracle SQL Tuning Pocket Reference Mark Gurry (Co-authored Oracle Performance Tuning way back in 1993 and updated since) O'Reilly $12.95 U.S. (at this price you might buy copies for some of your developers) Edited by Jonathan Gennick who is kind enough to answer questions on this list now and then. Dennis Williams DBA Lifetouch, Inc. [EMAIL PROTECTED] -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: DENNIS WILLIAMS INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists 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! Finance - Get real-time stock quotes http://finance.yahoo.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Rachel Carmichael INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists 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: Who don't I see my posting ?
Thanks Grant I have already done this, and this is what I got: SET oracle-l MAIL You are already receiving all mailing list messages. In order to hold the messages temporarily, issue a SET listname NOMAIL command to ListGuru. SET oracle-l REPRO You are already copied on all messages which you post to this mailing list. I think I'll have to unsuscribe and subscribe to see what happens. - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Monday, September 02, 2002 6:18 PM Diego Cutrone[SMTP:[EMAIL PROTECTED]] wrote: ok, I'm sorry Joe. But I really do not see my own postings. Send a help message to [EMAIL PROTECTED], ( e.g. put HELP ORACLE-L as the subject/body). This should return some useful info about the options available to you. While the engine running this list isn't ListServ, it probably has the same features, which include the option not to be sent you own posts, etc. (Jared might be able to confirm this). Ciao Fuzzy ;-) -- Woo Hoo! - H. Simpson -- The contents of this post are my opinions only If swallowed seek medical advice (Apologies for the excess signature) This email message (and attachments) may contain information confidential to TOWER Software. If you are not the intended recipient you cannot use, distribute or copy the message or message attachments. If you are not the intended recipient, please notify the sender by return email immediately and delete all copies of the message and attachments. Opinions, conclusions and other information in this message and attachments that do not relate to the official business of TOWER Software, are not given or endorsed by it. -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Grant Allen INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists 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.com -- Author: Diego Cutrone INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists 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: automatic segment space management
Dan, Why 3 extent sizes in each tablespace? I can see how you prevented the dreaded fragmentation problem by making them multiples of one another but I don't understand why you did that. If I'm using LMTs can I still do that? I haven't really used them, I'd thought to not specify the initial and next extent sizes and just let the LMT deal with it. As for the Goldilocks name, you are more than welcome to steal, uh, borrow, it :) Rachel --- Fink, Dan [EMAIL PROTECTED] wrote: We used this method (I wish I had come up with the Goldilocks code name...) in 8.0.4 on a couple of data warehouses. The bottom line is that we had 1 space related failure (application code filled up the error log) in over 9 months of operation. Which so impressed management that they decided to cut the support budget (not enough outages/calls to help desk). There were over 150 tablespaces per database so we could perform I/O balancing, object segregation, etc. We did not follow the SAFE (3 and only 3 extent sizes in the database). Rather we used 3 extent sizes per tablespace with medium a multiple of small and large a multiple of medium. PCTFREE - set to 10 (no updates, but provided a little space just in case this changed) PCTUSED - set to 80 (same reason as above) FREELIST - set to 5 (originally designed so only 2 processes would ever concurrently insert data) Sounds like you are on the right track. -Original Message- Sent: Tuesday, September 03, 2002 9:49 AM To: Multiple recipients of list ORACLE-L time for me to ask the experts again. My data warehouse will be 9.2, with all locally managed tablespaces. We will be following what I have taken to calling the Goldilocks principle -- that of small, medium and large tablespace extent sizes, with variations in that we will separate indexes and data, and will have even more separation for our fact tables into partitioned tables and tablespaces. However, now comes the time for me to work out storage clauses. And a quick read through the docs leaves me wondering if I should just turn on automatic segment-space management and not worry about setting PCTFREE, PCTUSED and FREELIST parameters. I can't find any real information or bugs on MetaLink either. Does anyone have any experience, good OR bad, with using this feature? If you are doing data warehouse work, what are good values for the parameters if I DO use them? One fact table is likely to be highly updated (customer info) as we collect more and more specific information from customers. The rest will be, as you would expect from a DW, mostly inserts. Help? Thanks! Rachel __ Do You Yahoo!? Yahoo! Finance - Get real-time stock quotes http://finance.yahoo.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Rachel Carmichael INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists 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.com -- Author: Fink, Dan INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists 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! Finance - Get real-time stock quotes http://finance.yahoo.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Rachel Carmichael INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists 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: automatic segment space management
Ron, yes it helps, thanks! I forgot to mention that I will be using UNIFORM sizes for the LMTs, I don't really see the advantage to autoallocate I can probably get away with the defaults for the parameters (if I don't use automatic segment space allocation) but it will waste some space in some of the tables. We will be partitioning most of the fact tables by date (the date dimension key is going to be the numeric representation of the date that row has info on) but likely the customer fact table will be hash partitioned as we don't intend to ever prune that table. Rachel --- Ron Rogers [EMAIL PROTECTED] wrote: Rachel, You did not say if you would be using the UNIFORM option for the LMT's. If you allow the system to choose the initial sizing there can be a lot of wasted space as the table size grows. The system will choose sizing options that you most likely would not choose. I can't find my reference to the sizes that are chosen but 16K 64K 1M and 4M sound correct. The extends will increase as the total size of the table increases. I find it easier to manage the sizes of the tables in the tablespace with uniform extent sizes that are manageable and mist likely to be filled in a reasonable time frame. For small static tables I used a multiple of the block size and tried to group similarly used table in the same tablespace. The tables that are continually growing daily I partitioned by date range and allowed the tablespace to autoextend. There is very little wasted space and the tablespaces are usually 100 % full until the next extent is needed. I accepted the default PCTFREE, PCTUSED and FREELIST parameters and I haven't seen and problems yet. I hope this helps, Ron ROR mª¿ªm [EMAIL PROTECTED] 09/03/02 11:48AM time for me to ask the experts again. My data warehouse will be 9.2, with all locally managed tablespaces. We will be following what I have taken to calling the Goldilocks principle -- that of small, medium and large tablespace extent sizes, with variations in that we will separate indexes and data, and will have even more separation for our fact tables into partitioned tables and tablespaces. However, now comes the time for me to work out storage clauses. And a quick read through the docs leaves me wondering if I should just turn on automatic segment-space management and not worry about setting PCTFREE, PCTUSED and FREELIST parameters. I can't find any real information or bugs on MetaLink either. Does anyone have any experience, good OR bad, with using this feature? If you are doing data warehouse work, what are good values for the parameters if I DO use them? One fact table is likely to be highly updated (customer info) as we collect more and more specific information from customers. The rest will be, as you would expect from a DW, mostly inserts. Help? Thanks! Rachel __ Do You Yahoo!? Yahoo! Finance - Get real-time stock quotes http://finance.yahoo.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Rachel Carmichael INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists 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.com -- Author: Ron Rogers INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists 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! Finance - Get real-time stock quotes http://finance.yahoo.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Rachel Carmichael INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists 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,
RE: automatic segment space management
I recall that these were the days before LMT and at the start of moving away from fine-grained object sizing. In hindsight, 1 size per tablespace makes more sense. There could be several exceptions. For example, all of the reference tables/indexes were in a single tablespace. Since these were fairly small and very static, we had a more classic approach to sizing (still only 3 extent sizes). LMTs are of 2 varieties Uniform size and Autoallocate. Uniform means 1 and only 1 extent size. Autoallocate has 4 extent sizes (Oracle documents only 3). I have used autoallocate when the rough size was not known or we were mixing several general sizes. Uniform would be good for known partitions, small reference tables, etc. So, if this is the goldilocks approach...who are the 3 bears? -Original Message- Sent: Tuesday, September 03, 2002 12:49 PM To: Multiple recipients of list ORACLE-L Dan, Why 3 extent sizes in each tablespace? I can see how you prevented the dreaded fragmentation problem by making them multiples of one another but I don't understand why you did that. If I'm using LMTs can I still do that? I haven't really used them, I'd thought to not specify the initial and next extent sizes and just let the LMT deal with it. As for the Goldilocks name, you are more than welcome to steal, uh, borrow, it :) Rachel --- Fink, Dan [EMAIL PROTECTED] wrote: We used this method (I wish I had come up with the Goldilocks code name...) in 8.0.4 on a couple of data warehouses. The bottom line is that we had 1 space related failure (application code filled up the error log) in over 9 months of operation. Which so impressed management that they decided to cut the support budget (not enough outages/calls to help desk). There were over 150 tablespaces per database so we could perform I/O balancing, object segregation, etc. We did not follow the SAFE (3 and only 3 extent sizes in the database). Rather we used 3 extent sizes per tablespace with medium a multiple of small and large a multiple of medium. PCTFREE - set to 10 (no updates, but provided a little space just in case this changed) PCTUSED - set to 80 (same reason as above) FREELIST - set to 5 (originally designed so only 2 processes would ever concurrently insert data) Sounds like you are on the right track. -Original Message- Sent: Tuesday, September 03, 2002 9:49 AM To: Multiple recipients of list ORACLE-L time for me to ask the experts again. My data warehouse will be 9.2, with all locally managed tablespaces. We will be following what I have taken to calling the Goldilocks principle -- that of small, medium and large tablespace extent sizes, with variations in that we will separate indexes and data, and will have even more separation for our fact tables into partitioned tables and tablespaces. However, now comes the time for me to work out storage clauses. And a quick read through the docs leaves me wondering if I should just turn on automatic segment-space management and not worry about setting PCTFREE, PCTUSED and FREELIST parameters. I can't find any real information or bugs on MetaLink either. Does anyone have any experience, good OR bad, with using this feature? If you are doing data warehouse work, what are good values for the parameters if I DO use them? One fact table is likely to be highly updated (customer info) as we collect more and more specific information from customers. The rest will be, as you would expect from a DW, mostly inserts. Help? Thanks! Rachel __ Do You Yahoo!? Yahoo! Finance - Get real-time stock quotes http://finance.yahoo.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Rachel Carmichael INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists 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.com -- Author: Fink, Dan INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists 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
RE: automatic segment space management
On Tablespace Creation In sys.dba_tablespaces , Field ALLOCATION_TYPE Defaults to
SYSTEM
. Hence NEXT_EXTENT of Created Tables is NOT Taken from the User-specified Value but
internally by Oracle itself . This leads to Excessively Large Number of EXTENTs (Small
in Size)
To Allow Table Creation with NEXT_EXTENT taken from User Specified Value , the Value
ALLOCATION_TYPE can be Changed to USER From the Original SYSTEM Value as follows
SQL exec sys.dbms_space_admin.tablespace_migrate_from_local('GAM_PT1_TBLSPC');
SQL exec sys.dbms_space_admin.tablespace_migrate_to_local('GAM_PT1_TBLSPC');
This does the needful Allows Object's NEXT_EXTENT Size to be User-Specified
We have used Such Tablespaces in Benchmarking Activities BOTH with Oracle 8.1.7 9.0
The Performance has been just fine
HTH
P.S. Feel free to mention any Disadvantages with this approach
-Original Message-
Sent: Tuesday, September 03, 2002 11:49 PM
To: Multiple recipients of list ORACLE-L
Rachel,
You did not say if you would be using the UNIFORM option for the
LMT's. If you allow the system to choose the initial sizing there can be
a lot of wasted space as the table size grows. The system will choose
sizing options that you most likely would not choose. I can't find my
reference to the sizes that are chosen but 16K 64K 1M and 4M sound
correct. The extends will increase as the total size of the table
increases.
I find it easier to manage the sizes of the tables in the tablespace
with uniform extent sizes that are manageable and mist likely to be
filled in a reasonable time frame. For small static tables I used a
multiple of the block size and tried to group similarly used table in
the same tablespace. The tables that are continually growing daily I
partitioned by date range and allowed the tablespace to autoextend.
There is very little wasted space and the tablespaces are usually 100 %
full until the next extent is needed.
I accepted the default PCTFREE, PCTUSED and FREELIST parameters and I
haven't seen and problems yet.
I hope this helps,
Ron
ROR mª¿ªm
[EMAIL PROTECTED] 09/03/02 11:48AM
time for me to ask the experts again.
My data warehouse will be 9.2, with all locally managed tablespaces.
We
will be following what I have taken to calling the Goldilocks
principle -- that of small, medium and large tablespace extent sizes,
with variations in that we will separate indexes and data, and will
have even more separation for our fact tables into partitioned tables
and tablespaces.
However, now comes the time for me to work out storage clauses. And a
quick read through the docs leaves me wondering if I should just turn
on automatic segment-space management and not worry about setting
PCTFREE, PCTUSED and FREELIST parameters. I can't find any real
information or bugs on MetaLink either.
Does anyone have any experience, good OR bad, with using this feature?
If you are doing data warehouse work, what are good values for the
parameters if I DO use them? One fact table is likely to be highly
updated (customer info) as we collect more and more specific
information from customers. The rest will be, as you would expect from
a DW, mostly inserts.
Help?
Thanks!
Rachel
__
Do You Yahoo!?
Yahoo! Finance - Get real-time stock quotes
http://finance.yahoo.com
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: Rachel Carmichael
INET: [EMAIL PROTECTED]
Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051
San Diego, California-- Public Internet access / Mailing Lists
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.com
--
Author: Ron Rogers
INET: [EMAIL PROTECTED]
Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051
San Diego, California-- Public Internet access / Mailing Lists
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.com
--
Author: VIVEK_SHARMA
INET: [EMAIL PROTECTED]
Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051
San Diego, California-- Public Internet access / Mailing Lists
To REMOVE yourself from
RE: automatic segment space management
Rachel The varchar business on your fact table worries me. I'm not trying to be critical, but to bring up some issues you may want to consider before you begin the big load. Unfortunately too many DWs end up with a flawed data model (ours included) that limit its usability. Sometimes the fortunate sites are the ones that get it so wrong it must be blasted away and recreated. Guess who gets to bear the brunt of that hasty learning exercise?? Yep, that's right. Here are my thoughts for what they are worth. 1. Normally the fact table is very large, so it is critical that it be designed as nearly right as possible. Especially with what is called the granularity, the lowest level of data that is stored. You can always aggregate up, never down. Daily data can be summed to weekly, but we can't take weekly data and figure out the daily amounts. 2. When you say the customers fill in the blanks, that worries me. That doesn't sound so much like a DW as an OLTP. Where is the history dimension? 3. The fact table usually is so large that the information only makes sense in aggregate. You aren't looking for the particular blue-eyed 23-year old female from Des Moines, but trying to find HOW MANY blue-eyed, etc. This means that you won't be scratching around with VARCHAR2 fields with query operators such as LIKE. Performance would be really BAD. 4. I don't know your application, just the minor details you've mentioned in passing, but consider something like this. The FACT table logs each new information that a user provides. VARCHAR2 fields. Never UPDATEd, just add a new record along with the date that record was added. From that we create an aggregate table CURRFACT. One row per customer. Weekly we scan the new records added to FACT, pulling new facts, updated facts into CURRFACT. Most of the fields in CURRFACT are single character flag fields. Bit map index the heck (sorry, but we have a fierce naughty word scanner) out of CURRFACT. Performance is awesome. Queries return before the users hit enter (just kidding). From to time marketing recognizes some relevant fact that isn't in CURRFACT. You add a new column to CURRFACT and start a really big query on FACT when you leave for the weekend that will populate the new column. There may be a few fields like address that you populate in CURRFACT just for convenience. But you don't search them. Also, if you ever need the history of how your customers have moved around, you have that data. And remember, a DW is all about history, never about current information. The modeling issues have a lot more about the performance and usability of the DW than the choices we have as DBAs such as LMT. Okay, I'll quit prattling on here. You probably didn't even get a say in the data model. They never ask the DBA. But if you raise the issues beforehand it'll amuse you more when they come back and ask you to redo everything. Hey, I just noticed that you won't be the production DBA on this! No worries! Dennis Williams DBA Lifetouch, Inc. [EMAIL PROTECTED] -Original Message- Sent: Tuesday, September 03, 2002 1:44 PM To: Multiple recipients of list ORACLE-L We may end up reworking the extent sizes, right now they are smaller than those but we are still in stage one, haven't gone live yet (and I don't even want to think about what a pain it will be to change things when we do go live). Data load test coming up soon, so I'll have a better idea of what I need to change things to, if I need to change them. There is no way the customer row will remain the same size unless I change all the varchar fields to char. We have VERY sparse data as yet and expect to be able to entice customers to fill in the blanks which will cause rows to grow. We are allocating WAY more space than we need at the moment and will be closely monitoring growth (once a day data loads) and I can always turn on autoextend if I need it. But then again, I am not the DBA who will be responsible for the production site, at least not for any space issues on the production DW. Rachel --- DENNIS WILLIAMS [EMAIL PROTECTED] wrote: Rachel - I have been using LMTs with uniform extents (Oracle-recommended variation) for a couple of years now with Oracle 8.1.6, and now 9.2. The Oracle-recommended extent sizes are 128k, 4m, and 128m. As far as PCTFREE, et. al., these are at the table level, so my opinion would be that the guidelines for these are unchanged from the pre-LMT days. The key issue with the highly updated customer table would be whether the size of the row is changing. If you can keep the row size constant, then you won't wind up with chained rows. The biggest issue facing you is whether you turn AUTOEXTEND on. I did that and have encountered relatively few problems. Well, one problem. I had tables set with large NEXT extents to minimize extents, and when one extended boy did my sys admin get excited. I changed that. A bigger issue in building your data warehouse is whether you
RE: query for detect redundant index
I presume 'redundant' means that a column in the leading position of an index is also in the leading position of another index. Here is a quick and dirty SQL script to generate this information. Unfortunately, it repeats the information, but it does give you the information. SQLbreak on table_name on column_nameSQL l 1 select ic1.table_name, 2 ic1.column_name, 3 ic1.index_name, 4 ic1.column_position, 5 ic2.index_name, 6 ic2.column_position, 7 decode(ic1.column_position, 8 ic2.column_position, 'Redundant') redundancy 9 from user_ind_columns ic1,10 user_ind_columns ic211 where ic1.table_name = ic2.table_name12 and ic1.column_name = ic2.column_name13* and ic1.index_name != ic2.index_nameSQL / TABLE_NAME COLUMN_NAME INDEX_NAME COLUMN_POSITION INDEX_NAME COLUMN_POSITION REDUNDANC-- --- --- --- --- EMP EMPNO PK_EMP 1 IX_EMP 1 Redundant IX_EMP 1 PK_EMP 1 Redundant ENAME IX_EMPNAME 1 IX_EMP 2 IX_EMP 2 IX_EMPNAME 1 -Original Message-From: Adriano Freire [mailto:[EMAIL PROTECTED]]Sent: Tuesday, September 03, 2002 11:15 AMTo: Multiple recipients of list ORACLE-LSubject: query for detect redundant index HI all, Do you've any pl/sql for detect redundant index? thanks, Adriano Freire
RE: automatic segment space management
So, if this is the goldilocks approach...who are the 3 bears? duh. small tablespace (Baby Bear), medium tablespace (Mama Bear) and large tablespace (Papa Bear) and if a table is Goldilocks then one and only one tablespace will be just right Rachel --- Fink, Dan [EMAIL PROTECTED] wrote: I recall that these were the days before LMT and at the start of moving away from fine-grained object sizing. In hindsight, 1 size per tablespace makes more sense. There could be several exceptions. For example, all of the reference tables/indexes were in a single tablespace. Since these were fairly small and very static, we had a more classic approach to sizing (still only 3 extent sizes). LMTs are of 2 varieties Uniform size and Autoallocate. Uniform means 1 and only 1 extent size. Autoallocate has 4 extent sizes (Oracle documents only 3). I have used autoallocate when the rough size was not known or we were mixing several general sizes. Uniform would be good for known partitions, small reference tables, etc. So, if this is the goldilocks approach...who are the 3 bears? -Original Message- Sent: Tuesday, September 03, 2002 12:49 PM To: Multiple recipients of list ORACLE-L Dan, Why 3 extent sizes in each tablespace? I can see how you prevented the dreaded fragmentation problem by making them multiples of one another but I don't understand why you did that. If I'm using LMTs can I still do that? I haven't really used them, I'd thought to not specify the initial and next extent sizes and just let the LMT deal with it. As for the Goldilocks name, you are more than welcome to steal, uh, borrow, it :) Rachel --- Fink, Dan [EMAIL PROTECTED] wrote: We used this method (I wish I had come up with the Goldilocks code name...) in 8.0.4 on a couple of data warehouses. The bottom line is that we had 1 space related failure (application code filled up the error log) in over 9 months of operation. Which so impressed management that they decided to cut the support budget (not enough outages/calls to help desk). There were over 150 tablespaces per database so we could perform I/O balancing, object segregation, etc. We did not follow the SAFE (3 and only 3 extent sizes in the database). Rather we used 3 extent sizes per tablespace with medium a multiple of small and large a multiple of medium. PCTFREE - set to 10 (no updates, but provided a little space just in case this changed) PCTUSED - set to 80 (same reason as above) FREELIST - set to 5 (originally designed so only 2 processes would ever concurrently insert data) Sounds like you are on the right track. -Original Message- Sent: Tuesday, September 03, 2002 9:49 AM To: Multiple recipients of list ORACLE-L time for me to ask the experts again. My data warehouse will be 9.2, with all locally managed tablespaces. We will be following what I have taken to calling the Goldilocks principle -- that of small, medium and large tablespace extent sizes, with variations in that we will separate indexes and data, and will have even more separation for our fact tables into partitioned tables and tablespaces. However, now comes the time for me to work out storage clauses. And a quick read through the docs leaves me wondering if I should just turn on automatic segment-space management and not worry about setting PCTFREE, PCTUSED and FREELIST parameters. I can't find any real information or bugs on MetaLink either. Does anyone have any experience, good OR bad, with using this feature? If you are doing data warehouse work, what are good values for the parameters if I DO use them? One fact table is likely to be highly updated (customer info) as we collect more and more specific information from customers. The rest will be, as you would expect from a DW, mostly inserts. Help? Thanks! Rachel __ Do You Yahoo!? Yahoo! Finance - Get real-time stock quotes http://finance.yahoo.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Rachel Carmichael INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists 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.com -- Author: Fink, Dan INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX:
RE: automatic segment space management
Rich - Good point. Yes, I create all the tables here, at least in production, and I probably wouldn't use autoextend if the situation were otherwise. The other thing to consider is if you are using uniform extents, by definition you have bought into the philosophy that you can have many extents and your database will not do a Linda Blair Exorcist imitation on you. If we use the guideline that the number of extents should be not many more than 1,000, then the 128K extent will get you 128M, which is good for most tables. While we are on the subject, anyone considering switching to LMTs should carefully read How to Stop Defragmenting and Start Living by Juan Loaiza, Rosanne Toohey, Bhaskar Himatsingka. You can find a copy on http://www.hotsos.com. Trying to implement a philosophy without fully understanding it is a recipe for failure. Dennis Williams DBA Lifetouch, Inc. [EMAIL PROTECTED] -Original Message- Sent: Tuesday, September 03, 2002 12:58 PM To: Multiple recipients of list ORACLE-L Dennis, who creates tables in your DB? If devs do, don't you worry that one could accidentally create a 512MB table in your 128K TS, instead of a 512KB one? I really would like to implement LMTs here, and am doing so in certain restricted instances where I'm the only one who creates the objects for that TS, but I'm a little leery of letting it go to the developers, even though I don't use AUTOEXTEND. Thx! Rich Jesse System/Database Administrator [EMAIL PROTECTED] Quad/Tech International, Sussex, WI USA -Original Message- From: DENNIS WILLIAMS [mailto:[EMAIL PROTECTED]] Sent: Tuesday, September 03, 2002 12:42 PM To: Multiple recipients of list ORACLE-L Subject: RE: automatic segment space management Rachel - I have been using LMTs with uniform extents (Oracle-recommended variation) for a couple of years now with Oracle 8.1.6, and now 9.2. The Oracle-recommended extent sizes are 128k, 4m, and 128m. As far as PCTFREE, et. al., these are at the table level, so my opinion would be that the guidelines for these are unchanged from the pre-LMT days. The key issue with the highly updated customer table would be whether the size of the row is changing. If you can keep the row size constant, then you won't wind up with chained rows. The biggest issue facing you is whether you turn AUTOEXTEND on. I did that and have encountered relatively few problems. Well, one problem. I had tables set with large NEXT extents to minimize extents, and when one extended boy did my sys admin get excited. I changed that. A bigger issue in building your data warehouse is whether you can use the partitioning option. Most of our queries were taking more than 2 minutes and I was able to partition and bring that down below 10 seconds. The users were pretty excited. Dennis Williams DBA Lifetouch, Inc. [EMAIL PROTECTED] -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Jesse, Rich INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists 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.com -- Author: DENNIS WILLIAMS INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists 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).
URGENT
Hi Friends, My all background processors accessing same file system, I did some reorg this weekend, But I shut down database like 4 times...all processors are waitingAny help will be highly appreicated!!! oracle@baan1 $ ps -ef|grep 14816 oracle 14816 1 2 13:59:00 - 0:49 ora_dbwr_baanIV oracle 80572 18974 2 14:27:27 pts/1 0:00 grep 14816 oracle@baan1 $ ps -ef|grep 15336 oracle 15336 1 0 13:59:00 - 0:12 ora_lgwr_baanIV oracle 83158 18974 2 14:27:55 pts/1 0:00 grep 15336 oracle@baan1 $ ps -ef|grep 17644 oracle 17644 1 0 13:59:00 - 0:16 ora_smon_baanIV oracle 85890 18974 2 14:28:21 pts/1 0:00 grep 17644 oracle@baan1 $ ps -ef|grep 18164 oracle 18164 1 0 13:59:00 - 0:03 ora_db01_baanIV oracle 84380 18974 1 14:28:44 pts/1 0:00 grep 18164 Thanks peter. _ Join the worlds largest e-mail service with MSN Hotmail. http://www.hotmail.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Peter R INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists 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: Default Location of RMAN backup sets
You have to state the directory (full path) in a format statement. Then rman will put it where you want it. Ruth - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Friday, August 30, 2002 10:28 PM Hello, I took an RMAN backup of a database (for testing purpose only, thank goodness). The question is, where did RMAN put the backup set files? I read through an RMAN book and the official Oracle RMAN User Guide and neither mentions the location of the RMAN backup ?!! Thanks for any input. ltiu -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: ltiu INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists 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.com -- Author: Ruth Gramolini INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists 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: automatic segment space management
The question posed was not whether extent management local should be used, but whether automatic segment space management should be used. As this is a data warehouse, I would not expect you to have transactions trying to change the same block. Assuming you are loading; that is, inserting data and not doing updates, wouldn't you try to cram as much data as possible into a block? Seems this could be done more easily by controlling these parameters yourself I've got one system using automatic segment space management without any problems, however when I posed the same question on using it a few months ago, the respone which trickled in way highly negative concerning its usage. Ian MacGregor Stanford Linear Accelerator Center [EMAIL PROTECTED] -Original Message- Sent: Tuesday, September 03, 2002 8:49 AM To: Multiple recipients of list ORACLE-L time for me to ask the experts again. My data warehouse will be 9.2, with all locally managed tablespaces. We will be following what I have taken to calling the Goldilocks principle -- that of small, medium and large tablespace extent sizes, with variations in that we will separate indexes and data, and will have even more separation for our fact tables into partitioned tables and tablespaces. However, now comes the time for me to work out storage clauses. And a quick read through the docs leaves me wondering if I should just turn on automatic segment-space management and not worry about setting PCTFREE, PCTUSED and FREELIST parameters. I can't find any real information or bugs on MetaLink either. Does anyone have any experience, good OR bad, with using this feature? If you are doing data warehouse work, what are good values for the parameters if I DO use them? One fact table is likely to be highly updated (customer info) as we collect more and more specific information from customers. The rest will be, as you would expect from a DW, mostly inserts. Help? Thanks! Rachel __ Do You Yahoo!? Yahoo! Finance - Get real-time stock quotes http://finance.yahoo.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Rachel Carmichael INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists 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.com -- Author: MacGregor, Ian A. INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists 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).
Pls respond...: URGENT
Hi Friends, My all background processors accessing same file system, I did some reorg this weekend, But I shut down database like 4 times...all processors are waitingAny help will be highly appreicated!!! oracle@baan1 $ ps -ef|grep 14816 oracle 14816 1 2 13:59:00 - 0:49 ora_dbwr_baanIV oracle 80572 18974 2 14:27:27 pts/1 0:00 grep 14816 oracle@baan1 $ ps -ef|grep 15336 oracle 15336 1 0 13:59:00 - 0:12 ora_lgwr_baanIV oracle 83158 18974 2 14:27:55 pts/1 0:00 grep 15336 oracle@baan1 $ ps -ef|grep 17644 oracle 17644 1 0 13:59:00 - 0:16 ora_smon_baanIV oracle 85890 18974 2 14:28:21 pts/1 0:00 grep 17644 oracle@baan1 $ ps -ef|grep 18164 oracle 18164 1 0 13:59:00 - 0:03 ora_db01_baanIV oracle 84380 18974 1 14:28:44 pts/1 0:00 grep 18164 Thanks peter. _ Join the worlds largest e-mail service with MSN Hotmail. http://www.hotmail.com Oracle documentation is here: http://tahiti.oracle.com/pls/tahiti/tahiti.homepage To unsubscribe: send a blank email to [EMAIL PROTECTED] To subscribe: send a blank email to [EMAIL PROTECTED] Visit the list archive: http://www.LAZYDBA.com/odbareadmail.pl Tell yer mates about http://www.farAwayJobs.com By using this list you agree to these terms:http://www.lazydba.com/legal.html _ Send and receive Hotmail on your mobile device: http://mobile.msn.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Peter R INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists 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: automatic segment space management
my fault, the customer table is a dimension table, not a fact table. Doh! --- DENNIS WILLIAMS [EMAIL PROTECTED] wrote: Rachel The varchar business on your fact table worries me. I'm not trying to be critical, but to bring up some issues you may want to consider before you begin the big load. Unfortunately too many DWs end up with a flawed data model (ours included) that limit its usability. Sometimes the fortunate sites are the ones that get it so wrong it must be blasted away and recreated. Guess who gets to bear the brunt of that hasty learning exercise?? Yep, that's right. Here are my thoughts for what they are worth. 1. Normally the fact table is very large, so it is critical that it be designed as nearly right as possible. Especially with what is called the granularity, the lowest level of data that is stored. You can always aggregate up, never down. Daily data can be summed to weekly, but we can't take weekly data and figure out the daily amounts. 2. When you say the customers fill in the blanks, that worries me. That doesn't sound so much like a DW as an OLTP. Where is the history dimension? 3. The fact table usually is so large that the information only makes sense in aggregate. You aren't looking for the particular blue-eyed 23-year old female from Des Moines, but trying to find HOW MANY blue-eyed, etc. This means that you won't be scratching around with VARCHAR2 fields with query operators such as LIKE. Performance would be really BAD. 4. I don't know your application, just the minor details you've mentioned in passing, but consider something like this. The FACT table logs each new information that a user provides. VARCHAR2 fields. Never UPDATEd, just add a new record along with the date that record was added. From that we create an aggregate table CURRFACT. One row per customer. Weekly we scan the new records added to FACT, pulling new facts, updated facts into CURRFACT. Most of the fields in CURRFACT are single character flag fields. Bit map index the heck (sorry, but we have a fierce naughty word scanner) out of CURRFACT. Performance is awesome. Queries return before the users hit enter (just kidding). From to time marketing recognizes some relevant fact that isn't in CURRFACT. You add a new column to CURRFACT and start a really big query on FACT when you leave for the weekend that will populate the new column. There may be a few fields like address that you populate in CURRFACT just for convenience. But you don't search them. Also, if you ever need the history of how your customers have moved around, you have that data. And remember, a DW is all about history, never about current information. The modeling issues have a lot more about the performance and usability of the DW than the choices we have as DBAs such as LMT. Okay, I'll quit prattling on here. You probably didn't even get a say in the data model. They never ask the DBA. But if you raise the issues beforehand it'll amuse you more when they come back and ask you to redo everything. Hey, I just noticed that you won't be the production DBA on this! No worries! Dennis Williams DBA Lifetouch, Inc. [EMAIL PROTECTED] -Original Message- Sent: Tuesday, September 03, 2002 1:44 PM To: Multiple recipients of list ORACLE-L We may end up reworking the extent sizes, right now they are smaller than those but we are still in stage one, haven't gone live yet (and I don't even want to think about what a pain it will be to change things when we do go live). Data load test coming up soon, so I'll have a better idea of what I need to change things to, if I need to change them. There is no way the customer row will remain the same size unless I change all the varchar fields to char. We have VERY sparse data as yet and expect to be able to entice customers to fill in the blanks which will cause rows to grow. We are allocating WAY more space than we need at the moment and will be closely monitoring growth (once a day data loads) and I can always turn on autoextend if I need it. But then again, I am not the DBA who will be responsible for the production site, at least not for any space issues on the production DW. Rachel --- DENNIS WILLIAMS [EMAIL PROTECTED] wrote: Rachel - I have been using LMTs with uniform extents (Oracle-recommended variation) for a couple of years now with Oracle 8.1.6, and now 9.2. The Oracle-recommended extent sizes are 128k, 4m, and 128m. As far as PCTFREE, et. al., these are at the table level, so my opinion would be that the guidelines for these are unchanged from the pre-LMT days. The key issue with the highly updated customer table would be whether the size of the row is changing. If you can keep the row size constant, then you won't wind up with chained rows. The biggest issue facing you is whether you turn AUTOEXTEND on. I did that and have
MS Access from Oracle
Can someone tell me where to start to find out how to access an MS Access table from Oracle. Basically what I want to do is be connected to an Oracle database in SQL*Plus and execute a query against an MS-Access table. Is this possible? Thanks, Jeff -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Eberhard, Jeff INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists 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: Pls respond...: URGENT
My one of disk showing full active...very hot!!! by bground processes accessing... hdisk51 88.7 926.6 139.3 2780 8 hdisk51 85.5 1217.0 176.2 3660 0 hdisk51 91.8 980.2 149.3 2948 From: Peter R [EMAIL PROTECTED] To: LazyDBA.com Discussion [EMAIL PROTECTED] Subject: Pls respond...: URGENT Date: Tue, 03 Sep 2002 19:32:14 + Hi Friends, My all background processors accessing same file system, I did some reorg this weekend, But I shut down database like 4 times...all processors are waitingAny help will be highly appreicated!!! oracle@baan1 $ ps -ef|grep 14816 oracle 14816 1 2 13:59:00 - 0:49 ora_dbwr_baanIV oracle 80572 18974 2 14:27:27 pts/1 0:00 grep 14816 oracle@baan1 $ ps -ef|grep 15336 oracle 15336 1 0 13:59:00 - 0:12 ora_lgwr_baanIV oracle 83158 18974 2 14:27:55 pts/1 0:00 grep 15336 oracle@baan1 $ ps -ef|grep 17644 oracle 17644 1 0 13:59:00 - 0:16 ora_smon_baanIV oracle 85890 18974 2 14:28:21 pts/1 0:00 grep 17644 oracle@baan1 $ ps -ef|grep 18164 oracle 18164 1 0 13:59:00 - 0:03 ora_db01_baanIV oracle 84380 18974 1 14:28:44 pts/1 0:00 grep 18164 Thanks peter. _ Join the worlds largest e-mail service with MSN Hotmail. http://www.hotmail.com Oracle documentation is here: http://tahiti.oracle.com/pls/tahiti/tahiti.homepage To unsubscribe: send a blank email to [EMAIL PROTECTED] To subscribe: send a blank email to [EMAIL PROTECTED] Visit the list archive: http://www.LAZYDBA.com/odbareadmail.pl Tell yer mates about http://www.farAwayJobs.com By using this list you agree to these terms:http://www.lazydba.com/legal.html _ Send and receive Hotmail on your mobile device: http://mobile.msn.com Oracle documentation is here: http://tahiti.oracle.com/pls/tahiti/tahiti.homepage To unsubscribe: send a blank email to [EMAIL PROTECTED] To subscribe: send a blank email to [EMAIL PROTECTED] Visit the list archive: http://www.LAZYDBA.com/odbareadmail.pl Tell yer mates about http://www.farAwayJobs.com By using this list you agree to these terms:http://www.lazydba.com/legal.html _ Chat with friends online, try MSN Messenger: http://messenger.msn.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Peter R INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists 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).
OT: Unix/Linux/NT Whitepaper
Came across this very slanted view of Unix/Linux vs Windows. Hope it comes in handy for anyone doing research on this particular topic. http://www.cuug.ab.ca/~leblancj/nt_to_unix.html mkb __ Do You Yahoo!? Yahoo! Finance - Get real-time stock quotes http://finance.yahoo.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: mkb INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists 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: MS Access from Oracle
Search on Metalink for 'Heterogenous Services'. The documents there describe how to setup a dblink to an MS Access database. HTH, Beth -Original Message- Sent: Tuesday, September 03, 2002 4:39 PM To: Multiple recipients of list ORACLE-L Can someone tell me where to start to find out how to access an MS Access table from Oracle. Basically what I want to do is be connected to an Oracle database in SQL*Plus and execute a query against an MS-Access table. Is this possible? Thanks, Jeff -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Eberhard, Jeff INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists 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.com -- Author: Seefelt, Beth INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists 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).
Fwd: RE: Pls respond...: URGENT
My database okay, its slow, I can't do much work, too much waitingI want to bring as normal processing... From: Fink, Dan [EMAIL PROTECTED] To: '[EMAIL PROTECTED]' [EMAIL PROTECTED] Subject: RE: Pls respond...: URGENT Date: Tue, 3 Sep 2002 13:58:02 -0600 Peter, What is the problem? What you are asking is not clear, which could be one reason you are not getting a response. Try restating the issue with exactly what the condition of the db is (up or down), what errors are being reported, etc. Dan Fink -Original Message- From: Peter R [mailto:[EMAIL PROTECTED]] Sent: Tuesday, September 03, 2002 2:33 PM To: Multiple recipients of list ORACLE-L Subject: Pls respond...: URGENT Hi Friends, My all background processors accessing same file system, I did some reorg this weekend, But I shut down database like 4 times...all processors are waitingAny help will be highly appreicated!!! oracle@baan1 $ ps -ef|grep 14816 oracle 14816 1 2 13:59:00 - 0:49 ora_dbwr_baanIV oracle 80572 18974 2 14:27:27 pts/1 0:00 grep 14816 oracle@baan1 $ ps -ef|grep 15336 oracle 15336 1 0 13:59:00 - 0:12 ora_lgwr_baanIV oracle 83158 18974 2 14:27:55 pts/1 0:00 grep 15336 oracle@baan1 $ ps -ef|grep 17644 oracle 17644 1 0 13:59:00 - 0:16 ora_smon_baanIV oracle 85890 18974 2 14:28:21 pts/1 0:00 grep 17644 oracle@baan1 $ ps -ef|grep 18164 oracle 18164 1 0 13:59:00 - 0:03 ora_db01_baanIV oracle 84380 18974 1 14:28:44 pts/1 0:00 grep 18164 Thanks peter. _ Join the world's largest e-mail service with MSN Hotmail. http://www.hotmail.com Oracle documentation is here: http://tahiti.oracle.com/pls/tahiti/tahiti.homepage To unsubscribe: send a blank email to [EMAIL PROTECTED] To subscribe: send a blank email to [EMAIL PROTECTED] Visit the list archive: http://www.LAZYDBA.com/odbareadmail.pl Tell yer mates about http://www.farAwayJobs.com By using this list you agree to these terms:http://www.lazydba.com/legal.html _ Send and receive Hotmail on your mobile device: http://mobile.msn.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Peter R INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists 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). _ Chat with friends online, try MSN Messenger: http://messenger.msn.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Peter R INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists 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: Pls respond...: URGENT
What kind of reorgs did you do? -Original Message- Sent: Tuesday, September 03, 2002 4:59 PM To: Multiple recipients of list ORACLE-L My one of disk showing full active...very hot!!! by bground processes accessing... hdisk51 88.7 926.6 139.3 2780 8 hdisk51 85.5 1217.0 176.2 3660 0 hdisk51 91.8 980.2 149.3 2948 From: Peter R [EMAIL PROTECTED] To: LazyDBA.com Discussion [EMAIL PROTECTED] Subject: Pls respond...: URGENT Date: Tue, 03 Sep 2002 19:32:14 + Hi Friends, My all background processors accessing same file system, I did some reorg this weekend, But I shut down database like 4 times...all processors are waitingAny help will be highly appreicated!!! oracle@baan1 $ ps -ef|grep 14816 oracle 14816 1 2 13:59:00 - 0:49 ora_dbwr_baanIV oracle 80572 18974 2 14:27:27 pts/1 0:00 grep 14816 oracle@baan1 $ ps -ef|grep 15336 oracle 15336 1 0 13:59:00 - 0:12 ora_lgwr_baanIV oracle 83158 18974 2 14:27:55 pts/1 0:00 grep 15336 oracle@baan1 $ ps -ef|grep 17644 oracle 17644 1 0 13:59:00 - 0:16 ora_smon_baanIV oracle 85890 18974 2 14:28:21 pts/1 0:00 grep 17644 oracle@baan1 $ ps -ef|grep 18164 oracle 18164 1 0 13:59:00 - 0:03 ora_db01_baanIV oracle 84380 18974 1 14:28:44 pts/1 0:00 grep 18164 Thanks peter. _ Join the world's largest e-mail service with MSN Hotmail. http://www.hotmail.com Oracle documentation is here: http://tahiti.oracle.com/pls/tahiti/tahiti.homepage To unsubscribe: send a blank email to [EMAIL PROTECTED] To subscribe: send a blank email to [EMAIL PROTECTED] Visit the list archive: http://www.LAZYDBA.com/odbareadmail.pl Tell yer mates about http://www.farAwayJobs.com By using this list you agree to these terms:http://www.lazydba.com/legal.html _ Send and receive Hotmail on your mobile device: http://mobile.msn.com Oracle documentation is here: http://tahiti.oracle.com/pls/tahiti/tahiti.homepage To unsubscribe: send a blank email to [EMAIL PROTECTED] To subscribe: send a blank email to [EMAIL PROTECTED] Visit the list archive: http://www.LAZYDBA.com/odbareadmail.pl Tell yer mates about http://www.farAwayJobs.com By using this list you agree to these terms:http://www.lazydba.com/legal.html _ Chat with friends online, try MSN Messenger: http://messenger.msn.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Peter R INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists 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.com -- Author: Seefelt, Beth INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists 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: ArcServe 2000 Agent for Oracle
The ArcServe agent has a tendency to login to your database and never logout, eventually eating up all your processes or logins if you don't keep an eye on it. It also tries to backup TEMPORARY tablespaces by putting them in backup mode, generating an ORA-3217 in the process. FYI: We're replacing it this month with Veritas Net Backup. Jared On Sunday 01 September 2002 19:38, Jahan Shanai wrote: Hi, We are planning to use ArcServe 2000 Agent for Oracle to take Hot Backup of our one of the databases. I was wondering whether anybody encountered any issue? Thanks for youe feebback in advance. Jahan Environment: ArcServe 2000 Agent for Oracle Windows 2000 SP2 Oracle 8.1.7.2.1 Standard Edition Attention: = This e-mail message and accompanying data may contain information that is confidential and subject to legal privilege. If you are not the intended recipient, you are notified that any use, dissemination, or copying of any part of this e-mail message and accompanying data, is prohibited. If you have received this e-mail message in error, please notify us immediately and delete this e-mail message from your computer. -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Jared Still INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists 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: automatic segment space management
Thanks Ian, that was indeed the question, although the other information is useful as well. if I understand what you are saying correctly, it works but it won't really buy me anything and I might do better controlling the space myself. And while you have had no problems, you've heard negative things about it. I can't afford to have this database not be available so I'll manage them on my own Rachel --- MacGregor, Ian A. [EMAIL PROTECTED] wrote: The question posed was not whether extent management local should be used, but whether automatic segment space management should be used. As this is a data warehouse, I would not expect you to have transactions trying to change the same block. Assuming you are loading; that is, inserting data and not doing updates, wouldn't you try to cram as much data as possible into a block? Seems this could be done more easily by controlling these parameters yourself I've got one system using automatic segment space management without any problems, however when I posed the same question on using it a few months ago, the respone which trickled in way highly negative concerning its usage. Ian MacGregor Stanford Linear Accelerator Center [EMAIL PROTECTED] -Original Message- Sent: Tuesday, September 03, 2002 8:49 AM To: Multiple recipients of list ORACLE-L time for me to ask the experts again. My data warehouse will be 9.2, with all locally managed tablespaces. We will be following what I have taken to calling the Goldilocks principle -- that of small, medium and large tablespace extent sizes, with variations in that we will separate indexes and data, and will have even more separation for our fact tables into partitioned tables and tablespaces. However, now comes the time for me to work out storage clauses. And a quick read through the docs leaves me wondering if I should just turn on automatic segment-space management and not worry about setting PCTFREE, PCTUSED and FREELIST parameters. I can't find any real information or bugs on MetaLink either. Does anyone have any experience, good OR bad, with using this feature? If you are doing data warehouse work, what are good values for the parameters if I DO use them? One fact table is likely to be highly updated (customer info) as we collect more and more specific information from customers. The rest will be, as you would expect from a DW, mostly inserts. Help? Thanks! Rachel __ Do You Yahoo!? Yahoo! Finance - Get real-time stock quotes http://finance.yahoo.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Rachel Carmichael INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists 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.com -- Author: MacGregor, Ian A. INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists 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! Finance - Get real-time stock quotes http://finance.yahoo.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Rachel Carmichael INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists 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: Pls respond...: URGENT
Is it possible there is a full analyze running on the database? You said you did a reorg this weekend. If you bounced it 4 times it could be going through backout and recovery. Either one would keep it pretty busy. R. Smith -Original Message- Sent: Tuesday, September 03, 2002 4:08 PM To: Multiple recipients of list ORACLE-L My database okay, its slow, I can't do much work, too much waitingI want to bring as normal processing... From: Fink, Dan [EMAIL PROTECTED] To: '[EMAIL PROTECTED]' [EMAIL PROTECTED] Subject: RE: Pls respond...: URGENT Date: Tue, 3 Sep 2002 13:58:02 -0600 Peter, What is the problem? What you are asking is not clear, which could be one reason you are not getting a response. Try restating the issue with exactly what the condition of the db is (up or down), what errors are being reported, etc. Dan Fink -Original Message- From: Peter R [mailto:[EMAIL PROTECTED]] Sent: Tuesday, September 03, 2002 2:33 PM To: Multiple recipients of list ORACLE-L Subject: Pls respond...: URGENT Hi Friends, My all background processors accessing same file system, I did some reorg this weekend, But I shut down database like 4 times...all processors are waitingAny help will be highly appreicated!!! oracle@baan1 $ ps -ef|grep 14816 oracle 14816 1 2 13:59:00 - 0:49 ora_dbwr_baanIV oracle 80572 18974 2 14:27:27 pts/1 0:00 grep 14816 oracle@baan1 $ ps -ef|grep 15336 oracle 15336 1 0 13:59:00 - 0:12 ora_lgwr_baanIV oracle 83158 18974 2 14:27:55 pts/1 0:00 grep 15336 oracle@baan1 $ ps -ef|grep 17644 oracle 17644 1 0 13:59:00 - 0:16 ora_smon_baanIV oracle 85890 18974 2 14:28:21 pts/1 0:00 grep 17644 oracle@baan1 $ ps -ef|grep 18164 oracle 18164 1 0 13:59:00 - 0:03 ora_db01_baanIV oracle 84380 18974 1 14:28:44 pts/1 0:00 grep 18164 Thanks peter. _ Join the world's largest e-mail service with MSN Hotmail. http://www.hotmail.com Oracle documentation is here: http://tahiti.oracle.com/pls/tahiti/tahiti.homepage To unsubscribe: send a blank email to [EMAIL PROTECTED] To subscribe: send a blank email to [EMAIL PROTECTED] Visit the list archive: http://www.LAZYDBA.com/odbareadmail.pl Tell yer mates about http://www.farAwayJobs.com By using this list you agree to these terms:http://www.lazydba.com/legal.html _ Send and receive Hotmail on your mobile device: http://mobile.msn.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Peter R INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists 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). _ Chat with friends online, try MSN Messenger: http://messenger.msn.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Peter R INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists 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.com -- Author: Smith, Ron L. INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists 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: dbms_space incorrect output
Check out note # 116565.1 on MetaLink.
It should clear it up.
Jared
On Saturday 31 August 2002 02:28, kommareddy sreenivasa wrote:
Hello all,
DB: 8i
OS: soalris 2.8
why DBMS_SPACE.FREE_BLOCKS is not giving correct
status:
create table ram1 (a number);
analyze table ram1 compute statistics;
select table_name, blocks,empty_blocks
from dba_tables
where table_name = 'RAM1';
TABLE_NAMEBLOCKS EMPTY_BLOCKS
RAM1 04
Using dbms_space.free_space:
set serveroutput on
declare
free number;
begin
dbms_space.free_blocks('SYS','RAM1','TABLE',0,free);
dbms_output.put_line('Free blocks '||free);
end;
/
Free blocks 0
PL/SQL procedure successfully completed.
am I missing something ?
regards,
srinivas
__
Do You Yahoo!?
Yahoo! Finance - Get real-time stock quotes
http://finance.yahoo.com
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: Jared Still
INET: [EMAIL PROTECTED]
Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051
San Diego, California-- Public Internet access / Mailing Lists
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).
How to speed up import
I do backup database nightly using export utility. When I restore database using import, it takes more than 6 hours to finish. Is there a way to speed up import process? Please advise. Thanks, David -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Nguyen, David M INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists 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: MS Access from Oracle
Doc ID: 109730.1 -Original Message- Sent: Tuesday, September 03, 2002 5:08 PM To: Multiple recipients of list ORACLE-L Search on Metalink for 'Heterogenous Services'. The documents there describe how to setup a dblink to an MS Access database. HTH, Beth -Original Message- Sent: Tuesday, September 03, 2002 4:39 PM To: Multiple recipients of list ORACLE-L Can someone tell me where to start to find out how to access an MS Access table from Oracle. Basically what I want to do is be connected to an Oracle database in SQL*Plus and execute a query against an MS-Access table. Is this possible? Thanks, Jeff -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Eberhard, Jeff INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists 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.com -- Author: Seefelt, Beth INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists 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.com -- Author: Khedr, Waleed INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists 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: Function-Based Index not working
There are quite a few restrictions on function-based indexes.
The Oracle SQL guide lists them all. Have you checked to
ensure that you're following all the rules?
Jared
On Saturday 31 August 2002 07:53, Marul Mehta wrote:
Even after giving the hint its not working.
I guess you can't have IS clause and Like with function-based index.
Marul.
- Original Message -
From: Naveen Nahata
To: Multiple recipients of list ORACLE-L
Sent: Saturday, August 31, 2002 7:28 PM
Subject: RE: Function-Based Index not working
I think everythying is fine. Did you try index hint? try that and see.
if that also doesn't work, then either we are missing something or the
Optimizer thinks so
Naveen
-Original Message-
From: Marul Mehta [mailto:[EMAIL PROTECTED]]
Sent: Saturday, August 31, 2002 6:33 PM
To: Multiple recipients of list ORACLE-L
Subject: Re: Function-Based Index not working
Hi Naveen,
Thanks a lot for the efforts you are putting in for me for such a
simple problem, but unfortunately, for me all the tips and tricks are not
solving the problem. Now these are my current statistics :-
+ alter session set QUERY_REWRITE_ENABLED=TRUE;
+ alter session set QUERY_REWRITE_INTEGRITY=TRUSTED;
+ alter session set optimizer_mode=FIRST_ROWS;
+ alter session set DB_FILE_MULTIBLOCK_READ_COUNT=1;
This procedure writes 180,000 records in employeees table
+ execute bulk_insert
Analyzing table and rebuilding index (though its not necessary)
+ analyze table employees compute statistics;
+ alter index upper_ix rebuild;
Making autotrace on
+ set autotrace traceonly explain
Fired the query:
SELECT last_name FROM employees WHERE UPPER(last_name) IS NOT NULL
ORDER BY UPPER(last_name); Elapsed: 00:00:00.00
Execution Plan
--
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=57 Card=4001 Bytes=2
0005)
10 SORT (ORDER BY) (Cost=57 Card=4001 Bytes=20005)
21 TABLE ACCESS (FULL) OF 'EMPLOYEES' (Cost=38 Card=4001 By
tes=20005)
Any clues what is happening? Should I insert more records in the table.
TIA,
Marul.
- Original Message -
From: Naveen Nahata
To: Multiple recipients of list ORACLE-L
Sent: Saturday, August 31, 2002 4:58 PM
Subject: RE: Function-Based Index not working
See the table's size is very small. Till it atleast 2 times the value
of DB_BLOCK_SIZE * DB_FILE_MULTIBLOCK_READ_COUNT it will not use index.
Set the value of DB_FILE_MULTIBLOCK_READ_COUNT to one.
Insert lots of values in the table. You can make a procedure to
insert random characters into the table, and then put it in a big loop.
Analyze table and thn run the same query.
It should work
naveen
-Original Message-
From: Marul Mehta [mailto:[EMAIL PROTECTED]]
Sent: Saturday, August 31, 2002 4:03 PM
To: Multiple recipients of list ORACLE-L
Subject: Re: Function-Based Index not working
Thanks a lot Naveen,
Even after executing the following the execution plan shows full
table scan :-
+ alter session set QUERY_REWRITE_ENABLED=TRUE;
+ alter session set QUERY_REWRITE_INTEGRITY=TRUSTED;
+ alter session set optimizer_mode=FIRST_ROWS;
+ Insert into employees values('A');
+ Insert into employees values('B');
+ analyze table employees compute statistics;
+
select last_name
FROM employees WHERE UPPER(last_name) IS NOT NULL
ORDER BY UPPER(last_name); 23
Elapsed: 00:00:00.00
Execution Plan
--
0 SELECT STATEMENT Optimizer=FIRST_ROWS (Cost=3 Card=2
Bytes=2 )
10 SORT (ORDER BY) (Cost=3 Card=2 Bytes=2)
21 TABLE ACCESS (FULL) OF 'EMPLOYEES' (Cost=1 Card=2
Bytes= 2)
Even after using the hint no change in the plan :-
+ select /* INDEX employees(upper_ix) */ last_name FROM employees
WHERE UPPER(last_name) IS NOT NULL;
Please tell me what else should I do to make this query use the
index which is created.
TIA,
Marul.
- Original Message -
From: Naveen Nahata
To: Multiple recipients of list ORACLE-L
Sent: Saturday, August 31, 2002 3:03 PM
Subject: RE: Function-Based Index not working
Marul,
1. you don't have table analyzed in which case Rule based
optimizer will be used. CBO is used if atleast one of the tables in the
query is ANALYZED 2. There is no data in your table. Optimizer goes for a
full tablescan if it thinks that it will be moer advisable to do a full
OPS Sequences: nocache == order ??
I'm managing an OPS configuration (4x HP 9000/N, HP-UX 11/64, RDBMS 8.1.7.1) and I'm having an application dependency on a temporal order of sequence numbers. With OPS that becomes a problem because each node caches a set of sequence numbers (20 by default). Oracle has an option, specifically for that situation, namely "ORDER". My question is whether ORDER is the same thing as NOCACHE and whether it is possible to have a NOCACHE sequence which will return numbers in an incorrect order (larger number before the smaller one). Please, o OPS gods and godesses, help me out and I'll sacrifice you a beer when I see you. Mladen Gogala
RE: How to speed up import
Are you importing to existing tables with indexes ?? Are you importing the indexes ?? One big way to speed up an import is to NOT import any indexes and, if the objects exist on the target database, to delete any indexes and rebuild them in a sperate action after the import is complete. -Original Message- Sent: Tuesday, September 03, 2002 4:35 PM To: Multiple recipients of list ORACLE-L I do backup database nightly using export utility. When I restore database using import, it takes more than 6 hours to finish. Is there a way to speed up import process? Please advise. Thanks, David -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Nguyen, David M INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists 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.com -- Author: Kevin Lange INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists 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: Great SQL Tuning Book
I agree with you Dennis, I've already bought it. - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Tuesday, September 03, 2002 12:34 PM I stumbled on this new book the other day and bought it. I think it is terrific. We DBAs are often asked to help tune SQL statements. This book offers lots of ideas for fixing SQL statements. Most thorough explanation of how the rule-based and cost-based optimizers work that I have encountered. Even provides a chart of the most common causes of bad SQL for each optimizer, and solutions for these problems. A valuable resource for every Oracle DBA. http://www.oreilly.com/catalog/orsqltunpr/ http://www.oreilly.com/catalog/orsqltunpr/ http://search.barnesandnoble.com/booksearch/isbnInquiry.asp?userid=1G60ZMKA1 J http://search.barnesandnoble.com/booksearch/isbnInquiry.asp?userid=1G60ZMKA 1Jisbn=0596002688 isbn=0596002688 Oracle SQL Tuning Pocket Reference Mark Gurry (Co-authored Oracle Performance Tuning way back in 1993 and updated since) O'Reilly $12.95 U.S. (at this price you might buy copies for some of your developers) Edited by Jonathan Gennick who is kind enough to answer questions on this list now and then. Dennis Williams DBA Lifetouch, Inc. [EMAIL PROTECTED] -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: DENNIS WILLIAMS INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists 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.com -- Author: Ramon E. Estevez INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists 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: Great SQL Tuning Book
I agree with you Dennis, I've already bought it. - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Tuesday, September 03, 2002 12:34 PM I stumbled on this new book the other day and bought it. I think it is terrific. We DBAs are often asked to help tune SQL statements. This book offers lots of ideas for fixing SQL statements. Most thorough explanation of how the rule-based and cost-based optimizers work that I have encountered. Even provides a chart of the most common causes of bad SQL for each optimizer, and solutions for these problems. A valuable resource for every Oracle DBA. http://www.oreilly.com/catalog/orsqltunpr/ http://www.oreilly.com/catalog/orsqltunpr/ http://search.barnesandnoble.com/booksearch/isbnInquiry.asp?userid=1G60ZMKA1 J http://search.barnesandnoble.com/booksearch/isbnInquiry.asp?userid=1G60ZMKA 1Jisbn=0596002688 isbn=0596002688 Oracle SQL Tuning Pocket Reference Mark Gurry (Co-authored Oracle Performance Tuning way back in 1993 and updated since) O'Reilly $12.95 U.S. (at this price you might buy copies for some of your developers) Edited by Jonathan Gennick who is kind enough to answer questions on this list now and then. Dennis Williams DBA Lifetouch, Inc. [EMAIL PROTECTED] -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: DENNIS WILLIAMS INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists 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.com -- Author: Ramon E. Estevez INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists 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: OPS Sequences: nocache == order ??
It looks like when option "ORDER" is used Oracle guarantees the generated values will be in order since the "CACHE" option will be ignored by Oracle even if it was requested. This is in the parallel mode. Look at note: Note:1031850.6 Waleed -Original Message-From: Gogala, Mladen [mailto:[EMAIL PROTECTED]]Sent: Tuesday, September 03, 2002 6:00 PMTo: Multiple recipients of list ORACLE-LSubject: OPS Sequences: nocache == order ?? I'm managing an OPS configuration (4x HP 9000/N, HP-UX 11/64, RDBMS 8.1.7.1) and I'm having an application dependency on a temporal order of sequence numbers. With OPS that becomes a problem because each node caches a set of sequence numbers (20 by default). Oracle has an option, specifically for that situation, namely "ORDER". My question is whether ORDER is the same thing as NOCACHE and whether it is possible to have a NOCACHE sequence which will return numbers in an incorrect order (larger number before the smaller one). Please, o OPS gods and godesses, help me out and I'll sacrifice you a beer when I see you. Mladen Gogala
RE: automatic segment space management
So, proper LMT means no LBE? ;) Great analogy! All the head-spinning and the green projectile vomiting and such... BTW, yes that is a good paper. I've read it and am trying to deal with the extent sizes as it applies to our DB, as only about two dozen of the 800+ tables are larger than 128MB and none are larger than 4GB. So, either I consider making the Large LMTs smaller, or maybe our li'l 25GB DB doesn't need a Papa Bear. Or maybe I just need to get used to the idea of having more than a couple hundred extents... :) Thanks! Rich Jesse System/Database Administrator [EMAIL PROTECTED] Quad/Tech International, Sussex, WI USA -Original Message- From: DENNIS WILLIAMS [mailto:[EMAIL PROTECTED]] Sent: Tuesday, September 03, 2002 3:04 PM To: Multiple recipients of list ORACLE-L Subject: RE: automatic segment space management Rich - Good point. Yes, I create all the tables here, at least in production, and I probably wouldn't use autoextend if the situation were otherwise. The other thing to consider is if you are using uniform extents, by definition you have bought into the philosophy that you can have many extents and your database will not do a Linda Blair Exorcist imitation on you. If we use the guideline that the number of extents should be not many more than 1,000, then the 128K extent will get you 128M, which is good for most tables. While we are on the subject, anyone considering switching to LMTs should carefully read How to Stop Defragmenting and Start Living by Juan Loaiza, Rosanne Toohey, Bhaskar Himatsingka. You can find a copy on http://www.hotsos.com. Trying to implement a philosophy without fully understanding it is a recipe for failure. Dennis Williams -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Jesse, Rich INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists 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).
PCTUSED - when is block added to freelist?
I have one huge table (takes up about 30% of the total database storage) which has a monthly batch deletion of old data. I had PCTFREE and PCTUSED set to the defaults of 10 and 40 respectively. I occurred to me that I could probably free up a lot more space by increasing the PCTUSED so that more blocks would be available to be written to (since getting more storage for the server is a bureaucratic nightmare here). So my question is, if I just raise the PCTUSED from 40 to, say, 75 would all blocks that fall into the 40-75 range become available for inserts? Or is it only after their next update or delete? Different sections of the docs seem to imply different things. The docs say: A lower PCTUSED reduces processing costs incurred during UPDATE and DELETE statements for moving a block to the free list when the block has fallen below that percentage of usage. This seems to imply that it won't be moved to the freelist until a delete or update is done that affects that block. But they also say: A higher PCTUSED increases processing cost during INSERTs and UPDATEs. This seems to imply that when it's looking to do the insert it might find that it can insert to a block. Anyway, why would a lower PCTUSED reduce processing costs during a DELETE but a higher PCTUSED wouldn't increase processing costs during a DELETE. That makes no sense. I'm befuddled. TIA, Jay Miller -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Miller, Jay INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists 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).
TEST -- pls ignore
RE: PCTUSED - when is block added to freelist?
1- Delete means freeing space. 2- When the used space in the blocks falls below PCTUSED, the block needs to go back to the free list. 3- This requires extra processing cost. 4- So lowering PCTUSED will lower the frequency of triggering this procedure. Waleed -Original Message- Sent: Tuesday, September 03, 2002 6:39 PM To: Multiple recipients of list ORACLE-L I have one huge table (takes up about 30% of the total database storage) which has a monthly batch deletion of old data. I had PCTFREE and PCTUSED set to the defaults of 10 and 40 respectively. I occurred to me that I could probably free up a lot more space by increasing the PCTUSED so that more blocks would be available to be written to (since getting more storage for the server is a bureaucratic nightmare here). So my question is, if I just raise the PCTUSED from 40 to, say, 75 would all blocks that fall into the 40-75 range become available for inserts? Or is it only after their next update or delete? Different sections of the docs seem to imply different things. The docs say: A lower PCTUSED reduces processing costs incurred during UPDATE and DELETE statements for moving a block to the free list when the block has fallen below that percentage of usage. This seems to imply that it won't be moved to the freelist until a delete or update is done that affects that block. But they also say: A higher PCTUSED increases processing cost during INSERTs and UPDATEs. This seems to imply that when it's looking to do the insert it might find that it can insert to a block. Anyway, why would a lower PCTUSED reduce processing costs during a DELETE but a higher PCTUSED wouldn't increase processing costs during a DELETE. That makes no sense. I'm befuddled. TIA, Jay Miller -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Miller, Jay INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists 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.com -- Author: Khedr, Waleed INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists 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).
hash_value and address
I see 2 different addreses for the same hash_value in v$sqlarea. Why do I see this? Here's an example: hash_value address -- --- 3749804 4064082C 3749804 4192941C I wonder if someone can shed some light on this Txs ___ Yahoo! Messenger Nueva versión: Webcam, voz, y mucho más ¡Gratis! Descárgalo ya desde http://messenger.yahoo.es -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: =?iso-8859-1?q?Pablo=20Rodriguez?= INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists 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: OPS Sequences: nocache == order ??
If you run OPS and specify order, it works like no cache. My question to you: Why cripple OPS and your business performance by having this requirement ? Spending a few bucks to get rid of this dependency will improve the performance, until you run in to the next problem ;-) Anjo. On Wednesday 04 September 2002 00:00, you wrote: I'm managing an OPS configuration (4x HP 9000/N, HP-UX 11/64 , RDBMS 8.1.7.1) and I'm having an application dependency on a temporal order of sequence numbers. With OPS that becomes a problem because each node caches a set of sequence numbers (20 by default). Oracle has an option, specifically for that situation, namely ORDER. My question is whether ORDER is the same thing as NOCACHE and whether it is possible to have a NOCACHE sequence which will return numbers in an incorrect order (larger number before the smaller one). Please, o OPS gods and godesses, help me out and I'll sacrifice you a beer when I see you. Mladen Gogala -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Anjo Kolk INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists 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: OPS Sequences: nocache == order ??
Yes, but when analyzed, it turns out that NOCACHE will also yield ordered results. What I'm interested in are internal differences in behavior. My assumption is that with ORDER oracle queries the instances directly, while NOCACHE will simply read/write everything from the disk. On 2002.09.03 18:38 Khedr, Waleed wrote: It looks like when option ORDER is used Oracle guarantees the generated values will be in order since the CACHE option will be ignored by Oracle even if it was requested. This is in the parallel mode. Look at note: Note:1031850.6 Waleed -Original Message- Sent: Tuesday, September 03, 2002 6:00 PM To: Multiple recipients of list ORACLE-L I'm managing an OPS configuration (4x HP 9000/N, HP-UX 11/64 , RDBMS 8.1.7.1) and I'm having an application dependency on a temporal order of sequence numbers. With OPS that becomes a problem because each node caches a set of sequence numbers (20 by default). Oracle has an option, specifically for that situation, namely ORDER. My question is whether ORDER is the same thing as NOCACHE and whether it is possible to have a NOCACHE sequence which will return numbers in an incorrect order (larger number before the smaller one). Please, o OPS gods and godesses, help me out and I'll sacrifice you a beer when I see you. Mladen Gogala -- Mladen Gogala -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Mladen Gogala INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists 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: hash_value and address
Could because of different child cursors ?! On Wednesday 04 September 2002 01:04, you wrote: I see 2 different addreses for the same hash_value in v$sqlarea. Why do I see this? Here's an example: hash_value address -- --- 3749804 4064082C 3749804 4192941C I wonder if someone can shed some light on this Txs ___ Yahoo! Messenger Nueva versión: Webcam, voz, y mucho más ¡Gratis! Descárgalo ya desde http://messenger.yahoo.es -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Anjo Kolk INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists 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).
Pinned Objects Locking Problem
I run a script to grant privileges on database objects to developers and it normally completes in a few minutes. Lately, the script started hanging and getting ORA 4021 after 5 minutes. Discovered the problem is a pin lock. The sessions holding the locks are not even active, they had accessed a related object sometime earlier in their session. I ended up killing the sessions because I could not find a better way, but there must be cleaner way to release these pin locks? And why is this happening now in 9i? Never had this problem before... Thanks, Debi -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Deborah Lorraine INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists 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: OPS Sequences: nocache == order ??
The way I see it is: If you specify ORDER then the only way Oracle can enforce this is getting it from the dictionary which means no caching will be implemented. If you need the data to be ordered then (in my opinion) it's better to declare what you need by using option ORDER. Using option NOCACHE alone believing it will give you the same functionality will not be guaranteed from one release to the other. Waleed -Original Message- Sent: Tuesday, September 03, 2002 7:29 PM To: Multiple recipients of list ORACLE-L Yes, but when analyzed, it turns out that NOCACHE will also yield ordered results. What I'm interested in are internal differences in behavior. My assumption is that with ORDER oracle queries the instances directly, while NOCACHE will simply read/write everything from the disk. On 2002.09.03 18:38 Khedr, Waleed wrote: It looks like when option ORDER is used Oracle guarantees the generated values will be in order since the CACHE option will be ignored by Oracle even if it was requested. This is in the parallel mode. Look at note: Note:1031850.6 Waleed -Original Message- Sent: Tuesday, September 03, 2002 6:00 PM To: Multiple recipients of list ORACLE-L I'm managing an OPS configuration (4x HP 9000/N, HP-UX 11/64 , RDBMS 8.1.7.1) and I'm having an application dependency on a temporal order of sequence numbers. With OPS that becomes a problem because each node caches a set of sequence numbers (20 by default). Oracle has an option, specifically for that situation, namely ORDER. My question is whether ORDER is the same thing as NOCACHE and whether it is possible to have a NOCACHE sequence which will return numbers in an incorrect order (larger number before the smaller one). Please, o OPS gods and godesses, help me out and I'll sacrifice you a beer when I see you. Mladen Gogala -- Mladen Gogala -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Mladen Gogala INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists 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.com -- Author: Khedr, Waleed INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists 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: OPS Sequences: nocache == order ??
Unfortunately, we have an application dependency and I was required to come up with a quick dirty fix. Thanks for your reply. On 2002.09.03 19:10 Anjo Kolk wrote: If you run OPS and specify order, it works like no cache. My question to you: Why cripple OPS and your business performance by having this requirement ? Spending a few bucks to get rid of this dependency will improve the performance, until you run in to the next problem ;-) Anjo. On Wednesday 04 September 2002 00:00, you wrote: I'm managing an OPS configuration (4x HP 9000/N, HP-UX 11/64 , RDBMS 8.1.7.1) and I'm having an application dependency on a temporal order of sequence numbers. With OPS that becomes a problem because each node caches a set of sequence numbers (20 by default). Oracle has an option, specifically for that situation, namely ORDER. My question is whether ORDER is the same thing as NOCACHE and whether it is possible to have a NOCACHE sequence which will return numbers in an incorrect order (larger number before the smaller one). Please, o OPS gods and godesses, help me out and I'll sacrifice you a beer when I see you. Mladen Gogala -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Anjo Kolk INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists 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). -- Mladen Gogala -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Mladen Gogala INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists 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: PCTUSED - when is block added to freelist?
Title: RE: PCTUSED - when is block added to freelist? The way I understand it: If you have a low pctused, then you have less blocks being moved to the freelist (because it's less probably that a block will be moved to the freelist): so reduced processing costs during update (if a row length is diminished by the update) or a delete. If you have a high pctused, then there's more of a chance of getting a chained (migrated) row, which would negatively affect the peformance of an insert or an update (if row length is increased by the update). My question is: why are the defaults pctfree 10 and pctused 40? Wouldn't it make more sense (less wasted space) for the defaults to be two numbers that add up to 90 of 95? e.g. pctfree 20 and pctused 70 -Original Message- From: Miller, Jay [mailto:[EMAIL PROTECTED]] I have one huge table (takes up about 30% of the total database storage) which has a monthly batch deletion of old data. I had PCTFREE and PCTUSED set to the defaults of 10 and 40 respectively. I occurred to me that I could probably free up a lot more space by increasing the PCTUSED so that more blocks would be available to be written to (since getting more storage for the server is a bureaucratic nightmare here). So my question is, if I just raise the PCTUSED from 40 to, say, 75 would all blocks that fall into the 40-75 range become available for inserts? Or is it only after their next update or delete? Different sections of the docs seem to imply different things. The docs say: A lower PCTUSED reduces processing costs incurred during UPDATE and DELETE statements for moving a block to the free list when the block has fallen below that percentage of usage. This seems to imply that it won't be moved to the freelist until a delete or update is done that affects that block. But they also say: A higher PCTUSED increases processing cost during INSERTs and UPDATEs. This seems to imply that when it's looking to do the insert it might find that it can insert to a block. Anyway, why would a lower PCTUSED reduce processing costs during a DELETE but a higher PCTUSED wouldn't increase processing costs during a DELETE. That makes no sense. I'm befuddled.
Re: PCTUSED - when is block added to freelist?
If I understand Jay's question correctly, what he's asking is not how PCTUSED and PCTFREE work, but what action or actions trigger Oracle to put a block back on the freelist after changing PCTUSED to a higher value? Is that correct Jay? Jared On Tuesday 03 September 2002 15:38, Miller, Jay wrote: I have one huge table (takes up about 30% of the total database storage) which has a monthly batch deletion of old data. I had PCTFREE and PCTUSED set to the defaults of 10 and 40 respectively. I occurred to me that I could probably free up a lot more space by increasing the PCTUSED so that more blocks would be available to be written to (since getting more storage for the server is a bureaucratic nightmare here). So my question is, if I just raise the PCTUSED from 40 to, say, 75 would all blocks that fall into the 40-75 range become available for inserts? Or is it only after their next update or delete? Different sections of the docs seem to imply different things. The docs say: A lower PCTUSED reduces processing costs incurred during UPDATE and DELETE statements for moving a block to the free list when the block has fallen below that percentage of usage. This seems to imply that it won't be moved to the freelist until a delete or update is done that affects that block. But they also say: A higher PCTUSED increases processing cost during INSERTs and UPDATEs. This seems to imply that when it's looking to do the insert it might find that it can insert to a block. Anyway, why would a lower PCTUSED reduce processing costs during a DELETE but a higher PCTUSED wouldn't increase processing costs during a DELETE. That makes no sense. I'm befuddled. TIA, Jay Miller -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Jared Still INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists 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: automatic segment space management
Exactly. Ian MacGregor Stanford Linear Accelerator Center [EMAIL PROTECTED] -Original Message- Sent: Tuesday, September 03, 2002 2:23 PM To: Multiple recipients of list ORACLE-L Thanks Ian, that was indeed the question, although the other information is useful as well. if I understand what you are saying correctly, it works but it won't really buy me anything and I might do better controlling the space myself. And while you have had no problems, you've heard negative things about it. I can't afford to have this database not be available so I'll manage them on my own Rachel --- MacGregor, Ian A. [EMAIL PROTECTED] wrote: The question posed was not whether extent management local should be used, but whether automatic segment space management should be used. As this is a data warehouse, I would not expect you to have transactions trying to change the same block. Assuming you are loading; that is, inserting data and not doing updates, wouldn't you try to cram as much data as possible into a block? Seems this could be done more easily by controlling these parameters yourself I've got one system using automatic segment space management without any problems, however when I posed the same question on using it a few months ago, the respone which trickled in way highly negative concerning its usage. Ian MacGregor Stanford Linear Accelerator Center [EMAIL PROTECTED] -Original Message- Sent: Tuesday, September 03, 2002 8:49 AM To: Multiple recipients of list ORACLE-L time for me to ask the experts again. My data warehouse will be 9.2, with all locally managed tablespaces. We will be following what I have taken to calling the Goldilocks principle -- that of small, medium and large tablespace extent sizes, with variations in that we will separate indexes and data, and will have even more separation for our fact tables into partitioned tables and tablespaces. However, now comes the time for me to work out storage clauses. And a quick read through the docs leaves me wondering if I should just turn on automatic segment-space management and not worry about setting PCTFREE, PCTUSED and FREELIST parameters. I can't find any real information or bugs on MetaLink either. Does anyone have any experience, good OR bad, with using this feature? If you are doing data warehouse work, what are good values for the parameters if I DO use them? One fact table is likely to be highly updated (customer info) as we collect more and more specific information from customers. The rest will be, as you would expect from a DW, mostly inserts. Help? Thanks! Rachel __ Do You Yahoo!? Yahoo! Finance - Get real-time stock quotes http://finance.yahoo.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Rachel Carmichael INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists 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.com -- Author: MacGregor, Ian A. INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists 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! Finance - Get real-time stock quotes http://finance.yahoo.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Rachel Carmichael INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists 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:
RE: automatic segment space management
While Oracle says you can have unlimited extents, practically it doesn't hand more than at MOST between 1000 and 4000 extents in a table. And you can just adjust your baby bear, mama bear and papa bear extent sizes in all your tablespaces, no one says the extent sizes are fixed! Geez, years from now, when someone talks about the Goldilocks method of extent management I'll be able to say I started it all. then I'll start running for my life. :) Rachel --- Jesse, Rich [EMAIL PROTECTED] wrote: So, proper LMT means no LBE? ;) Great analogy! All the head-spinning and the green projectile vomiting and such... BTW, yes that is a good paper. I've read it and am trying to deal with the extent sizes as it applies to our DB, as only about two dozen of the 800+ tables are larger than 128MB and none are larger than 4GB. So, either I consider making the Large LMTs smaller, or maybe our li'l 25GB DB doesn't need a Papa Bear. Or maybe I just need to get used to the idea of having more than a couple hundred extents... :) Thanks! Rich Jesse System/Database Administrator [EMAIL PROTECTED] Quad/Tech International, Sussex, WI USA -Original Message- From: DENNIS WILLIAMS [mailto:[EMAIL PROTECTED]] Sent: Tuesday, September 03, 2002 3:04 PM To: Multiple recipients of list ORACLE-L Subject: RE: automatic segment space management Rich - Good point. Yes, I create all the tables here, at least in production, and I probably wouldn't use autoextend if the situation were otherwise. The other thing to consider is if you are using uniform extents, by definition you have bought into the philosophy that you can have many extents and your database will not do a Linda Blair Exorcist imitation on you. If we use the guideline that the number of extents should be not many more than 1,000, then the 128K extent will get you 128M, which is good for most tables. While we are on the subject, anyone considering switching to LMTs should carefully read How to Stop Defragmenting and Start Living by Juan Loaiza, Rosanne Toohey, Bhaskar Himatsingka. You can find a copy on http://www.hotsos.com. Trying to implement a philosophy without fully understanding it is a recipe for failure. Dennis Williams -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Jesse, Rich INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists 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! Finance - Get real-time stock quotes http://finance.yahoo.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Rachel Carmichael INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists 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: OPS Sequences: nocache == order ??
I agree with Anoj, you need to talk to the business folks to remove this dependency. Else you may encounter waits/queues on getting the next sequence numbers. One of the benfits in OPS and in RAC is the sequence cache option, because each instance will not have to query the Oracle's fast cache areas for the next sequence or wait in queue to get the next number. Any ways if you see slowness you now where to look! Reply-To: [EMAIL PROTECTED] To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Date: Tue, 03 Sep 2002 15:54:06 -0800 Unfortunately, we have an application dependency and I was required to come up with a quick dirty fix. Thanks for your reply. On 2002.09.03 19:10 Anjo Kolk wrote: If you run OPS and specify order, it works like no cache. My question to you: Why cripple OPS and your business performance by having this requirement ? Spending a few bucks to get rid of this dependency will improve the performance, until you run in to the next problem ;-) Anjo. On Wednesday 04 September 2002 00:00, you wrote: I'm managing an OPS configuration (4x HP 9000/N, HP-UX 11/64 , RDBMS 8.1.7.1) and I'm having an application dependency on a temporal order of sequence numbers. With OPS that becomes a problem because each node caches a set of sequence numbers (20 by default). Oracle has an option, specifically for that situation, namely ORDER. My question is whether ORDER is the same thing as NOCACHE and whether it is possible to have a NOCACHE sequence which will return numbers in an incorrect order (larger number before the smaller one). Please, o OPS gods and godesses, help me out and I'll sacrifice you a beer when I see you. Mladen Gogala -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Anjo Kolk INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists 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). -- Mladen Gogala -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Mladen Gogala INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists 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). Murali Vallath Oracle Certified DBA http://www8.ewebcity.com/muralivallath/ http://www.summerksyus.com/ _ Chat with friends online, try MSN Messenger: http://messenger.msn.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Murali Vallath INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists 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: ArcServe 2000 Agent for Oracle
Jared, i hope u hv taken care of veritas nbu licenses. the resellers try to apply 1 Oracle agent license for each database instance instead of 1 license for each server machine. -Mandar -Original Message- From: Jared Still [mailto:[EMAIL PROTECTED]] Sent: Tuesday, September 03, 2002 2:23 PM To: Multiple recipients of list ORACLE-L Subject: Re: ArcServe 2000 Agent for Oracle The ArcServe agent has a tendency to login to your database and never logout, eventually eating up all your processes or logins if you don't keep an eye on it. It also tries to backup TEMPORARY tablespaces by putting them in backup mode, generating an ORA-3217 in the process. FYI: We're replacing it this month with Veritas Net Backup. Jared On Sunday 01 September 2002 19:38, Jahan Shanai wrote: Hi, We are planning to use ArcServe 2000 Agent for Oracle to take Hot Backup of our one of the databases. I was wondering whether anybody encountered any issue? Thanks for youe feebback in advance. Jahan Environment: ArcServe 2000 Agent for Oracle Windows 2000 SP2 Oracle 8.1.7.2.1 Standard Edition Attention: = This e-mail message and accompanying data may contain information that is confidential and subject to legal privilege. If you are not the intended recipient, you are notified that any use, dissemination, or copying of any part of this e-mail message and accompanying data, is prohibited. If you have received this e-mail message in error, please notify us immediately and delete this e-mail message from your computer. -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Jared Still INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists 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.com -- Author: Mandar A. Ghosalkar INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists 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: Pls respond...: URGENT
Try RE-Booting the Machine , if you think the behaviour is very abnormal If you are on IBM AIX , ensure that you have applied the the 1 off patch for fsync available on 8.1.7.4 (32 Bit) OR 8.1.7.2 (64 Bit) Setting Multiple db_writers might help , after turning disk_async_io = false . O.S. Other than Solaris , AIX may NOt have a proper implementation of asynchronous IO If you have a Test Machine Load the Database on that see if it is choking too -Original Message- Sent: Wednesday, September 04, 2002 2:29 AM To: Multiple recipients of list ORACLE-L My one of disk showing full active...very hot!!! by bground processes accessing... hdisk51 88.7 926.6 139.3 2780 8 hdisk51 85.5 1217.0 176.2 3660 0 hdisk51 91.8 980.2 149.3 2948 From: Peter R [EMAIL PROTECTED] To: LazyDBA.com Discussion [EMAIL PROTECTED] Subject: Pls respond...: URGENT Date: Tue, 03 Sep 2002 19:32:14 + Hi Friends, My all background processors accessing same file system, I did some reorg this weekend, But I shut down database like 4 times...all processors are waitingAny help will be highly appreicated!!! oracle@baan1 $ ps -ef|grep 14816 oracle 14816 1 2 13:59:00 - 0:49 ora_dbwr_baanIV oracle 80572 18974 2 14:27:27 pts/1 0:00 grep 14816 oracle@baan1 $ ps -ef|grep 15336 oracle 15336 1 0 13:59:00 - 0:12 ora_lgwr_baanIV oracle 83158 18974 2 14:27:55 pts/1 0:00 grep 15336 oracle@baan1 $ ps -ef|grep 17644 oracle 17644 1 0 13:59:00 - 0:16 ora_smon_baanIV oracle 85890 18974 2 14:28:21 pts/1 0:00 grep 17644 oracle@baan1 $ ps -ef|grep 18164 oracle 18164 1 0 13:59:00 - 0:03 ora_db01_baanIV oracle 84380 18974 1 14:28:44 pts/1 0:00 grep 18164 Thanks peter. _ Join the world's largest e-mail service with MSN Hotmail. http://www.hotmail.com Oracle documentation is here: http://tahiti.oracle.com/pls/tahiti/tahiti.homepage To unsubscribe: send a blank email to [EMAIL PROTECTED] To subscribe: send a blank email to [EMAIL PROTECTED] Visit the list archive: http://www.LAZYDBA.com/odbareadmail.pl Tell yer mates about http://www.farAwayJobs.com By using this list you agree to these terms:http://www.lazydba.com/legal.html _ Send and receive Hotmail on your mobile device: http://mobile.msn.com Oracle documentation is here: http://tahiti.oracle.com/pls/tahiti/tahiti.homepage To unsubscribe: send a blank email to [EMAIL PROTECTED] To subscribe: send a blank email to [EMAIL PROTECTED] Visit the list archive: http://www.LAZYDBA.com/odbareadmail.pl Tell yer mates about http://www.farAwayJobs.com By using this list you agree to these terms:http://www.lazydba.com/legal.html _ Chat with friends online, try MSN Messenger: http://messenger.msn.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Peter R INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists 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.com -- Author: VIVEK_SHARMA INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists 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: Pls respond...: URGENT
if exp/imp were used Ensure that Statistics are DELETED ( RE-Created , if needed) as they will be highly incorrect Check for DEGREE 1 of the Tables Indexes . This will also Cause Optimizer to choose BAD Plans if NO Statistics Exist -Original Message- Sent: Wednesday, September 04, 2002 3:00 AM To: Multiple recipients of list ORACLE-L Is it possible there is a full analyze running on the database? You said you did a reorg this weekend. If you bounced it 4 times it could be going through backout and recovery. Either one would keep it pretty busy. R. Smith -Original Message- Sent: Tuesday, September 03, 2002 4:08 PM To: Multiple recipients of list ORACLE-L My database okay, its slow, I can't do much work, too much waitingI want to bring as normal processing... From: Fink, Dan [EMAIL PROTECTED] To: '[EMAIL PROTECTED]' [EMAIL PROTECTED] Subject: RE: Pls respond...: URGENT Date: Tue, 3 Sep 2002 13:58:02 -0600 Peter, What is the problem? What you are asking is not clear, which could be one reason you are not getting a response. Try restating the issue with exactly what the condition of the db is (up or down), what errors are being reported, etc. Dan Fink -Original Message- From: Peter R [mailto:[EMAIL PROTECTED]] Sent: Tuesday, September 03, 2002 2:33 PM To: Multiple recipients of list ORACLE-L Subject: Pls respond...: URGENT Hi Friends, My all background processors accessing same file system, I did some reorg this weekend, But I shut down database like 4 times...all processors are waitingAny help will be highly appreicated!!! oracle@baan1 $ ps -ef|grep 14816 oracle 14816 1 2 13:59:00 - 0:49 ora_dbwr_baanIV oracle 80572 18974 2 14:27:27 pts/1 0:00 grep 14816 oracle@baan1 $ ps -ef|grep 15336 oracle 15336 1 0 13:59:00 - 0:12 ora_lgwr_baanIV oracle 83158 18974 2 14:27:55 pts/1 0:00 grep 15336 oracle@baan1 $ ps -ef|grep 17644 oracle 17644 1 0 13:59:00 - 0:16 ora_smon_baanIV oracle 85890 18974 2 14:28:21 pts/1 0:00 grep 17644 oracle@baan1 $ ps -ef|grep 18164 oracle 18164 1 0 13:59:00 - 0:03 ora_db01_baanIV oracle 84380 18974 1 14:28:44 pts/1 0:00 grep 18164 Thanks peter. _ Join the world's largest e-mail service with MSN Hotmail. http://www.hotmail.com Oracle documentation is here: http://tahiti.oracle.com/pls/tahiti/tahiti.homepage To unsubscribe: send a blank email to [EMAIL PROTECTED] To subscribe: send a blank email to [EMAIL PROTECTED] Visit the list archive: http://www.LAZYDBA.com/odbareadmail.pl Tell yer mates about http://www.farAwayJobs.com By using this list you agree to these terms:http://www.lazydba.com/legal.html _ Send and receive Hotmail on your mobile device: http://mobile.msn.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Peter R INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists 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). _ Chat with friends online, try MSN Messenger: http://messenger.msn.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Peter R INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists 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.com -- Author: Smith, Ron L. INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists 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
require info on OID / LDAP / LDIF
list, i have been learning about OID for the last couple of weeks.. i have managed to install and run the ldap server on my w2k, i can run the OID manager GUI, and can also do a ldap://wb-rahul:4034; from my IE5 now i want to pupulate the directory with some new info, and test the storage and search of OID... there are a couple of things i do not understand... 1) my understanding of oid is : it would store (and display) static data in a tree like hierarchical structure is this true ? 2) suppose i want to store state, street name, zip in a tree like order.. how to do it ? the syntax is too complicated for me.!! all the docs on metalink writes about cn and dn and sn anyone out there using OID/LDAP in this fashion ? TIA -rahul -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Rahul INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists 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: automatic segment space management
One note: 9i automatic segment space management does not automate PCTFREE; that still functions as before. It does cause PCTUSED, FREELISTS, and FREELIST GROUPS to be ignored, however... - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Tuesday, September 03, 2002 9:48 AM time for me to ask the experts again. My data warehouse will be 9.2, with all locally managed tablespaces. We will be following what I have taken to calling the Goldilocks principle -- that of small, medium and large tablespace extent sizes, with variations in that we will separate indexes and data, and will have even more separation for our fact tables into partitioned tables and tablespaces. However, now comes the time for me to work out storage clauses. And a quick read through the docs leaves me wondering if I should just turn on automatic segment-space management and not worry about setting PCTFREE, PCTUSED and FREELIST parameters. I can't find any real information or bugs on MetaLink either. Does anyone have any experience, good OR bad, with using this feature? If you are doing data warehouse work, what are good values for the parameters if I DO use them? One fact table is likely to be highly updated (customer info) as we collect more and more specific information from customers. The rest will be, as you would expect from a DW, mostly inserts. Help? Thanks! Rachel __ Do You Yahoo!? Yahoo! Finance - Get real-time stock quotes http://finance.yahoo.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Rachel Carmichael INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists 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.com -- Author: Tim Gorman INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists 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: OPS Sequences: nocache == order ??
Mladen,
Is there any way to have developers/users access
the sequence via a function, instead of accessing the sequence
directly?
If so, then perhaps you could modify the sequence
to addthe temporal component, while maintaining the use of a cached
sequence for uniqueness? Such as:
SQL create or replace function
gen_seqq(in_seq in number)
2return number 3
as 4 v_return_nbr number;
5 begin 6
selectto_number(to_char(sysdate,'MMDDHH24MISS')||ltrim(to_char(in_seq,'')))
7
intov_return_nbr
8 from dual;
9 return v_return_nbr;10*
end gen_seqq;SQL /
Function created.
SQL create table x (y number);
Table created.
SQL create sequence xq;
Sequence created.
SQL insert into x values
(gen_seqq(xq.nextval));
1 row created.
SQL
Big and ugly numbers yes, but I think some folks
get a strange thrill out of 20-digit numbers.
It fits the requirement of being temporal (to the
second, at least) and unique. You can throw in HSECS from V$TIMER if
someone gets picky enough to want to go to the centi-second level as well.
Yeah, and you can throw in USERENV('INSTANCEID') too, just for some real
OPS/RAC-ness! Best of all, it fits the DBA-half of your brain by being
fully cacheable and non-pinging...
...of course, you can embed the use of the SEQUENCE
object inside the function; I left it on the "outside" in this example
just to make it more flexible with regard to which sequence object it
uses...
If they don't like the idea of using a stored
function to get the sequence number, then tell 'em that "it's more ANSI standard
that way" and it's "database independent". That gets 'em every
time...
Hope this helps...
-Tim
- Original Message -
From: "Mladen Gogala" [EMAIL PROTECTED]
To: "Multiple recipients of list ORACLE-L"
[EMAIL PROTECTED]
Sent: Tuesday, September 03, 2002 5:54
PM
Subject: Re: OPS Sequences: nocache == order
??
Unfortunately, we have an application dependency and I was required
to come up with a quick dirty fix. Thanks for your reply.
On 2002.09.03 19:10 Anjo Kolk wrote:
If you run OPS and specify order, it works like no cache.
My question to you: "Why cripple OPS and your business performance
by having this requirement ?" Spending a few bucks to get rid of
this dependency will improve the performance, until you run in to
the next problem ;-)Anjo.
On Wednesday 04
September 2002 00:00, you wrote: I'm managing an OPS
configuration (4x HP 9000/N, HP-UX 11/64 , RDBMS
8.1.7.1) and I'm having an application dependency on a
temporal order of sequence numbers. With OPS
that becomes a problem because each node caches a set of sequence
numbers (20 by default). Oracle has an option,
specifically for that situation, namely "ORDER".
My question is whether ORDER is the same thing as NOCACHE and whether it
is possible to have a NOCACHE sequence which
will return numbers in an incorrect order (larger
number before the smaller one). Please, o
OPS gods and godesses, help me out and I'll sacrifice you a beer
when I see you. Mladen Gogala
-- Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- Author: Anjo Kolk INET: [EMAIL PROTECTED]
Fat City Network Services -- (858) 538-5051 FAX:
(858) 538-5051 San Diego,
California -- Public Internet access /
Mailing Lists
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).--
Mladen Gogala -- Please see the official ORACLE-L FAQ:
http://www.orafaq.com --
Author: Mladen Gogala INET: [EMAIL PROTECTED]
Fat City Network Services -- (858) 538-5051 FAX: (858)
538-5051 San Diego, California
-- Public Internet access / Mailing Lists
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).
Changing sysdate
Title: RE: Constraints problem Hi, Is it possible to change the sysdate? (Solaris 5.8, Oracle 8.1.7) TIA. K.
Inserts are taking time !
Hi All, We have a table which can contain more than half a million records. When we try to insert some 10k records in the empty table it get inserted in 10 min. but as the size increases time taken to insert also increases. After 350,000 records it takes around an hour to insert 10k records. There are around 15 columns in it out of which 11 are indexed. There is one concatenated function-based index on two columns of Varchar type and two separate index for the same two columns. I have checked the free space for the tablespaces to which the table and indexes are attached to. They are in two separate tbs. Any clues why this is happenning. TIA Marul.
