Re: How do YOU use Java in the DB?
I use it for emails with attachments and running OS commands within database; Look at akadia.com for javamail and here it explains how it is used. - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Friday, June 07, 2002 5:03 AM I use it for email. Jared On Thursday 06 June 2002 14:07, Jesse, Rich wrote: So, we've finally taken the jump and are using JSPs (erm...Java Stored Procedures, not Java Server Pages) on 8.1.7.2.0. Informal Poll: How do you use Java Stored Procedures? 1) Easy interface from PL/SQL to lp and other OS commands. 2) 3) 4) ... Rich Jesse System/Database Administrator [EMAIL PROTECTED] Quad/Tech International, Sussex, WI USA -- 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: Bunyamin Karadeniz 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: Can we find SQL user
We can definitely find the user , but considering my scenario 'all the people are logged in with the same oracle user' , I want to know:From what machine the SQL statement was fired. thx Sam name--- Reardon, Bruce (CALBBAY) [EMAIL PROTECTED] wrote: Have a look at v$session In particular the osuser, terminal and machine fields - these may help Also look at the listener log file - this may help Or do you have an application server sitting in the middle? HTH, Bruce Reardon -Original Message- From: sam d [mailto:[EMAIL PROTECTED]] Sent: Friday, 7 June 2002 15:18 Hi List, Suppose I have m1,m2,m3 machines, all the users sitting on these machines are using oracle 'user1' to connect to the server. As all the people are logged in with the same user name ,Can we find which user(or machine) has issued which SQL statement. Thanks Sam __ Do You Yahoo!? Yahoo! - Official partner of 2002 FIFA World Cup http://fifaworldcup.yahoo.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: sam d 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).
9iAS - Error WTE-03503
Title: 9iAS - Error WTE-03503 During the installation of 9iAS on my system, with Origin DB residing on the same machine as the APP Server (as this is a trial installation, will shift to the appropriate arrangement once its all oktested), I am facing a problem in installing the Database cache. It got stuck at 96% with the error that Can't import users. I found this document on Metalink which said the workaround for this is to "use DBA studio and import users/sync users option". Can anyone explain this in more detail. I tried to launch DBA studio (the one which comes with Isuite and not the DBA studio of Oracle 8i-Origin DB- installed on the same machine) and for import it required Management server to be configured, which I did. But now when I try to launch DBA studio with the Management server option, it requires a username password with the newly added management server name written in the management server field. Which username would it be. What can be the problems with wte-03503, and possible solutions for that. Regards, Hussain Ahmed Qadri DBA Shaukat Khanum Memorial Cancer Hospital And Research center
RE: Oracle 32 Bit running on Solaris 64 Bit
Helmut We run both on the same box, but only in dev. If my memory serves me correctly, there's a metalink note about it which recommends NOT using 64-bit oracle unless you need the increased memory addressing. We needed to increase the size of the shared pool above what we would use in 32-bit to avoid 'unable to allocate x bytes of shared memory' errors. It may also be slightly slower. HTH David Lord -Original Message- Sent: 06 June 2002 15:28 To: Multiple recipients of list ORACLE-L Hi there! We are running 32-Bit Oracle Software on Sun Solaris 8 (64-Bit). What is the advantage of doing this? Why don't we use 64-Bit Orlacle on 64-Bit Solaris? Nobody here can answer my question and the systems were set up by a consultant. So nobody really knows why this was done... Since we are talking about productions systems, upgrading Oracle Software is not an option... This is 8.1.7 on Sun Solaris. Thanks, Helmut ** This message (including any attachments) is confidential and may be legally privileged. If you are not the intended recipient, you should not disclose, copy or use any part of it - please delete all copies immediately and notify the Hays Group Email Helpdesk at [EMAIL PROTECTED] Any information, statements or opinions contained in this message (including any attachments) are given by the author. They are not given on behalf of Hays unless subsequently confirmed by an individual other than the author who is duly authorised to represent Hays. A member of the Hays plc group of companies. Hays plc is registered in England and Wales number 2150950. Registered Office Hays House Millmead Guildford Surrey GU2 4HJ. ** -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Lord, David - CSG 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).
[no subject]
no application server involved. thx Sameer -- We can definitely find the user , but considering my scenario 'all the people are logged in with the same oracle user' , I want to know:From what machine the SQL statement was fired. thx Sam name--- Reardon, Bruce (CALBBAY) [EMAIL PROTECTED] wrote: Have a look at v$session In particular the osuser, terminal and machine fields - these may help Also look at the listener log file - this may help Or do you have an application server sitting in the middle? HTH, Bruce Reardon -Original Message- From: sam d [mailto:[EMAIL PROTECTED]] Sent: Friday, 7 June 2002 15:18 Hi List, Suppose I have m1,m2,m3 machines, all the users sitting on these machines are using oracle 'user1' to connect to the server. As all the people are logged in with the same user name ,Can we find which user(or machine) has issued which SQL statement. Thanks Sam __ Do You Yahoo!? Yahoo! - Official partner of 2002 FIFA World Cup http://fifaworldcup.yahoo.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: sam d 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: Can we find SQL user
Sam, It looks like you have read Bruce's answer a little too fast. You might also like to RTFM the SQL Reference book, section 'functions', entry SYS_CONTEXT - although if you also want the SQL text, as Mladen said you must any way join V$SESSION and V$SQLAREA - or V$SQLTEXT if your statements are more than 999 characters long. Concerning Bruce's question about an application server, quite obviously if you have a tier sitting in the middle you are done - unless, that is, you use that wonderful package, DBMS_APPLICATION_INFO, to fill up (typically) CLIENT_INFO in which case you are back to SYS_CONTEXT/V$SESSION. - Original Message - From: sam d [EMAIL PROTECTED] To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Thu, 06 Jun 2002 23:13:21 We can definitely find the user , but considering my scenario 'all the people are logged in with the same oracle user' , I want to know:From what machine the SQL statement was fired. thx Sam name--- Reardon, Bruce (CALBBAY) [EMAIL PROTECTED] wrote: Have a look at v$session In particular the osuser, terminal and machine fields - these may help Also look at the listener log file - this may help Or do you have an application server sitting in the middle? HTH, Bruce Reardon -Original Message- From: sam d [mailto:[EMAIL PROTECTED]] Sent: Friday, 7 June 2002 15:18 Hi List, Suppose I have m1,m2,m3 machines, all the users sitting on these machines are using oracle 'user1' to connect to the server. As all the people are logged in with the same user name ,Can we find which user(or machine) has issued which SQL statement. Thanks Sam -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Stephane Faroul 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: Textfile into oracle
Title: RE: Textfile into oracle save as txt first -Original Message-From: Clinton Naude [mailto:[EMAIL PROTECTED]]Sent: 06 June 2002 22:28To: Multiple recipients of list ORACLE-LSubject: RE: Textfile into oracle Yes, use Sqlloader... -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]] Sent: Thursday, June 06, 2002 12:04 PM To: Multiple recipients of list ORACLE-L Subject: Textfile into oracle Hallo, I have this excelfile. Is it possible to import this into an oracle table. This file is supposed to be located on unix machine from the beginning and the import into an oracle table would be done from unix. How should I name the fields in the oracle table. I mean should I use the 9-10 different field names in the excelfile. There is at least 100 different columns in this excelfile. Is it possible to import a file like that into oracle table, Please help me with some hints. (See attached file: try.xls) Thanks in advance Roland
Oracle DBA with 8i through 11i Experience Needed in Columbus,
Position: Oracle Applications Side DBA Location: Columbus, Ohio area (Lancaster) Industry: Manufacturing Salary Range: Mid 70s Base Salary -depends on experience A relocation package is available. This company is an industry leader in its field of products. Growing,profitable, well established. Very good opportunities in this firm and its conglomerate parent firm. The Position: To provide support for ORACLE 11i and 9i running on HP-UX. Security, performance tuning, patch management, backup recovery, troubleshooting, upgrades, and user support. PLEASE DO NOT send your resume for this position UNLESS you and have the skills outlined below for this position. DO NOT send your resume unless you have a stable work history. Candidates whose work history includes frequent job changes connot be considered. If you are employed by a consulting company you must have a long term project history. This is a full time staff position so no sub-contractors or third parties please. No H1-B Candidates Please. Requirements: -Solid Oracle DBA experience.. 8i, 9i and 11i. -Solid PL/SQL experience. -Unix -U.S. citizenship or permanent residency For immediate consideration, please send your resume as a Word attachment to: OraStaff, Inc. Email: [EMAIL PROTECTED] Please use job code: One/Columbus/DBA/John ph: 1-800 -549-8502 All Submissions are handled in confidence. *We pay referral fees. So please contact me if you know of anyone who would be qualified/interested in the posiition described above- if it is not a match for your skills. Thanks, Bill Law -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: OraStaff 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: Mysterious Deadlock
Walter, It may help to see where and why the problem occurs: set the following event in the init.ora 60 trace name errorstack level 10 This will show where in the code oracle gets the deadlock and may help to diagnose your problem. Anjo. Walter K wrote: We have an application that is periodically encountering what appears to be a self-deadlock. Only one session is listed and it holds an exclusive (X) lock and is waiting for a share (S) lock with NO ROWS waited. I have pasted the deadlock graph at the bottom- -hopefully it will be legible. Does anyone know how such a lock could be produced? I'd really like to be able to recreate such a scenario. Thanks! -w Current SQL statement for this session: update ACCOUNT set ACCOUNT_ID=:ACCOUNT_ID,ACCOUNT_NAME=:ACCOUNT_NAME,LAST_ MODIFIED=:LAST_MODIFIED,CUSTOMER_NODE_ID=:CUSTOMER_NODE _ID,ACCOUNT_TYPE_ID=:ACCOUNT_TYPE_ID,ACCOUNT_BALANCE=:A CCOUNT_BALANCE,BALANCE_DATE=:BALANCE_DATE,INVOICE_ID=:I NVOICE_ID,PREVIOUS_INVOICE_ID=:PREVIOUS_INVOICE_ID,CRED IT_LIMIT=:CREDIT_LIMIT,UNBILLED_AMOUNT=:UNBILLED_AMOUNT ,ACCOUNT_ACTION_CODE=:ACCOUNT_ACTION_CODE,ACCOUNT_ACTIO N_DATE=:ACCOUNT_ACTION_DATE where ACCOUNT_ID=:key_ACCOUNT_ID The following deadlock is not an ORACLE error. It is a deadlock due to user error in the design of an application or from issuing incorrect ad-hoc SQL. The following information may aid in determining the deadlock: Deadlock graph: -Blocker(s) --- --Waiter(s)- Resource Name process session holds waits process session holds waits TX-0007004c-26bf34 95 X 34 95 S session 95: DID 0001-0025-0002E096 session 95: DID 0001-0025-0002E096 Rows waited on: Session 95: no row -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Walter K 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: 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).
Tool for migrating from forms 3 to forms 6
Hi Gurus any one have idea of any tool which can be used for migrating from forms 3 to forms 6. Help needed in this is very much appreciated Ayap This communication contains information, which is confidential and may also be privileged. It is for the exclusive use of the intended recipient(s). If you are not the intended recipient(s), please note that any distribution, printing, copying or use of this communication or the information in it is strictly prohibited. If you have received this communication in error, please notify the sender immediately and then destroy any copies of it. -- 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).
TAF
Hi , I am configuring TAF on oracle 8.1.7 (solaris). I was just wondering if anyone on the list has had experience on this and can give me any advice- more specifically on the the method- preconnect and basic. Are there any significant benefits of having the preconnect setting set? Any input would be really appreciated.. Rgds Fawzia ** Information in this email is confidential and may be privileged. It is intended for the addressee only. If you have received it in error, please notify the sender immediately and delete it from your system. You should not otherwise copy it, retransmit it or use or disclose its contents to anyone. Thank you for your co-operation. ** -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Malik, Fawzia 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: Oracle DBA with 8i through 11i Experience Needed in Columbus,
If you were advertising this in a decent country, you'd now be sued for discrimination. Unfortunately, here you can get away with this sort of rubbish... Cheers Nuno Souto [EMAIL PROTECTED] - Original Message - DO NOT send your resume unless you have a stable work history. Candidates whose work history includes frequent job changes connot be considered. If you are employed by a consulting company you must have a long term project history. -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Nuno Souto 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: Tool for migrating from forms 3 to forms 6
Hi, you could try migrator. See www.logis.cz Robert ---Original Message-- Date: Fri, 07 Jun 2002 03:13:37 -0800 To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED]Sender: [EMAIL PROTECTED] Reply-To: [EMAIL PROTECTED] Errors-To: [EMAIL PROTECTED] From: [EMAIL PROTECTED] Subject: Tool for migrating from forms 3 to forms 6 Organization: Fat City Network Services, San Diego, CaliforniaPrecedence: bulk Content-Transfer-Encoding: 7bit Hi Gurus any one have idea of any tool which can be used for migrating from forms 3to forms 6. Help needed in this is very much appreciated Ayap This communication contains information, which is confidential and may alsobe privileged. It is for the exclusive use of the intended recipient(s). Ifyou are not the intended recipient(s), please note that any distribution,printing, copying or use of this communication or the information in it isstrictly prohibited. If you have received this communication in error,please notify the sender immediately and then destroy any copies of it. -- 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-5051San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail messageto: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and inthe message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You mayalso send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: RT 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: Tool for migrating from forms 3 to forms 6
Hi, you could try migrator. See www.logis.cz Robert ---Original Message-- Date: Fri, 07 Jun 2002 03:13:37 -0800 To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED]Sender: [EMAIL PROTECTED] Reply-To: [EMAIL PROTECTED] Errors-To: [EMAIL PROTECTED] From: [EMAIL PROTECTED] Subject: Tool for migrating from forms 3 to forms 6 Organization: Fat City Network Services, San Diego, CaliforniaPrecedence: bulk Content-Transfer-Encoding: 7bit Hi Gurus any one have idea of any tool which can be used for migrating from forms 3to forms 6. Help needed in this is very much appreciated Ayap This communication contains information, which is confidential and may alsobe privileged. It is for the exclusive use of the intended recipient(s). Ifyou are not the intended recipient(s), please note that any distribution,printing, copying or use of this communication or the information in it isstrictly prohibited. If you have received this communication in error,please notify the sender immediately and then destroy any copies of it. -- 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-5051San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail messageto: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and inthe message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You mayalso send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: RT 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: Oracle DBA with 8i through 11i Experience Needed in Columbus,
Anyone who wants to know WHO this is, feel free to email me. Also, mid 70's for oracle apps DBA, what a joke. joe OraStaff wrote: Position: Oracle Applications Side DBA Location: Columbus, Ohio area (Lancaster) Industry: Manufacturing Salary Range: Mid 70s Base Salary -depends on experience A relocation package is available. This company is an industry leader in its field of products. Growing,profitable, well established. Very good opportunities in this firm and its conglomerate parent firm. The Position: To provide support for ORACLE 11i and 9i running on HP-UX. Security, performance tuning, patch management, backup recovery, troubleshooting, upgrades, and user support. PLEASE DO NOT send your resume for this position UNLESS you and have the skills outlined below for this position. DO NOT send your resume unless you have a stable work history. Candidates whose work history includes frequent job changes connot be considered. If you are employed by a consulting company you must have a long term project history. This is a full time staff position so no sub-contractors or third parties please. No H1-B Candidates Please. Requirements: -Solid Oracle DBA experience.. 8i, 9i and 11i. -Solid PL/SQL experience. -Unix -U.S. citizenship or permanent residency For immediate consideration, please send your resume as a Word attachment to: OraStaff, Inc. Email: [EMAIL PROTECTED] Please use job code: One/Columbus/DBA/John ph: 1-800 -549-8502 All Submissions are handled in confidence. *We pay referral fees. So please contact me if you know of anyone who would be qualified/interested in the posiition described above- if it is not a match for your skills. Thanks, Bill Law -- 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).
Oracle Manufacturing -Dba activities
Dear list Can someone give me info about Oracle manufacturing and dba responsibilities for the same. I will appreaciate the detailed feedback or any attachment to me directely. TIA Best Regards, Ramesh D Papnoi Oracle DBA @ Chemtex Global Engineers Pvt. Ltd., Mumbai, India (BrainBench Brainbuzz Certified Oracle 8/8i DBA Developer) http://www22.Brinkster.com/rpapnoi -- 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: runaway oracle.exe thread on NT / W2K
Tried it, but after 5 minutes - still there. Interestingly, is that if do shutdown immediate, the thread is still there!!! [EMAIL PROTECTED] 6/6/02 7:21:57 PM Jeffrey,As an idea - does orakill let you kill the thread off?This may let you "workaround" the issue without restarting the service.Bruce Reardon-Original Message-Sent: Friday, 7 June 2002 6:05This has now happened on 3 separeate boxes. This has happened while putting on an Oracle Applications patch or in the last case, after starting the concurrent managers for 11i with a lot of requests scheduled to compile all of the flex fields. In every instance, the thread id does not match anything in oracle.We notice that box is using 50-100% cpu even though nothing is running. Stop concurrent managers. Terminate web sessions. Exit all sqlplus sessions. Use pslist from sysinternals.com and it shows a running thread of oracle.exe using lots of user and kernal time. This thread id is not shown in v$session/processOracle has not been of much help to date.Even after doing a shutdown immediate, cpu is still high and thread is running. Have to stop the service to get rid of it all.We had been on 8.1.7.1.5 but upgraded to 8.1.7.3.2 since minimum for our Oracle Apps patches was 8.1.7.2.x just went to the latest and greatest since know eventually would be required.Has anyone else seen anything like this.Jeffrey BeckstromDatabase AdministratorGreater Cleveland Regional Transit Authority1240 W. 6th StreetCleveland, Ohio 44113(216) 781-4204--Please see the official ORACLE-L FAQ: http://www.orafaq.com--Author: Reardon, Bruce (CALBBAY) INET: [EMAIL PROTECTED]Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051San Diego, California -- Public Internet access / Mailing ListsTo REMOVE yourself from this mailing list, send an E-Mail messageto: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and inthe message BODY, include a line containing: UNSUB ORACLE-L(or the name of mailing list you want to be removed from). You mayalso send the HELP command for other information (like subscribing).
Re: Oracle DBA with 8i through 11i Experience Needed in Columbus,
Depends on what you mean by decent; quite a few silly laws have been enacted in many countries because of someone's idea of decency. In the US, the forms of discrimination that are legally prohibited involve race, gender, age, sexual orientation, ethnicity/national-origin, and a couple other things I don't recall right now. Restrictions like the ones mentioned in this advertisement aren't against the law, merely foolish. I personally don't believe that foolishness requires legislation, only malice... I'm sure Winston Churchill or someone equally clever had something relevant to say on the topic. At the moment, the only quote I can remember is Churchill's democracy is the absolute worst form of government, except of course for all the others... - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Friday, June 07, 2002 5:43 AM If you were advertising this in a decent country, you'd now be sued for discrimination. Unfortunately, here you can get away with this sort of rubbish... Cheers Nuno Souto [EMAIL PROTECTED] - Original Message - DO NOT send your resume unless you have a stable work history. Candidates whose work history includes frequent job changes connot be considered. If you are employed by a consulting company you must have a long term project history. -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Nuno Souto 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: runaway oracle.exe thread on NT / W2K
Problem is that can not reproduce on demand. Yesterday, we were doing the wrap up for PO G patches, i.e. generate messagess, flexfields, etc. Experienced the problem, tried redoing the same tasks and worked. Then after bringin up concurrent manager, lots of compile flexfield requests started (we had upped number of standard managers from 6 to 30). Since CPU at 100%, kept lowering number of standard managers from 30 to 20 and eventually back to 6. When requests done noticed cpu at 50%. Stopped everything and still at 50% due to a thread. Have been running 8.1.7.3.2 on another dev database for about a month with no problems, but there also had the problem but don't recall when since at the time thought it was a fluke. Did not get concerned until happened again (couple times) on another server and that's when openned the TAR. Therefore, don't know how to reproduce but getting leary of moving it to production. Jeffrey BeckstromDatabase AdministratorGreater Cleveland Regional Transit Authority1240 W. 6th StreetCleveland, Ohio 44113(216) 781-4204 [EMAIL PROTECTED] 6/6/02 7:31:32 PM If you cannot tie that thread to v$process then it is likely a problem with a background thread. If you are using sqlnet expire time it creates 2 threads for each connection, the timer thread will not show up in v$process. There are also a few other threads that will not show up related to process management. One approach: Once you have a database instance with this problem that you are willing to crash you can attach to it with a debugger to get a look at what is up. The simple way to do this with little expertise is to use: drwtsn32 -p oracle.exe_pid# This will generate a dump file (given that you haven't reconfigured dr watson) that support can review (well, the BDE group can) for content. They'll need to know the EXACT version of the database you have as well as the OS version (including service packs and hot fixes) to get the right dbg symbols in place. Regards,Michael SaleAuthor: Oracle9i for Windows(R) 2000 Tips Techniqueshttp://www.amazon.com/exec/obidos/ASIN/0072194626 -Original Message-From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]] On Behalf Of Jeffrey BeckstromSent: Thursday, June 06, 2002 2:05 PMTo: Multiple recipients of list ORACLE-LSubject: runaway oracle.exe thread on NT / W2K This has now happened on 3 separeate boxes. This has happened while putting on an Oracle Applications patch or in the last case,after starting the concurrent managers for 11i with a lot of requests scheduled to compile all of the flex fields. In every instance, the thread id does not match anything in oracle. We notice that box is using 50-100% cpu even though nothing is running. Stop concurrent managers. Terminate web sessions. Exit all sqlplus sessions. Use pslist from sysinternals.com and it shows a running thread of oracle.exe using lots of user and kernal time. This thread id is not shown in v$session/process Oracle has not been of much help to date. Even after doing a shutdown immediate, cpu is still high and thread is running. Have to stop the service to get rid of it all. We had been on 8.1.7.1.5 but upgraded to 8.1.7.3.2 since minimum for our Oracle Apps patches was 8.1.7.2.x just went to the latest and greatest since know eventually would be required. Has anyone else seen anything like this. Jeffrey BeckstromDatabase AdministratorGreater Cleveland Regional Transit Authority1240 W. 6th StreetCleveland, Ohio 44113(216) 781-4204p
Re: TAF
Fawzia, PRECONNECT establishes the second/failover database connection along with the first/primary, so your client-server database connection is really client-server1/server2. Should the first/primary connection fail, then you failover to the second. BASIC establishes only the first/primary database connection but has the information available to establish the second/failover connection should the first/primary fail. PRECONNECT will failover faster, as the second/failover connection has already been established. In a large environment (i.e. hundreds of database connections) full of BASIC connections, you can expect the mass exodus from one database instance to the failover to cause BASIC connections to wait a noticeable amount of time. One problem with PRECONNECT that I've noticed is the scenario where the second/failover instance is terminated for some reason first. Let's say a bunch of PRECONNECT TAF connections are working away happily via their first/primary connection to the primary database instance, and there is some reason to terminate the second database instance or some/all of the second/failover database connections to the second database instance. In this case, PRECONNECT will not notice the loss of the second/failover database connection, allowing the first/primary connection to continue working happily away. But after bringing the second database instance back online, if the first database instance should now fail, then all those PRECONNECT sessions will simply fail. Upshot: BASIC is more robust, because it fails over only when it needs to, but you can get caught in a traffic jam in the event of failover. PRECONNECT can fail over faster because of previously established failover connections, but if anything happens to that second connection after the time it is established, then you'll be out of luck. Hope this helps... -Tim - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Friday, June 07, 2002 5:43 AM Hi , I am configuring TAF on oracle 8.1.7 (solaris). I was just wondering if anyone on the list has had experience on this and can give me any advice- more specifically on the the method- preconnect and basic. Are there any significant benefits of having the preconnect setting set? Any input would be really appreciated.. Rgds Fawzia ** Information in this email is confidential and may be privileged. It is intended for the addressee only. If you have received it in error, please notify the sender immediately and delete it from your system. You should not otherwise copy it, retransmit it or use or disclose its contents to anyone. Thank you for your co-operation. ** -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Malik, Fawzia 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: Case tool
Yes. Cool tool for diagram of db architecture. (but I never used tools like Rational Rose) I don't use auto generated scripts (Vim is the winner :-) etc. But for the thing like to create schema picture is it great. JP On Thursday 06 June 2002 23:07, you wrote: Anyone used CaseStudio from: http://www.casestudio.com/enu/default.html ? Your opinion, please? Igor Neyman, OCP DBA [EMAIL PROTECTED] -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Jan Pruner 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).
EVENT=10235 trace name context forever, level 2
Something for the internals folks. Last week we had a situation where the production system froze and we had to reboot to get everything working again. Working with Oracle Support, we determained that we had encountered bug 2230693. The workaround was to set the event listed above. Sence then I have noticed a significant increasein library cache miss rates rates(5%-10%). The event doc indicates that level 2 on 10235 "Do 1 AND fill memory with junk on alloc / free". Does this indicate that the library cache also backfills the hash area with junk leading to the miss rate performace I am seeing, or should I be looking elsewhere? TIA, John P Weatherman Database Administrator Replacements Ltd.
Slow disk-to-disk - [W2K OT]
I've a script that until last Friday had been taking approximately 1 hour to take an Offline disk-to-disk backup of one of our databases. Now it's taking 2.5 hours. We've ruled our virus scanning software as a potential culprit. Basic Config is is a Compaq server W2K SP1 connected to a Compaq SAN. The target and source disks are both in the SAN. Xcopy is being used to perform disk-to-disk. OK pretty off topic but just in case anyone has any bright ideas or has experienced someting similiar I'd appreciate feedback. - Seán O' Neill Organon (Ireland) Ltd. [subscribed: digest mode] This message, including attached files, may contain confidential information and is intended only for the use by the individual and/or the entity to which it is addressed. Any unauthorized use, dissemination of, or copying of the information contained herein is not allowed and may lead to irreparable harm and damage for which you may be held liable. If you receive this message in error or if it is intended for someone else please notify the sender by returning this e-mail immediately and delete the message. -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: O'Neill, Sean 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: How do YOU use Java in the DB?
I use it to keep me up. :-) On Friday 07 June 2002 04:03, you wrote: I use it for email. Jared On Thursday 06 June 2002 14:07, Jesse, Rich wrote: So, we've finally taken the jump and are using JSPs (erm...Java Stored Procedures, not Java Server Pages) on 8.1.7.2.0. Informal Poll: How do you use Java Stored Procedures? 1) Easy interface from PL/SQL to lp and other OS commands. 2) 3) 4) ... Rich Jesse System/Database Administrator [EMAIL PROTECTED] Quad/Tech International, Sussex, WI USA -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Jan Pruner 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).
ORAPERF
Hi Anjo, Tried uploading a statspack report to ORAPERF and the reply I get only goes down to BREAK DOWN OF CPU TIME and than finishes. Should I not be uploading level 10 statspack reportt? Sorry for sending this through this list but figured this was the quickest way TIA Jack === De informatie verzonden in dit e-mailbericht is vertrouwelijk en is uitsluitend bestemd voor de geadresseerde. Openbaarmaking, vermenigvuldiging, verspreiding en/of verstrekking van deze informatie aan derden is, behoudens voorafgaande schriftelijke toestemming van Ernst Young, niet toegestaan. Ernst Young staat niet in voor de juiste en volledige overbrenging van de inhoud van een verzonden e-mailbericht, noch voor tijdige ontvangst daarvan. Ernst Young kan niet garanderen dat een verzonden e-mailbericht vrij is van virussen, noch dat e-mailberichten worden overgebracht zonder inbreuk of tussenkomst van onbevoegde derden. Indien bovenstaand e-mailbericht niet aan u is gericht, verzoeken wij u vriendelijk doch dringend het e-mailbericht te retourneren aan de verzender en het origineel en eventuele kopieën te verwijderen en te vernietigen. Ernst Young hanteert bij de uitoefening van haar werkzaamheden algemene voorwaarden, waarin een beperking van aansprakelijkheid is opgenomen. De algemene voorwaarden worden u op verzoek kosteloos toegezonden. = The information contained in this communication is confidential and is intended solely for the use of the individual or entity to whom it is addressed. You should not copy, disclose or distribute this communication without the authority of Ernst Young. Ernst Young is neither liable for the proper and complete transmission of the information contained in this communication nor for any delay in its receipt. Ernst Young does not guarantee that the integrity of this communication has been maintained nor that the communication is free of viruses, interceptions or interference. If you are not the intended recipient of this communication please return the communication to the sender and delete and destroy all copies. In carrying out its engagements, Ernst Young applies general terms and conditions, which contain a clause that limits its liability. A copy of these terms and conditions is available on request free of charge. === -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Jack van Zanen 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).
Who should I buy Oracle software from?
I have a client that is purchasing additional Oracle db product. The company that the client has worked with for several years turns out to be a not authorized reseller. This is the first that I have heard of not authorized Oracle software resellers. Are there any reasons why one would purchase from an authorized vs a not authorized reseller? Thx - Don -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Don 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).
Recreating TEMP
Hi all , I have 2 TEMPORARY tablespaces which I want to drop and recreate with better storage options. My worries: 1)If I connect as sysdba, what TEMP talespace will sysdba's session be using given that a select from dba_users shows SYS SYSTEM users having TEMP as their TEMPORARY tablespace. Won't the dropping of TEMP leave sysdba in suspense over which TEMP to use ? Or as long as no sort operations, no need for TEMP ? Waiting for your comments before: SQLDROP tablespace TEMP 2INCLUDING CONTENTS 3and DATAFILES; SQL CREATE TEMPORARY TABLESPACE TEMP ; CSW -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Simon Waibale 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: Archiving in OPS
Thanks to all for your answers, I think that the NFS solution is the one. Tks. - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Thursday, June 06, 2002 1:18 PM Metalink has a note on best practices on this. The best method of these in my opinion is to have n unqiue archive destinations (one per instance). Then you can NFS cross mount these destinations to the same location from each instance in your environment. RMAN can then backup/recover the entire database from a single server without having to worry about moving files around. Note that if an instance fails, any of the surviving instances can and will archive logs on behalf of the failed instance to the local instances archive destination. Bill --- Ramon E. Estevez [EMAIL PROTECTED] wrote: Hi list, Scenario OPS 2 nodes, Oracle 8.1.7, AIX. (New dealing with OPS) What is the best solution for implementing archiving in OPS. As far as I understand the 2 instances will be generating archives. If one of the instance goes down, what happen with the destination of those archives ? I will use RMAN with Legato for the backup strategy. I have been gathering tips from the list in those days, but any special recomendations, documentation or scripts about it will be very highly apreciated. TIA Ramon E. Estevez [EMAIL PROTECTED] 809-565-3121 __ Do You Yahoo!? Yahoo! - Official partner of 2002 FIFA World Cup http://fifaworldcup.yahoo.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Bill Pass 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:Tool for migrating from forms 3 to forms 6
Go to www.kumaran.com. Good people, know what their doing. We had a VERY good experience with them when we left forms 3 behind. Dick Goulet Reply Separator Author: [EMAIL PROTECTED] Date: 6/7/2002 3:13 AM Hi Gurus any one have idea of any tool which can be used for migrating from forms 3 to forms 6. Help needed in this is very much appreciated Ayap This communication contains information, which is confidential and may also be privileged. It is for the exclusive use of the intended recipient(s). If you are not the intended recipient(s), please note that any distribution, printing, copying or use of this communication or the information in it is strictly prohibited. If you have received this communication in error, please notify the sender immediately and then destroy any copies of it. -- 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: 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: Recreating TEMP
Simon, You only use the TEMP tablespace when performing a GROUP BY or a sort. In your case, this will not be happening to the SYS account during the drop and recreate of the tablespace. PS - change your drop tablespace command to: SQLDROP tablespace TEMP 3and DATAFILES; There are no contents to drop - its a temporary space. Also, consider using Locally Managed TBS for the TEMP space - it will make your life much more pleasant. You will not need to do this again. Balance (or match) the extent size of the TBS with the SORT_AREA_SIZE init parameter so that everything is used efficiently. Hope this helps. Tom Mercadante Oracle Certified Professional -Original Message- Sent: Friday, June 07, 2002 10:39 AM To: Multiple recipients of list ORACLE-L Hi all , I have 2 TEMPORARY tablespaces which I want to drop and recreate with better storage options. My worries: 1)If I connect as sysdba, what TEMP talespace will sysdba's session be using given that a select from dba_users shows SYS SYSTEM users having TEMP as their TEMPORARY tablespace. Won't the dropping of TEMP leave sysdba in suspense over which TEMP to use ? Or as long as no sort operations, no need for TEMP ? Waiting for your comments before: SQLDROP tablespace TEMP 2INCLUDING CONTENTS 3and DATAFILES; SQL CREATE TEMPORARY TABLESPACE TEMP ; CSW -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Simon Waibale 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: Mercadante, Thomas F 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).
Correct way to determine freelists and freelists groups
Hi all, We have a bunch of TEMP tables in our 8161 databases. They are actually permenant tables but have temporary data in them. Some of these are small (less than a meg) to few large ones (100M or more). I truncate these tables once a week. Until now these used to reside with other regular tables in a common tablespace and cause fragmentation. I have now created a new tablespace to store these temp tables and associated indexes. This new tablespace is created with 1M extents (basically simulation LMT with fixed extent size), space is not a concern. After reading metalink note 107090.1, which recommends as follows ... Tips for PCTUSED and PCTFREE * If the application frequently performs UPDATES that alter sizes of rows greatly, then PCTFREE can be set high and PCTUSED can be set low. This would allow for large amount of space in data blocks for row size growth. * If there is more INSERT activity with less UPDATES, the PCTFREE can be set low with average value for PCTUSED to avoid chaining of rows. * If the main concern is performance and more space is available, then PCTFREE can be set very high and PCTUSED very low. * If the main concern in space and not performance, then PCTFREE can set very low and PCTUSED very high. I am more concerned about performance than space usage, so point 4 stands out. Most of these tables will be used by multiple sessions to insert/update/delete data multiple times a day (and night too). Now that I have a chance to reorg these tables, I want to come up with appropriate values for * INITTRANS (current value is 1) * FREELISTS (current value is 1 and 5 on some) * FREELIST GROUPS (current value is 1 and 6 on some) Any ideas on how to do this? Are there any scripts that I can run or monitor something that would help me? My DB version is 8161. Thanks in advance 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! *2 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: using a lot of temporary tablespace with large sort area size
Title: RE: using a lot of temporary tablespace with large sort area size Guys, Have 20Gb and 16CPUS available on host. Need to do large full-table scans/joins to create materialized view. Since I have to do the full-table scans of large tables - decided to use parallel query option. Eliminated significant I/O contention by using DIRECT IO. Using very very large sort_area_size, however, still writing out significant segments to temporary tablespace which doesn't make sense to me. Any ideas?
RE: Who should I buy Oracle software from?
Don, If I were you, I'd either stay out if this, or recomend that they speak with Oracle about this. If your client gets screwed later, you don't want to be on the blame end of things. They could literally come after YOU for a bad recommendation. Tom Mercadante Oracle Certified Professional -Original Message- Sent: Friday, June 07, 2002 10:44 AM To: Multiple recipients of list ORACLE-L I have a client that is purchasing additional Oracle db product. The company that the client has worked with for several years turns out to be a not authorized reseller. This is the first that I have heard of not authorized Oracle software resellers. Are there any reasons why one would purchase from an authorized vs a not authorized reseller? Thx - Don -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Don 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: Mercadante, Thomas F 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: Case tool
Jan, Did you try it's 'versioning' feature (comparing versions)? That's what caught my attention. I downloaded demo, and it crashes with Access violation..., when I'm trying to create new model (NT4, SP6). Did you have similar problems? Igor Neyman, OCP DBA [EMAIL PROTECTED] - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Friday, June 07, 2002 10:03 AM Yes. Cool tool for diagram of db architecture. (but I never used tools like Rational Rose) I don't use auto generated scripts (Vim is the winner :-) etc. But for the thing like to create schema picture is it great. JP On Thursday 06 June 2002 23:07, you wrote: Anyone used CaseStudio from: http://www.casestudio.com/enu/default.html ? Your opinion, please? Igor Neyman, OCP DBA [EMAIL PROTECTED] -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Jan Pruner 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: Igor Neyman 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: using a lot of temporary tablespace with large sort area size
Paula, With so much data, Oralce (or Oracle, if you prefer) might be hash joining your tables and writing temp hash segments. What is your hash_area_size? jack silvey --- [EMAIL PROTECTED] wrote: Guys, Have 20Gb and 16CPUS available on host. Need to do large full-table scans/joins to create materialized view. Since I have to do the full-table scans of large tables - decided to use parallel query option. Eliminated significant I/O contention by using DIRECT IO. Using very very large sort_area_size, however, still writing out significant segments to temporary tablespace which doesn't make sense to me. Any ideas? __ Do You Yahoo!? Yahoo! - Official partner of 2002 FIFA World Cup http://fifaworldcup.yahoo.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Jack Silvey 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 should I buy Oracle software from?
Resellers can lose their authorization because of some irregularity, such as failure to comply with the terms of the reseller agreement, poor recordkeeping, accounting foulups, or even outright embezzlement or criminal behavior. Oracle can refuse to do business with anyone it chooses, pretty much. Since software can be ordered/downloaded for free from OTN, anybody with chutzpah can sell Oracle software if they dare... As far as I know, only authorized Oracle resellers can sell support contracts. That is something that can not be faked easily... - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Friday, June 07, 2002 8:44 AM I have a client that is purchasing additional Oracle db product. The company that the client has worked with for several years turns out to be a not authorized reseller. This is the first that I have heard of not authorized Oracle software resellers. Are there any reasons why one would purchase from an authorized vs a not authorized reseller? Thx - Don -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Don 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: using a lot of temporary tablespace with large sort area size
Title: RE: using a lot of temporary tablespace with large sort area size What about hash_area_size? Paul x3704 -Original Message-From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]]Sent: Friday, June 07, 2002 10:58 AMTo: Multiple recipients of list ORACLE-LSubject: RE: using a lot of temporary tablespace with large sort area size Guys, Have 20Gb and 16CPUS available on host. Need to do large full-table scans/joins to create materialized view. Since I have to do the full-table scans of large tables - decided to use parallel query option. Eliminated significant I/O contention by using DIRECT IO. Using very very large sort_area_size, however, still writing out significant segments to temporary tablespace which doesn't make sense to me. Any ideas?
Re: Case tool
No, I didn't. I never had a problem like Access ... used NT4 too. JP On Friday 07 June 2002 17:29, you wrote: Jan, Did you try it's 'versioning' feature (comparing versions)? That's what caught my attention. I downloaded demo, and it crashes with Access violation..., when I'm trying to create new model (NT4, SP6). Did you have similar problems? Igor Neyman, OCP DBA [EMAIL PROTECTED] - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Friday, June 07, 2002 10:03 AM Yes. Cool tool for diagram of db architecture. (but I never used tools like Rational Rose) I don't use auto generated scripts (Vim is the winner :-) etc. But for the thing like to create schema picture is it great. JP On Thursday 06 June 2002 23:07, you wrote: Anyone used CaseStudio from: http://www.casestudio.com/enu/default.html ? Your opinion, please? Igor Neyman, OCP DBA [EMAIL PROTECTED] -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Jan Pruner 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).
Newline char in oracle files
Title: Newline char in oracle files Hi everybody, I have a very odd problem I hope someone can help me with. I've installed Oracle, OEM and OWB on a couple of NT 4.0 servers in the UK. The text files that Oracle generates during the install - the .config, .ora and the .ctl files from OWB - are generated using a lf as the newline character instead of a cr. The result is that in notepad they all appear as one run-on line with embedded control characters (line feeds). I have not had this problem on any of the servers in the US. I'm using the same installation media in both places. Is there UK specific media I should be using? Or is there is something in the regional settings on the servers that would determine what newline character is used? I've checked the Oracle and Microsoft support sites and even opened a tar but am still stumped, so any suggestions will be appreciated. I've tried contacting our sysadmin about it but I don't expect any response until the England/Argentina match is over :-) TIA, Beth
Re: Can we find SQL user
Bruce already mentioned using v$session. Have you tried it? If you try it, you will find it. Jared On Friday 07 June 2002 00:13, sam d wrote: We can definitely find the user , but considering my scenario 'all the people are logged in with the same oracle user' , I want to know:From what machine the SQL statement was fired. thx Sam name--- Reardon, Bruce (CALBBAY) [EMAIL PROTECTED] wrote: Have a look at v$session In particular the osuser, terminal and machine fields - these may help Also look at the listener log file - this may help Or do you have an application server sitting in the middle? HTH, Bruce Reardon -Original Message- From: sam d [mailto:[EMAIL PROTECTED]] Sent: Friday, 7 June 2002 15:18 Hi List, Suppose I have m1,m2,m3 machines, all the users sitting on these machines are using oracle 'user1' to connect to the server. As all the people are logged in with the same user name ,Can we find which user(or machine) has issued which SQL statement. Thanks Sam __ Do You Yahoo!? Yahoo! - Official partner of 2002 FIFA World Cup http://fifaworldcup.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).
Re: Oracle DBA with 8i through 11i Experience Needed in Columbus,
Interesting. I know Bill is only passing on salary information that his clients provide, but 70k is really lowball for someone that has this kind of experience. When the market picks up, people that take these positions are going to bolt at the first chance. Jared On Friday 07 June 2002 03:23, OraStaff wrote: Position: Oracle Applications Side DBA Location: Columbus, Ohio area (Lancaster) Industry: Manufacturing Salary Range: Mid 70s Base Salary -depends on experience A relocation package is available. This company is an industry leader in its field of products. Growing,profitable, well established. Very good opportunities in this firm and its conglomerate parent firm. The Position: To provide support for ORACLE 11i and 9i running on HP-UX. Security, performance tuning, patch management, backup recovery, troubleshooting, upgrades, and user support. PLEASE DO NOT send your resume for this position UNLESS you and have the skills outlined below for this position. DO NOT send your resume unless you have a stable work history. Candidates whose work history includes frequent job changes connot be considered. If you are employed by a consulting company you must have a long term project history. This is a full time staff position so no sub-contractors or third parties please. No H1-B Candidates Please. Requirements: -Solid Oracle DBA experience.. 8i, 9i and 11i. -Solid PL/SQL experience. -Unix -U.S. citizenship or permanent residency For immediate consideration, please send your resume as a Word attachment to: OraStaff, Inc. Email: [EMAIL PROTECTED] Please use job code: One/Columbus/DBA/John ph: 1-800 -549-8502 All Submissions are handled in confidence. *We pay referral fees. So please contact me if you know of anyone who would be qualified/interested in the posiition described above- if it is not a match for your skills. Thanks, Bill Law -- 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: Is this a good upgrade path from 8.0.4 to 8.1.7.2?
I did an 8.0.4.3 to 8.1.7.2 upgrade back in January. My main nightmare with that particular upgrade is not likely to affect you (we had a disk failure while backing up the database prior to upgrade and the server didn't switch to the mirrored disk until after we rebooted - ended up delaying the start of the upgrade from midnight to 3am). Other than that (which understandably stands out in my memory) the main issue was with installing java. If you intend to install it I highly recommend you print out a copy of Document 156477.1. Allow plenty of time for the installation (I hadn't allowed enough time since I had only done 8.1.6 java installations before and hadn't realized how long all the extra steps took). And just in case you have problems and need to uninstall be sure to have a copy of Document 159801.1 handy. I recommend doing the java installation *after* you apply the patch. This will save considerable time. Good luck! Jay Miller -Original Message- Sent: Thursday, June 06, 2002 12:44 PM To: Multiple recipients of list ORACLE-L We are preparing to do an upgrade of our Data Warehouse on Sun Solaris from version 8.0.4.0.0 to 8.1.7.2. We've done a number of upgrades in the past but this is the first time we are going from 8.0.4 to 8.1.7. Following is the basic, high level plan: 1. Upgrade from 8.0.4 to 8.1.7.0 (Oracle software already pre-installed in separate Oracle Home). 2. Apply 8.1.7.2 patchset 3. Apply separate one-off-patch that we had created for a star transformation bug. We are going to use the manual migration method as outlined in the 8.1.7 migration manual. I've already tested this approach on a full-size RMAN clone of production that we moved to our QA box. However, that box already had 8.1.7.2 pre-installed on it before I actually migrated the database. Just wondering if anyone has gone directly from 8.0.4 to 8.1.7.2.Are there any gotchas or concerns? At one point I thought we might have to upgrade from 8.0.4 to 8.1.5 and then from 8.1.5 to 8.1.7. But now I can't find anywhere in the documentation where it says that I need to do that so we are thinking about saving a little time and going straight from 8.0.4 to 8.1.7. This is a 200 Gig warehouse which uses Verita Quick I/O, partitioning, star schema, etc. Thanks to anyone who can alert me to any potential pitfalls. I cannot afford any glitches at all on this upgrade. Thanks, Cherie Machler Oracle DBA Gelco Information Network -- 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: 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).
Re: Newline char in oracle files
I think it is not a problem of regional setting. It's the difference between UN*X versus MS platform. You can convert it to DOS format or use some better editor which can handle UN*X text files. JP On Friday 07 June 2002 17:38, you wrote: Hi everybody, I have a very odd problem I hope someone can help me with. I've installed Oracle, OEM and OWB on a couple of NT 4.0 servers in the UK. The text files that Oracle generates during the install - the .config, .ora and the .ctl files from OWB - are generated using a lf as the newline character instead of a cr. The result is that in notepad they all appear as one run-on line with embedded control characters (line feeds). I have not had this problem on any of the servers in the US. I'm using the same installation media in both places. Is there UK specific media I should be using? Or is there is something in the regional settings on the servers that would determine what newline character is used? I've checked the Oracle and Microsoft support sites and even opened a tar but am still stumped, so any suggestions will be appreciated. I've tried contacting our sysadmin about it but I don't expect any response until the England/Argentina match is over :-) TIA, Beth -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Jan Pruner 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: Newline char in oracle files
Title: Message Tom, Consider it done! Thanks for the advice. I hadn't tried wordpad and prefer notepad, but its definitely a step up from Word which was what I have been using. Beth -Original Message-From: Mercadante, Thomas F [mailto:[EMAIL PROTECTED]] Sent: Friday, June 07, 2002 11:34 AMTo: '[EMAIL PROTECTED]'Cc: Seefelt, BethSubject: RE: Newline char in oracle files Beth, Use WordPad - it interprets and opens the files just fine. Most of newer Oracle *.sql files have the same issue - if you use WordPad, they open fine. This bit of advice just cost you a virtual glassof Tetley Tea. Please submit ASAP! thank you! Tom Mercadante Oracle Certified Professional -Original Message-From: Seefelt, Beth [mailto:[EMAIL PROTECTED]]Sent: Friday, June 07, 2002 11:38 AMTo: Multiple recipients of list ORACLE-LSubject: Newline char in oracle files Hi everybody, I have a very odd problem I hope someone can help me with. I've installed Oracle, OEM and OWB on a couple of NT 4.0 servers in the UK. The text files that Oracle generates during the install - the .config, .ora and the .ctl files from OWB - are generated using a lf as the newline character instead of a cr. The result is that in notepad they all appear as one run-on line with embedded control characters (line feeds). I have not had this problem on any of the servers in the US. I'm using the same installation media in both places. Is there UK specific media I should be using? Or is there is something in the regional settings on the servers that would determine what newline character is used? I've checked the Oracle and Microsoft support sites and even opened a tar but am still stumped, so any suggestions will be appreciated. I've tried contacting our sysadmin about it but I don't expect any response until the England/Argentina match is over :-) TIA, Beth
RE: Can we find SQL user
set lines 250 set pages 30 set pause off set feed off clear break clear col col osuser format a12 col usernameformat a10 this is it! col program format a60 col sid format 9 col sql_testformat a100 break on osuser- on sid dupl - on username - on program skip 1 SELECT osuser , sid , serial# , username, program , status , sql_text fromv$session a, v$sqltext b where a.sql_address=b.address(+) and a.type != 'BACKGROUND' order byosuser , sid , address , piece / -Message d'origine- De: Jared Still [mailto:[EMAIL PROTECTED]] Date: vendredi 7 juin 2002 17:58 À: Multiple recipients of list ORACLE-L Objet: Re: Can we find SQL user Bruce already mentioned using v$session. Have you tried it? If you try it, you will find it. Jared On Friday 07 June 2002 00:13, sam d wrote: We can definitely find the user , but considering my scenario 'all the people are logged in with the same oracle user' , I want to know:From what machine the SQL statement was fired. thx Sam name--- Reardon, Bruce (CALBBAY) [EMAIL PROTECTED] wrote: Have a look at v$session In particular the osuser, terminal and machine fields - these may help Also look at the listener log file - this may help Or do you have an application server sitting in the middle? HTH, Bruce Reardon -Original Message- From: sam d [mailto:[EMAIL PROTECTED]] Sent: Friday, 7 June 2002 15:18 Hi List, Suppose I have m1,m2,m3 machines, all the users sitting on these machines are using oracle 'user1' to connect to the server. As all the people are logged in with the same user name ,Can we find which user(or machine) has issued which SQL statement. Thanks Sam __ Do You Yahoo!? Yahoo! - Official partner of 2002 FIFA World Cup http://fifaworldcup.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). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Bernard, Gilbert 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: Newline char in oracle files
Title: Newline char in oracle files Beth, Use WordPad - it interprets and opens the files just fine. Most of newer Oracle *.sql files have the same issue - if you use WordPad, they open fine. This bit of advice just cost you a virtual glassof Tetley Tea. Please submit ASAP! thank you! Tom Mercadante Oracle Certified Professional -Original Message-From: Seefelt, Beth [mailto:[EMAIL PROTECTED]]Sent: Friday, June 07, 2002 11:38 AMTo: Multiple recipients of list ORACLE-LSubject: Newline char in oracle files Hi everybody, I have a very odd problem I hope someone can help me with. I've installed Oracle, OEM and OWB on a couple of NT 4.0 servers in the UK. The text files that Oracle generates during the install - the .config, .ora and the .ctl files from OWB - are generated using a lf as the newline character instead of a cr. The result is that in notepad they all appear as one run-on line with embedded control characters (line feeds). I have not had this problem on any of the servers in the US. I'm using the same installation media in both places. Is there UK specific media I should be using? Or is there is something in the regional settings on the servers that would determine what newline character is used? I've checked the Oracle and Microsoft support sites and even opened a tar but am still stumped, so any suggestions will be appreciated. I've tried contacting our sysadmin about it but I don't expect any response until the England/Argentina match is over :-) TIA, Beth
RE: using a lot of temporary tablespace with large sort area size
Title: RE: using a lot of temporary tablespace with large sort area size I would think that this is the most efficient way to join the data. BTW, Version is 8.1.7.2 of Oracle. The hash_area_size is hash_area_size integer 8388608 Also, never see from top session more than 4 GB memory being used. Does anyone know of a limit on processes that can be setup on Solaris (NOT ON SGA) but on dynamic memory like sort_area_size on the OS itself??? -Original Message- From: Jack Silvey [mailto:[EMAIL PROTECTED]] Sent: Friday, June 07, 2002 11:38 AM To: Multiple recipients of list ORACLE-L Subject: RE: using a lot of temporary tablespace with large sort area size Paula, With so much data, Oralce (or Oracle, if you prefer) might be hash joining your tables and writing temp hash segments. What is your hash_area_size? jack silvey --- [EMAIL PROTECTED] wrote: Guys, Have 20Gb and 16CPUS available on host. Need to do large full-table scans/joins to create materialized view. Since I have to do the full-table scans of large tables - decided to use parallel query option. Eliminated significant I/O contention by using DIRECT IO. Using very very large sort_area_size, however, still writing out significant segments to temporary tablespace which doesn't make sense to me. Any ideas? __ Do You Yahoo!? Yahoo! - Official partner of 2002 FIFA World Cup http://fifaworldcup.yahoo.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Jack Silvey 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: using a lot of temporary tablespace with large sort area size
Title: RE: using a lot of temporary tablespace with large sort area size Guys, Note: Oracle does not recommend using the HASH_AREA_SIZE parameter unless the instance is configured with the shared server option. Oracle recommends that you enable automatic sizing of SQL working areas by setting PGA_AGGREGATE_TARGET instead. HASH_AREA_SIZE is retained for backward compatibility. Any opinions on how well PGA_AGGRE... works versus using specific parameters? -Original Message- From: Jack Silvey [mailto:[EMAIL PROTECTED]] Sent: Friday, June 07, 2002 11:38 AM To: Multiple recipients of list ORACLE-L Subject: RE: using a lot of temporary tablespace with large sort area size Paula, With so much data, Oralce (or Oracle, if you prefer) might be hash joining your tables and writing temp hash segments. What is your hash_area_size? jack silvey --- [EMAIL PROTECTED] wrote: Guys, Have 20Gb and 16CPUS available on host. Need to do large full-table scans/joins to create materialized view. Since I have to do the full-table scans of large tables - decided to use parallel query option. Eliminated significant I/O contention by using DIRECT IO. Using very very large sort_area_size, however, still writing out significant segments to temporary tablespace which doesn't make sense to me. Any ideas? __ Do You Yahoo!? Yahoo! - Official partner of 2002 FIFA World Cup http://fifaworldcup.yahoo.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Jack Silvey 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: using a lot of temporary tablespace with large sort area size
This query will help to see type of sortfor 8i above select user,segtype,extents from v$sort_usage; Regards Rafiq Reply-To: [EMAIL PROTECTED] To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Date: Fri, 07 Jun 2002 07:38:26 -0800 Paula, With so much data, Oralce (or Oracle, if you prefer) might be hash joining your tables and writing temp hash segments. What is your hash_area_size? jack silvey --- [EMAIL PROTECTED] wrote: Guys, Have 20Gb and 16CPUS available on host. Need to do large full-table scans/joins to create materialized view. Since I have to do the full-table scans of large tables - decided to use parallel query option. Eliminated significant I/O contention by using DIRECT IO. Using very very large sort_area_size, however, still writing out significant segments to temporary tablespace which doesn't make sense to me. Any ideas? __ Do You Yahoo!? Yahoo! - Official partner of 2002 FIFA World Cup http://fifaworldcup.yahoo.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Jack Silvey 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). MOHAMMAD RAFIQ _ 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: Mohammad Rafiq 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: ORAPERF
Jack, It should work, please send me the file to [EMAIL PROTECTED] and I will have a look why it fails. Anjo. Jack van Zanen wrote: Hi Anjo, Tried uploading a statspack report to ORAPERF and the reply I get only goes down to BREAK DOWN OF CPU TIME and than finishes. Should I not be uploading level 10 statspack reportt? Sorry for sending this through this list but figured this was the quickest way TIA Jack === De informatie verzonden in dit e-mailbericht is vertrouwelijk en is uitsluitend bestemd voor de geadresseerde. Openbaarmaking, vermenigvuldiging, verspreiding en/of verstrekking van deze informatie aan derden is, behoudens voorafgaande schriftelijke toestemming van Ernst Young, niet toegestaan. Ernst Young staat niet in voor de juiste en volledige overbrenging van de inhoud van een verzonden e-mailbericht, noch voor tijdige ontvangst daarvan. Ernst Young kan niet garanderen dat een verzonden e-mailbericht vrij is van virussen, noch dat e-mailberichten worden overgebracht zonder inbreuk of tussenkomst van onbevoegde derden. Indien bovenstaand e-mailbericht niet aan u is gericht, verzoeken wij u vriendelijk doch dringend het e-mailbericht te retourneren aan de verzender en het origineel en eventuele kopieën te verwijderen en te vernietigen. Ernst Young hanteert bij de uitoefening van haar werkzaamheden algemene voorwaarden, waarin een beperking van aansprakelijkheid is opgenomen. De algemene voorwaarden worden u op verzoek kosteloos toegezonden. = The information contained in this communication is confidential and is intended solely for the use of the individual or entity to whom it is addressed. You should not copy, disclose or distribute this communication without the authority of Ernst Young. Ernst Young is neither liable for the proper and complete transmission of the information contained in this communication nor for any delay in its receipt. Ernst Young does not guarantee that the integrity of this communication has been maintained nor that the communication is free of viruses, interceptions or interference. If you are not the intended recipient of this communication please return the communication to the sender and delete and destroy all copies. In carrying out its engagements, Ernst Young applies general terms and conditions, which contain a clause that limits its liability. A copy of these terms and conditions is available on request free of charge. === -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Jack van Zanen 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: 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: Oracle DBA with 8i through 11i Experience Needed in Columbus,
Columbus, Ohio is not a real big market.I'm not sure what market rates are there but I would assume that they are not top of the line. Cherie Machler With in-laws that live in Columbus Jared Still jkstill@cybco To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] n.com cc: Sent by: Subject: Re: Oracle DBA with 8i through 11i Experience Needed in [EMAIL PROTECTED]Columbus, om 06/07/02 11:03 AM Please respond to ORACLE-L Interesting. I know Bill is only passing on salary information that his clients provide, but 70k is really lowball for someone that has this kind of experience. When the market picks up, people that take these positions are going to bolt at the first chance. Jared On Friday 07 June 2002 03:23, OraStaff wrote: Position: Oracle Applications Side DBA Location: Columbus, Ohio area (Lancaster) Industry: Manufacturing Salary Range: Mid 70s Base Salary -depends on experience A relocation package is available. This company is an industry leader in its field of products. Growing,profitable, well established. Very good opportunities in this firm and its conglomerate parent firm. The Position: To provide support for ORACLE 11i and 9i running on HP-UX. Security, performance tuning, patch management, backup recovery, troubleshooting, upgrades, and user support. PLEASE DO NOT send your resume for this position UNLESS you and have the skills outlined below for this position. DO NOT send your resume unless you have a stable work history. Candidates whose work history includes frequent job changes connot be considered. If you are employed by a consulting company you must have a long term project history. This is a full time staff position so no sub-contractors or third parties please. No H1-B Candidates Please. Requirements: -Solid Oracle DBA experience.. 8i, 9i and 11i. -Solid PL/SQL experience. -Unix -U.S. citizenship or permanent residency For immediate consideration, please send your resume as a Word attachment to: OraStaff, Inc. Email: [EMAIL PROTECTED] Please use job code: One/Columbus/DBA/John ph: 1-800 -549-8502 All Submissions are handled in confidence. *We pay referral fees. So please contact me if you know of anyone who would be qualified/interested in the posiition described above- if it is not a match for your skills. Thanks, Bill Law -- 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: 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
Re: Oracle DBA with 8i through 11i Experience Needed in Columbus,
Jared, i agree totally and have further info on that position/company, etc. joe Jared Still wrote: Interesting. I know Bill is only passing on salary information that his clients provide, but 70k is really lowball for someone that has this kind of experience. When the market picks up, people that take these positions are going to bolt at the first chance. Jared On Friday 07 June 2002 03:23, OraStaff wrote: Position: Oracle Applications Side DBA Location: Columbus, Ohio area (Lancaster) Industry: Manufacturing Salary Range: Mid 70s Base Salary -depends on experience A relocation package is available. This company is an industry leader in its field of products. Growing,profitable, well established. Very good opportunities in this firm and its conglomerate parent firm. The Position: To provide support for ORACLE 11i and 9i running on HP-UX. Security, performance tuning, patch management, backup recovery, troubleshooting, upgrades, and user support. PLEASE DO NOT send your resume for this position UNLESS you and have the skills outlined below for this position. DO NOT send your resume unless you have a stable work history. Candidates whose work history includes frequent job changes connot be considered. If you are employed by a consulting company you must have a long term project history. This is a full time staff position so no sub-contractors or third parties please. No H1-B Candidates Please. Requirements: -Solid Oracle DBA experience.. 8i, 9i and 11i. -Solid PL/SQL experience. -Unix -U.S. citizenship or permanent residency For immediate consideration, please send your resume as a Word attachment to: OraStaff, Inc. Email: [EMAIL PROTECTED] Please use job code: One/Columbus/DBA/John ph: 1-800 -549-8502 All Submissions are handled in confidence. *We pay referral fees. So please contact me if you know of anyone who would be qualified/interested in the posiition described above- if it is not a match for your skills. Thanks, Bill Law -- 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).
Index Constraint
Hi List, What's the diffrent between unique constrint unique index, when we have to use which, I think both are the same. Any Idea? And what about normal index reverse index? when we have to use normal and when reverse index, I think Reverse index normaly using for FK index, Is it right or not? Thanks Hamid Alavi Office 818 737-0526 Cell818 402-1987 === Confidentiality Statement === The information contained in this message and any attachments is intended only for the use of the individual or entity to which it is addressed, and may contain information that is PRIVILEGED, CONFIDENTIAL and exempt from disclosure under applicable law. If you have received this message in error, you are prohibited from copying, distributing, or using the information. Please contact the sender immediately by return e-mail and delete the original message from your system. = End Confidentiality Statement = -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Hamid Alavi 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: EXPORT BUG: oncorrect storage parameters being produced RESOLVED new bug
Hi, Check out Bug 1575222 in metalink (someone sent it to me). Exact bug. Suppossedly fixed in 8.1.7.2 Will test with my 8.1.7.3 version. -- 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: EXPORT BUG: oncorrect storage parameters being produced RESOLVED new bug
Title: RE: EXPORT BUG: oncorrect storage parameters being produced RESOLVED new bug Hi, Check out Bug 1575222 in metalink (someone sent it to me). Exact bug. Suppoesedly fised in 8.1.7.2 -Original Message- From: Patricia Karla Cordeiro [EMAIL PROTECTED]@SUNGARD Sent: Friday, June 07, 2002 2:55 PM To: LazyDBA.com Discussion Subject: RE: EXPORT BUG: oncorrect storage parameters being produced Bug :617486 Hi, Not sure if it helps, but if the object ACCOUNT already exists, on another schema, isn't it possible that you have a public synonym for this object (on the old schema), and imp is trying to create the PK on the wrong object? Try running IMP with same USER as TOUSER clause. HTH, Patricia. -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]] Sent: Sexta-feira, 7 de Junho de 2002 15:10 To: LazyDBA.com Discussion Subject: EXPORT BUG: oncorrect storage parameters being produced Bug:617486 I posted earlier and someone responded that this was a bug prior to 8.1.7 Please note that error occurs ONLY with the constraints tables etc are all created without error. If I strip the tablespace params out of the statement and THEN run the statement, I get no errors. I had the departmental dba re-run the export using the exp version that comes with 8.1.7 and we still are getting the same error on import IMP-00015: following statement failed because the object already exists: ALTER TABLE ACCOUNT ADD CONSTRAINT PK_ACCOUNT PRIMARY KEY (ACCOUNT_ID ,GENERATION) USING INDEX PCTFREE 10 INITRANS 2 MAXTRANS 255 STORAGE(INIT IAL 8388608) TABLESPACE AURDEV_TS ENABLE Furthermore, I believe that the error message itself is incorrect. Here's the setup. The schema that the export was run on has default tbs of AURDEV_TS The schema I am trying to import into has default tbs of Users Both schemas in same Oracle instance. I dont know if it is looking to try to create the object in the other tbs as written, but the import users has no rights to that tbs. The object DOES indeed exist in the tbs but under a different schema. I found an OLDer bug as follows: Bug:617486 caused exports to contain invalid DDL for storage clauses. This fix allow IMPort to IGNORE the ORA-2219 error if the table already exists providing a way to import any EXPORT generated with a version of EXP suffering from Bug:617486 Similar but older version and different error message though similar behavior. But we are on 8.1.7 and should not see this. Any comments? (I have no access to metalink) Thanks, Hannah 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 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 -- 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: Correct way to determine freelists and freelists groups
Are there any scripts table_access_paths.sql from www.ixora.com.au will suggest number of freelists for a table -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Greg Moore 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).
Oracle one-off Patch Install util
Anyone used this yet? Does it run better than Oracle's RDA (haven't had a successful run of that yet!)? http://metalink.oracle.com/metalink/plsql/showdoc?db=NOTid=189489.1blackfr ame=0 And, Jared, you'll be happy that it appears to be written in Perl. :) Rich Jesse System/Database Administrator [EMAIL PROTECTED] Quad/Tech International, Sussex, WI USA -- 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).
RE: using a lot of temporary tablespace with large sort area size
Title: RE: using a lot of temporary tablespace with large sort area size Man significantly improved perf. by setting hash_area_size for session. Okay so guyshave read in metalink says hash_area_size is 2* sort_area_size by default - is this not true? Most be the case that it isn't true. -Original Message-From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]]Sent: Friday, June 07, 2002 12:54 PMTo: Multiple recipients of list ORACLE-LSubject: RE: using a lot of temporary tablespace with large sort area size Guys, Note: Oracle does not recommend using the HASH_AREA_SIZE parameter unless the instance is configured with the shared server option. Oracle recommends that you enable automatic sizing of SQL working areas by setting PGA_AGGREGATE_TARGET instead. HASH_AREA_SIZE is retained for backward compatibility. Any opinions on how well PGA_AGGRE... works versus using specific parameters? -Original Message- From: Jack Silvey [mailto:[EMAIL PROTECTED]] Sent: Friday, June 07, 2002 11:38 AM To: Multiple recipients of list ORACLE-L Subject: RE: using a lot of temporary tablespace with large sort area size Paula, With so much data, Oralce (or Oracle, if you prefer) might be hash joining your tables and writing temp hash segments. What is your hash_area_size? jack silvey --- [EMAIL PROTECTED] wrote: Guys, Have 20Gb and 16CPUS available on host. Need to do large full-table scans/joins to create materialized view. Since I have to do the full-table scans of large tables - decided to use parallel query option. Eliminated significant I/O contention by using DIRECT IO. Using very very large sort_area_size, however, still writing out significant segments to temporary tablespace which doesn't make sense to me. Any ideas? __ Do You Yahoo!? Yahoo! - Official partner of 2002 FIFA World Cup http://fifaworldcup.yahoo.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Jack Silvey 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).
connect as SYSDBA using ADO
I am trying to find out how to connect as SYSDBA using ADO in Visual Basic. Did not see any mention of sysdba in the doc. Alex Hillman -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Alex Hillman 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: Index Constraint
No, don't use reverse index for FKs, on certain versions they caused havoc by allowing parent key to be deleted (Metastink has an alert on this one). Reverse indexes are preferred in OPS environment AFAIK. 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: Correct way to determine freelists and freelists groups
Thanks 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! -Original Message- Sent: Friday, June 07, 2002 3:47 PM To: Multiple recipients of list ORACLE-L Are there any scripts table_access_paths.sql from www.ixora.com.au will suggest number of freelists for a table -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Greg Moore 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). *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: Index Constraint
rtfm Igor Neyman, OCP DBA [EMAIL PROTECTED] - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Friday, June 07, 2002 3:46 PM Hi List, What's the diffrent between unique constrint unique index, when we have to use which, I think both are the same. Any Idea? And what about normal index reverse index? when we have to use normal and when reverse index, I think Reverse index normaly using for FK index, Is it right or not? Thanks Hamid Alavi Office 818 737-0526 Cell818 402-1987 === Confidentiality Statement === The information contained in this message and any attachments is intended only for the use of the individual or entity to which it is addressed, and may contain information that is PRIVILEGED, CONFIDENTIAL and exempt from disclosure under applicable law. If you have received this message in error, you are prohibited from copying, distributing, or using the information. Please contact the sender immediately by return e-mail and delete the original message from your system. = End Confidentiality Statement = -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Hamid Alavi 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: Igor Neyman 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: Index Constraint
What about PK, which one should be, normal or Reverse, I can see in one of my database, Erwin generate all PK as normal/reverse ? -Original Message- Sent: Friday, June 07, 2002 1:33 PM To: Multiple recipients of list ORACLE-L No, don't use reverse index for FKs, on certain versions they caused havoc by allowing parent key to be deleted (Metastink has an alert on this one). Reverse indexes are preferred in OPS environment AFAIK. 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! === Confidentiality Statement === The information contained in this message and any attachments is intended only for the use of the individual or entity to which it is addressed, and may contain information that is PRIVILEGED, CONFIDENTIAL and exempt from disclosure under applicable law. If you have received this message in error, you are prohibited from copying, distributing, or using the information. Please contact the sender immediately by return e-mail and delete the original message from your system. = End Confidentiality Statement = -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Hamid Alavi 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: Index Constraint
Igore, rtfm is a very complex answer, please reply clearly!! -Original Message- Sent: Friday, June 07, 2002 1:18 PM To: Multiple recipients of list ORACLE-L rtfm Igor Neyman, OCP DBA [EMAIL PROTECTED] - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Friday, June 07, 2002 3:46 PM Hi List, What's the diffrent between unique constrint unique index, when we have to use which, I think both are the same. Any Idea? And what about normal index reverse index? when we have to use normal and when reverse index, I think Reverse index normaly using for FK index, Is it right or not? Thanks Hamid Alavi Office 818 737-0526 Cell818 402-1987 === Confidentiality Statement === The information contained in this message and any attachments is intended only for the use of the individual or entity to which it is addressed, and may contain information that is PRIVILEGED, CONFIDENTIAL and exempt from disclosure under applicable law. If you have received this message in error, you are prohibited from copying, distributing, or using the information. Please contact the sender immediately by return e-mail and delete the original message from your system. = End Confidentiality Statement = -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Hamid Alavi 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: Igor Neyman 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). === Confidentiality Statement === The information contained in this message and any attachments is intended only for the use of the individual or entity to which it is addressed, and may contain information that is PRIVILEGED, CONFIDENTIAL and exempt from disclosure under applicable law. If you have received this message in error, you are prohibited from copying, distributing, or using the information. Please contact the sender immediately by return e-mail and delete the original message from your system. = End Confidentiality Statement = -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Hamid Alavi 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: Correct way to determine freelists and freelists groups
I try to use table_access_path.sql but got stoped at @save_sqlplus_setting and couldn't find it, do I have to save my sqlplus setting manualy or some thing else, If any body run this script can you give me a hint for running it. Thanks -Original Message- Sent: Friday, June 07, 2002 1:33 PM To: Multiple recipients of list ORACLE-L Thanks 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! -Original Message- Sent: Friday, June 07, 2002 3:47 PM To: Multiple recipients of list ORACLE-L Are there any scripts table_access_paths.sql from www.ixora.com.au will suggest number of freelists for a table -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Greg Moore 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). === Confidentiality Statement === The information contained in this message and any attachments is intended only for the use of the individual or entity to which it is addressed, and may contain information that is PRIVILEGED, CONFIDENTIAL and exempt from disclosure under applicable law. If you have received this message in error, you are prohibited from copying, distributing, or using the information. Please contact the sender immediately by return e-mail and delete the original message from your system. = End Confidentiality Statement = -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Hamid Alavi 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: Index Constraint
An example on my understanding of Reverse Key Indices: Say you have a table EMP with a column EMPNO, and an index on this field.This number is incremented sequentially for every new employee that joins in. And as employees retire, say, the records are deleted. This would generally mean deletion of records, with lower employee numbers. And subsequent deletion of indices. As such, deletions from the index are likely to be concentrated on a small set of leaf blocks towards the beginning of the index. A reverse key index, which reverses the bytes for the column value, could help in uniformly dividing the deletions across various branches of the index, and avoid a skewed index. Raj One attachment (0k) Jamadagni, Rajendra To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Rajendra.Jamadagnicc: @espn.com Subject: RE: Index Constraint Sent by: [EMAIL PROTECTED] June 07, 2002 04:33 PM Please respond to ORACLE-L No, don't use reverse index for FKs, on certain versions they caused havoc by allowing parent key to be deleted (Metastink has an alert on this one). Reverse indexes are preferred in OPS environment AFAIK. 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! (See attached file: InterScan_Disclaimer.txt) InterScan_Disclaimer.txt Description: Binary data
RE: Oracle one-off Patch Install util
Here is an excerpt from the actual document: OPatch can be invoked directly as: perl opatch command [command_options] [ -h[elp] ] [ -n[o_op] ] or you can use the wrapper script that will use the version of perl installed in $ORACLE_HOME and invoke it as: opatch command [command_options] [ -h[elp] ] [ -n[o_op] ] where: perl is the perl binary with a version of 5.6.0 or greater -Original Message- From: Jesse, Rich [mailto:[EMAIL PROTECTED]] Sent: Friday, June 07, 2002 4:04 PM To: Multiple recipients of list ORACLE-L Subject: Oracle one-off Patch Install util Anyone used this yet? Does it run better than Oracle's RDA (haven't had a successful run of that yet!)? http://metalink.oracle.com/metalink/plsql/showdoc?db=NOTid=18 9489.1blackfr ame=0 And, Jared, you'll be happy that it appears to be written in Perl. :) Rich Jesse System/Database Administrator [EMAIL PROTECTED] Quad/Tech International, Sussex, WI USA -- 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: Gogala, Mladen 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: Correct way to determine freelists and freelists groups
Either comment out the save_sqlplus_setting script or download it from from www.ixora.com.au. Do the same for script restore_sqlplus_settings. Both scripts just save/restore your current SQL*Plus settings should another script modify them. Hamid Alavi wrote: I try to use table_access_path.sql but got stoped at @save_sqlplus_setting and couldn't find it, do I have to save my sqlplus setting manualy or some thing else, If any body run this script can you give me a hint for running it. Thanks -Original Message- Sent: Friday, June 07, 2002 1:33 PM To: Multiple recipients of list ORACLE-L Thanks 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! -Original Message- Sent: Friday, June 07, 2002 3:47 PM To: Multiple recipients of list ORACLE-L Are there any scripts table_access_paths.sql from www.ixora.com.au will suggest number of freelists for a table -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Greg Moore 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). === Confidentiality Statement === The information contained in this message and any attachments is intended only for the use of the individual or entity to which it is addressed, and may contain information that is PRIVILEGED, CONFIDENTIAL and exempt from disclosure under applicable law. If you have received this message in error, you are prohibited from copying, distributing, or using the information. Please contact the sender immediately by return e-mail and delete the original message from your system. = End Confidentiality Statement = -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Hamid Alavi 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: Suzy Vordos 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).
Horrendous Execution Plan from CBO
Greetings, One of our developers came to me with a fairly simple query that runs much faster when she uses the RBO. I looked at the execution plans generated by both the RBO and CBO and the CBO's plan is horrible. I was able to get a reasonable plan from the CBO using a USE_NL hint. Do any of you SQL tuning gurus have any suggestions? I've listed all the gory details below. Thanks, Mark Stahlke Oracle DuhBA Denver Newspaper Agency The Gory Details: Background: Oracle 7.3.4 (I know, I know). PUB has 531324 rows. CNR has 601007 rows. Both tables analyzed. The Query: 1 SELECT 2 c.acct_key, 3 c.pub, 4 c.ref_nbr, 5 c.sls_nbr_1 cnr_sls_nbr, 6 p.sls_eff_iss_1, 7 p.sls_nbr_1_1, 8 p.sls_nbr_1_2, 9 p.sls_eff_iss_2, 10 p.sls_nbr_2_1, 11 p.sls_nbr_2_2, 12 p.sls_eff_iss_3, 13 p.sls_nbr_3_1, 14 p.sls_nbr_3_2 15 FROM cnr c, pub p 16 WHERE c.acct_key = p.acct_key 17* AND c.pub = p.pub Plan and stats without hints: Execution Plan -- 0 SELECT STATEMENT Optimizer=CHOOSE (Cost=28838 Card=597847 By tes=100438296) 10 MERGE JOIN (Cost=28838 Card=597847 Bytes=100438296) 21 SORT (JOIN) 32 TABLE ACCESS (FULL) OF 'PUB' (Cost=841 Card=529489 Byt es=55066856) 41 SORT (JOIN) 54 TABLE ACCESS (FULL) OF 'CNR' (Cost=2195 Card=598749 By tes=38319936) Statistics -- 365 recursive calls 12740 db block gets 53167 consistent gets 96684 physical reads 4956 redo size 45285104 bytes sent via SQL*Net to client 441377 bytes received via SQL*Net from client 40070 SQL*Net roundtrips to/from client 0 sorts (memory) 2 sorts (disk) 601007 rows processed Plan and stats with /*+ RULE */ Execution Plan -- 0 SELECT STATEMENT Optimizer=HINT: RULE 10 NESTED LOOPS 21 TABLE ACCESS (FULL) OF 'PUB' 31 TABLE ACCESS (BY ROWID) OF 'CNR' 43 INDEX (RANGE SCAN) OF 'PK_CNR' (UNIQUE) Statistics -- 0 recursive calls 4 db block gets 2828280 consistent gets 69635 physical reads 0 redo size 45285104 bytes sent via SQL*Net to client 441389 bytes received via SQL*Net from client 40070 SQL*Net roundtrips to/from client 0 sorts (memory) 0 sorts (disk) 601007 rows processed Plan and stats with /*+ USE_NL(c p) */ Execution Plan -- 0 SELECT STATEMENT Optimizer=CHOOSE (Cost=1204217 Card=601007 Bytes=100969176) 10 NESTED LOOPS (Cost=1204217 Card=601007 Bytes=100969176) 21 TABLE ACCESS (FULL) OF 'CNR' (Cost=2203 Card=601007 Byte s=38464448) 31 TABLE ACCESS (BY ROWID) OF 'PUB' (Cost=844 Card=531324 B ytes=55257696) 43 INDEX (UNIQUE SCAN) OF 'PK_PUB' (UNIQUE) Statistics -- 0 recursive calls 4 db block gets 3062526 consistent gets 69490 physical reads 0 redo size 45285104 bytes sent via SQL*Net to client 441396 bytes received via SQL*Net from client 40070 SQL*Net roundtrips to/from client 0 sorts (memory) 0 sorts (disk) 601007 rows processed -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Stahlke, Mark 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: Index Constraint
Hamid, Which part of RTFM you didn't understand? Igor Neyman, OCP DBA [EMAIL PROTECTED] - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Friday, June 07, 2002 5:28 PM Igore, rtfm is a very complex answer, please reply clearly!! -Original Message- Sent: Friday, June 07, 2002 1:18 PM To: Multiple recipients of list ORACLE-L rtfm Igor Neyman, OCP DBA [EMAIL PROTECTED] - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Friday, June 07, 2002 3:46 PM Hi List, What's the diffrent between unique constrint unique index, when we have to use which, I think both are the same. Any Idea? And what about normal index reverse index? when we have to use normal and when reverse index, I think Reverse index normaly using for FK index, Is it right or not? Thanks Hamid Alavi Office 818 737-0526 Cell818 402-1987 === Confidentiality Statement === The information contained in this message and any attachments is intended only for the use of the individual or entity to which it is addressed, and may contain information that is PRIVILEGED, CONFIDENTIAL and exempt from disclosure under applicable law. If you have received this message in error, you are prohibited from copying, distributing, or using the information. Please contact the sender immediately by return e-mail and delete the original message from your system. = End Confidentiality Statement = -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Hamid Alavi 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: Igor Neyman 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). === Confidentiality Statement === The information contained in this message and any attachments is intended only for the use of the individual or entity to which it is addressed, and may contain information that is PRIVILEGED, CONFIDENTIAL and exempt from disclosure under applicable law. If you have received this message in error, you are prohibited from copying, distributing, or using the information. Please contact the sender immediately by return e-mail and delete the original message from your system. = End Confidentiality Statement = -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Hamid Alavi 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: Igor Neyman 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: Index Constraint
read the fine manual you might, by doing a bit of research, get the answers on your own. It's definitely a way to learn more --- Hamid Alavi [EMAIL PROTECTED] wrote: Igore, rtfm is a very complex answer, please reply clearly!! -Original Message- Sent: Friday, June 07, 2002 1:18 PM To: Multiple recipients of list ORACLE-L rtfm Igor Neyman, OCP DBA [EMAIL PROTECTED] - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Friday, June 07, 2002 3:46 PM Hi List, What's the diffrent between unique constrint unique index, when we have to use which, I think both are the same. Any Idea? And what about normal index reverse index? when we have to use normal and when reverse index, I think Reverse index normaly using for FK index, Is it right or not? Thanks Hamid Alavi Office 818 737-0526 Cell818 402-1987 === Confidentiality Statement === The information contained in this message and any attachments is intended only for the use of the individual or entity to which it is addressed, and may contain information that is PRIVILEGED, CONFIDENTIAL and exempt from disclosure under applicable law. If you have received this message in error, you are prohibited from copying, distributing, or using the information. Please contact the sender immediately by return e-mail and delete the original message from your system. = End Confidentiality Statement = -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Hamid Alavi 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: Igor Neyman 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). === Confidentiality Statement === The information contained in this message and any attachments is intended only for the use of the individual or entity to which it is addressed, and may contain information that is PRIVILEGED, CONFIDENTIAL and exempt from disclosure under applicable law. If you have received this message in error, you are prohibited from copying, distributing, or using the information. Please contact the sender immediately by return e-mail and delete the original message from your system. = End Confidentiality Statement = -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Hamid Alavi 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! - Official partner of 2002 FIFA World Cup http://fifaworldcup.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).
RE: Index Constraint
Read The Frigging Manual -Original Message- From: Hamid Alavi [mailto:[EMAIL PROTECTED]] Sent: Friday, June 07, 2002 5:28 PM To: Multiple recipients of list ORACLE-L Subject: RE: Index Constraint Igore, rtfm is a very complex answer, please reply clearly!! -Original Message- Sent: Friday, June 07, 2002 1:18 PM To: Multiple recipients of list ORACLE-L rtfm Igor Neyman, OCP DBA [EMAIL PROTECTED] - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Friday, June 07, 2002 3:46 PM Hi List, What's the diffrent between unique constrint unique index, when we have to use which, I think both are the same. Any Idea? And what about normal index reverse index? when we have to use normal and when reverse index, I think Reverse index normaly using for FK index, Is it right or not? Thanks Hamid Alavi Office 818 737-0526 Cell818 402-1987 === Confidentiality Statement === The information contained in this message and any attachments is intended only for the use of the individual or entity to which it is addressed, and may contain information that is PRIVILEGED, CONFIDENTIAL and exempt from disclosure under applicable law. If you have received this message in error, you are prohibited from copying, distributing, or using the information. Please contact the sender immediately by return e-mail and delete the original message from your system. = End Confidentiality Statement = -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Hamid Alavi 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: Igor Neyman 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). === Confidentiality Statement === The information contained in this message and any attachments is intended only for the use of the individual or entity to which it is addressed, and may contain information that is PRIVILEGED, CONFIDENTIAL and exempt from disclosure under applicable law. If you have received this message in error, you are prohibited from copying, distributing, or using the information. Please contact the sender immediately by return e-mail and delete the original message from your system. = End Confidentiality Statement = -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Hamid Alavi 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: Gogala, Mladen 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: Oracle one-off Patch Install util
has anyone actually found these utilities? Oracle claims they are with 9iR2, but I can't find them. Jared Gogala, Mladen [EMAIL PROTECTED] Sent by: [EMAIL PROTECTED] 06/07/2002 02:43 PM Please respond to ORACLE-L To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] cc: Subject:RE: Oracle one-off Patch Install util Here is an excerpt from the actual document: OPatch can be invoked directly as: perl opatch command [command_options] [ -h[elp] ] [ -n[o_op] ] or you can use the wrapper script that will use the version of perl installed in $ORACLE_HOME and invoke it as: opatch command [command_options] [ -h[elp] ] [ -n[o_op] ] where: perl is the perl binary with a version of 5.6.0 or greater -Original Message- From: Jesse, Rich [mailto:[EMAIL PROTECTED]] Sent: Friday, June 07, 2002 4:04 PM To: Multiple recipients of list ORACLE-L Subject: Oracle one-off Patch Install util Anyone used this yet? Does it run better than Oracle's RDA (haven't had a successful run of that yet!)? http://metalink.oracle.com/metalink/plsql/showdoc?db=NOTid=18 9489.1blackfr ame=0 And, Jared, you'll be happy that it appears to be written in Perl. :) Rich Jesse System/Database Administrator [EMAIL PROTECTED] Quad/Tech International, Sussex, WI USA -- 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: Gogala, Mladen 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: Index Constraint
Running To Find Mama. Just kidding around, JoJo -Original Message- Sent: Friday, June 07, 2002 2:28 PM To: Multiple recipients of list ORACLE-L Igore, rtfm is a very complex answer, please reply clearly!! -Original Message- Sent: Friday, June 07, 2002 1:18 PM To: Multiple recipients of list ORACLE-L rtfm Igor Neyman, OCP DBA [EMAIL PROTECTED] - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Friday, June 07, 2002 3:46 PM Hi List, What's the diffrent between unique constrint unique index, when we have to use which, I think both are the same. Any Idea? And what about normal index reverse index? when we have to use normal and when reverse index, I think Reverse index normaly using for FK index, Is it right or not? Thanks Hamid Alavi Office 818 737-0526 Cell818 402-1987 === Confidentiality Statement === The information contained in this message and any attachments is intended only for the use of the individual or entity to which it is addressed, and may contain information that is PRIVILEGED, CONFIDENTIAL and exempt from disclosure under applicable law. If you have received this message in error, you are prohibited from copying, distributing, or using the information. Please contact the sender immediately by return e-mail and delete the original message from your system. = End Confidentiality Statement = -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Hamid Alavi 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: Igor Neyman 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). === Confidentiality Statement === The information contained in this message and any attachments is intended only for the use of the individual or entity to which it is addressed, and may contain information that is PRIVILEGED, CONFIDENTIAL and exempt from disclosure under applicable law. If you have received this message in error, you are prohibited from copying, distributing, or using the information. Please contact the sender immediately by return e-mail and delete the original message from your system. = End Confidentiality Statement = -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Hamid Alavi 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: JoJo Al-Zawawi 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).
Simple Oracle database question.
Hi, Currently I have only one database running on a single host. Supposedly, I need to create multiple databases on the same host. My question is can I open two databases at the same time? I have not done it before, so just wanted to make sure that I can work on two databases all at once. Thanks in advance TrangDo You Yahoo!? Sign-up for Video Highlights of 2002 FIFA World Cup
RE: Index Constraint
Igore, The RTFM part ! -Original Message- Sent: Friday, June 07, 2002 3:04 PM To: Multiple recipients of list ORACLE-L Hamid, Which part of RTFM you didn't understand? Igor Neyman, OCP DBA [EMAIL PROTECTED] - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Friday, June 07, 2002 5:28 PM Igore, rtfm is a very complex answer, please reply clearly!! -Original Message- Sent: Friday, June 07, 2002 1:18 PM To: Multiple recipients of list ORACLE-L rtfm Igor Neyman, OCP DBA [EMAIL PROTECTED] - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Friday, June 07, 2002 3:46 PM Hi List, What's the diffrent between unique constrint unique index, when we have to use which, I think both are the same. Any Idea? And what about normal index reverse index? when we have to use normal and when reverse index, I think Reverse index normaly using for FK index, Is it right or not? Thanks Hamid Alavi Office 818 737-0526 Cell818 402-1987 === Confidentiality Statement === The information contained in this message and any attachments is intended only for the use of the individual or entity to which it is addressed, and may contain information that is PRIVILEGED, CONFIDENTIAL and exempt from disclosure under applicable law. If you have received this message in error, you are prohibited from copying, distributing, or using the information. Please contact the sender immediately by return e-mail and delete the original message from your system. = End Confidentiality Statement = -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Hamid Alavi 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: Igor Neyman 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). === Confidentiality Statement === The information contained in this message and any attachments is intended only for the use of the individual or entity to which it is addressed, and may contain information that is PRIVILEGED, CONFIDENTIAL and exempt from disclosure under applicable law. If you have received this message in error, you are prohibited from copying, distributing, or using the information. Please contact the sender immediately by return e-mail and delete the original message from your system. = End Confidentiality Statement = -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Hamid Alavi 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: Igor Neyman 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
Re: Horrendous Execution Plan from CBO
When were statistics last generated? Any significant DML since then? Jared Stahlke, Mark [EMAIL PROTECTED] Sent by: [EMAIL PROTECTED] 06/07/2002 02:51 PM Please respond to ORACLE-L To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] cc: Subject:Horrendous Execution Plan from CBO Greetings, One of our developers came to me with a fairly simple query that runs much faster when she uses the RBO. I looked at the execution plans generated by both the RBO and CBO and the CBO's plan is horrible. I was able to get a reasonable plan from the CBO using a USE_NL hint. Do any of you SQL tuning gurus have any suggestions? I've listed all the gory details below. Thanks, Mark Stahlke Oracle DuhBA Denver Newspaper Agency The Gory Details: Background: Oracle 7.3.4 (I know, I know). PUB has 531324 rows. CNR has 601007 rows. Both tables analyzed. The Query: 1 SELECT 2 c.acct_key, 3 c.pub, 4 c.ref_nbr, 5 c.sls_nbr_1 cnr_sls_nbr, 6 p.sls_eff_iss_1, 7 p.sls_nbr_1_1, 8 p.sls_nbr_1_2, 9 p.sls_eff_iss_2, 10 p.sls_nbr_2_1, 11 p.sls_nbr_2_2, 12 p.sls_eff_iss_3, 13 p.sls_nbr_3_1, 14 p.sls_nbr_3_2 15 FROM cnr c, pub p 16 WHERE c.acct_key = p.acct_key 17* AND c.pub = p.pub Plan and stats without hints: Execution Plan -- 0 SELECT STATEMENT Optimizer=CHOOSE (Cost=28838 Card=597847 By tes=100438296) 10 MERGE JOIN (Cost=28838 Card=597847 Bytes=100438296) 21 SORT (JOIN) 32 TABLE ACCESS (FULL) OF 'PUB' (Cost=841 Card=529489 Byt es=55066856) 41 SORT (JOIN) 54 TABLE ACCESS (FULL) OF 'CNR' (Cost=2195 Card=598749 By tes=38319936) Statistics -- 365 recursive calls 12740 db block gets 53167 consistent gets 96684 physical reads 4956 redo size 45285104 bytes sent via SQL*Net to client 441377 bytes received via SQL*Net from client 40070 SQL*Net roundtrips to/from client 0 sorts (memory) 2 sorts (disk) 601007 rows processed Plan and stats with /*+ RULE */ Execution Plan -- 0 SELECT STATEMENT Optimizer=HINT: RULE 10 NESTED LOOPS 21 TABLE ACCESS (FULL) OF 'PUB' 31 TABLE ACCESS (BY ROWID) OF 'CNR' 43 INDEX (RANGE SCAN) OF 'PK_CNR' (UNIQUE) Statistics -- 0 recursive calls 4 db block gets 2828280 consistent gets 69635 physical reads 0 redo size 45285104 bytes sent via SQL*Net to client 441389 bytes received via SQL*Net from client 40070 SQL*Net roundtrips to/from client 0 sorts (memory) 0 sorts (disk) 601007 rows processed Plan and stats with /*+ USE_NL(c p) */ Execution Plan -- 0 SELECT STATEMENT Optimizer=CHOOSE (Cost=1204217 Card=601007 Bytes=100969176) 10 NESTED LOOPS (Cost=1204217 Card=601007 Bytes=100969176) 21 TABLE ACCESS (FULL) OF 'CNR' (Cost=2203 Card=601007 Byte s=38464448) 31 TABLE ACCESS (BY ROWID) OF 'PUB' (Cost=844 Card=531324 B ytes=55257696) 43 INDEX (UNIQUE SCAN) OF 'PK_PUB' (UNIQUE) Statistics -- 0 recursive calls 4 db block gets 3062526 consistent gets 69490 physical reads 0 redo size 45285104 bytes sent via SQL*Net to client 441396 bytes received via SQL*Net from client 40070 SQL*Net roundtrips to/from client 0 sorts (memory) 0 sorts (disk) 601007 rows processed -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Stahlke, Mark 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
RE: Horrendous Execution Plan from CBO
Thanks for the quick responses. I analyzed both tables immediately before I started testing. The USE_MERGE hint gives me the same execution plan I get without hints. Mark Stahlke Oracle DuhBA Denver Newspaper Agency -Original Message- From: [EMAIL PROTECTED] [SMTP:[EMAIL PROTECTED]] Sent: Friday, June 07, 2002 3:29 PM To: [EMAIL PROTECTED] Cc: [EMAIL PROTECTED] Subject:Re: Horrendous Execution Plan from CBO When were statistics last generated? Any significant DML since then? Jared Stahlke, Mark [EMAIL PROTECTED] Sent by: [EMAIL PROTECTED] 06/07/2002 02:51 PM Please respond to ORACLE-L To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] cc: Subject:Horrendous Execution Plan from CBO Greetings, One of our developers came to me with a fairly simple query that runs much faster when she uses the RBO. I looked at the execution plans generated by both the RBO and CBO and the CBO's plan is horrible. I was able to get a reasonable plan from the CBO using a USE_NL hint. Do any of you SQL tuning gurus have any suggestions? I've listed all the gory details below. Thanks, Mark Stahlke Oracle DuhBA Denver Newspaper Agency The Gory Details: Background: Oracle 7.3.4 (I know, I know). PUB has 531324 rows. CNR has 601007 rows. Both tables analyzed. The Query: 1 SELECT 2 c.acct_key, 3 c.pub, 4 c.ref_nbr, 5 c.sls_nbr_1 cnr_sls_nbr, 6 p.sls_eff_iss_1, 7 p.sls_nbr_1_1, 8 p.sls_nbr_1_2, 9 p.sls_eff_iss_2, 10 p.sls_nbr_2_1, 11 p.sls_nbr_2_2, 12 p.sls_eff_iss_3, 13 p.sls_nbr_3_1, 14 p.sls_nbr_3_2 15 FROM cnr c, pub p 16 WHERE c.acct_key = p.acct_key 17* AND c.pub = p.pub Plan and stats without hints: Execution Plan -- 0 SELECT STATEMENT Optimizer=CHOOSE (Cost=28838 Card=597847 By tes=100438296) 10 MERGE JOIN (Cost=28838 Card=597847 Bytes=100438296) 21 SORT (JOIN) 32 TABLE ACCESS (FULL) OF 'PUB' (Cost=841 Card=529489 Byt es=55066856) 41 SORT (JOIN) 54 TABLE ACCESS (FULL) OF 'CNR' (Cost=2195 Card=598749 By tes=38319936) Statistics -- 365 recursive calls 12740 db block gets 53167 consistent gets 96684 physical reads 4956 redo size 45285104 bytes sent via SQL*Net to client 441377 bytes received via SQL*Net from client 40070 SQL*Net roundtrips to/from client 0 sorts (memory) 2 sorts (disk) 601007 rows processed Plan and stats with /*+ RULE */ Execution Plan -- 0 SELECT STATEMENT Optimizer=HINT: RULE 10 NESTED LOOPS 21 TABLE ACCESS (FULL) OF 'PUB' 31 TABLE ACCESS (BY ROWID) OF 'CNR' 43 INDEX (RANGE SCAN) OF 'PK_CNR' (UNIQUE) Statistics -- 0 recursive calls 4 db block gets 2828280 consistent gets 69635 physical reads 0 redo size 45285104 bytes sent via SQL*Net to client 441389 bytes received via SQL*Net from client 40070 SQL*Net roundtrips to/from client 0 sorts (memory) 0 sorts (disk) 601007 rows processed Plan and stats with /*+ USE_NL(c p) */ Execution Plan -- 0 SELECT STATEMENT Optimizer=CHOOSE (Cost=1204217 Card=601007 Bytes=100969176) 10 NESTED LOOPS (Cost=1204217 Card=601007 Bytes=100969176) 21 TABLE ACCESS (FULL) OF 'CNR' (Cost=2203 Card=601007 Byte s=38464448) 31 TABLE ACCESS (BY ROWID) OF 'PUB' (Cost=844 Card=531324 B ytes=55257696) 43 INDEX (UNIQUE SCAN) OF 'PK_PUB' (UNIQUE) Statistics -- 0 recursive calls 4 db block gets 3062526 consistent gets
RE: Horrendous Execution Plan from CBO
Mark, Do any of you SQL tuning gurus have any suggestions? I've listed all the gory details below. I am not a SQL tuning guru, but it looks like this is an ideal example where Hash joins would be of immense help. You could set a largish value for HASH_AREA_SIZE (defaults to twice SORT_AREA_SIZE) to reduce visits to TEMP to write out hash tables... It would be nice if you could post the explain/costs for all three methods at the end of your tests. John Kanagaraj Oracle Applications DBA DBSoft Inc (W): 408-970-7002 The manuals for Oracle are here: http://tahiti.oracle.com The manual for Life is here: http://www.gospelcom.net ** The opinions and statements above are entirely my own and not those of my employer or clients ** -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: John Kanagaraj 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: Simple Oracle database question.
Yes, just make sure your ORACLE_SID is set correctly when creating or starting the new database. You can even have multiple databases each using a diff version of Oracle. Meomeo Nguyen wrote: Hi, Currently I have only one database running on a single host. Supposedly, I need to create multiple databases on the same host. My question is can I open two databases at the same time? I have not done it before, so just wanted to make sure that I can work on two databases all at once. Thanks in advance Trang -- Do You Yahoo!? Sign-up for Video Highlights of 2002 FIFA World Cup -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Suzy Vordos 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: Simple Oracle database question.
On my dev box I have following databases running oraclei@orion ps -ef | grep pmonoraclei 13487 1 1 21:23:53 ? 2:27 ora_pmon_INTDEV oraclei 13529 1 1 21:23:57 ? 2:18 ora_pmon_INTSTAGE oraclef 1846 1 1 00:26:03 ? 0:14 ora_pmon_FINTEST oraclei 25861 1 1 07:41:40 ? 1:18 ora_pmon_ABCTVoraclei 29711 1 1 Jun 06 ? 3:08 ora_pmon_OLDCSI oraclei 1095 1 1 02:49:31 ? 2:02 ora_pmon_DEVL oraclei 17655 1 1 21:26:33 ? 2:27 ora_pmon_DEVCRM oraclei 17700 1 1 21:26:34 ? 2:27 ora_pmon_TESTCRM oraclei 17808 1 1 21:26:37 ? 0:17 ora_pmon_DESGN oraclei 7664 1 1 05:46:02 ? 1:38 ora_pmon_DAYOLD oraclef 13932 1 1 19:51:12 ? 0:15 ora_pmon_FINDEV oraclei 25678 1 1 03:57:18 ? 1:48 ora_pmon_ACPT oracles 11583 1 1 21:22:38 ? 0:14 ora_pmon_WHTEST oraclei 27835 1 1 May 23 ? 41:13 ora_pmon_TESTBK oraclei 12692 1 1 21:23:21 ? 2:22 ora_pmon_EXTSTAGE oraclei 12694 1 1 21:23:22 ? 2:24 ora_pmon_EXTDEV oraclei 15219 1 1 00:56:12 ? 2:09 ora_pmon_ABCDEV oraclei 160 1 1 00:43:19 ? 3:26 ora_pmon_OLDABC so, the answer is Yes ... 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! -Original Message-From: Meomeo Nguyen [mailto:[EMAIL PROTECTED]]Sent: Friday, June 07, 2002 6:28 PMTo: Multiple recipients of list ORACLE-LSubject: Simple Oracle database question. Hi, Currently I have only one database running on a single host. Supposedly, I need to create multiple databases on the same host. My question is can I open two databases at the same time? I have not done it before, so just wanted to make sure that I can work on two databases all at once. Thanks in advance Trang Do You Yahoo!?Sign-up for Video Highlights of 2002 FIFA World Cup *This e-mail message is confidential, intended only for the named recipient(s) above and may contain information that is privileged, attorney work product or exempt from disclosure under applicable law. If you have received this message in error, or are not the named recipient(s), please immediately notify corporate MIS at (860) 766-2000 and delete this e-mail message from your computer, Thank you.*2
RE: Oracle one-off Patch Install util
I've used RDA for Solaris just recently. It's pretty slick. Somebody put a lot of thought into this tool. The html pages it generates are a pretty good snapshot of your environment. It makes use of frames which makes it easy to navigate through the information. Gerardo -Original Message- Sent: Friday, June 07, 2002 1:04 PM To: Multiple recipients of list ORACLE-L Anyone used this yet? Does it run better than Oracle's RDA (haven't had a successful run of that yet!)? http://metalink.oracle.com/metalink/plsql/showdoc?db=NOTid=189489.1blackfr ame=0 And, Jared, you'll be happy that it appears to be written in Perl. :) Rich Jesse System/Database Administrator [EMAIL PROTECTED] Quad/Tech International, Sussex, WI USA -- 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: Molina, Gerardo 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: Oracle one-off Patch Install util
Jared, Now that you mention it ... couldn't find it on Windoz NT and AIX 5 installations ... 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! *2 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: Index Constraint
Thanks ALL, Specialy Igore. -Original Message- Sent: Friday, June 07, 2002 3:28 PM To: Multiple recipients of list ORACLE-L Read The Frigging Manual -Original Message- From: Hamid Alavi [mailto:[EMAIL PROTECTED]] Sent: Friday, June 07, 2002 5:28 PM To: Multiple recipients of list ORACLE-L Subject: RE: Index Constraint Igore, rtfm is a very complex answer, please reply clearly!! -Original Message- Sent: Friday, June 07, 2002 1:18 PM To: Multiple recipients of list ORACLE-L rtfm Igor Neyman, OCP DBA [EMAIL PROTECTED] - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Friday, June 07, 2002 3:46 PM Hi List, What's the diffrent between unique constrint unique index, when we have to use which, I think both are the same. Any Idea? And what about normal index reverse index? when we have to use normal and when reverse index, I think Reverse index normaly using for FK index, Is it right or not? Thanks Hamid Alavi Office 818 737-0526 Cell818 402-1987 === Confidentiality Statement === The information contained in this message and any attachments is intended only for the use of the individual or entity to which it is addressed, and may contain information that is PRIVILEGED, CONFIDENTIAL and exempt from disclosure under applicable law. If you have received this message in error, you are prohibited from copying, distributing, or using the information. Please contact the sender immediately by return e-mail and delete the original message from your system. = End Confidentiality Statement = -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Hamid Alavi 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: Igor Neyman 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). === Confidentiality Statement === The information contained in this message and any attachments is intended only for the use of the individual or entity to which it is addressed, and may contain information that is PRIVILEGED, CONFIDENTIAL and exempt from disclosure under applicable law. If you have received this message in error, you are prohibited from copying, distributing, or using the information. Please contact the sender immediately by return e-mail and delete the original message from your system. = End Confidentiality Statement = -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Hamid Alavi 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: Gogala, Mladen 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
RE: Simple Oracle database question.
If you are on Unix make sure your ORACLE_SID environment variable is set to the database you want to use. -Original Message-From: Meomeo Nguyen [mailto:[EMAIL PROTECTED]]Sent: Friday, June 07, 2002 3:28 PMTo: Multiple recipients of list ORACLE-LSubject: Simple Oracle database question. Hi, Currently I have only one database running on a single host. Supposedly, I need to create multiple databases on the same host. My question is can I open two databases at the same time? I have not done it before, so just wanted to make sure that I can work on two databases all at once. Thanks in advance Trang Do You Yahoo!?Sign-up for Video Highlights of 2002 FIFA World Cup
RE: Index Constraint
Igore, I didn't expect even in this group you can find such low class people If you want to use these sort of words please keep it for yourself. -Original Message- Sent: Friday, June 07, 2002 1:18 PM To: Multiple recipients of list ORACLE-L rtfm Igor Neyman, OCP DBA [EMAIL PROTECTED] - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Friday, June 07, 2002 3:46 PM Hi List, What's the diffrent between unique constrint unique index, when we have to use which, I think both are the same. Any Idea? And what about normal index reverse index? when we have to use normal and when reverse index, I think Reverse index normaly using for FK index, Is it right or not? Thanks Hamid Alavi Office 818 737-0526 Cell818 402-1987 === Confidentiality Statement === The information contained in this message and any attachments is intended only for the use of the individual or entity to which it is addressed, and may contain information that is PRIVILEGED, CONFIDENTIAL and exempt from disclosure under applicable law. If you have received this message in error, you are prohibited from copying, distributing, or using the information. Please contact the sender immediately by return e-mail and delete the original message from your system. = End Confidentiality Statement = -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Hamid Alavi 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: Igor Neyman 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). === Confidentiality Statement === The information contained in this message and any attachments is intended only for the use of the individual or entity to which it is addressed, and may contain information that is PRIVILEGED, CONFIDENTIAL and exempt from disclosure under applicable law. If you have received this message in error, you are prohibited from copying, distributing, or using the information. Please contact the sender immediately by return e-mail and delete the original message from your system. = End Confidentiality Statement = -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Hamid Alavi 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: Horrendous Execution Plan from CBO
I'm going along with John. Try the hash join. CBO was less mature in v7, so it may need a little 'help' to get the plan you want. Such as a 'hash' hint, or the use_nl hint if you don't use the hash. Jared Stahlke, Mark [EMAIL PROTECTED] Sent by: [EMAIL PROTECTED] 06/07/2002 03:43 PM Please respond to ORACLE-L To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] cc: Subject:RE: Horrendous Execution Plan from CBO Thanks for the quick responses. I analyzed both tables immediately before I started testing. The USE_MERGE hint gives me the same execution plan I get without hints. Mark Stahlke Oracle DuhBA Denver Newspaper Agency -Original Message- From: [EMAIL PROTECTED] [SMTP:[EMAIL PROTECTED]] Sent: Friday, June 07, 2002 3:29 PM To: [EMAIL PROTECTED] Cc: [EMAIL PROTECTED] Subject:Re: Horrendous Execution Plan from CBO When were statistics last generated? Any significant DML since then? Jared Stahlke, Mark [EMAIL PROTECTED] Sent by: [EMAIL PROTECTED] 06/07/2002 02:51 PM Please respond to ORACLE-L To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] cc: Subject:Horrendous Execution Plan from CBO Greetings, One of our developers came to me with a fairly simple query that runs much faster when she uses the RBO. I looked at the execution plans generated by both the RBO and CBO and the CBO's plan is horrible. I was able to get a reasonable plan from the CBO using a USE_NL hint. Do any of you SQL tuning gurus have any suggestions? I've listed all the gory details below. Thanks, Mark Stahlke Oracle DuhBA Denver Newspaper Agency The Gory Details: Background: Oracle 7.3.4 (I know, I know). PUB has 531324 rows. CNR has 601007 rows. Both tables analyzed. The Query: 1 SELECT 2 c.acct_key, 3 c.pub, 4 c.ref_nbr, 5 c.sls_nbr_1 cnr_sls_nbr, 6 p.sls_eff_iss_1, 7 p.sls_nbr_1_1, 8 p.sls_nbr_1_2, 9 p.sls_eff_iss_2, 10 p.sls_nbr_2_1, 11 p.sls_nbr_2_2, 12 p.sls_eff_iss_3, 13 p.sls_nbr_3_1, 14 p.sls_nbr_3_2 15 FROM cnr c, pub p 16 WHERE c.acct_key = p.acct_key 17* AND c.pub = p.pub Plan and stats without hints: Execution Plan -- 0 SELECT STATEMENT Optimizer=CHOOSE (Cost=28838 Card=597847 By tes=100438296) 10 MERGE JOIN (Cost=28838 Card=597847 Bytes=100438296) 21 SORT (JOIN) 32 TABLE ACCESS (FULL) OF 'PUB' (Cost=841 Card=529489 Byt es=55066856) 41 SORT (JOIN) 54 TABLE ACCESS (FULL) OF 'CNR' (Cost=2195 Card=598749 By tes=38319936) Statistics -- 365 recursive calls 12740 db block gets 53167 consistent gets 96684 physical reads 4956 redo size 45285104 bytes sent via SQL*Net to client 441377 bytes received via SQL*Net from client 40070 SQL*Net roundtrips to/from client 0 sorts (memory) 2 sorts (disk) 601007 rows processed Plan and stats with /*+ RULE */ Execution Plan -- 0 SELECT STATEMENT Optimizer=HINT: RULE 10 NESTED LOOPS 21 TABLE ACCESS (FULL) OF 'PUB' 31 TABLE ACCESS (BY ROWID) OF 'CNR' 43 INDEX (RANGE SCAN) OF 'PK_CNR' (UNIQUE) Statistics -- 0 recursive calls
RE: Horrendous Execution Plan from CBO
Thanks for the tips. The hash join looks like the best bet. Someday we'll upgrade to 8.1.7. Someday... Here is the plan and stats using /*+ USE_HASH(c p) */ Execution Plan -- 0 SELECT STATEMENT Optimizer=CHOOSE (Cost=104706 Card=601007 B ytes=100969176) 10 HASH JOIN (Cost=104706 Card=601007 Bytes=100969176) 21 TABLE ACCESS (FULL) OF 'CNR' (Cost=2203 Card=601007 Byte s=38464448) 31 TABLE ACCESS (FULL) OF 'PUB' (Cost=844 Card=531324 Bytes =55257696) Statistics -- 0 recursive calls 8 db block gets 58649 consistent gets 91957 physical reads 0 redo size 45285104 bytes sent via SQL*Net to client 441398 bytes received via SQL*Net from client 40070 SQL*Net roundtrips to/from client 0 sorts (memory) 0 sorts (disk) 601007 rows processed -Original Message- From: John Kanagaraj [SMTP:[EMAIL PROTECTED]] Sent: Friday, June 07, 2002 4:44 PM To: Multiple recipients of list ORACLE-L Subject:RE: Horrendous Execution Plan from CBO Mark, Do any of you SQL tuning gurus have any suggestions? I've listed all the gory details below. I am not a SQL tuning guru, but it looks like this is an ideal example where Hash joins would be of immense help. You could set a largish value for HASH_AREA_SIZE (defaults to twice SORT_AREA_SIZE) to reduce visits to TEMP to write out hash tables... It would be nice if you could post the explain/costs for all three methods at the end of your tests. John Kanagaraj Oracle Applications DBA DBSoft Inc (W): 408-970-7002 The manuals for Oracle are here: http://tahiti.oracle.com The manual for Life is here: http://www.gospelcom.net ** The opinions and statements above are entirely my own and not those of my employer or clients ** -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: John Kanagaraj 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: Stahlke, Mark 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: TAF
Exactly that is what my benchmarks have also noticed. Couple of other points... 1. Since the PRECONNECT always maintains a connection with the secondary instance, there is a 2% overhead compared to the BASIC option. 2. I don't and did not see the need for a PRECONNECT option. Off course like Tim indicates there is potential traffic jam situation in the case of the BASIC option and there could be small delay. Unless your application is being developed for the Stock Exchange or for the space station (where such an application may not be useful anyways) I mean machine critical nature you should not have to worry. 3. You could use the RETRY/DELAY option combined with the BASIC option to cover for any failures/timeouts when the jam occurs. I had a paper presented on this very subject at the 2001 Open world. You should find it on the Oracle website. Regards Murali Vallath Reply-To: [EMAIL PROTECTED] To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Date: Fri, 07 Jun 2002 05:18:27 -0800 Fawzia, PRECONNECT establishes the second/failover database connection along with the first/primary, so your client-server database connection is really client-server1/server2. Should the first/primary connection fail, then you failover to the second. BASIC establishes only the first/primary database connection but has the information available to establish the second/failover connection should the first/primary fail. PRECONNECT will failover faster, as the second/failover connection has already been established. In a large environment (i.e. hundreds of database connections) full of BASIC connections, you can expect the mass exodus from one database instance to the failover to cause BASIC connections to wait a noticeable amount of time. One problem with PRECONNECT that I've noticed is the scenario where the second/failover instance is terminated for some reason first. Let's say a bunch of PRECONNECT TAF connections are working away happily via their first/primary connection to the primary database instance, and there is some reason to terminate the second database instance or some/all of the second/failover database connections to the second database instance. In this case, PRECONNECT will not notice the loss of the second/failover database connection, allowing the first/primary connection to continue working happily away. But after bringing the second database instance back online, if the first database instance should now fail, then all those PRECONNECT sessions will simply fail. Upshot: BASIC is more robust, because it fails over only when it needs to, but you can get caught in a traffic jam in the event of failover. PRECONNECT can fail over faster because of previously established failover connections, but if anything happens to that second connection after the time it is established, then you'll be out of luck. Hope this helps... -Tim - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Friday, June 07, 2002 5:43 AM Hi , I am configuring TAF on oracle 8.1.7 (solaris). I was just wondering if anyone on the list has had experience on this and can give me any advice- more specifically on the the method- preconnect and basic. Are there any significant benefits of having the preconnect setting set? Any input would be really appreciated.. Rgds Fawzia ** Information in this email is confidential and may be privileged. It is intended for the addressee only. If you have received it in error, please notify the sender immediately and delete it from your system. You should not otherwise copy it, retransmit it or use or disclose its contents to anyone. Thank you for your co-operation. ** -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Malik, Fawzia 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
Re: Horrendous Execution Plan from CBO
7.3.4? You're brave... Try to analyze with the following syntax: ANALYZE TABLE tname COMPUTE STATISTICS FOR ALL INDEXED COLUMNS; There was a funny with CBO that it ignored index stats completely even in some very simple joins. With 7.3.4. Usually this fixed the problem. Can't remember which patch level fixed the problem, try this syntax and see if it resolves this issue. Hint to try: FIRST_ROWS (no need to specify table aliases) Since both tables are of nearly the same number of rows hash joins would be a bad idea, particularly at 7.3.4. Stick to either merge (if you want the lot selected) or nested loops (if you want subset of all joined rows). Make sure those join columns are of the SAME data type, it may be suffering from implicit conversion. HTH Cheers Nuno Souto [EMAIL PROTECTED] - Original Message - Do any of you SQL tuning gurus have any suggestions? I've listed all the gory details below. -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Nuno Souto 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: Simple Oracle database question.
I currently have 4 databases running and am in the process of creating a 5th one. thisis on a solaris box. It is possible to run as many instances as disk space and memory will allow. It might not be good for performance but you can run more than one on the box. --- Meomeo Nguyen [EMAIL PROTECTED] wrote: Hi, Currently I have only one database running on a single host. Supposedly, I need to create multiple databases on the same host. My question is can I open two databases at the same time? I have not done it before, so just wanted to make sure that I can work on two databases all at once. Thanks in advance Trang - Do You Yahoo!? Sign-up for Video Highlights of 2002 FIFA World Cup __ Do You Yahoo!? Yahoo! - Official partner of 2002 FIFA World Cup http://fifaworldcup.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: Index Constraint
rtfm can be read as read the fine manual exactly what sort of language are you accusing Igor (and it would be nice if you managed to spell his name correctly) of using? you have spent a good deal of time asking people on this list for help for things that you could easily find in the manuals, if you had only looked. We have been more than patient, answering your questions and attempting to help you. There are, however, limits. In the future, please try to look through the manuals to find the answers you need. Besides the fact that you will stop annoying people on the list, you will also find that that's an excellent way to learn. --- Hamid Alavi [EMAIL PROTECTED] wrote: Igore, I didn't expect even in this group you can find such low class people If you want to use these sort of words please keep it for yourself. -Original Message- Sent: Friday, June 07, 2002 1:18 PM To: Multiple recipients of list ORACLE-L rtfm Igor Neyman, OCP DBA [EMAIL PROTECTED] - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Friday, June 07, 2002 3:46 PM Hi List, What's the diffrent between unique constrint unique index, when we have to use which, I think both are the same. Any Idea? And what about normal index reverse index? when we have to use normal and when reverse index, I think Reverse index normaly using for FK index, Is it right or not? Thanks Hamid Alavi Office 818 737-0526 Cell818 402-1987 === Confidentiality Statement === The information contained in this message and any attachments is intended only for the use of the individual or entity to which it is addressed, and may contain information that is PRIVILEGED, CONFIDENTIAL and exempt from disclosure under applicable law. If you have received this message in error, you are prohibited from copying, distributing, or using the information. Please contact the sender immediately by return e-mail and delete the original message from your system. = End Confidentiality Statement = -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Hamid Alavi 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: Igor Neyman 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). === Confidentiality Statement === The information contained in this message and any attachments is intended only for the use of the individual or entity to which it is addressed, and may contain information that is PRIVILEGED, CONFIDENTIAL and exempt from disclosure under applicable law. If you have received this message in error, you are prohibited from copying, distributing, or using the information. Please contact the sender immediately by return e-mail and delete the original message from your system. = End Confidentiality Statement = -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Hamid Alavi 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! - Official partner of 2002 FIFA World Cup
RE: Oracle one-off Patch Install util
RDA was written (or at least managed) by Anita Bardeen who used to spend a good deal of time on this list...she'll be happy to know you like it! Rachel --- Molina, Gerardo [EMAIL PROTECTED] wrote: I've used RDA for Solaris just recently. It's pretty slick. Somebody put a lot of thought into this tool. The html pages it generates are a pretty good snapshot of your environment. It makes use of frames which makes it easy to navigate through the information. Gerardo -Original Message- Sent: Friday, June 07, 2002 1:04 PM To: Multiple recipients of list ORACLE-L Anyone used this yet? Does it run better than Oracle's RDA (haven't had a successful run of that yet!)? http://metalink.oracle.com/metalink/plsql/showdoc?db=NOTid=189489.1blackfr ame=0 And, Jared, you'll be happy that it appears to be written in Perl. :) Rich Jesse System/Database Administrator [EMAIL PROTECTED] Quad/Tech International, Sussex, WI USA -- 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: Molina, Gerardo 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! - Official partner of 2002 FIFA World Cup http://fifaworldcup.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: Oracle DBA with 8i through 11i Experience Needed in
I'm sure Winston Churchill or someone equally clever had something relevant to say on the topic. At the moment, the only quote I can remember is Churchill's democracy is the absolute worst form of government, except of course for all the others... Ferenc Mantfeld : Churchill, amongst all his other famous quotes once said : Two things in the world are infinite, man's greed and man's stupidity, but I am unsure of the former. and Aussie is a fantastic place to live, if you can block out the mental picture of being financially raped by the government from month to month. I can speak from experience having lived for 6.5 years in USA and now emigrated to Aussie almost 2 years ago. But let us not start a separate thread on this, because everyone has patriotic views which nobody else will convince them otherwise of. I also wish anyone looking for a knowledgeable Ora-Apps DBA at US$70K lots of luck finding that person. The old goes, when you pay peanuts, . -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Ferenc Mantfeld 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).
SQL Question
Hello All, It looks as if I've hit a brick wall and I'd very much appreciate if you can help. I've got a query that has to get some names and tie them to members. Name table is the main table with and member table is child table. desc Names FIRST_INIT NOT NULL CHAR(4) SECOND_INIT NOT NULL CHAR(1) INIT_SEQUENCE NOT NULL NUMBER LAST_NAMEVARCHAR2(30) FIRST_NAME VARCHAR2(20) FLAGNOT NULL NUMBER desc MEMBER MEM_FIRST_INIT CHAR(4) MEM_SECOND_INIT CHAR(1) MEM_INIT_SEQUENCENUMBER Member table references Names table on FIRST_INIT, SECOND_INIT, INIT_SEQUENCE. __ Do You Yahoo!? Yahoo! - Official partner of 2002 FIFA World Cup http://fifaworldcup.yahoo.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Viktor 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).
SQL Question (DISREGARD 1ST MESSAGE, SORRY)
Hello All, It looks as if I've hit a brick wall and I'd very much appreciate if you can help. desc Names FIRST_INIT NOT NULL CHAR(4) SECOND_INIT NOT NULL CHAR(1) INIT_SEQUENCE NOT NULL NUMBER LAST_NAMEVARCHAR2(30) FIRST_NAME VARCHAR2(20) FLAGNOT NULL NUMBER desc MEMBER MEM_FIRST_INIT CHAR(4) MEM_SECOND_INIT CHAR(1) MEM_INIT_SEQUENCENUMBER Member table references Names table on FIRST_INIT, SECOND_INIT, INIT_SEQUENCE (FOREIGN KEYS). Names table has NOT NULL column flag. It can only be 0 or 1 -- means Name is a member. But, not all NAME records with FLAG 1 are in MEMBER. In other words, records in MEMBER usually represent other some other types of memership. But, in this case, I need to get those NAME(parent) records that have FLAG = 1, and those MEMBER(child) records that reference NAMES via foreign keys. Sometimes NAME record with FLAG = 1, also has a MEMBER record, and it could be that record with FLAG = 1 does not have a record in MEMBER. I need all those with FLAG = 1 in NAMES + all records that are in MEMBER. Is there another way besides the UNION: SELECT a.first_init||a.second_init||a.init_sequence INITIALS, a.last_name LAST_NAME, a.first_name FIRST_NAME, a.flag MEMBER_FLAG, FROM names a WHERE a.advisor_flag = 1 UNION SELECT a.first_init||a.second_init||a.init_sequence INITIALS, a.last_name LAST_NAME, a.first_name FIRST_NAME, a.flag MEMBER_FLAG FROM names a, members m WHERE a.first_init = m.mem_first_init and a.second_init = m.mem_second_init and a.init_sequence = m.mem_init_sequence Thanks a lot in advance! Regards, Viktor __ Do You Yahoo!? Yahoo! - Official partner of 2002 FIFA World Cup http://fifaworldcup.yahoo.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Viktor 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: Correct way to determine freelists and freelists groups
I try to use table_access_path.sql but got stoped at @save_sqlplus_setting On the page where you downloaded the script, click on and read Prerequisites. -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Greg Moore 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: Remove Duplicates
If your Duplicates are large in number then the following method works fine.. 1. create table TMP as select distinct ( set of columns ) from ORIGINAL_TABLE 2. truncate table ORIGINAL_TABLE 3. insert into ORIGINAL_TABLE select * from TMP/use SQL Loader after taking a dump from TMP. Bhulu --- David Wagoner [EMAIL PROTECTED] wrote: Here is an interesting script I found on Metalink (Note:1019920.6) for removing duplicates, but I have not tried it yet: == Title: == Script to Eliminate Non-unique Rows === Disclaimer: === This script is provided for educational purposes only. It is NOT supported by Oracle World Wide Technical Support. The script has been tested and appears to work as intended. However, you should always test any script before relying on it. PROOFREAD THIS SCRIPT PRIOR TO USING IT! Due to differences in the way text editors, email packages and operating systems handle text formatting (spaces, tabs and carriage returns), this script may not be in an executable state when you first receive it. Check over the script to ensure that errors of this type are corrected. = Abstract: = This script removes all but one row (all but the row with the highest rowid) from owner.table in each group of rows having identical values in column(s). Multiple columns must be separated with commas (without spaces). Script TFSUNIQU is intended primarily for use in deleting rows that prevent the creation of a unique index on the columns in column(s). It will happily delete rows that are not identical, as long as the rows are identical with respect to the values of the columns in column(s). = Requirements: = You must have DELETE privileges on the selected table. === Script: === --- cut -- cut -- cut -- SET ECHO off REM NAME: TFSUNIQU.SQL REM USAGE:@path/tfsuniqu schema_name table_name column_name(s) REM REM REQUIREMENTS: REM DELETE on selected table REM REM AUTHOR: REMGrant Franjione, Phil Joel, and Cary Millsap REM(c)1994 Oracle Corporation REM REM PURPOSE: REMRemoves all but one row (all but the row with the highest rowid) REMfrom owner.table in each group of rows having identical values REMin colum(s). Multiple columns must be seperated with commas REM(without spaces). REM REMTFSUNIQU is intended primarily for use in deleting rows that REMprevent the creation of a unique index on the columns in REMcolumn(s). It will happily delete rows that are not identical, REMas long as the rows are identical with respect to the values of REMthe columns in column(s). REM REM EXAMPLE: REM N/A REM REM DISCLAIMER: REMThis script is provided for educational purposes only. It is NOT REMsupported by Oracle World Wide Technical Support. REMThe script has been tested and appears to work as intended. REMYou should always run new scripts on a test instance initially. REM REM Main text of script follows: def owner = 1 def table = 2 def uukey = 3 delete from owner..table where rowid in ( select rowid from owner..table minus select min(rowid) from owner..table group by uukey ) / undef owner undef table undef uukey --- cut -- cut -- cut -- David B. Wagoner Database Administrator Arsenal Digital Solutions Worldwide, Inc. 8000 Regency Parkway, Suite 110 Cary, NC 27511-8582 Office (919) 466-6723 Pager [EMAIL PROTECTED] Fax (919) 466-6783 http://www.arsenaldigital.com/ http://www.arsenaldigital.com/ *** NOTICE *** This e-mail message is confidential, intended only for the named recipient(s) above and may contain information that is privileged, 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 the sender by phone or email and delete this e-mail message from your computer. Thank you. -Original Message- Sent: Tuesday, June 04, 2002 2:54 PM To: Multiple recipients of list ORACLE-L I know I have seen this posted before... We have a large range partitioned table that
Re: SQL Question
Are you looking for something trivial like: select n.first_name, 'E.' middle_initial,n.last_name,m.mem_init_sequence from names n, member m where n.first_init=m.mem_first_init and n.second_init=m.mem_second_init order by 1 desc, 2 asc; On 2002.06.08 01:33 Viktor wrote: Hello All, It looks as if I've hit a brick wall and I'd very much appreciate if you can help. I've got a query that has to get some names and tie them to members. Name table is the main table with and member table is child table. desc Names FIRST_INIT NOT NULL CHAR(4) SECOND_INIT NOT NULL CHAR(1) INIT_SEQUENCE NOT NULL NUMBER LAST_NAMEVARCHAR2(30) FIRST_NAME VARCHAR2(20) FLAGNOT NULL NUMBER desc MEMBER MEM_FIRST_INIT CHAR(4) MEM_SECOND_INIT CHAR(1) MEM_INIT_SEQUENCENUMBER Member table references Names table on FIRST_INIT, SECOND_INIT, INIT_SEQUENCE. __ Do You Yahoo!? Yahoo! - Official partner of 2002 FIFA World Cup http://fifaworldcup.yahoo.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Viktor 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).
