Re: admin_restrictions_listener
You have to be logged on to the machine in order to START the TNS Listener, in any case. But to STOP, setting ADMIN_RESTRICTIONS_listener-name doesn't matter; that parameter only prevents runtime parameter changes (forcing parameter changes through editing of listener.ora and STOP/START or RELOAD). Only passwording the Listener prevents unauthorized STOP or SERVICES commands... - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Thursday, October 17, 2002 8:08 PM I understood that admin_restrictions_listener=on meant that you had to be physically logged on to the machine containing the listener.ora file to be able to stop/start/change. Is this not correct? If correct, then 600 permissions would mean that you also had to be logged on as the oracle owner: and if you are the oracle owner logged on to the same machine as the listener then you'd not need to use lsnrctl to create havoc. What am i missing here? -Original Message- Sent: Friday, 18 October 2002 4:11 am To: Multiple recipients of list ORACLE-L Not true. I'm talking about accessing the TNS Listener process from a lsnrctl executable on another machine entirely (i.e. my laptop, for example). Changing the file permissions on the tnslsnr executable on the server won't prevent commands (like STOP) received over the network... Passwording the TNS Listener is the only protection for that... - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Thursday, October 17, 2002 12:29 PM Although if you set the lsnrctl to 700 that problem goes away (that's what we did). I'm still amazed that it's world executable. Jay Miller -Original Message- Sent: Thursday, October 17, 2002 10:35 AM To: Multiple recipients of list ORACLE-L yup. i can run lsnrctl from my laptop somewhere on your network and stop the listener otherwise... - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Thursday, October 17, 2002 3:38 AM hi all I have my listener.ora owned by the oracle owner with 600 permissions and admin_restrictions_listener set. On a machine in the DMZ. Is there any point in having a password as well? thanks trevor Disclaimer. This e-mail is private and confidential. If you are not the intended recipient, please advise us by return e-mail immediately, and delete the e-mail and any attachments without using or disclosing the contents in any way. The views expressed in this e-mail are those of the author, and do not represent those of this company unless this is clearly indicated. You should scan this e-mail and any attachments for viruses. This company accepts no liability for any direct or indirect damage or loss resulting from the use of any attachments to this e-mail. -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Tim Gorman INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Miller, Jay INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please
Re: how to release blocks of table?
Thanks Mladen and Lee I am always amazed by the ability and knowledge of the people on this list. You guys (all the list members) always come up with many options to do something so if some option does not work you have others. Yechiel Adar Mehish - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Thursday, October 17, 2002 9:13 PM -Original Message- The way to decrease the initial size is 'alter table xxx deallocate unused keep nnn'. I found it in the doc after sending the previous msg. Another possibility: If the storage specifications for the tables in the database are acceptable, and you are the one doing the export, you can specify compress=n when you do the export. Then the initial extent will be as specified when the original table was created. Some shops create large tables with a small initial extent for this reason: When they only want the schema structure in someplace else, they can get it without the big initial extents. You can also use emacs to directly modify the export file to change the storage parameters therein. Something similar to the following: Create the file fix-extents.el with the following: (beginning-of-buffer) (while (re-search-forward INITIAL[ 0-9]*NEXT[ 0-9]*MINEXTENTS nil t) (replace-match INITIAL 1M NEXT 1M MINEXTENTS nil nil)) (save-buffer) Run the following to fix the dump file test.dmp: emacs -batch test.dmp -l fix-extents.el -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Stephen Lee INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Yechiel Adar INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: SNP acronym?
Yup The original oracle parameters were called snapshot_interval snapshot_processes or something like that. In v9, you get a CJQ (Job Queue Coordinator - obviously some dyslexic at Oracle), and Jnnn's hth connor --- Johnston, Tim [EMAIL PROTECTED] wrote: It's the oracle job queue processes... The following is an educated guess... If I'm wrong I'm sure someone will correct me... :-) I think SNP originally stood for snapshot job... When Oracle introduced snapshots, they needed a mechanism to schedule and run snapshot refreshes... Hence the SNP background processes... Then someone at Oracle decided they could externalize this mechanism and DBMS_JOB was born... Can anyone confirm (or crush) the theory? Tim -Original Message- Sent: Thursday, October 17, 2002 5:29 PM To: Multiple recipients of list ORACLE-L What does the background job queue acronym SNP stand for ? = Connor McDonald http://www.oracledba.co.uk http://www.oaktable.net Remember amateurs built the ark - Professionals built the Titanic __ Do You Yahoo!? Everything you'll ever need on one web page from News and Sport to Email and Music Charts http://uk.my.yahoo.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: =?iso-8859-1?q?Connor=20McDonald?= INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Template for Space Estimates
- Forwarded by Rajesh Rao/CHASE on 10/17/02 05:15 PM - Rajesh Rao To: [EMAIL PROTECTED] October 17, cc: 2002 02:59 PMSubject: Template for Space Estimates I need to estimate disk space sizing requirements for a new database server. Does anyone have a document that I can send forth to the developers, asking for inputs from them, about the objects, the growth patterns, et all? Or any links? Thanks Raj -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Template for Space Estimates
I need to estimate disk space sizing requirements for a new database server. Does anyone have a document that I can send forth to the developers, asking for inputs from them, about the objects, the growth patterns, et all? Or any links? Thanks Raj -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Re: OEM can't seem to discover 1 instance
The instance should be listed in the oratab file, the listener.ora file and the snmp_rw.ora file. It is automatically added to the snmp_ro.ora file at agent start up. Ensure that the Intelligent Agent is running. If any changes need to be made to any of the previously mentioned files, stop and restart the agent after making the change. Then try to discover the node. Reginald W. Bailey DBA, Consultant and MOKF John Weatherman [EMAIL PROTECTED]@fatcity.com on 10/17/2002 03:10:41 PM Please respond to [EMAIL PROTECTED] Sent by: [EMAIL PROTECTED] To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] cc: Anybody seen anything like this? I am running 9.2.0.1 OEM auto discovery. The Intelligent Agent is running on the target node. For some reason, it seems unable to discover 1 instance, the other 3 on that node are found. TNS seems ok, I can connect to the instance from other boxes without any difficulty. I have removed the snmp_ro.ora and recycled the IA, Management Server, etc. Still can't find this instance. It is finding 9i (r1 and r2) instances. This is an 8i instance (8.1.7.4) but I don't think that's it, as the development version on another node is found just fine. TIA, John P Weatherman Database Administrator Replacements Ltd. -Original Message- Sent: Thursday, October 17, 2002 2:47 PM To: Multiple recipients of list ORACLE-L Yes, there is. You might notice that someone has issued a create database command because all of your data will be gone fishing. When the noise level reaches 500 decibels, someone has issued CREATE DATABASE command on your production database, which, of course, no longer exists. System accounting should be able to tell you who has logged in as oracle and from which PC. Then you go and publicly execute that person by beating him to the pulp with a baseball bat (so called Soprano method) -Original Message- From: Boivin, Patrice J [mailto:BoivinP;mar.dfo-mpo.gc.ca] Sent: Thursday, October 17, 2002 12:42 PM To: Multiple recipients of list ORACLE-L Subject: auditing CREATE DATABASE Is there a way of capturing info on people who issue CREATE DATABASE statements? My Oracle Support technician says auditing won't catch this, only catches successful attempts. Regards, Patrice Boivin Systems Analyst (Oracle Certified DBA) Systems Admin Operations | Admin. et Exploit. des systèmes Technology Services| Services technologiques Informatics Branch | Direction de l'informatique Maritimes Region, DFO | Région des Maritimes, MPO E-Mail: [EMAIL PROTECTED] -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Boivin, Patrice J INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Gogala, Mladen INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: John Weatherman INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT
Archive files and their Management
Title: Archive files and their Management Greetings Gurus Just wondering... Our archive log's directory has grown substantially and space is becoming an issue. How do you know which archive files is safe to delete? In other words... Do you delete all archive files older than the last backup? Should you keep all archive files until it is obviously pointless? Please advise. Many thanks Denham Eva Oracle DBA UNIX is basically a simple operating system, but you have to be a genius to understand the simplicity. Dennis Ritchie. DISCLAIMER This message is for the named person's use only. It may contain confidential, proprietary or legally privileged information. No confidentiality or privilege is waived or lost by any mistransmission. If you receive this message in error, please immediately delete it and all copies of it from your system, destroy any hard copies of it and notify the sender. You must not, directly or indirectly, use, disclose, distribute, print, or copy any part of this message if you are not the intended recipient. TFMC, its holding company, and any of its subsidiaries each reserve the right to monitor and manage all e-mail communications through its networks. Any views expressed in this message are those of the individual sender, except where the message states otherwise and the sender is authorized to state them to be views of any such entity. This e-mail message has been scanned for Viruses and Content and cleared by MailMarshal - For more information please visit www.marshalsoftware.com
Dates
Hi, how do i round dates like this: Date = 01-01-1996 Return = 01-01-2000 or Date = 01-01-1992 Return = 01-01-1990 Thanks. __ Do you Yahoo!? Faith Hill - Exclusive Performances, Videos More http://faith.yahoo.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Imran Ashraf INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Re: Displaying Foreign characters
Hello Viktor I think that the problem is not with oracle. If you have the same nls_lang on the client and the database oracle will not do translation but will work on the principal of GIGO (garbage in, garbage out), meaning what you put in is what you get. You need a program on the client side that can display the special characters that you get in your data. We are working also with we8iso8859p1 and we store and retrieve Hebrew letters with no problems. Yechiel AdarMehish - Original Message - From: Viktor To: Multiple recipients of list ORACLE-L Sent: Thursday, October 17, 2002 10:10 PM Subject: Displaying Foreign characters Hello all, Is there a simple way to display correctlyforeign characters primarily found in name and address records? Currectly, NLS_LANGUAGE = AMERICAN in init.ora and NLS_LANG = AMERICAN_AMERICA.WE8ISO8859P1 in the registry on client side. Problem is is that name and address records may have characters like "a"with 2 dots on top, and many others, and when querying the db, obviously they're not displayed correctly. Is therean easy way to do this reql quick? Thanks much in advance! Do you Yahoo!?Faith Hill - Exclusive Performances, Videos, morefaith.yahoo.com
Re: Dates
hi imran! don't exactly understand what you want to do, but rounding e.g. the year works like that select round(to_char(sysdate, ''),-2) from dual; i dont understand what you wanna do with days and months, but if its always 01-01 then select '01-01-'||round(to_char(sysdate, ''),-2) from dual; will do. regards daniel Imran Ashraf wrote: Hi, how do i round dates like this: Date = 01-01-1996 Return = 01-01-2000 or Date = 01-01-1992 Return = 01-01-1990 Thanks. __ Do you Yahoo!? Faith Hill - Exclusive Performances, Videos More http://faith.yahoo.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Imran Ashraf INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Daniel Wisser, Mag. Papyrus Quality Assurance DB Team ISIS Information Systems Alter Wienerweg 12 A-2344 Ma. Enzersdorf, Austria Phone: +43-2236-27551-149 Fax: +43-2236-21081 E-mail: [EMAIL PROTECTED] Hotline: +43-2236-27551-111 Visit the ISIS Website: http://www.isis-papyrus.com --- This e-mail is only intended for the recipient and not legally binding. Unauthorised use, publication, reproduction or disclosure of the content of this e-mail is not permitted. --- -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Daniel Wisser INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Re: Archive files and their Management
Title: Archive files and their Management Our policy is to keep archive for the last 2 days at least. We are doing daily backup so it gives us the option to restore from the last backup or the one before without restoring archive logs. Yechiel AdarMehish - Original Message - From: Denham Eva To: Multiple recipients of list ORACLE-L Sent: Friday, October 18, 2002 12:28 PM Subject: Archive files and their Management Greetings Gurus Just wondering... Our archive log's directory has grown substantially and space is becoming an issue. How do you know which archive files is safe to delete? In other words... Do you delete all archive files older than the last backup? Should you keep all archive files until it is obviously pointless? Please advise. Many thanks Denham Eva Oracle DBA "UNIX is basically a simple operating system, but you have to be a genius to understand the simplicity." Dennis Ritchie. DISCLAIMER This message is for the named person's use only. It may contain confidential, proprietary or legally privileged information. No confidentiality or privilege is waived or lost by any mistransmission. If you receive this message in error, please immediately delete it and all copies of it from your system, destroy any hard copies of it and notify the sender. You must not, directly or indirectly, use, disclose, distribute, print, or copy any part of this message if you are not the intended recipient. TFMC, its holding company, and any of its subsidiaries each reserve the right to monitor and manage all e-mail communications through its networks. Any views expressed in this message are those of the individual sender, except where the message states otherwise and the sender is authorized to state them to be views of any such entity. This e-mail message has been scanned for Viruses and Content and cleared by MailMarshal - For more information please visit www.marshalsoftware.com
RE: Dates
Hi,
how do i round dates like this:
Date = 01-01-1996
Return = 01-01-2000
or
Date = 01-01-1992
Return = 01-01-1990
Thanks.
create or replace function decade_round(p_date in date)
return date
is
d_result date;
begin
d_result := to_date('01/01'||ltrim(round(to_number(to_char(p_date,
'')),-1)), 'DD/MM/');
return d_result;
end;
/
Regards,
Stephane Faroult
Oriole
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: Stephane Faroul
INET: [EMAIL PROTECTED]
Fat City Network Services-- 858-538-5051 http://www.fatcity.com
San Diego, California-- Mailing list and web hosting services
-
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from). You may
also send the HELP command for other information (like subscribing).
Re: Dates
Im running this query: select round(add_months(sysdate,500),'') from dual; This retuns: 01-JAN-2044. I want it to return: 01-JAN-2040. Thanks --- Daniel Wisser [EMAIL PROTECTED] wrote: hi imran! don't exactly understand what you want to do, but rounding e.g. the year works like that select round(to_char(sysdate, ''),-2) from dual; i dont understand what you wanna do with days and months, but if its always 01-01 then select '01-01-'||round(to_char(sysdate, ''),-2) from dual; will do. regards daniel Imran Ashraf wrote: Hi, how do i round dates like this: Date = 01-01-1996 Return = 01-01-2000 or Date = 01-01-1992 Return = 01-01-1990 Thanks. __ Do you Yahoo!? Faith Hill - Exclusive Performances, Videos More http://faith.yahoo.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Imran Ashraf INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Daniel Wisser, Mag. Papyrus Quality Assurance DB Team ISIS Information Systems Alter Wienerweg 12 A-2344 Ma. Enzersdorf, Austria Phone: +43-2236-27551-149 Fax: +43-2236-21081 E-mail: [EMAIL PROTECTED] Hotline: +43-2236-27551-111 Visit the ISIS Website: http://www.isis-papyrus.com --- This e-mail is only intended for the recipient and not legally binding. Unauthorised use, publication, reproduction or disclosure of the content of this e-mail is not permitted. --- -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Daniel Wisser INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). __ Do you Yahoo!? Faith Hill - Exclusive Performances, Videos More http://faith.yahoo.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Imran Ashraf INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Re: Please help, comment required urgently
Hello George I think that you need to tune the first statement the do the most gets and the most i/o. The same statement also access the most rows. It is fired up about every 5 seconds and is very resource consuming. I am not a tuning expert but this is my 0.01$ worth. Yechiel Adar Mehish - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Friday, October 18, 2002 10:53 AM Hi guys, I need a second opinion on the following Statspack output, I got my suspicions but my manager and the client is not buying what I am say, Not knowing anything of the system architecture please look at the output and say what would concern you. What assumptions/recommendations you would make. Thx George George Leonard Oracle Database Administrator Dimension Data (Pty) Ltd (Reg. No. 1987/006597/07) Tel: (+27 11) 575 0573 Fax: (+27 11) 576 0573 E-mail:[EMAIL PROTECTED] Web: http://www.didata.co.za You Have The Obligation to Inform One Honestly of the risk, And As a Person You Are Committed to Educate Yourself to the Total Risk In Any Activity! Once Informed Totally Aware of the Risk, Every Fool Has the Right to Kill or Injure Themselves as They See Fit! -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Yechiel Adar INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: Archive files and their Management
Title: Archive files and their Management theoretically only the archive files after the backup started are needed. but there might be issues of a corruptedbackup, and to guard against that we have a policy of keeping archive files for one week. you should also keep checking your backups by restoring them to a separate location periodically regards Naveen -Original Message-From: Yechiel Adar [mailto:[EMAIL PROTECTED]]Sent: Friday, October 18, 2002 4:49 PMTo: Multiple recipients of list ORACLE-LSubject: Re: Archive files and their Management Our policy is to keep archive for the last 2 days at least. We are doing daily backup so it gives us the option to restore from the last backup or the one before without restoring archive logs. Yechiel AdarMehish - Original Message - From: Denham Eva To: Multiple recipients of list ORACLE-L Sent: Friday, October 18, 2002 12:28 PM Subject: Archive files and their Management Greetings Gurus Just wondering... Our archive log's directory has grown substantially and space is becoming an issue. How do you know which archive files is safe to delete? In other words... Do you delete all archive files older than the last backup? Should you keep all archive files until it is obviously pointless? Please advise. Many thanks Denham Eva Oracle DBA "UNIX is basically a simple operating system, but you have to be a genius to understand the simplicity." Dennis Ritchie. DISCLAIMER This message is for the named person's use only. It may contain confidential, proprietary or legally privileged information. No confidentiality or privilege is waived or lost by any mistransmission. If you receive this message in error, please immediately delete it and all copies of it from your system, destroy any hard copies of it and notify the sender. You must not, directly or indirectly, use, disclose, distribute, print, or copy any part of this message if you are not the intended recipient. TFMC, its holding company, and any of its subsidiaries each reserve the right to monitor and manage all e-mail communications through its networks. Any views expressed in this message are those of the individual sender, except where the message states otherwise and the sender is authorized to state them to be views of any such entity. This e-mail message has been scanned for Viruses and Content and cleared by MailMarshal - For more information please visit www.marshalsoftware.com
RE: how to release blocks of table?
Hello list
I use this script to do reorganize - redimension.
It show the use/free space of each table and index. You can change it and then
move/rebuild.
You must change USER and TABLESPACE.
Do not do it ONLINE, at least with big tables. (I do it online and get ORA-0600s).
Connect as the table´s owner.
++
set serveroutput on size 100
set feedback off
set echo off
set linesize 3
set trimspool on
spool c:\move_tables.sql
prompt ** Cambiaste USER y TABLESPACE destino??? (pulsa ENTER o CRTL+C):
prompt ** Conectate como el propietario de las tablas !!!
pause
DECLARE
var1number;
var2number;
var3number;
var4number;
var5number;
var6number;
var7number;
BEGIN
dbms_output.put_line('set feedback on');
dbms_output.put_line('set echo on');
dbms_output.put_line('spool c:\move_tables.log');
dbms_output.put_line('alter session set SORT_AREA_SIZE=2500;');
dbms_output.put_line('select to_char(sysdate, ''MM/DD/ HH24:MI:SS'') from dual;');
FOR TB in (select owner, table_name, pct_free, pct_used, ini_trans, max_trans,
initial_extent, decode(next_extent,null,initial_extent,next_extent) nexte,
min_extents, max_extents, pct_increase, freelists, freelist_groups, decode(logging,
'YES', ' logging ', ' nologging ') logg
from dba_tables where owner = 'EPPESETA' order by TABLE_NAME)
LOOP
dbms_output.put_line(chr(0));
dbms_output.put_line('alter TABLE '|| TB.owner ||'.'|| TB.table_name || ' move
tablespace USERS ' || TB.logg
|| chr(10) || ' pctfree ' || TB.pct_free || ' pctused ' ||
TB.pct_used || ' initrans ' || TB.ini_trans || ' maxtrans '|| TB.max_trans
|| chr(10) || ' storage ( initial ' || TB.initial_extent || '
next ' || TB.nexte ||
' minextents ' || TB.min_extents || ' maxextents UNLIMITED '
||
' pctincrease 0 freelists ' || TB.freelists || ' freelist
groups ' || TB.freelist_groups ||');');
-- Espacio ocupado por la tabla
dbms_space.unused_space(''||TB.owner||'',''||TB.table_name||'','TABLE',VAR1,VAR2,VAR3,VAR4,VAR5,VAR6,VAR7);
dbms_output.put_line('-- %Libre:'|| round((VAR4*100)/VAR2) ||' Total_KB:'||
VAR2/1024 || ' Usado_KB: ' || ((VAR2/1024)-(VAR4/1024)) ||' Libre_KB:'||VAR4/1024
||' -- Blk_Total:'|| VAR1 || ' Blk_Libres:'||VAR3);
-- Indices de esa tabla
FOR INDX in (select owner, index_name, pct_free, ini_trans, max_trans,
initial_extent, decode(next_extent,null,initial_extent,next_extent) nexte,
min_extents, max_extents, pct_increase, freelists, freelist_groups, decode(logging,
'YES', ' logging ', ' nologging ') logg
from dba_indexes where table_owner=TB.owner and
table_name=TB.table_name order by index_name)
LOOP
dbms_output.put_line(' alter INDEX ' || INDX.owner ||'.'|| INDX.index_name || '
rebuild online '|| INDX.logg
|| chr(10) || ' pctfree ' || INDX.pct_free || ' initrans '
|| INDX.ini_trans || ' maxtrans '|| INDX.max_trans
|| chr(10) || ' storage ( initial ' || INDX.initial_extent ||
' next ' || INDX.nexte ||
' minextents ' || INDX.min_extents || ' maxextents UNLIMITED '
||
' pctincrease 0 freelists ' || INDX.freelists || ' freelist
groups ' || INDX.freelist_groups ||');');
-- Espacio ocupado por el índice
dbms_space.unused_space(''||INDX.owner||'',''||INDX.index_name||'','INDEX',VAR1,VAR2,VAR3,VAR4,VAR5,VAR6,VAR7);
dbms_output.put_line('-- %Libre:'|| round((VAR4*100)/VAR2) ||' Total_KB:'||
VAR2/1024 || ' Usado_KB: ' || ((VAR2/1024)-(VAR4/1024)) || ' Libre_KB:'||VAR4/1024
||' -- Blk_Total:'|| VAR1 || ' Blk_Libres:'||VAR3);
END LOOP;
END LOOP;
dbms_output.put_line('select to_char(sysdate, ''MM/DD/ HH24:MI:SS'') from dual;');
dbms_output.put_line('select * from dba_indexes where status''VALID'';');
dbms_output.put_line('spool off');
END;
/
spool off
spool c:\extensiones_mon_cache.log
column segment_name format a20
column owner format a10
prompt ** EXTENSIONES de TABLAS e INDICES. Cambia INITIAL de los siguientes
segmentos (pulsa ENTER):
pause
select owner, segment_name, segment_type, tablespace_name, sum(bytes)/1024 Kb,
count(*) from dba_extents where owner 'SYS' and segment_type='INDEX' group by
segment_name,owner,segment_type,tablespace_name having count(*)3 order by count(*);
prompt **
select owner, segment_name, segment_type, tablespace_name, sum(bytes)/1024 Kb,
count(*) from dba_extents where owner 'SYS' and segment_type='TABLE' group by
segment_name,owner,segment_type,tablespace_name having count(*)3 order by count(*);
prompt * MONITORING y CACHE (pulsa ENTER):
pause
Re: Archive files and their Management
I do an rman backup of the archivelogs each time I do a backup of the database. I use the delete option and delete the archivelogs because I can restore them from the backup. HTH, Ruth - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Friday, October 18, 2002 6:28 AM Greetings Gurus Just wondering... Our archive log's directory has grown substantially and space is becoming an issue. How do you know which archive files is safe to delete? In other words... Do you delete all archive files older than the last backup? Should you keep all archive files until it is obviously pointless? Please advise. Many thanks Denham Eva Oracle DBA UNIX is basically a simple operating system, but you have to be a genius to understand the simplicity. Dennis Ritchie. _ DISCLAIMER This message is for the named person's use only. It may contain confidential, proprietary or legally privileged information. No confidentiality or privilege is waived or lost by any mistransmission. If you receive this message in error, please immediately delete it and all copies of it from your system, destroy any hard copies of it and notify the sender. You must not, directly or indirectly, use, disclose, distribute, print, or copy any part of this message if you are not the intended recipient. TFMC, its holding company, and any of its subsidiaries each reserve the right to monitor and manage all e-mail communications through its networks. Any views expressed in this message are those of the individual sender, except where the message states otherwise and the sender is authorized to state them to be the views of any such entity. _ This e-mail message has been scanned for Viruses and Content and cleared by MailMarshal For more information please visit www.marshalsoftware.com _ -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Ruth Gramolini INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: OEM can't seem to discover 1 instance
this is why I gave up on OEM. it sometimes discovers things and sometimes not. I just use Dba Studio now. -Original Message- [mailto:Reginald.W.Bailey;jpmorgan.com] Sent: Friday, October 18, 2002 5:14 AM To: Multiple recipients of list ORACLE-L The instance should be listed in the oratab file, the listener.ora file and the snmp_rw.ora file. It is automatically added to the snmp_ro.ora file at agent start up. Ensure that the Intelligent Agent is running. If any changes need to be made to any of the previously mentioned files, stop and restart the agent after making the change. Then try to discover the node. Reginald W. Bailey DBA, Consultant and MOKF John Weatherman [EMAIL PROTECTED]@fatcity.com on 10/17/2002 03:10:41 PM Please respond to [EMAIL PROTECTED] Sent by: [EMAIL PROTECTED] To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] cc: Anybody seen anything like this? I am running 9.2.0.1 OEM auto discovery. The Intelligent Agent is running on the target node. For some reason, it seems unable to discover 1 instance, the other 3 on that node are found. TNS seems ok, I can connect to the instance from other boxes without any difficulty. I have removed the snmp_ro.ora and recycled the IA, Management Server, etc. Still can't find this instance. It is finding 9i (r1 and r2) instances. This is an 8i instance (8.1.7.4) but I don't think that's it, as the development version on another node is found just fine. TIA, John P Weatherman Database Administrator Replacements Ltd. -Original Message- Sent: Thursday, October 17, 2002 2:47 PM To: Multiple recipients of list ORACLE-L Yes, there is. You might notice that someone has issued a create database command because all of your data will be gone fishing. When the noise level reaches 500 decibels, someone has issued CREATE DATABASE command on your production database, which, of course, no longer exists. System accounting should be able to tell you who has logged in as oracle and from which PC. Then you go and publicly execute that person by beating him to the pulp with a baseball bat (so called Soprano method) -Original Message- From: Boivin, Patrice J [mailto:BoivinP;mar.dfo-mpo.gc.ca] Sent: Thursday, October 17, 2002 12:42 PM To: Multiple recipients of list ORACLE-L Subject: auditing CREATE DATABASE Is there a way of capturing info on people who issue CREATE DATABASE statements? My Oracle Support technician says auditing won't catch this, only catches successful attempts. Regards, Patrice Boivin Systems Analyst (Oracle Certified DBA) Systems Admin Operations | Admin. et Exploit. des systèmes Technology Services| Services technologiques Informatics Branch | Direction de l'informatique Maritimes Region, DFO | Région des Maritimes, MPO E-Mail: [EMAIL PROTECTED] -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Boivin, Patrice J INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Gogala, Mladen INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: John Weatherman INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051
RE: OEM can't seem to discover 1 instance
Hmm, thanks to all who are replying. It looks like the instance is not making it into either snmp_rw.ora, snmp_ro.ora or the services.ora. I have manually edited them, but when I restart the agent, it wipes out the manual entries in the snmp_ro.ora and the services.ora. The oratab and listener look fine. Any more good advice? TIA, John P Weatherman Database Administrator Replacements Ltd. -Original Message- [mailto:Reginald.W.Bailey;jpmorgan.com] Sent: Friday, October 18, 2002 5:14 AM To: Multiple recipients of list ORACLE-L The instance should be listed in the oratab file, the listener.ora file and the snmp_rw.ora file. It is automatically added to the snmp_ro.ora file at agent start up. Ensure that the Intelligent Agent is running. If any changes need to be made to any of the previously mentioned files, stop and restart the agent after making the change. Then try to discover the node. Reginald W. Bailey DBA, Consultant and MOKF John Weatherman [EMAIL PROTECTED]@fatcity.com on 10/17/2002 03:10:41 PM Please respond to [EMAIL PROTECTED] Sent by: [EMAIL PROTECTED] To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] cc: Anybody seen anything like this? I am running 9.2.0.1 OEM auto discovery. The Intelligent Agent is running on the target node. For some reason, it seems unable to discover 1 instance, the other 3 on that node are found. TNS seems ok, I can connect to the instance from other boxes without any difficulty. I have removed the snmp_ro.ora and recycled the IA, Management Server, etc. Still can't find this instance. It is finding 9i (r1 and r2) instances. This is an 8i instance (8.1.7.4) but I don't think that's it, as the development version on another node is found just fine. TIA, John P Weatherman Database Administrator Replacements Ltd. -Original Message- Sent: Thursday, October 17, 2002 2:47 PM To: Multiple recipients of list ORACLE-L Yes, there is. You might notice that someone has issued a create database command because all of your data will be gone fishing. When the noise level reaches 500 decibels, someone has issued CREATE DATABASE command on your production database, which, of course, no longer exists. System accounting should be able to tell you who has logged in as oracle and from which PC. Then you go and publicly execute that person by beating him to the pulp with a baseball bat (so called Soprano method) -Original Message- From: Boivin, Patrice J [mailto:BoivinP;mar.dfo-mpo.gc.ca] Sent: Thursday, October 17, 2002 12:42 PM To: Multiple recipients of list ORACLE-L Subject: auditing CREATE DATABASE Is there a way of capturing info on people who issue CREATE DATABASE statements? My Oracle Support technician says auditing won't catch this, only catches successful attempts. Regards, Patrice Boivin Systems Analyst (Oracle Certified DBA) Systems Admin Operations | Admin. et Exploit. des systèmes Technology Services| Services technologiques Informatics Branch | Direction de l'informatique Maritimes Region, DFO | Région des Maritimes, MPO E-Mail: [EMAIL PROTECTED] -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Boivin, Patrice J INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Gogala, Mladen INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: John Weatherman INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or
Re: Dates
hi!
then i think this is what you need:
select to_date('01-01-'||round(to_char(sysdate, ''), -1),
'DD-MM-') from dual;
reg.
daniel
Imran Ashraf wrote:
Im running this query:
select round(add_months(sysdate,500),'')
from dual;
This retuns:
01-JAN-2044.
I want it to return:
01-JAN-2040.
Thanks
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: Daniel Wisser
INET: [EMAIL PROTECTED]
Fat City Network Services-- 858-538-5051 http://www.fatcity.com
San Diego, California-- Mailing list and web hosting services
-
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from). You may
also send the HELP command for other information (like subscribing).
Specify Rollback for an Export
I am performing an export of a set of very large tables. I am not using CONSISTENT mode. The exports are failing due to snapshot too old errors. The application uses many small rollback segments, but has one large one for big transactions. Is it possible to force the export to use the large segment without taking all the others (except System) offline? I have seen this question asked on the list before, but did not find a definitive follow-up answer. Thanks Erik -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Erik Williams INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Re: Please help, comment required urgently
George, Two things jump out together: The SQL statement with hash value = 3509998681 is consuming about 25% of the total response-time (i.e. total processing plus total wait) on the system. This SQL statement is executing 900 times during the one-hour sample period... Waits on the "cache buffers chains" are consuming another 16% of total response-time With these two things consuming 41% of everything consumed by the database instance during this time period, there is no chance that anything else is more important... Chances are excellent that these two things are related. Since the SQL statement has over 329m buffer gets and about 0.5m buffer cache-misses (i.e. physical reads) to it's credit, this indicates a buffer-cache hit-ratio of over 99.7%, which is sure proof that something is seriously wrong! :-) My guess is that the query is using an inappropriate and/or inefficientindex for a long, long, long range-scan operation, which is racking up all of those buffer gets. What do you expect from the rule-based optimizer? If you were running CBO and this happened, I'd suggest gathering column-level "histogram" statistics on the table. My guess also is that many concurrent users are running this statement during the course of the sample period, causing the latch contention for cache buffers in the Buffer Cache, thus the relationship between the two symptoms? I can see that DB_CACHE_LRU_LATCHES has been pushed up to 48; don't know what CPU_COUNT is, but obviously this change has had zero impact on the latch contention problem. Tuning the SQL will fix the problem; accomodating the problem by configuring more latches has no impact. Tuning that one SQL statement (plus a few of it's look-alikes, also listed in the report) will resolve the major performance issues you are experiencing. In fact, it will have a miraculous impact... Hope this helps... -Tim - Original Message - From: "Leonard, George" [EMAIL PROTECTED] To: "Multiple recipients of list ORACLE-L" [EMAIL PROTECTED] Sent: Friday, October 18, 2002 2:53 AM Subject: Please help, comment required urgently Hi guys, I need a second opinion on the following Statspack output, I got my suspicions but my manager and the client is not buying what I am say, Not knowing anything of the system architecture please look at the output and say what would concern you. What assumptions/recommendations you would make. Thx George George Leonard Oracle Database Administrator Dimension Data (Pty) Ltd (Reg. No. 1987/006597/07) Tel: (+27 11) 575 0573 Fax: (+27 11) 576 0573 E-mail:[EMAIL PROTECTED] Web: http://www.didata.co.za You Have The Obligation to Inform One Honestly of the risk, And As a Person You Are Committed to Educate Yourself to the Total Risk In Any Activity! Once Informed Totally Aware of the Risk, Every Fool Has the Right to Kill or Injure Themselves as They See Fit!
RE: Please help, comment required urgently
Thanks for your contributions on this Tim (and on everything else you respond to). These little tips of how to analyze statspack reports properly all add up and whilst I did look at the report and I did glean some of you what suggested I certainly did not pick up all that you spotted John -Original Message- Sent: 18 October 2002 14:54 To: Multiple recipients of list ORACLE-L George, Two things jump out together: * The SQL statement with hash value = 3509998681 is consuming about 25% of the total response-time (i.e. total processing plus total wait) on the system. This SQL statement is executing 900 times during the one-hour sample period... * Waits on the cache buffers chains are consuming another 16% of total response-time With these two things consuming 41% of everything consumed by the database instance during this time period, there is no chance that anything else is more important... Chances are excellent that these two things are related. Since the SQL statement has over 329m buffer gets and about 0.5m buffer cache-misses (i.e. physical reads) to it's credit, this indicates a buffer-cache hit-ratio of over 99.7%, which is sure proof that something is seriously wrong! :-) My guess is that the query is using an inappropriate and/or inefficient index for a long, long, long range-scan operation, which is racking up all of those buffer gets. What do you expect from the rule-based optimizer? If you were running CBO and this happened, I'd suggest gathering column-level histogram statistics on the table. My guess also is that many concurrent users are running this statement during the course of the sample period, causing the latch contention for cache buffers in the Buffer Cache, thus the relationship between the two symptoms? I can see that DB_CACHE_LRU_LATCHES has been pushed up to 48; don't know what CPU_COUNT is, but obviously this change has had zero impact on the latch contention problem. Tuning the SQL will fix the problem; accomodating the problem by configuring more latches has no impact. Tuning that one SQL statement (plus a few of it's look-alikes, also listed in the report) will resolve the major performance issues you are experiencing. In fact, it will have a miraculous impact... Hope this helps... -Tim - Original Message - [EMAIL PROTECTED] To: Multiple recipients of list ORACLE-L mailto:ORACLE-L;fatcity.com [EMAIL PROTECTED] Sent: Friday, October 18, 2002 2:53 AM Hi guys, I need a second opinion on the following Statspack output, I got my suspicions but my manager and the client is not buying what I am say, Not knowing anything of the system architecture please look at the output and say what would concern you. What assumptions/recommendations you would make. Thx George George Leonard Oracle Database Administrator Dimension Data (Pty) Ltd (Reg. No. 1987/006597/07) Tel: (+27 11) 575 0573 Fax: (+27 11) 576 0573 E-mail:[EMAIL PROTECTED] Web:http://www.didata.co.za http://www.didata.co.za You Have The Obligation to Inform One Honestly of the risk, And As a Person You Are Committed to Educate Yourself to the Total Risk In Any Activity! Once Informed Totally Aware of the Risk, Every Fool Has the Right to Kill or Injure Themselves as They See Fit! -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: Urgent problem with query in CBO Vs RBO
Title: RE: Urgent problem with query in CBO Vs RBO
I don't think the reply I sent last night made it to the list.
We eventually traced it to the behavior of _unnest_subquery parameter. Setting it to false solved the problem. This was first reported in 817 and supposed to be fixed in 8173 and 901 code base. Looks like the lid on the jar of bugs is leaking ... and it made it into 920 code as well.
The biggest problem we found that the explain plan shows that it doesn't even look at a major portion of the sql statement when it fails. 10053 trace shows that the optimizer looking at all tables involved and checking for different paths, but the explain plan is not reporting all the tables involved.
Here is an example ...
-- this doesn't work -
select SUM(a.csed_dollars)
FROM client_supplied_ep_dollars a
WHERE a.csed_pob_id = 213841
AND a.csed_date = (SELECT MAX(b.csed_date)
FROM client_supplied_ep_dollars b
WHERE b.csed_ep_number = a.csed_ep_number
AND b.csed_pob_id = a.csed_pob_id)
AND EXISTS (SELECT 'x'
FROM v_log_master l,
episode_airings e,
units u
WHERE u.ut_ea_ep_number = a.csed_ep_number
AND u.ut_pob_id = a.csed_pob_id
AND u.ut_disposition_ind IS NULL
AND e.life_cycle_status 2
AND e.ep_number = u.ut_ea_ep_number
AND e.est_dt BETWEEN to_date('03/01/2002','mm/dd/') and
to_date('03/31/2002','mm/dd/')
-- AND e.est_dt BETWEEN m_start_date AND m_end_date
AND l.log_date = e.est_dt
AND l.log_network = NVL(e.act_log_network, e.log_network)
AND l.log_no = NVL(e.act_log_number,e.log_number)
AND (NVL(e.did_not_air_ind,'N') = 'N' OR e.life_cycle_status = 9
OR
(NVL(e.did_not_air_ind,'N') = 'Y' AND l.master_log_date IS NULL)))
SQL /
SUM(A.CSED_DOLLARS)
---
123
Execution Plan
--
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=13 Card=1 Bytes=41)
1 0 SORT (AGGREGATE)
2 1 VIEW (Cost=13 Card=1 Bytes=41)
3 2 FILTER
4 3 SORT (GROUP BY) (Cost=13 Card=1 Bytes=42)
5 4 HASH JOIN (Cost=11 Card=1 Bytes=42)
6 5 INDEX (RANGE SCAN) OF 'CSED_PRIM' (UNIQUE) (Cost=2 Card=12 Bytes=192)
7 5 TABLE ACCESS (BY INDEX ROWID) OF 'CLIENT_SUPPLIED_EP_DOLLARS' (Cost=8 Card=12 Bytes=312)
8 7 INDEX (RANGE SCAN) OF 'CSED_PRIM' (UNIQUE) (Cost=2 Card=1)
SQL spool off
end
And
--- this works --
select SUM(a.csed_dollars)
FROM client_supplied_ep_dollars a
WHERE a.csed_pob_id = 213841
AND a.csed_date = (SELECT MAX(b.csed_date)
FROM client_supplied_ep_dollars b
WHERE b.csed_ep_number = a.csed_ep_number
AND b.csed_pob_id = a.csed_pob_id
group by 1) -- this group by fixes the query ...
AND EXISTS (SELECT 'x'
FROM v_log_master l,
episode_airings e,
units u
WHERE u.ut_ea_ep_number = a.csed_ep_number
AND u.ut_pob_id = a.csed_pob_id
AND u.ut_disposition_ind IS NULL
AND e.life_cycle_status 2
AND e.ep_number = u.ut_ea_ep_number
AND e.est_dt BETWEEN to_date('03/01/2002','mm/dd/') and
to_date('03/31/2002','mm/dd/')
-- AND e.est_dt BETWEEN m_start_date AND m_end_date
AND l.log_date = e.est_dt
AND l.log_network = NVL(e.act_log_network, e.log_network)
AND l.log_no = NVL(e.act_log_number,e.log_number)
AND (NVL(e.did_not_air_ind,'N') = 'N' OR e.life_cycle_status = 9
OR
(NVL(e.did_not_air_ind,'N') = 'Y' AND l.master_log_date IS NULL)))
SQL /
SUM(A.CSED_DOLLARS)
---
15
Execution Plan
--
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=3 Card=1 Bytes=19)
1 0 SORT (AGGREGATE)
2 1 TABLE ACCESS (BY INDEX ROWID) OF 'CLIENT_SUPPLIED_EP_DOLLARS' (Cost=3 Card=1 Bytes=19)
3 2 INDEX (RANGE SCAN) OF 'CSED_PRIM' (UNIQUE) (Cost=2 Card=1)
4 3 SORT (GROUP BY NOSORT) (Cost=2 Card=1 Bytes=16)
5 4 FIRST ROW (Cost=2 Card=1 Bytes=16)
6 5 INDEX (RANGE SCAN (MIN/MAX)) OF 'CSED_PRIM' (UNIQUE) (Cost=2 Card=12488)
7 3 NESTED LOOPS (Cost=8 Card=1 Bytes=65)
8 7 NESTED LOOPS (Cost=5 Card=1 Bytes=55)
9 8 NESTED LOOPS (Cost=5 Card=1 Bytes=41)
10 9 NESTED LOOPS (Cost=4 Card=1 Bytes=35)
11 10 TABLE ACCESS (BY INDEX ROWID) OF 'EPISODE_AIRINGS' (Cost=3 Card=1 Bytes=21)
12 11 INDEX (UNIQUE SCAN) OF 'EPI_PK' (UNIQUE) (Cost=2 Card=997448)
13 10 TABLE ACCESS (BY INDEX ROWID) OF 'LOGS' (Cost=1 Card=1 Bytes=14)
14 13 INDEX (UNIQUE SCAN) OF 'LOG_PK_PRIM' (UNIQUE)
15 9 TABLE ACCESS (BY INDEX ROWID) OF 'INVOICE_NETWORK_XREF' (Cost=1 Card=1 Bytes=6)
16 15 INDEX (UNIQUE SCAN) OF 'INVOICE_NETWORK_XREF_PK' (UNIQUE)
17 8 INDEX (UNIQUE SCAN) OF 'SYS_C0018280' (UNIQUE)
18 7 TABLE ACCESS (BY INDEX ROWID) OF 'UNITS' (Cost=3 Card=1 Bytes=10)
19 18 INDEX (RANGE SCAN) OF 'UT_POB_FRGN' (NON-UNIQUE)(Cost=2 Card=1)
SQL spool off
end
Raj
__
Rajendra Jamadagni MIS, ESPN
RE: OEM can't seem to discover 1 instance
John, Try this On the client node: 1. Make sure all instances are in oratab listener.ora. 2. Stop the agent. 3. rm $ORACLE_HOME/network/agent/*.q 4. rm $ORACLE_HOME/network/agent/services.ora 5. rm $ORACLE_HOME/network/admin/snmp_r*.ora 6. start the agent 7. Check the pertinent log files in $ORACLE_HOME/network/log On the OEM console: 1. Delete the node you are trying to discover. 2. Discover the node If you're lucky the instances will show up??? ...JIM... Jim Howerton Senior Oracle DBA University of Alabama at Birmingham Health System Information Services e: [EMAIL PROTECTED] v: 205-934-9111 f: 205-934-0632 [EMAIL PROTECTED] 10/18/02 8:38:37 AM Hmm, thanks to all who are replying. It looks like the instance is not making it into either snmp_rw.ora, snmp_ro.ora or the services.ora. I have manually edited them, but when I restart the agent, it wipes out the manual entries in the snmp_ro.ora and the services.ora. The oratab and listener look fine. Any more good advice? TIA, John P Weatherman Database Administrator Replacements Ltd. -Original Message- [mailto:Reginald.W.Bailey;jpmorgan.com] Sent: Friday, October 18, 2002 5:14 AM To: Multiple recipients of list ORACLE-L The instance should be listed in the oratab file, the listener.ora file and the snmp_rw.ora file. It is automatically added to the snmp_ro.ora file at agent start up. Ensure that the Intelligent Agent is running. If any changes need to be made to any of the previously mentioned files, stop and restart the agent after making the change. Then try to discover the node. Reginald W. Bailey DBA, Consultant and MOKF John Weatherman [EMAIL PROTECTED]@fatcity.com on 10/17/2002 03:10:41 PM Please respond to [EMAIL PROTECTED] Sent by: [EMAIL PROTECTED] To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] cc: Anybody seen anything like this? I am running 9.2.0.1 OEM auto discovery. The Intelligent Agent is running on the target node. For some reason, it seems unable to discover 1 instance, the other 3 on that node are found. TNS seems ok, I can connect to the instance from other boxes without any difficulty. I have removed the snmp_ro.ora and recycled the IA, Management Server, etc. Still can't find this instance. It is finding 9i (r1 and r2) instances. This is an 8i instance (8.1.7.4) but I don't think that's it, as the development version on another node is found just fine. TIA, John P Weatherman Database Administrator Replacements Ltd. -Original Message- Sent: Thursday, October 17, 2002 2:47 PM To: Multiple recipients of list ORACLE-L Yes, there is. You might notice that someone has issued a create database command because all of your data will be gone fishing. When the noise level reaches 500 decibels, someone has issued CREATE DATABASE command on your production database, which, of course, no longer exists. System accounting should be able to tell you who has logged in as oracle and from which PC. Then you go and publicly execute that person by beating him to the pulp with a baseball bat (so called Soprano method) -Original Message- From: Boivin, Patrice J [mailto:BoivinP;mar.dfo-mpo.gc.ca] Sent: Thursday, October 17, 2002 12:42 PM To: Multiple recipients of list ORACLE-L Subject: auditing CREATE DATABASE Is there a way of capturing info on people who issue CREATE DATABASE statements? My Oracle Support technician says auditing won't catch this, only catches successful attempts. Regards, Patrice Boivin Systems Analyst (Oracle Certified DBA) Systems Admin Operations | Admin. et Exploit. des systèmes Technology Services| Services technologiques Informatics Branch | Direction de l'informatique Maritimes Region, DFO | Région des Maritimes, MP O E-Mail: [EMAIL PROTECTED] -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Boivin, Patrice J INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Gogala, Mladen INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line
RE: OEM can't seem to discover 1 instance
LOL, don't I hear THAT all the time! -Original Message- Thomas F Sent: 18 October 2002 14:24 To: Multiple recipients of list ORACLE-L this is why I gave up on OEM. it sometimes discovers things and sometimes not. I just use Dba Studio now. -Original Message- [mailto:Reginald.W.Bailey;jpmorgan.com] Sent: Friday, October 18, 2002 5:14 AM To: Multiple recipients of list ORACLE-L The instance should be listed in the oratab file, the listener.ora file and the snmp_rw.ora file. It is automatically added to the snmp_ro.ora file at agent start up. Ensure that the Intelligent Agent is running. If any changes need to be made to any of the previously mentioned files, stop and restart the agent after making the change. Then try to discover the node. Reginald W. Bailey DBA, Consultant and MOKF John Weatherman [EMAIL PROTECTED]@fatcity.com on 10/17/2002 03:10:41 PM Please respond to [EMAIL PROTECTED] Sent by: [EMAIL PROTECTED] To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] cc: Anybody seen anything like this? I am running 9.2.0.1 OEM auto discovery. The Intelligent Agent is running on the target node. For some reason, it seems unable to discover 1 instance, the other 3 on that node are found. TNS seems ok, I can connect to the instance from other boxes without any difficulty. I have removed the snmp_ro.ora and recycled the IA, Management Server, etc. Still can't find this instance. It is finding 9i (r1 and r2) instances. This is an 8i instance (8.1.7.4) but I don't think that's it, as the development version on another node is found just fine. TIA, John P Weatherman Database Administrator Replacements Ltd. -Original Message- Sent: Thursday, October 17, 2002 2:47 PM To: Multiple recipients of list ORACLE-L Yes, there is. You might notice that someone has issued a create database command because all of your data will be gone fishing. When the noise level reaches 500 decibels, someone has issued CREATE DATABASE command on your production database, which, of course, no longer exists. System accounting should be able to tell you who has logged in as oracle and from which PC. Then you go and publicly execute that person by beating him to the pulp with a baseball bat (so called Soprano method) -Original Message- From: Boivin, Patrice J [mailto:BoivinP;mar.dfo-mpo.gc.ca] Sent: Thursday, October 17, 2002 12:42 PM To: Multiple recipients of list ORACLE-L Subject: auditing CREATE DATABASE Is there a way of capturing info on people who issue CREATE DATABASE statements? My Oracle Support technician says auditing won't catch this, only catches successful attempts. Regards, Patrice Boivin Systems Analyst (Oracle Certified DBA) Systems Admin Operations | Admin. et Exploit. des systèmes Technology Services| Services technologiques Informatics Branch | Direction de l'informatique Maritimes Region, DFO | Région des Maritimes, MPO E-Mail: [EMAIL PROTECTED] -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Boivin, Patrice J INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Gogala, Mladen INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: John Weatherman INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please
RE: SNP acronym?
Title: SNP acronym? Saturday Night Paging -Original Message-From: Markham, Richard [mailto:[EMAIL PROTECTED]]Sent: Thursday, October 17, 2002 5:29 PMTo: Multiple recipients of list ORACLE-LSubject: SNP acronym? What does the background job queue acronym SNP stand for ?
RE: OEM can't seem to discover 1 instance
Is this UNIX or Windows? Where is your oratab file located and what is your ORACLE_HOME directory? The discovery process looks in your oratab (which can be located anywhere as long as the ORATAB environment variable is defined) or the Windows registry to locate all of the ORACLE_HOMEs that are in use and gets the SIDs from the oratab. I set a GLOBAL_DBNAME in my listener.ora to uniquely identify each db. If GLOBAL_DBNAME does not exist it uses the tnsnames.ora file to determine the services to manage. Check your tnsnames.ora file to make sure you have entries for the dbs. HTH Phil Wilson ([EMAIL PROTECTED]) DBA, Operations Group SkillSoft, Learning Solutions for the Human Enterprise 506.462.1124(w) 506.447.0334(c) -Original Message- Sent: Friday, October 18, 2002 10:39 AM To: Multiple recipients of list ORACLE-L Hmm, thanks to all who are replying. It looks like the instance is not making it into either snmp_rw.ora, snmp_ro.ora or the services.ora. I have manually edited them, but when I restart the agent, it wipes out the manual entries in the snmp_ro.ora and the services.ora. The oratab and listener look fine. Any more good advice? TIA, John P Weatherman Database Administrator Replacements Ltd. -Original Message- [mailto:Reginald.W.Bailey;jpmorgan.com] Sent: Friday, October 18, 2002 5:14 AM To: Multiple recipients of list ORACLE-L The instance should be listed in the oratab file, the listener.ora file and the snmp_rw.ora file. It is automatically added to the snmp_ro.ora file at agent start up. Ensure that the Intelligent Agent is running. If any changes need to be made to any of the previously mentioned files, stop and restart the agent after making the change. Then try to discover the node. Reginald W. Bailey DBA, Consultant and MOKF John Weatherman [EMAIL PROTECTED]@fatcity.com on 10/17/2002 03:10:41 PM Please respond to [EMAIL PROTECTED] Sent by: [EMAIL PROTECTED] To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] cc: Anybody seen anything like this? I am running 9.2.0.1 OEM auto discovery. The Intelligent Agent is running on the target node. For some reason, it seems unable to discover 1 instance, the other 3 on that node are found. TNS seems ok, I can connect to the instance from other boxes without any difficulty. I have removed the snmp_ro.ora and recycled the IA, Management Server, etc. Still can't find this instance. It is finding 9i (r1 and r2) instances. This is an 8i instance (8.1.7.4) but I don't think that's it, as the development version on another node is found just fine. TIA, John P Weatherman Database Administrator Replacements Ltd. -Original Message- Sent: Thursday, October 17, 2002 2:47 PM To: Multiple recipients of list ORACLE-L Yes, there is. You might notice that someone has issued a create database command because all of your data will be gone fishing. When the noise level reaches 500 decibels, someone has issued CREATE DATABASE command on your production database, which, of course, no longer exists. System accounting should be able to tell you who has logged in as oracle and from which PC. Then you go and publicly execute that person by beating him to the pulp with a baseball bat (so called Soprano method) -Original Message- From: Boivin, Patrice J [mailto:BoivinP;mar.dfo-mpo.gc.ca] Sent: Thursday, October 17, 2002 12:42 PM To: Multiple recipients of list ORACLE-L Subject: auditing CREATE DATABASE Is there a way of capturing info on people who issue CREATE DATABASE statements? My Oracle Support technician says auditing won't catch this, only catches successful attempts. Regards, Patrice Boivin Systems Analyst (Oracle Certified DBA) Systems Admin Operations | Admin. et Exploit. des systèmes Technology Services| Services technologiques Informatics Branch | Direction de l'informatique Maritimes Region, DFO | Région des Maritimes, MPO E-Mail: [EMAIL PROTECTED] -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Boivin, Patrice J INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Gogala, Mladen INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To
RE: OEM can't seem to discover 1 instance
Has this instance ever been successfully discovered before? I've found that if I delete it from OEM it will then discover the database. Mike From: John Weatherman [EMAIL PROTECTED] Reply-To: [EMAIL PROTECTED] To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Subject: RE: OEM can't seem to discover 1 instance Date: Fri, 18 Oct 2002 05:38:37 -0800 Hmm, thanks to all who are replying. It looks like the instance is not making it into either snmp_rw.ora, snmp_ro.ora or the services.ora. I have manually edited them, but when I restart the agent, it wipes out the manual entries in the snmp_ro.ora and the services.ora. The oratab and listener look fine. Any more good advice? TIA, John P Weatherman Database Administrator Replacements Ltd. -Original Message- [mailto:Reginald.W.Bailey;jpmorgan.com] Sent: Friday, October 18, 2002 5:14 AM To: Multiple recipients of list ORACLE-L The instance should be listed in the oratab file, the listener.ora file and the snmp_rw.ora file. It is automatically added to the snmp_ro.ora file at agent start up. Ensure that the Intelligent Agent is running. If any changes need to be made to any of the previously mentioned files, stop and restart the agent after making the change. Then try to discover the node. Reginald W. Bailey DBA, Consultant and MOKF John Weatherman [EMAIL PROTECTED]@fatcity.com on 10/17/2002 03:10:41 PM Please respond to [EMAIL PROTECTED] Sent by: [EMAIL PROTECTED] To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] cc: Anybody seen anything like this? I am running 9.2.0.1 OEM auto discovery. The Intelligent Agent is running on the target node. For some reason, it seems unable to discover 1 instance, the other 3 on that node are found. TNS seems ok, I can connect to the instance from other boxes without any difficulty. I have removed the snmp_ro.ora and recycled the IA, Management Server, etc. Still can't find this instance. It is finding 9i (r1 and r2) instances. This is an 8i instance (8.1.7.4) but I don't think that's it, as the development version on another node is found just fine. TIA, John P Weatherman Database Administrator Replacements Ltd. -Original Message- Sent: Thursday, October 17, 2002 2:47 PM To: Multiple recipients of list ORACLE-L Yes, there is. You might notice that someone has issued a create database command because all of your data will be gone fishing. When the noise level reaches 500 decibels, someone has issued CREATE DATABASE command on your production database, which, of course, no longer exists. System accounting should be able to tell you who has logged in as oracle and from which PC. Then you go and publicly execute that person by beating him to the pulp with a baseball bat (so called Soprano method) -Original Message- From: Boivin, Patrice J [mailto:BoivinP;mar.dfo-mpo.gc.ca] Sent: Thursday, October 17, 2002 12:42 PM To: Multiple recipients of list ORACLE-L Subject: auditing CREATE DATABASE Is there a way of capturing info on people who issue CREATE DATABASE statements? My Oracle Support technician says auditing won't catch this, only catches successful attempts. Regards, Patrice Boivin Systems Analyst (Oracle Certified DBA) Systems Admin Operations | Admin. et Exploit. des systèmes Technology Services| Services technologiques Informatics Branch | Direction de l'informatique Maritimes Region, DFO | Région des Maritimes, MPO E-Mail: [EMAIL PROTECTED] -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Boivin, Patrice J INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Gogala, Mladen INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: John Weatherman INET: [EMAIL PROTECTED] Fat City Network Services--
RE: OEM can't seem to discover 1 instance
When you do a lsnrctl status is there anything different for the instance that isn't discovered and the ones that are? -Original Message- Sent: Friday, October 18, 2002 9:39 AM To: Multiple recipients of list ORACLE-L Hmm, thanks to all who are replying. It looks like the instance is not making it into either snmp_rw.ora, snmp_ro.ora or the services.ora. I have manually edited them, but when I restart the agent, it wipes out the manual entries in the snmp_ro.ora and the services.ora. The oratab and listener look fine. Any more good advice? TIA, John P Weatherman Database Administrator Replacements Ltd. -Original Message- [mailto:Reginald.W.Bailey;jpmorgan.com] Sent: Friday, October 18, 2002 5:14 AM To: Multiple recipients of list ORACLE-L The instance should be listed in the oratab file, the listener.ora file and the snmp_rw.ora file. It is automatically added to the snmp_ro.ora file at agent start up. Ensure that the Intelligent Agent is running. If any changes need to be made to any of the previously mentioned files, stop and restart the agent after making the change. Then try to discover the node. Reginald W. Bailey DBA, Consultant and MOKF John Weatherman [EMAIL PROTECTED]@fatcity.com on 10/17/2002 03:10:41 PM Please respond to [EMAIL PROTECTED] Sent by: [EMAIL PROTECTED] To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] cc: Anybody seen anything like this? I am running 9.2.0.1 OEM auto discovery. The Intelligent Agent is running on the target node. For some reason, it seems unable to discover 1 instance, the other 3 on that node are found. TNS seems ok, I can connect to the instance from other boxes without any difficulty. I have removed the snmp_ro.ora and recycled the IA, Management Server, etc. Still can't find this instance. It is finding 9i (r1 and r2) instances. This is an 8i instance (8.1.7.4) but I don't think that's it, as the development version on another node is found just fine. TIA, John P Weatherman Database Administrator Replacements Ltd. -Original Message- Sent: Thursday, October 17, 2002 2:47 PM To: Multiple recipients of list ORACLE-L Yes, there is. You might notice that someone has issued a create database command because all of your data will be gone fishing. When the noise level reaches 500 decibels, someone has issued CREATE DATABASE command on your production database, which, of course, no longer exists. System accounting should be able to tell you who has logged in as oracle and from which PC. Then you go and publicly execute that person by beating him to the pulp with a baseball bat (so called Soprano method) -Original Message- From: Boivin, Patrice J [mailto:BoivinP;mar.dfo-mpo.gc.ca] Sent: Thursday, October 17, 2002 12:42 PM To: Multiple recipients of list ORACLE-L Subject: auditing CREATE DATABASE Is there a way of capturing info on people who issue CREATE DATABASE statements? My Oracle Support technician says auditing won't catch this, only catches successful attempts. Regards, Patrice Boivin Systems Analyst (Oracle Certified DBA) Systems Admin Operations | Admin. et Exploit. des systèmes Technology Services| Services technologiques Informatics Branch | Direction de l'informatique Maritimes Region, DFO | Région des Maritimes, MPO E-Mail: [EMAIL PROTECTED] -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Boivin, Patrice J INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Gogala, Mladen INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: John Weatherman INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services
Re: Specify Rollback for an Export
The only correct resolution to this is to ensure that DML transactions referencing the tables being backed up, are not run at the same time. Bringing the big rollback segment online wont buy you anything. You could delay the inevitable by removing the optimal clauses if set. Raj Erik Williams ewilliams@brTo: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] ownco.com cc: Sent by: Subject: Specify Rollback for an Export root@fatcity. com October 18, 2002 10:13 AM Please respond to ORACLE-L I am performing an export of a set of very large tables. I am not using CONSISTENT mode. The exports are failing due to snapshot too old errors. The application uses many small rollback segments, but has one large one for big transactions. Is it possible to force the export to use the large segment without taking all the others (except System) offline? I have seen this question asked on the list before, but did not find a definitive follow-up answer. Thanks Erik -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: OEM can't seem to discover 1 instance
Er, what I meant to say in my last email is to delete the node from OEM and then rediscover all of the databases on it fresh. Mike From: John Weatherman [EMAIL PROTECTED] Reply-To: [EMAIL PROTECTED] To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Subject: RE: OEM can't seem to discover 1 instance Date: Fri, 18 Oct 2002 05:38:37 -0800 Hmm, thanks to all who are replying. It looks like the instance is not making it into either snmp_rw.ora, snmp_ro.ora or the services.ora. I have manually edited them, but when I restart the agent, it wipes out the manual entries in the snmp_ro.ora and the services.ora. The oratab and listener look fine. Any more good advice? TIA, John P Weatherman Database Administrator Replacements Ltd. -Original Message- [mailto:Reginald.W.Bailey;jpmorgan.com] Sent: Friday, October 18, 2002 5:14 AM To: Multiple recipients of list ORACLE-L The instance should be listed in the oratab file, the listener.ora file and the snmp_rw.ora file. It is automatically added to the snmp_ro.ora file at agent start up. Ensure that the Intelligent Agent is running. If any changes need to be made to any of the previously mentioned files, stop and restart the agent after making the change. Then try to discover the node. Reginald W. Bailey DBA, Consultant and MOKF John Weatherman [EMAIL PROTECTED]@fatcity.com on 10/17/2002 03:10:41 PM Please respond to [EMAIL PROTECTED] Sent by: [EMAIL PROTECTED] To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] cc: Anybody seen anything like this? I am running 9.2.0.1 OEM auto discovery. The Intelligent Agent is running on the target node. For some reason, it seems unable to discover 1 instance, the other 3 on that node are found. TNS seems ok, I can connect to the instance from other boxes without any difficulty. I have removed the snmp_ro.ora and recycled the IA, Management Server, etc. Still can't find this instance. It is finding 9i (r1 and r2) instances. This is an 8i instance (8.1.7.4) but I don't think that's it, as the development version on another node is found just fine. TIA, John P Weatherman Database Administrator Replacements Ltd. -Original Message- Sent: Thursday, October 17, 2002 2:47 PM To: Multiple recipients of list ORACLE-L Yes, there is. You might notice that someone has issued a create database command because all of your data will be gone fishing. When the noise level reaches 500 decibels, someone has issued CREATE DATABASE command on your production database, which, of course, no longer exists. System accounting should be able to tell you who has logged in as oracle and from which PC. Then you go and publicly execute that person by beating him to the pulp with a baseball bat (so called Soprano method) -Original Message- From: Boivin, Patrice J [mailto:BoivinP;mar.dfo-mpo.gc.ca] Sent: Thursday, October 17, 2002 12:42 PM To: Multiple recipients of list ORACLE-L Subject: auditing CREATE DATABASE Is there a way of capturing info on people who issue CREATE DATABASE statements? My Oracle Support technician says auditing won't catch this, only catches successful attempts. Regards, Patrice Boivin Systems Analyst (Oracle Certified DBA) Systems Admin Operations | Admin. et Exploit. des systèmes Technology Services| Services technologiques Informatics Branch | Direction de l'informatique Maritimes Region, DFO | Région des Maritimes, MPO E-Mail: [EMAIL PROTECTED] -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Boivin, Patrice J INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Gogala, Mladen INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: John Weatherman INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051
RE: Specify Rollback for an Export
Erik - The problem lies not with your export but with the other transactions. Basically, export is taking a query of the table when it starts (like a select * with no where clause). As it is exporting the table, other applications are making changes to the table. These changes are held in the rollback segments, but when the transaction that caused the changes commits, eventually Oracle releases these. In your case, before the export reaches the end of the table, it tries to retrieve a block, learns it is changed, checks the rollback segments, learns it has been released, then reports an error that it cannot recreate the block as it existed when the export began. The best choice is to perform your export when the table isn't being changed so much, i.e.. not when batch jobs are hammering the table. Second choice would be to create a second set of larger rollback segments and switch usage to them. Third choice available on 9i is to use automatic undo, which has a target parameter of how long in time to hang onto changed blocks. Dennis Williams DBA, 40%OCP Lifetouch, Inc. [EMAIL PROTECTED] -Original Message- Sent: Friday, October 18, 2002 9:14 AM To: Multiple recipients of list ORACLE-L I am performing an export of a set of very large tables. I am not using CONSISTENT mode. The exports are failing due to snapshot too old errors. The application uses many small rollback segments, but has one large one for big transactions. Is it possible to force the export to use the large segment without taking all the others (except System) offline? I have seen this question asked on the list before, but did not find a definitive follow-up answer. Thanks Erik -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Erik Williams INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: DENNIS WILLIAMS INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: OEM can't seem to discover 1 instance
Scrap OEM. Seriously, we ran into way too many of these little annoying problems. We decided that we had better things to do with our time. Have you had to re-register all of your jobs, yet? That one was a real pain. Jay [EMAIL PROTECTED] 10/18/02 09:38AM Hmm, thanks to all who are replying. It looks like the instance is not making it into either snmp_rw.ora, snmp_ro.ora or the services.ora. I have manually edited them, but when I restart the agent, it wipes out the manual entries in the snmp_ro.ora and the services.ora. The oratab and listener look fine. Any more good advice? TIA, John P Weatherman Database Administrator Replacements Ltd. **DISCLAIMER This e-mail message and any files transmitted with it are intended for the use of the individual or entity to which they are addressed and may contain information that is privileged, proprietary and confidential. If you are not the intended recipient, you may not use, copy or disclose to anyone the message or any information contained in the message. If you have received this communication in error, please notify the sender and delete this e-mail message. The contents do not represent the opinion of DE except to the extent that it relates to their official business. -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Jay Hostetter INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: OEM can't seem to discover 1 instance
I agree with Jay. Everytime I use OEM there are 10 or 20 annoying little things that make it incredibly frustrating to use. Jared mentioned a while back that the new product manager for OEM is on this list. Maybe he could set up something (like a *big* mailbox) to take suggestions for changes. I would love to bag it completely but we're stuck with it because of the integration with OWB, for a little while longer anyway. -Original Message- Sent: Friday, October 18, 2002 11:24 AM To: Multiple recipients of list ORACLE-L Scrap OEM. Seriously, we ran into way too many of these little annoying problems. We decided that we had better things to do with our time. Have you had to re-register all of your jobs, yet? That one was a real pain. Jay [EMAIL PROTECTED] 10/18/02 09:38AM Hmm, thanks to all who are replying. It looks like the instance is not making it into either snmp_rw.ora, snmp_ro.ora or the services.ora. I have manually edited them, but when I restart the agent, it wipes out the manual entries in the snmp_ro.ora and the services.ora. The oratab and listener look fine. Any more good advice? TIA, John P Weatherman Database Administrator Replacements Ltd. **DISCLAIMER This e-mail message and any files transmitted with it are intended for the use of the individual or entity to which they are addressed and may contain information that is privileged, proprietary and confidential. If you are not the intended recipient, you may not use, copy or disclose to anyone the message or any information contained in the message. If you have received this communication in error, please notify the sender and delete this e-mail message. The contents do not represent the opinion of DE except to the extent that it relates to their official business. -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Jay Hostetter INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Seefelt, Beth INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: Urgent problem with query in CBO Vs RBO
Title: RE: Urgent problem with query in CBO Vs RBO
"Looks like the lid on the "jar of bugs" is
leaking ... and it made it into 920 code as well."
I think this a pretty common
occurrence at Oracle... I think it has to do with the way they branch
their code streams... Subsequentpatches are often not integrated
into the latest stream I've run into this kind of thing several
times...
Tim
-Original Message-From: Jamadagni, Rajendra
[mailto:[EMAIL PROTECTED]]Sent: Friday, October 18, 2002
9:59 AMTo: Multiple recipients of list ORACLE-LSubject:
RE: Urgent problem with query in CBO Vs RBO
I don't think the reply I sent last night made it to the
list.
We eventually traced it to the behavior of _unnest_subquery
parameter. Setting it to false solved the problem. This was first reported in
817 and supposed to be fixed in 8173 and 901 code base. Looks like the lid on
the "jar of bugs" is leaking ... and it made it into 920 code as
well.
The biggest problem we found that the explain plan shows that
it doesn't even look at a major portion of the sql statement when it fails.
10053 trace shows that the optimizer looking at all tables involved and
checking for different paths, but the explain plan is not reporting all the
tables involved.
Here is an example ...
-- this doesn't work
- select
SUM(a.csed_dollars) FROM
client_supplied_ep_dollars a WHERE a.csed_pob_id
= 213841 AND a.csed_date = (SELECT
MAX(b.csed_date)
FROM client_supplied_ep_dollars b
WHERE b.csed_ep_number = a.csed_ep_number
AND b.csed_pob_id = a.csed_pob_id) AND EXISTS (SELECT 'x'
FROM v_log_master l,
episode_airings e,
units
u
WHERE u.ut_ea_ep_number = a.csed_ep_number
AND u.ut_pob_id = a.csed_pob_id
AND u.ut_disposition_ind IS NULL
AND e.life_cycle_status 2
AND e.ep_number = u.ut_ea_ep_number
AND e.est_dt BETWEEN to_date('03/01/2002','mm/dd/') and
to_date('03/31/2002','mm/dd/') --
AND e.est_dt BETWEEN m_start_date AND m_end_date
AND l.log_date = e.est_dt
AND l.log_network = NVL(e.act_log_network,
e.log_network)
AND l.log_no =
NVL(e.act_log_number,e.log_number)
AND (NVL(e.did_not_air_ind,'N') = 'N' OR e.life_cycle_status = 9
OR
(NVL(e.did_not_air_ind,'N') = 'Y' AND l.master_log_date IS NULL)))
SQL /
SUM(A.CSED_DOLLARS)
---
123
Execution Plan --
0 SELECT
STATEMENT Optimizer=CHOOSE (Cost=13 Card=1
Bytes=41)
1 0 SORT
(AGGREGATE)
2
1 VIEW (Cost=13 Card=1
Bytes=41)
3
2
FILTER
4
3 SORT (GROUP BY) (Cost=13
Card=1
Bytes=42)
5
4 HASH JOIN
(Cost=11 Card=1
Bytes=42)
6
5
INDEX (RANGE SCAN) OF 'CSED_PRIM' (UNIQUE) (Cost=2 Card=12 Bytes=192)
7
5
TABLE ACCESS (BY INDEX ROWID) OF 'CLIENT_SUPPLIED_EP_DOLLARS' (Cost=8 Card=12
Bytes=312) 8
7
INDEX (RANGE SCAN) OF 'CSED_PRIM' (UNIQUE) (Cost=2
Card=1)
SQL spool off end
And
--- this works
-- select
SUM(a.csed_dollars) FROM
client_supplied_ep_dollars a WHERE a.csed_pob_id
= 213841 AND a.csed_date = (SELECT
MAX(b.csed_date)
FROM client_supplied_ep_dollars b
WHERE b.csed_ep_number = a.csed_ep_number
AND b.csed_pob_id = a.csed_pob_id
group by 1) -- this group by fixes the query ...
AND EXISTS (SELECT 'x'
FROM v_log_master l,
episode_airings e,
units
u
WHERE u.ut_ea_ep_number = a.csed_ep_number
AND u.ut_pob_id = a.csed_pob_id
AND u.ut_disposition_ind IS NULL
AND e.life_cycle_status 2
AND e.ep_number = u.ut_ea_ep_number
AND e.est_dt BETWEEN to_date('03/01/2002','mm/dd/') and
to_date('03/31/2002','mm/dd/') --
AND e.est_dt BETWEEN m_start_date AND m_end_date
AND l.log_date = e.est_dt
AND l.log_network = NVL(e.act_log_network,
e.log_network)
AND l.log_no =
NVL(e.act_log_number,e.log_number)
AND (NVL(e.did_not_air_ind,'N') = 'N' OR e.life_cycle_status = 9
OR
(NVL(e.did_not_air_ind,'N') = 'Y' AND l.master_log_date IS NULL)))
SQL /
SUM(A.CSED_DOLLARS)
---
15
Execution Plan --
0 SELECT
STATEMENT Optimizer=CHOOSE (Cost=3 Card=1
Bytes=19)
1 0 SORT
(AGGREGATE)
2
1 TABLE ACCESS (BY INDEX ROWID) OF
'CLIENT_SUPPLIED_EP_DOLLARS' (Cost=3 Card=1
Bytes=19)
3
2 INDEX (RANGE SCAN) OF 'CSED_PRIM'
(UNIQUE) (Cost=2
Card=1)
4
3 SORT (GROUP BY NOSORT)
(Cost=2 Card=1
Bytes=16)
5
4 FIRST ROW
(Cost=2 Card=1
Bytes=16)
6
5
INDEX (RANGE SCAN (MIN/MAX)) OF 'CSED_PRIM' (UNIQUE) (Cost=2
Card=12488)
RE: Specify Rollback for an Export
I'm not sure whether there is another way, but can the following be done? in the after logon trigger specify: 'SET TRANSACTION USE ROLLBACK SEGMENT...? don't know whether this statement is allowed inside a trigger and also whether it will work. and also, export commits periodically, the statement will go off after the first commit(even if it was allowed) regards naveen -Original Message- Sent: Friday, October 18, 2002 7:44 PM To: Multiple recipients of list ORACLE-L I am performing an export of a set of very large tables. I am not using CONSISTENT mode. The exports are failing due to snapshot too old errors. The application uses many small rollback segments, but has one large one for big transactions. Is it possible to force the export to use the large segment without taking all the others (except System) offline? I have seen this question asked on the list before, but did not find a definitive follow-up answer. Thanks Erik -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Erik Williams INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Naveen Nahata INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
CA's AutoSys
To All, I'll admit this is somewhat off-topic, but since the project is going to handle all scheduled batch jobs that bang on our PeopleSoft Oracle database, I figured I'd ask if anyone has an opinion preferably from experience using the product. The Complete A^^holes are here today to do a demo install so any input you all have would be appreciated. Thanks in advance Dick Goulet -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: Specify Rollback for an Export
Hi, The snapshot too old error comes when someone has started a query on one table and at the same time some transaction is going on. So at this time the select query will read from rollback segment to create a consistent view in time. Now in what happens select query has started reading the rollback segment and that transaction has completed. At this particular time the snapshot too old error will come. The only solution i can tell u to increase your rollback segment size and also increase your export buffer ( so that faster export can be done as snapshot is also related to time). Or do your export when least amount of activity is going on in db. Regards Darshan Singh -Original Message- Williams Sent: Friday, October 18, 2002 4:14 PM To: Multiple recipients of list ORACLE-L I am performing an export of a set of very large tables. I am not using CONSISTENT mode. The exports are failing due to snapshot too old errors. The application uses many small rollback segments, but has one large one for big transactions. Is it possible to force the export to use the large segment without taking all the others (except System) offline? I have seen this question asked on the list before, but did not find a definitive follow-up answer. Thanks Erik -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Erik Williams INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Darshan Singh INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Resource Management
Hello Listers, Anyone using Oracle's Database Resource Manager? Does it really work as outlined in the Docs? Care to share your experience? Thanks, - Kirti -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Deshpande, Kirti INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: Urgent problem with query in CBO Vs RBO
Title: RE: Urgent problem with query in CBO Vs RBO
isn't
this what we pay the maintenance costs for - for them to get this thing
right?
no
excuse for this shoddy bit of work.
-Original Message-From: Johnston, Tim
[mailto:[EMAIL PROTECTED]]Sent: Friday, October 18, 2002 11:54
AMTo: Multiple recipients of list ORACLE-LSubject: RE:
Urgent problem with query in CBO Vs RBO
"Looks like the lid on the
"jar of bugs" is leaking ... and it made it into 920 code as
well."
I think this a pretty common
occurrence at Oracle... I think it has to do with the way they branch
their code streams... Subsequentpatches are often not integrated
into the latest stream I've run into this kind of thing several
times...
Tim
-Original Message-From: Jamadagni, Rajendra
[mailto:[EMAIL PROTECTED]]Sent: Friday, October 18,
2002 9:59 AMTo: Multiple recipients of list
ORACLE-LSubject: RE: Urgent problem with query in CBO Vs
RBO
I don't think the reply I sent last night made it to the
list.
We eventually traced it to the behavior of _unnest_subquery
parameter. Setting it to false solved the problem. This was first reported
in 817 and supposed to be fixed in 8173 and 901 code base. Looks like the
lid on the "jar of bugs" is leaking ... and it made it into 920 code as
well.
The biggest problem we found that the explain plan shows
that it doesn't even look at a major portion of the sql statement when it
fails. 10053 trace shows that the optimizer looking at all tables involved
and checking for different paths, but the explain plan is not reporting all
the tables involved.
Here is an example ...
-- this doesn't work
- select
SUM(a.csed_dollars) FROM
client_supplied_ep_dollars a WHERE
a.csed_pob_id = 213841 AND a.csed_date
= (SELECT MAX(b.csed_date)
FROM client_supplied_ep_dollars b
WHERE b.csed_ep_number = a.csed_ep_number
AND b.csed_pob_id = a.csed_pob_id) AND EXISTS (SELECT 'x'
FROM v_log_master l,
episode_airings e,
units
u
WHERE u.ut_ea_ep_number = a.csed_ep_number
AND u.ut_pob_id = a.csed_pob_id
AND u.ut_disposition_ind IS NULL
AND e.life_cycle_status 2
AND e.ep_number =
u.ut_ea_ep_number
AND e.est_dt BETWEEN to_date('03/01/2002','mm/dd/') and
to_date('03/31/2002','mm/dd/') --
AND e.est_dt BETWEEN m_start_date AND m_end_date
AND l.log_date = e.est_dt
AND l.log_network = NVL(e.act_log_network,
e.log_network)
AND l.log_no =
NVL(e.act_log_number,e.log_number)
AND (NVL(e.did_not_air_ind,'N') = 'N' OR e.life_cycle_status = 9
OR
(NVL(e.did_not_air_ind,'N') = 'Y' AND l.master_log_date IS NULL)))
SQL /
SUM(A.CSED_DOLLARS)
---
123
Execution Plan --
0 SELECT
STATEMENT Optimizer=CHOOSE (Cost=13 Card=1
Bytes=41)
1 0 SORT
(AGGREGATE)
2
1 VIEW (Cost=13 Card=1
Bytes=41)
3
2
FILTER
4
3 SORT (GROUP BY) (Cost=13
Card=1
Bytes=42)
5
4 HASH JOIN
(Cost=11 Card=1
Bytes=42)
6
5
INDEX (RANGE SCAN) OF 'CSED_PRIM' (UNIQUE) (Cost=2 Card=12 Bytes=192)
7
5
TABLE ACCESS (BY INDEX ROWID) OF 'CLIENT_SUPPLIED_EP_DOLLARS' (Cost=8
Card=12 Bytes=312) 8
7
INDEX (RANGE SCAN) OF 'CSED_PRIM' (UNIQUE) (Cost=2
Card=1)
SQL spool off end
And
--- this works
-- select
SUM(a.csed_dollars) FROM
client_supplied_ep_dollars a WHERE
a.csed_pob_id = 213841 AND a.csed_date
= (SELECT MAX(b.csed_date)
FROM client_supplied_ep_dollars b
WHERE b.csed_ep_number = a.csed_ep_number
AND b.csed_pob_id = a.csed_pob_id
group by 1) -- this group by fixes the query ...
AND EXISTS (SELECT 'x'
FROM v_log_master l,
episode_airings e,
units
u
WHERE u.ut_ea_ep_number = a.csed_ep_number
AND u.ut_pob_id = a.csed_pob_id
AND u.ut_disposition_ind IS NULL
AND e.life_cycle_status 2
AND e.ep_number =
u.ut_ea_ep_number
AND e.est_dt BETWEEN to_date('03/01/2002','mm/dd/') and
to_date('03/31/2002','mm/dd/') --
AND e.est_dt BETWEEN m_start_date AND m_end_date
AND l.log_date = e.est_dt
AND l.log_network = NVL(e.act_log_network,
e.log_network)
AND l.log_no =
NVL(e.act_log_number,e.log_number)
AND (NVL(e.did_not_air_ind,'N') = 'N' OR e.life_cycle_status = 9
OR
(NVL(e.did_not_air_ind,'N') = 'Y' AND l.master_log_date IS NULL)))
SQL /
RE: OEM can't seem to discover 1 instance
Check the log files in the log directory. If the agent fails to log into the instance, it will be in the log files. They are found at $TNS_ADMIN/network/log. The agent will always wipe out the snmp_ro.ora and services.ora files upon restarting. It should include entries from oratab and the listener.ora file. Ensure that the database is up and running. RWB John Weatherman [EMAIL PROTECTED]@fatcity.com on 10/18/2002 08:38:38 AM Please respond to [EMAIL PROTECTED] Sent by: [EMAIL PROTECTED] To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] cc: Hmm, thanks to all who are replying. It looks like the instance is not making it into either snmp_rw.ora, snmp_ro.ora or the services.ora. I have manually edited them, but when I restart the agent, it wipes out the manual entries in the snmp_ro.ora and the services.ora. The oratab and listener look fine. Any more good advice? TIA, John P Weatherman Database Administrator Replacements Ltd. -Original Message- [mailto:Reginald.W.Bailey;jpmorgan.com] Sent: Friday, October 18, 2002 5:14 AM To: Multiple recipients of list ORACLE-L The instance should be listed in the oratab file, the listener.ora file and the snmp_rw.ora file. It is automatically added to the snmp_ro.ora file at agent start up. Ensure that the Intelligent Agent is running. If any changes need to be made to any of the previously mentioned files, stop and restart the agent after making the change. Then try to discover the node. Reginald W. Bailey DBA, Consultant and MOKF John Weatherman [EMAIL PROTECTED]@fatcity.com on 10/17/2002 03:10:41 PM Please respond to [EMAIL PROTECTED] Sent by: [EMAIL PROTECTED] To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] cc: Anybody seen anything like this? I am running 9.2.0.1 OEM auto discovery. The Intelligent Agent is running on the target node. For some reason, it seems unable to discover 1 instance, the other 3 on that node are found. TNS seems ok, I can connect to the instance from other boxes without any difficulty. I have removed the snmp_ro.ora and recycled the IA, Management Server, etc. Still can't find this instance. It is finding 9i (r1 and r2) instances. This is an 8i instance (8.1.7.4) but I don't think that's it, as the development version on another node is found just fine. TIA, John P Weatherman Database Administrator Replacements Ltd. -Original Message- Sent: Thursday, October 17, 2002 2:47 PM To: Multiple recipients of list ORACLE-L Yes, there is. You might notice that someone has issued a create database command because all of your data will be gone fishing. When the noise level reaches 500 decibels, someone has issued CREATE DATABASE command on your production database, which, of course, no longer exists. System accounting should be able to tell you who has logged in as oracle and from which PC. Then you go and publicly execute that person by beating him to the pulp with a baseball bat (so called Soprano method) -Original Message- From: Boivin, Patrice J [mailto:BoivinP;mar.dfo-mpo.gc.ca] Sent: Thursday, October 17, 2002 12:42 PM To: Multiple recipients of list ORACLE-L Subject: auditing CREATE DATABASE Is there a way of capturing info on people who issue CREATE DATABASE statements? My Oracle Support technician says auditing won't catch this, only catches successful attempts. Regards, Patrice Boivin Systems Analyst (Oracle Certified DBA) Systems Admin Operations | Admin. et Exploit. des systèmes Technology Services| Services technologiques Informatics Branch | Direction de l'informatique Maritimes Region, DFO | Région des Maritimes, MPO E-Mail: [EMAIL PROTECTED] -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Boivin, Patrice J INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Gogala, Mladen INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send
Re: Please help, comment required urgently
To give credit where credit is due... I've never bothered to learn how to interpret a STATSPACK report; I've left that with what little I ever knew about interpreting a BSTAT/ESTAT report. Rather, I just send the report to the YAPP report post-processor at www.oraperf.com and it formats everything in such a way that things just jump out at you. It is quite instructive to de-construct the resulting YAPP report back to the original STATSPACK and BSTAT/ESTAT report (and thence further back to the originating V$ views). Big-time kudos to Anjo and friends for creating and maintaining this invaluable resource!!! - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Friday, October 18, 2002 8:34 AM Thanks for your contributions on this Tim (and on everything else you respond to). These little tips of how to analyze statspack reports properly all add up and whilst I did look at the report and I did glean some of you what suggested I certainly did not pick up all that you spotted John -Original Message- Sent: 18 October 2002 14:54 To: Multiple recipients of list ORACLE-L George, Two things jump out together: * The SQL statement with hash value = 3509998681 is consuming about 25% of the total response-time (i.e. total processing plus total wait) on the system. This SQL statement is executing 900 times during the one-hour sample period... * Waits on the cache buffers chains are consuming another 16% of total response-time With these two things consuming 41% of everything consumed by the database instance during this time period, there is no chance that anything else is more important... Chances are excellent that these two things are related. Since the SQL statement has over 329m buffer gets and about 0.5m buffer cache-misses (i.e. physical reads) to it's credit, this indicates a buffer-cache hit-ratio of over 99.7%, which is sure proof that something is seriously wrong! :-) My guess is that the query is using an inappropriate and/or inefficient index for a long, long, long range-scan operation, which is racking up all of those buffer gets. What do you expect from the rule-based optimizer? If you were running CBO and this happened, I'd suggest gathering column-level histogram statistics on the table. My guess also is that many concurrent users are running this statement during the course of the sample period, causing the latch contention for cache buffers in the Buffer Cache, thus the relationship between the two symptoms? I can see that DB_CACHE_LRU_LATCHES has been pushed up to 48; don't know what CPU_COUNT is, but obviously this change has had zero impact on the latch contention problem. Tuning the SQL will fix the problem; accomodating the problem by configuring more latches has no impact. Tuning that one SQL statement (plus a few of it's look-alikes, also listed in the report) will resolve the major performance issues you are experiencing. In fact, it will have a miraculous impact... Hope this helps... -Tim - Original Message - [EMAIL PROTECTED] To: Multiple recipients of list ORACLE-L mailto:ORACLE-L;fatcity.com [EMAIL PROTECTED] Sent: Friday, October 18, 2002 2:53 AM Hi guys, I need a second opinion on the following Statspack output, I got my suspicions but my manager and the client is not buying what I am say, Not knowing anything of the system architecture please look at the output and say what would concern you. What assumptions/recommendations you would make. Thx George George Leonard Oracle Database Administrator Dimension Data (Pty) Ltd (Reg. No. 1987/006597/07) Tel: (+27 11) 575 0573 Fax: (+27 11) 576 0573 E-mail:[EMAIL PROTECTED] Web:http://www.didata.co.za http://www.didata.co.za You Have The Obligation to Inform One Honestly of the risk, And As a Person You Are Committed to Educate Yourself to the Total Risk In Any Activity! Once Informed Totally Aware of the Risk, Every Fool Has the Right to Kill or Injure Themselves as They See Fit! -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Tim Gorman INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com
RE: Specify Rollback for an Export
Sorry for my post. mis-read the question completely regards naveen -Original Message- Sent: Friday, October 18, 2002 8:54 PM To: Multiple recipients of list ORACLE-L I'm not sure whether there is another way, but can the following be done? in the after logon trigger specify: 'SET TRANSACTION USE ROLLBACK SEGMENT...? don't know whether this statement is allowed inside a trigger and also whether it will work. and also, export commits periodically, the statement will go off after the first commit(even if it was allowed) regards naveen -Original Message- Sent: Friday, October 18, 2002 7:44 PM To: Multiple recipients of list ORACLE-L I am performing an export of a set of very large tables. I am not using CONSISTENT mode. The exports are failing due to snapshot too old errors. The application uses many small rollback segments, but has one large one for big transactions. Is it possible to force the export to use the large segment without taking all the others (except System) offline? I have seen this question asked on the list before, but did not find a definitive follow-up answer. Thanks Erik -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Erik Williams INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Naveen Nahata INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Naveen Nahata INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: CA's AutoSys
Title: RE: CA's AutoSys Run Screaming in the opposite direction. Product was a semi-disaster at two previous job sites. Failed to run jobs when scheduled, failed to detected when dependent jobs failed. Just plain didn't work well. Of course this was when Platinum owned the product, who knows, maybe CA improved it. (although I would doubt it). Matt Adams - GE Appliances - [EMAIL PROTECTED] Write a poem about a haircut! But lofty, noble, tragic, full of love, treachery, retribution, quiet heroism in the face of certain doom! Six lines, cleverly rhymed, and every word beginning with the letter s! -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]] Sent: Friday, October 18, 2002 11:50 AM To: Multiple recipients of list ORACLE-L Subject: CA's AutoSys To All, I'll admit this is somewhat off-topic, but since the project is going to handle all scheduled batch jobs that bang on our PeopleSoft Oracle database, I figured I'd ask if anyone has an opinion preferably from experience using the product. The Complete A^^holes are here today to do a demo install so any input you all have would be appreciated. Thanks in advance Dick Goulet -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: INET: [EMAIL PROTECTED] Fat City Network Services -- 858-538-5051 http://www.fatcity.com San Diego, California -- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: CA's AutoSys
Dick, We have been using that product for quite some time now... and it does a pretty good job of scheduling things. In our environment *all* jobs are handled by Autosys, and we are not supposed to use 'cron'. We use the product for scheduling jobs on a variety of h/w platforms( IBM-AIX, HP, Sun, SGI etc). It does need some dedicated resources to manage a large and dynamic environment. In our case, Operations and Production Control group has 2-3 FTEs maintaining the job schedules. The database used by Autosys is an Oracle 8i database and we have a Senior DBA responsible for it, as it is one of the *most critical* databases in the Company. - Kirti -Original Message- Sent: Friday, October 18, 2002 10:50 AM To: Multiple recipients of list ORACLE-L To All, I'll admit this is somewhat off-topic, but since the project is going to handle all scheduled batch jobs that bang on our PeopleSoft Oracle database, I figured I'd ask if anyone has an opinion preferably from experience using the product. The Complete A^^holes are here today to do a demo install so any input you all have would be appreciated. Thanks in advance Dick Goulet -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Deshpande, Kirti INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: Archive files and their Management
Title: Archive files and their Management I'm up for my backup and recovery class here soon, butthis conversation has brought up questions that I think some of you wouldn't mind discussing anyway. I see whereimplementing archive logs requires that youmodify the database startup procedure to turn on archiving before opening the database.At thispoint the DBA should do an immediate "baseline"backup (orafaq =)) which implies a hot/online backup.I amcurious how thisgoes with point in timecold backups. ie.DBA does a cold backup opens the database in archive log mode.Can you apply accumulated archive logsto a restore fromcold backup ?perhaps there are issues with the control file information. -Original Message-From: Naveen Nahata [mailto:[EMAIL PROTECTED]]Sent: Friday, October 18, 2002 7:53 AMTo: Multiple recipients of list ORACLE-LSubject: RE: Archive files and their Management theoretically only the archive files after the backup started are needed. but there might be issues of a corruptedbackup, and to guard against that we have a policy of keeping archive files for one week. you should also keep checking your backups by restoring them to a separate location periodically regards Naveen -Original Message-From: Yechiel Adar [mailto:[EMAIL PROTECTED]]Sent: Friday, October 18, 2002 4:49 PMTo: Multiple recipients of list ORACLE-LSubject: Re: Archive files and their Management Our policy is to keep archive for the last 2 days at least. We are doing daily backup so it gives us the option to restore from the last backup or the one before without restoring archive logs. Yechiel AdarMehish - Original Message - From: Denham Eva To: Multiple recipients of list ORACLE-L Sent: Friday, October 18, 2002 12:28 PM Subject: Archive files and their Management Greetings Gurus Just wondering... Our archive log's directory has grown substantially and space is becoming an issue. How do you know which archive files is safe to delete? In other words... Do you delete all archive files older than the last backup? Should you keep all archive files until it is obviously pointless? Please advise. Many thanks Denham Eva Oracle DBA "UNIX is basically a simple operating system, but you have to be a genius to understand the simplicity." Dennis Ritchie. DISCLAIMER This message is for the named person's use only. It may contain confidential, proprietary or legally privileged information. No confidentiality or privilege is waived or lost by any mistransmission. If you receive this message in error, please immediately delete it and all copies of it from your system, destroy any hard copies of it and notify the sender. You must not, directly or indirectly, use, disclose, distribute, print, or copy any part of this message if you are not the intended recipient. TFMC, its holding company, and any of its subsidiaries each reserve the right to monitor and manage all e-mail communications through its networks. Any views expressed in this message are those of the individual sender, except where the message states otherwise and the sender is authorized to state them to be views of any such entity. This e-mail message has been scanned for Viruses and Content and cleared by MailMarshal - For more information please visit www.marshalsoftware.com
Oracle 11i DBA position in Wisconsin..Position Re-opened
If outdoor life is your thing then consider this company in the Wisconsin Dells, Wisconsin area that needs an Oracle 11i DBA to join their staff. This area is well known for its' boating, fishing, camping, etc. Relocation Assistance is provided. PLEASE DO NOT send your resume for this position UNLESS you 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 H-1B candidates please. *Requirements: -Degree preferred. -Solid Oracle DBA experience that MUST include at least 1 yr with 11i. -Must be a U.S. citizen or perm. resident Base Salary is open and depends on experience. For immediate consideration, please email your resume as an attachment to: OraStaff, Inc. Email: [EMAIL PROTECTED] Phone: 1-800-549-8502. Please Use Job Code: One/Wisconsin Dells/11i DBA/Judy I pay referral fees. So please contact me if you know of anyone who would be qualified/interested in the position described above- if it is not a match for your skills. Thanks. -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: OraStaff INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: Specify Rollback for an Export
You try using direct=y on the export to see if it speeds things up enough that you don't get shot down. There are some cases where you can't do this (I would have to look it up to get them all): For example, if you are using the query= parameter. But most of the time, you can greatly reduce the export time using the direct=y parameter. -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Stephen Lee INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Re: OEM can't seem to discover 1 instance
Beth, I heard that the new project manager is Gaja. We can expect a better product from him and his team. Ruth - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Friday, October 18, 2002 11:54 AM I agree with Jay. Everytime I use OEM there are 10 or 20 annoying little things that make it incredibly frustrating to use. Jared mentioned a while back that the new product manager for OEM is on this list. Maybe he could set up something (like a *big* mailbox) to take suggestions for changes. I would love to bag it completely but we're stuck with it because of the integration with OWB, for a little while longer anyway. -Original Message- Sent: Friday, October 18, 2002 11:24 AM To: Multiple recipients of list ORACLE-L Scrap OEM. Seriously, we ran into way too many of these little annoying problems. We decided that we had better things to do with our time. Have you had to re-register all of your jobs, yet? That one was a real pain. Jay [EMAIL PROTECTED] 10/18/02 09:38AM Hmm, thanks to all who are replying. It looks like the instance is not making it into either snmp_rw.ora, snmp_ro.ora or the services.ora. I have manually edited them, but when I restart the agent, it wipes out the manual entries in the snmp_ro.ora and the services.ora. The oratab and listener look fine. Any more good advice? TIA, John P Weatherman Database Administrator Replacements Ltd. **DISCLAIMER This e-mail message and any files transmitted with it are intended for the use of the individual or entity to which they are addressed and may contain information that is privileged, proprietary and confidential. If you are not the intended recipient, you may not use, copy or disclose to anyone the message or any information contained in the message. If you have received this communication in error, please notify the sender and delete this e-mail message. The contents do not represent the opinion of DE except to the extent that it relates to their official business. -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Jay Hostetter INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Seefelt, Beth INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Ruth Gramolini INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Re: Please help, comment required urgently
What would concern me is the 99.x% buffer cache hit ratio along with an apparently huge number of LIO's per second. This database is spinning its wheels, due to poor SQL and/or poorly written procedural code. That's my 30 second analysis. :) Is this a commercial or custom app? Jared Leonard, George [EMAIL PROTECTED] Sent by: [EMAIL PROTECTED] 10/18/2002 01:53 AM Please respond to ORACLE-L To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] cc: Subject:Please help, comment required urgently Hi guys, I need a second opinion on the following Statspack output, I got my suspicions but my manager and the client is not buying what I am say, Not knowing anything of the system architecture please look at the output and say what would concern you. What assumptions/recommendations you would make. Thx George George Leonard Oracle Database Administrator Dimension Data (Pty) Ltd (Reg. No. 1987/006597/07) Tel: (+27 11) 575 0573 Fax: (+27 11) 576 0573 E-mail:[EMAIL PROTECTED] Web: http://www.didata.co.za You Have The Obligation to Inform One Honestly of the risk, And As a Person You Are Committed to Educate Yourself to the Total Risk In Any Activity! Once Informed Totally Aware of the Risk, Every Fool Has the Right to Kill or Injure Themselves as They See Fit! 7-8.lst Description: Binary data
RE: Archive files and their Management
I'm not quite sure what you mean by modify the database startup procedure. You must be in MOUNT mode to change between archive and noarchive. You don't need to change the mode each time you startup you database. Once you put a database in archivelog mode, you are in that mode until you change it - no matter how many startup/shutdowns you do. It doesn't matter if you have a cold vs. hot backup. As long as you have database backup and all of your archivelogs, you can do a point-in-time recovery from that backup until your last archivelog. Jay [EMAIL PROTECTED] 10/18/02 12:09PM I'm up for my backup and recovery class here soon, but this conversation has brought up questions that I think some of you wouldn't mind discussing anyway. I see where implementing archive logs requires that you modify the database startup procedure to turn on archiving before opening the database. At this point the DBA should do an immediate baseline backup (orafaq =)) which implies a hot/online backup. I am curious how this goes with point in time cold backups. ie. DBA does a cold backup opens the database in archive log mode. Can you apply accumulated archive logs to a restore from cold backup ? perhaps there are issues with the control file information. -Original Message- Sent: Friday, October 18, 2002 7:53 AM To: Multiple recipients of list ORACLE-L theoretically only the archive files after the backup started are needed. but there might be issues of a corrupted backup, and to guard against that we have a policy of keeping archive files for one week. you should also keep checking your backups by restoring them to a separate location periodically regards Naveen **DISCLAIMER This e-mail message and any files transmitted with it are intended for the use of the individual or entity to which they are addressed and may contain information that is privileged, proprietary and confidential. If you are not the intended recipient, you may not use, copy or disclose to anyone the message or any information contained in the message. If you have received this communication in error, please notify the sender and delete this e-mail message. The contents do not represent the opinion of DE except to the extent that it relates to their official business. -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Jay Hostetter INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Re:Resource Management
Kirti, I've toyed with the product due to a perceived need here. The experiment was to have two users loggin to the database both running very nasty queries (we have the GOD of obfuscated SQL here). On the first pass, with no resource management in place both queries ran successfully in 30 minutes but boy did the server take a hit (4 processors with 10% idle across them). On the second pass we put resource management in place for one user really clamping down on it. The first user's query completed in 10 minutes where as the second user took 45 minutes to complete and the server didn't even break a sweat (one processor was 100% idle throughout). I'd like to do more with it, but PeopleSoft does things in a way the precludes resource manager from being effective. Dick Goulet Reply Separator Author: Deshpande; Kirti [EMAIL PROTECTED] Date: 10/18/2002 8:00 AM Hello Listers, Anyone using Oracle's Database Resource Manager? Does it really work as outlined in the Docs? Care to share your experience? Thanks, - Kirti -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Deshpande, Kirti INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: Archive files and their Management
Title: Archive files and their Management The key to the issue is very simple, Redo Logs (including archived) contain information that cannot be recovered by other means (unless you capture EVERY statement issued). As such, they are to be protected at all costs. The vast majority of unrecoverable databases that I have dealt with have not been because of data files or control file problems, but archived redo logs. If you have redo logs, you can recreate database files that are empty and apply all tx to them. If you are missing a log or it cannot be read, your recovery stops at that point. It is possible to open a database with out having recovered all tx, but this is not a production level scenario...it is a 'we have tried everything else and if this doesn't work I'm Flipping Burgers at McDonalds. So, off my soapbox... Uncompressed archive logs are on at least 2 different backup tapes/alternate media Compressed archive logs are on at least 3 different backup tapes/alternate media Once an archive log has been backed up 5 times, it can be deleted. Dan Fink -Original Message-From: Denham Eva [mailto:[EMAIL PROTECTED]]Sent: Friday, October 18, 2002 4:29 AMTo: Multiple recipients of list ORACLE-LSubject: Archive files and their Management Greetings Gurus Just wondering... Our archive log's directory has grown substantially and space is becoming an issue. How do you know which archive files is safe to delete? In other words... Do you delete all archive files older than the last backup? Should you keep all archive files until it is obviously pointless? Please advise. Many thanks Denham Eva Oracle DBA "UNIX is basically a simple operating system, but you have to be a genius to understand the simplicity." Dennis Ritchie. DISCLAIMER This message is for the named person's use only. It may contain confidential, proprietary or legally privileged information. No confidentiality or privilege is waived or lost by any mistransmission. If you receive this message in error, please immediately delete it and all copies of it from your system, destroy any hard copies of it and notify the sender. You must not, directly or indirectly, use, disclose, distribute, print, or copy any part of this message if you are not the intended recipient. TFMC, its holding company, and any of its subsidiaries each reserve the right to monitor and manage all e-mail communications through its networks. Any views expressed in this message are those of the individual sender, except where the message states otherwise and the sender is authorized to state them to be views of any such entity. This e-mail message has been scanned for Viruses and Content and cleared by MailMarshal - For more information please visit www.marshalsoftware.com
RE: CA's AutoSys
Title: RE: CA's AutoSys I too had issues with this product... But, it was several years ago... Back then it was owned by Platinum and the product ran on NT 3.51... We were using it to schedule all our Unix batch jobs for the Sales Force Automation project I was working on... Unfortunately, I was the first projectusing it at the company and had to suffer through many of the issues detailed below... If I remember correctly, it eventually got to a "workable" state after acouple months of patches and workarounds... I remember one job in particular... PSFUNX35...Every Friday night/Saturday morning about 3:00 AM I would get a call from the operations center in Parkersburg, WV... "Hello Tim... This is Jack in Parkersburg... Sorry to bother you... But, PS - FUN - X35 failed again..." He used to pronounce the job as... PS[break] FUN[break] X35... Needless to say it was not very fun... :-) Tim -Original Message-From: Adams, Matthew (GECP, MABG, 088130) [mailto:[EMAIL PROTECTED]]Sent: Friday, October 18, 2002 12:19 PMTo: Multiple recipients of list ORACLE-LSubject: RE: CA's AutoSys Run Screaming in the opposite direction. Product was a semi-disaster at two previous job sites. Failed to run jobs when scheduled, failed to detected when dependent jobs failed. Just plain didn't work well. Of course this was when Platinum owned the product, who knows, maybe CA improved it. (although I would doubt it). Matt Adams - GE Appliances - [EMAIL PROTECTED] Write a poem about a haircut! But lofty, noble, tragic, full of love, treachery, retribution, quiet heroism in the face of certain doom! Six lines, cleverly rhymed, and every word beginning with the letter s! -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]] Sent: Friday, October 18, 2002 11:50 AM To: Multiple recipients of list ORACLE-L Subject: CA's AutoSys To All, I'll admit this is somewhat off-topic, but since the project is going to handle all scheduled batch jobs that bang on our PeopleSoft Oracle database, I figured I'd ask if anyone has an opinion preferably from experience using the product. The Complete A^^holes are here today to do a demo install so any input you all have would be appreciated. Thanks in advance Dick Goulet -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: INET: [EMAIL PROTECTED] Fat City Network Services -- 858-538-5051 http://www.fatcity.com San Diego, California -- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Re: Please help, comment required urgently
I just ran the statspack output through OraPerf as well. This is truly an amazing resource. Jared Tim Gorman [EMAIL PROTECTED] Sent by: [EMAIL PROTECTED] 10/18/2002 09:19 AM Please respond to ORACLE-L To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] cc: Subject:Re: Please help, comment required urgently To give credit where credit is due... I've never bothered to learn how to interpret a STATSPACK report; I've left that with what little I ever knew about interpreting a BSTAT/ESTAT report. Rather, I just send the report to the YAPP report post-processor at www.oraperf.com and it formats everything in such a way that things just jump out at you. It is quite instructive to de-construct the resulting YAPP report back to the original STATSPACK and BSTAT/ESTAT report (and thence further back to the originating V$ views). Big-time kudos to Anjo and friends for creating and maintaining this invaluable resource!!! - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Friday, October 18, 2002 8:34 AM Thanks for your contributions on this Tim (and on everything else you respond to). These little tips of how to analyze statspack reports properly all add up and whilst I did look at the report and I did glean some of you what suggested I certainly did not pick up all that you spotted John -Original Message- Sent: 18 October 2002 14:54 To: Multiple recipients of list ORACLE-L George, Two things jump out together: * The SQL statement with hash value = 3509998681 is consuming about 25% of the total response-time (i.e. total processing plus total wait) on the system. This SQL statement is executing 900 times during the one-hour sample period... * Waits on the cache buffers chains are consuming another 16% of total response-time With these two things consuming 41% of everything consumed by the database instance during this time period, there is no chance that anything else is more important... Chances are excellent that these two things are related. Since the SQL statement has over 329m buffer gets and about 0.5m buffer cache-misses (i.e. physical reads) to it's credit, this indicates a buffer-cache hit-ratio of over 99.7%, which is sure proof that something is seriously wrong! :-) My guess is that the query is using an inappropriate and/or inefficient index for a long, long, long range-scan operation, which is racking up all of those buffer gets. What do you expect from the rule-based optimizer? If you were running CBO and this happened, I'd suggest gathering column-level histogram statistics on the table. My guess also is that many concurrent users are running this statement during the course of the sample period, causing the latch contention for cache buffers in the Buffer Cache, thus the relationship between the two symptoms? I can see that DB_CACHE_LRU_LATCHES has been pushed up to 48; don't know what CPU_COUNT is, but obviously this change has had zero impact on the latch contention problem. Tuning the SQL will fix the problem; accomodating the problem by configuring more latches has no impact. Tuning that one SQL statement (plus a few of it's look-alikes, also listed in the report) will resolve the major performance issues you are experiencing. In fact, it will have a miraculous impact... Hope this helps... -Tim - Original Message - [EMAIL PROTECTED] To: Multiple recipients of list ORACLE-L mailto:ORACLE-L;fatcity.com [EMAIL PROTECTED] Sent: Friday, October 18, 2002 2:53 AM Hi guys, I need a second opinion on the following Statspack output, I got my suspicions but my manager and the client is not buying what I am say, Not knowing anything of the system architecture please look at the output and say what would concern you. What assumptions/recommendations you would make. Thx George George Leonard Oracle Database Administrator Dimension Data (Pty) Ltd (Reg. No. 1987/006597/07) Tel: (+27 11) 575 0573 Fax: (+27 11) 576 0573 E-mail:[EMAIL PROTECTED] Web:http://www.didata.co.za http://www.didata.co.za You Have The Obligation to Inform One Honestly of the risk, And As a Person You Are Committed to Educate Yourself to the Total Risk In Any Activity! Once Informed Totally Aware of the Risk, Every Fool Has the Right to Kill or Injure Themselves as They See Fit! -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the
Re: OEM can't seem to discover 1 instance
Gaja is the new project manager. some of the people also working on the product are John Beresniewicz and (I believe) James Morle. Should be a really good product when they are done with it --- Ruth Gramolini [EMAIL PROTECTED] wrote: Beth, I heard that the new project manager is Gaja. We can expect a better product from him and his team. Ruth - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Friday, October 18, 2002 11:54 AM I agree with Jay. Everytime I use OEM there are 10 or 20 annoying little things that make it incredibly frustrating to use. Jared mentioned a while back that the new product manager for OEM is on this list. Maybe he could set up something (like a *big* mailbox) to take suggestions for changes. I would love to bag it completely but we're stuck with it because of the integration with OWB, for a little while longer anyway. -Original Message- Sent: Friday, October 18, 2002 11:24 AM To: Multiple recipients of list ORACLE-L Scrap OEM. Seriously, we ran into way too many of these little annoying problems. We decided that we had better things to do with our time. Have you had to re-register all of your jobs, yet? That one was a real pain. Jay [EMAIL PROTECTED] 10/18/02 09:38AM Hmm, thanks to all who are replying. It looks like the instance is not making it into either snmp_rw.ora, snmp_ro.ora or the services.ora. I have manually edited them, but when I restart the agent, it wipes out the manual entries in the snmp_ro.ora and the services.ora. The oratab and listener look fine. Any more good advice? TIA, John P Weatherman Database Administrator Replacements Ltd. **DISCLAIMER This e-mail message and any files transmitted with it are intended for the use of the individual or entity to which they are addressed and may contain information that is privileged, proprietary and confidential. If you are not the intended recipient, you may not use, copy or disclose to anyone the message or any information contained in the message. If you have received this communication in error, please notify the sender and delete this e-mail message. The contents do not represent the opinion of DE except to the extent that it relates to their official business. -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Jay Hostetter INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Seefelt, Beth INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Ruth Gramolini INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). __ Do you Yahoo!? Faith Hill - Exclusive Performances, Videos More http://faith.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 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line
RE: Archive files and their Management
Title: RE: Archive files and their Management ok thanks for the info. The following was MISLEADING: http://www.orafaq.com/faqdbabr.htm Section: How does one put a database into ARCHIVELOG mode? To enable ARCHIVELOG mode, simple change your database startup command script, and bounce the database I took that as every startup would need to include the new commands. -Original Message- From: Jay Hostetter [mailto:[EMAIL PROTECTED]] Sent: Friday, October 18, 2002 12:49 PM To: Multiple recipients of list ORACLE-L Subject: RE: Archive files and their Management I'm not quite sure what you mean by modify the database startup procedure. You must be in MOUNT mode to change between archive and noarchive. You don't need to change the mode each time you startup you database. Once you put a database in archivelog mode, you are in that mode until you change it - no matter how many startup/shutdowns you do. It doesn't matter if you have a cold vs. hot backup. As long as you have database backup and all of your archivelogs, you can do a point-in-time recovery from that backup until your last archivelog. Jay [EMAIL PROTECTED] 10/18/02 12:09PM I'm up for my backup and recovery class here soon, but this conversation has brought up questions that I think some of you wouldn't mind discussing anyway. I see where implementing archive logs requires that you modify the database startup procedure to turn on archiving before opening the database. At this point the DBA should do an immediate baseline backup (orafaq =)) which implies a hot/online backup. I am curious how this goes with point in time cold backups. ie. DBA does a cold backup opens the database in archive log mode. Can you apply accumulated archive logs to a restore from cold backup ? perhaps there are issues with the control file information. -Original Message- Sent: Friday, October 18, 2002 7:53 AM To: Multiple recipients of list ORACLE-L theoretically only the archive files after the backup started are needed. but there might be issues of a corrupted backup, and to guard against that we have a policy of keeping archive files for one week. you should also keep checking your backups by restoring them to a separate location periodically regards Naveen **DISCLAIMER This e-mail message and any files transmitted with it are intended for the use of the individual or entity to which they are addressed and may contain information that is privileged, proprietary and confidential. If you are not the intended recipient, you may not use, copy or disclose to anyone the message or any information contained in the message. If you have received this communication in error, please notify the sender and delete this e-mail message. The contents do not represent the opinion of DE except to the extent that it relates to their official business. -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Jay Hostetter INET: [EMAIL PROTECTED] Fat City Network Services -- 858-538-5051 http://www.fatcity.com San Diego, California -- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: CA's AutoSys
We use CA-Workload which I think is the same product packaged differently. We schedule about 400 jobs each night through it. The jobs run on NT, VMS, Oracle, and Oracle Express with interdependencies between jobs on different platforms. I have to say it works pretty well, but only after about a year of really banging at it to get it just right. It will do what they say it will, but takes a fair amount of (human) resources to get there if your jobstream is large and complex. And dealing with CA is no picnic either... -Original Message- Sent: Friday, October 18, 2002 11:50 AM To: Multiple recipients of list ORACLE-L To All, I'll admit this is somewhat off-topic, but since the project is going to handle all scheduled batch jobs that bang on our PeopleSoft Oracle database, I figured I'd ask if anyone has an opinion preferably from experience using the product. The Complete A^^holes are here today to do a demo install so any input you all have would be appreciated. Thanks in advance Dick Goulet -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Seefelt, Beth INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: CA's AutoSys
We too make extensive use of the product, though our team does not need to maintain it. Just the backend database. Seems to work as intended, alerting the operations staff in case of failures. I have not heard the folks trash this product. Though, I have had them ask me for JIL scripts, whatever that means, to setup jobs, at which point, I go to my good old cron. Raj Deshpande, Kirti kirti.deshpande@veTo: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] rizon.com cc: Sent by: Subject: RE: CA's AutoSys [EMAIL PROTECTED] October 18, 2002 12:39 PM Please respond to ORACLE-L Dick, We have been using that product for quite some time now... and it does a pretty good job of scheduling things. In our environment *all* jobs are handled by Autosys, and we are not supposed to use 'cron'. We use the product for scheduling jobs on a variety of h/w platforms( IBM-AIX, HP, Sun, SGI etc). It does need some dedicated resources to manage a large and dynamic environment. In our case, Operations and Production Control group has 2-3 FTEs maintaining the job schedules. The database used by Autosys is an Oracle 8i database and we have a Senior DBA responsible for it, as it is one of the *most critical* databases in the Company. - Kirti -Original Message- Sent: Friday, October 18, 2002 10:50 AM To: Multiple recipients of list ORACLE-L To All, I'll admit this is somewhat off-topic, but since the project is going to handle all scheduled batch jobs that bang on our PeopleSoft Oracle database, I figured I'd ask if anyone has an opinion preferably from experience using the product. The Complete A^^holes are here today to do a demo install so any input you all have would be appreciated. Thanks in advance Dick Goulet -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Oracle OCP Self-Study CDROM's
Has anyone used Oracle's self-study CDROM's to prepare for the OCA / OCP exams? If so, I would like to know what you think of them. Thanks, Ken Janusz, CPIM
Re: Re:Resource Management
Dick, Which version of Oracle ? Was your test with 2 different user ID's ? Our problem is that all users log on with the same user id, which seems to diminish the product's effectiveness. thanks. ed - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Friday, October 18, 2002 12:54 PM Kirti, I've toyed with the product due to a perceived need here. The experiment was to have two users loggin to the database both running very nasty queries (we have the GOD of obfuscated SQL here). On the first pass, with no resource management in place both queries ran successfully in 30 minutes but boy did the server take a hit (4 processors with 10% idle across them). On the second pass we put resource management in place for one user really clamping down on it. The first user's query completed in 10 minutes where as the second user took 45 minutes to complete and the server didn't even break a sweat (one processor was 100% idle throughout). I'd like to do more with it, but PeopleSoft does things in a way the precludes resource manager from being effective. Dick Goulet Reply Separator Author: Deshpande; Kirti [EMAIL PROTECTED] Date: 10/18/2002 8:00 AM Hello Listers, Anyone using Oracle's Database Resource Manager? Does it really work as outlined in the Docs? Care to share your experience? Thanks, - Kirti -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Deshpande, Kirti INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Ed Lewis INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: OEM can't seem to discover 1 instance
Gaja is not active on the list due to his new workload... ... but he receives 'oracle-l updates' whenever we talk ;) - Kirti -Original Message- Sent: Friday, October 18, 2002 10:54 AM To: Multiple recipients of list ORACLE-L I agree with Jay. Everytime I use OEM there are 10 or 20 annoying little things that make it incredibly frustrating to use. Jared mentioned a while back that the new product manager for OEM is on this list. Maybe he could set up something (like a *big* mailbox) to take suggestions for changes. I would love to bag it completely but we're stuck with it because of the integration with OWB, for a little while longer anyway. -Original Message- Sent: Friday, October 18, 2002 11:24 AM To: Multiple recipients of list ORACLE-L Scrap OEM. Seriously, we ran into way too many of these little annoying problems. We decided that we had better things to do with our time. Have you had to re-register all of your jobs, yet? That one was a real pain. Jay [EMAIL PROTECTED] 10/18/02 09:38AM Hmm, thanks to all who are replying. It looks like the instance is not making it into either snmp_rw.ora, snmp_ro.ora or the services.ora. I have manually edited them, but when I restart the agent, it wipes out the manual entries in the snmp_ro.ora and the services.ora. The oratab and listener look fine. Any more good advice? TIA, John P Weatherman Database Administrator Replacements Ltd. **DISCLAIMER This e-mail message and any files transmitted with it are intended for the use of the individual or entity to which they are addressed and may contain information that is privileged, proprietary and confidential. If you are not the intended recipient, you may not use, copy or disclose to anyone the message or any information contained in the message. If you have received this communication in error, please notify the sender and delete this e-mail message. The contents do not represent the opinion of DE except to the extent that it relates to their official business. -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Jay Hostetter INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Seefelt, Beth INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Deshpande, Kirti INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: Please help, comment required urgently
HELP (I'm an idiot) OK, never claimed math was my strong suit. Why is this 25% of total response time? (Truthfully, I don't really understand the column heading of total.. Here are the top SQL statements ordered by physical reads per execute: StatementExecutes Physical Reads Hash Value Of Total Reads/Execute SELECT T5.CONFL 903549033608.01 3509998681 47.31 % Barb -- From: Tim Gorman[SMTP:[EMAIL PROTECTED]] Reply To: [EMAIL PROTECTED] Sent: Friday, October 18, 2002 7:53 AM To: Multiple recipients of list ORACLE-L Subject: Re: Please help, comment required urgently George, Two things jump out together: * The SQL statement with hash value = 3509998681 is consuming about 25% of the total response-time (i.e. total processing plus total wait) on the system. This SQL statement is executing 900 times during the one-hour sample period... * Waits on the cache buffers chains are consuming another 16% of total response-time With these two things consuming 41% of everything consumed by the database instance during this time period, there is no chance that anything else is more important... Chances are excellent that these two things are related. Since the SQL statement has over 329m buffer gets and about 0.5m buffer cache-misses (i.e. physical reads) to it's credit, this indicates a buffer-cache hit-ratio of over 99.7%, which is sure proof that something is seriously wrong! :-) My guess is that the query is using an inappropriate and/or inefficient index for a long, long, long range-scan operation, which is racking up all of those buffer gets. What do you expect from the rule-based optimizer? If you were running CBO and this happened, I'd suggest gathering column-level histogram statistics on the table. My guess also is that many concurrent users are running this statement during the course of the sample period, causing the latch contention for cache buffers in the Buffer Cache, thus the relationship between the two symptoms? I can see that DB_CACHE_LRU_LATCHES has been pushed up to 48; don't know what CPU_COUNT is, but obviously this change has had zero impact on the latch contention problem. Tuning the SQL will fix the problem; accomodating the problem by configuring more latches has no impact. Tuning that one SQL statement (plus a few of it's look-alikes, also listed in the report) will resolve the major performance issues you are experiencing. In fact, it will have a miraculous impact... Hope this helps... -Tim - Original Message - From: Leonard, George [EMAIL PROTECTED] To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Friday, October 18, 2002 2:53 AM Subject: Please help, comment required urgently Hi guys, I need a second opinion on the following Statspack output, I got my suspicions but my manager and the client is not buying what I am say, Not knowing anything of the system architecture please look at the output and say what would concern you. What assumptions/recommendations you would make. Thx George George Leonard Oracle Database Administrator Dimension Data (Pty) Ltd (Reg. No. 1987/006597/07) Tel: (+27 11) 575 0573 Fax: (+27 11) 576 0573 E-mail:[EMAIL PROTECTED] Web: http://www.didata.co.za You Have The Obligation to Inform One Honestly of the risk, And As a Person You Are Committed to Educate Yourself to the Total Risk In Any Activity! Once Informed Totally Aware of the Risk, Every Fool Has the Right to Kill or Injure Themselves as They See Fit! -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Baker, Barbara INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
IN vs. EXISTS
Hi All, I have 2 tables BIG - 100 million records SMALL - 1 million records. I want to delete all the records in BIG that are in small. There is a PK on field1. Which of the below methods would you choose and why? DELETE FROM big WHERE field1 IN (SELECT field1 FROM small); DELETE FROM big a WHERE EXISTS (SELECT 1 FROM small B WHERE b.field1 = a.field1); Thanks Rick -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Re[2]: Re:Resource Management
Ed, The version is 8.1.6 and yes it was with two different usernames. In the end since we wanted to apply this to PeopleSoft where everyone is the same userid it was impossible too. Still looking for another solution. Dick Goulet Reply Separator Author: Ed Lewis [EMAIL PROTECTED] Date: 10/18/2002 10:12 AM Dick, Which version of Oracle ? Was your test with 2 different user ID's ? Our problem is that all users log on with the same user id, which seems to diminish the product's effectiveness. thanks. ed - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Friday, October 18, 2002 12:54 PM Kirti, I've toyed with the product due to a perceived need here. The experiment was to have two users loggin to the database both running very nasty queries (we have the GOD of obfuscated SQL here). On the first pass, with no resource management in place both queries ran successfully in 30 minutes but boy did the server take a hit (4 processors with 10% idle across them). On the second pass we put resource management in place for one user really clamping down on it. The first user's query completed in 10 minutes where as the second user took 45 minutes to complete and the server didn't even break a sweat (one processor was 100% idle throughout). I'd like to do more with it, but PeopleSoft does things in a way the precludes resource manager from being effective. Dick Goulet Reply Separator Author: Deshpande; Kirti [EMAIL PROTECTED] Date: 10/18/2002 8:00 AM Hello Listers, Anyone using Oracle's Database Resource Manager? Does it really work as outlined in the Docs? Care to share your experience? Thanks, - Kirti -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Deshpande, Kirti INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Ed Lewis INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: Specify Rollback for an Export
Thank you all for your feedback. I will try again tonight with the following changes 1) take all rb segments off line except the large one 2) set the buffer to 1000 (was default before). I will let you know how things went (or didnt) on Monday morning. Thanks again. Erik -Original Message- Sent: Friday, October 18, 2002 10:14 AM To: Multiple recipients of list ORACLE-L I am performing an export of a set of very large tables. I am not using CONSISTENT mode. The exports are failing due to snapshot too old errors. The application uses many small rollback segments, but has one large one for big transactions. Is it possible to force the export to use the large segment without taking all the others (except System) offline? I have seen this question asked on the list before, but did not find a definitive follow-up answer. Thanks Erik -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Erik Williams INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Erik Williams INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: IN vs. EXISTS
Rick - I would check the EXPLAIN PLAN for both queries, and see whether Oracle uses a different query plan. Usually the prescription for better SQL performance is to avoid NOT IN, as the following snippet that I snagged off the 'net explains. Use NOT EXISTS in Place of NOT IN In sub-query statements such as the following, the NOT IN clause causes an internal sort/merge. The NOT IN clause is the all-time slowest test possible as it forces a full read of the table in the sub-query SELECT. Avoid using NOT IN clause either by replacing it with Outer Joins or with a NOT EXISTS clause Dennis Williams DBA, 40%OCP Lifetouch, Inc. [EMAIL PROTECTED] -Original Message- Sent: Friday, October 18, 2002 1:32 PM To: Multiple recipients of list ORACLE-L Hi All, I have 2 tables BIG - 100 million records SMALL - 1 million records. I want to delete all the records in BIG that are in small. There is a PK on field1. Which of the below methods would you choose and why? DELETE FROM big WHERE field1 IN (SELECT field1 FROM small); DELETE FROM big a WHERE EXISTS (SELECT 1 FROM small B WHERE b.field1 = a.field1); Thanks Rick -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: DENNIS WILLIAMS INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: Re[2]: Re:Resource Management
Dick - Thanks for posting your results. We have a different ERP (Lawson), and we have been considering whether Resource Management is worth the effort. Currently all users access Oracle through a single userid, but there is an option for each user to have a separate login. The price is that you then have to create and maintain many userids. Dennis Williams DBA, 40%OCP Lifetouch, Inc. [EMAIL PROTECTED] -Original Message- Sent: Friday, October 18, 2002 1:32 PM To: Multiple recipients of list ORACLE-L Ed, The version is 8.1.6 and yes it was with two different usernames. In the end since we wanted to apply this to PeopleSoft where everyone is the same userid it was impossible too. Still looking for another solution. Dick Goulet Reply Separator Author: Ed Lewis [EMAIL PROTECTED] Date: 10/18/2002 10:12 AM Dick, Which version of Oracle ? Was your test with 2 different user ID's ? Our problem is that all users log on with the same user id, which seems to diminish the product's effectiveness. thanks. ed - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Friday, October 18, 2002 12:54 PM Kirti, I've toyed with the product due to a perceived need here. The experiment was to have two users loggin to the database both running very nasty queries (we have the GOD of obfuscated SQL here). On the first pass, with no resource management in place both queries ran successfully in 30 minutes but boy did the server take a hit (4 processors with 10% idle across them). On the second pass we put resource management in place for one user really clamping down on it. The first user's query completed in 10 minutes where as the second user took 45 minutes to complete and the server didn't even break a sweat (one processor was 100% idle throughout). I'd like to do more with it, but PeopleSoft does things in a way the precludes resource manager from being effective. Dick Goulet Reply Separator Author: Deshpande; Kirti [EMAIL PROTECTED] Date: 10/18/2002 8:00 AM Hello Listers, Anyone using Oracle's Database Resource Manager? Does it really work as outlined in the Docs? Care to share your experience? Thanks, - Kirti -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Deshpande, Kirti INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Ed Lewis INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L
Data Guard
Hi I have setup dataguard on v8.1.7.4.I have been noticing some times the same archived log files shipped multiple at standby archival destination like arch_1_18959.arc arch_1_18959.arc.ODGship.ODGbad arch_1_18959.arc.ODGbad and some of message in data guard logs. No messages in either oth alert log . What is this message means ? Thx -Seema _ Get a speedy connection with MSN Broadband. Join now! http://resourcecenter.msn.com/access/plans/freeactivation.asp -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Seema Singh INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: Specify Rollback for an Export
Erik - Here is another thought for you. If the table you are exporting is that large, how long will it take you to import it somewhere? Import can be several times longer than export, since it is doing inserts rather than just a query. Tables that take hours to export can take days to import. What is your goal with this export? One thought is to break the export into several files by exporting based on a SQL statement. Then you can either just export the data you need or if you need it all, on a multi-CPU system you can run several import jobs at the same time. Dennis Williams DBA, 40%OCP Lifetouch, Inc. [EMAIL PROTECTED] -Original Message- Sent: Friday, October 18, 2002 2:03 PM To: Multiple recipients of list ORACLE-L Thank you all for your feedback. I will try again tonight with the following changes 1) take all rb segments off line except the large one 2) set the buffer to 1000 (was default before). I will let you know how things went (or didnt) on Monday morning. Thanks again. Erik -Original Message- Sent: Friday, October 18, 2002 10:14 AM To: Multiple recipients of list ORACLE-L I am performing an export of a set of very large tables. I am not using CONSISTENT mode. The exports are failing due to snapshot too old errors. The application uses many small rollback segments, but has one large one for big transactions. Is it possible to force the export to use the large segment without taking all the others (except System) offline? I have seen this question asked on the list before, but did not find a definitive follow-up answer. Thanks Erik -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Erik Williams INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Erik Williams INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: DENNIS WILLIAMS INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: IN vs. EXISTS
Dennis, I have read about NOT IN. Oracle doc says the following but not conclusive In certain circumstances, it is better to use IN rather than EXISTS. In general, if the selective predicate is in the subquery, then use IN. If the selective predicate is in the parent query, then use EXISTS. It appears in my ex. using exists that Oracle will evaluate all 100 million records to see if it gets deleted Whereas the WHERE IN will use the PK index to locate the rows to be deleted Thanks Rick DENNIS WILLIAMS DWILLIAMS@LIFE To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] TOUCH.COMcc: Sent by: Subject: RE: IN vs. EXISTS [EMAIL PROTECTED] m 10/18/2002 03:53 PM Please respond to ORACLE-L Rick - I would check the EXPLAIN PLAN for both queries, and see whether Oracle uses a different query plan. Usually the prescription for better SQL performance is to avoid NOT IN, as the following snippet that I snagged off the 'net explains. Use NOT EXISTS in Place of NOT IN In sub-query statements such as the following, the NOT IN clause causes an internal sort/merge. The NOT IN clause is the all-time slowest test possible as it forces a full read of the table in the sub-query SELECT. Avoid using NOT IN clause either by replacing it with Outer Joins or with a NOT EXISTS clause Dennis Williams DBA, 40%OCP Lifetouch, Inc. [EMAIL PROTECTED] -Original Message- Sent: Friday, October 18, 2002 1:32 PM To: Multiple recipients of list ORACLE-L Hi All, I have 2 tables BIG - 100 million records SMALL - 1 million records. I want to delete all the records in BIG that are in small. There is a PK on field1. Which of the below methods would you choose and why? DELETE FROM big WHERE field1 IN (SELECT field1 FROM small); DELETE FROM big a WHERE EXISTS (SELECT 1 FROM small B WHERE b.field1 = a.field1); Thanks Rick -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: DENNIS WILLIAMS INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services
RE: CA's AutoSys
Oh, I don't know about that. At one of the places you used to work, they _finally_ got it to work, but it did need at least one full-time, dedicated employee to monitor, maintain and massage it. Terry Ball, DBA Birch Telecom Work: 816-300-1335 FAX: 816-300-1800 -Original Message- Sent: Friday, October 18, 2002 11:19 AM To: Multiple recipients of list ORACLE-L Run Screaming in the opposite direction. Product was a semi-disaster at two previous job sites. Failed to run jobs when scheduled, failed to detected when dependent jobs failed. Just plain didn't work well. Of course this was when Platinum owned the product, who knows, maybe CA improved it. (although I would doubt it). Matt Adams - GE Appliances - [EMAIL PROTECTED] Write a poem about a haircut! But lofty, noble, tragic, full of love, treachery, retribution, quiet heroism in the face of certain doom! Six lines, cleverly rhymed, and every word beginning with the letter s! -Original Message- Sent: Friday, October 18, 2002 11:50 AM To: Multiple recipients of list ORACLE-L To All, I'll admit this is somewhat off-topic, but since the project is going to handle all scheduled batch jobs that bang on our PeopleSoft Oracle database, I figured I'd ask if anyone has an opinion preferably from experience using the product. The Complete A^^holes are here today to do a demo install so any input you all have would be appreciated. Thanks in advance Dick Goulet -- Please see the official ORACLE-L FAQ: http://www.orafaq.com http://www.orafaq.com -- Author: INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Ball, Terry INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: IN vs. EXISTS
Hello Rick, I think that if you use EXISTS instead of IN the optimizer will have to do a FTS on the big table because he can't use any avaiable index on it. And it's a big table While if you use IN and you have an index in the parent table (the BIG one) this index CAN be used (here, field1 must be indexed),and I think it'll be much faster. Of course it depends on the index's selectivity as well. Also, remember that with the IN operator the subquery is executed just once, and with EXISTS it's executed once by each parent row (so it must execute very efficiently) And that IN can use parent indexes (when avaiable and some conditions are met) and can't use any indexes to resolve the subquery, while EXISTS can't use the parent query indexes and CAN use indexes on the subquery. Please correct me if I'm wrong. HTH Greetings Diego Cutrone Hi All, I have 2 tables BIG - 100 million records SMALL - 1 million records. I want to delete all the records in BIG that are in small. There is a PK on field1. Which of the below methods would you choose and why? DELETE FROM big WHERE field1 IN (SELECT field1 FROM small); DELETE FROM big a WHERE EXISTS (SELECT 1 FROM small B WHERE b.field1 = a.field1); Thanks Rick -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: dcutrone INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: SQL Plus Default Value for 1 Help
The problem comes down to this:
If there is a '' in the script, and there is no value already
assigned to it, sqlplus is going to stop processing, and
wait for user input.
I've never found a way around this.
It would be nice if there were some facility such as all
unix shells have to test for the existence and number
of command line variables.
If this is something you must have, you might want to consider
using Perl to do this, as it's fairly easy there.
Or use something like expect as a frontend to sqlplus, but I think
that's more work than just doing the job in Perl.
Jared
Post, Ethan [EMAIL PROTECTED]
10/18/2002 02:04 PM
To: [EMAIL PROTECTED], [EMAIL PROTECTED]
cc: Robert Fendley (E-mail) [EMAIL PROTECTED]
Subject:RE: SQL Plus Default Value for 1 Help
Thanks Jared, I have been looking at Steve Adams accept.sql script and
seeing if I can tweak that in someway. But I think you are right, this is
a
difficult task indeed.
---
--
-- Script: accept.sql
-- Purpose: to prompt for a script parameter, but allow a default
value
--
-- Copyright: (c) Ixora Pty Ltd
-- Author: Steve Adams
--
-- Synopsis:@accept name prompt default
--
---
accept _value_entered prompt 2 [3]
column _value_returned new_value 1 noprint
set termout off
select nvl('_value_entered', '3') _value_returned from dual;
set termout on
undefine 1 2 3 _value_entered
column _value_returned clear
-Original Message-
Sent: Friday, October 18, 2002 3:58 PM
To: [EMAIL PROTECTED]
Cc: [EMAIL PROTECTED]
Importance: High
Ethan,
I think you're stuck here.
There isn't any memory structure in sqlplus that I'm aware of
that will allow you to check for the existence of variables
passed on the command line, without actually checking the
variable itself.
Below is the method I use for passing command line
variables to sqlplus, and prompting the user if they
don't exist.
column ctest noprint new_value utest
prompt Enter the value:
set feed off echo off term off
select '1' ctest from dual;
set feed on term on
select 'utest' from dual;
I don't see any way to directly supply a default value in sqlplus, though
it
would really be cool if someone else has a way to do so in sqlplus.
This would likely require something like expect, or Perls Telnet
module to catch user input, and timeout if not supplied in a timely
manner.
Jared
Post, Ethan [EMAIL PROTECTED]
Sent by: [EMAIL PROTECTED]
10/18/2002 02:23 PM
Please respond to ORACLE-L
To: Multiple recipients of list ORACLE-L
[EMAIL PROTECTED]
cc:
Subject:SQL Plus Default Value for 1 Help
Anyone got an example for the following...
I have a SQL Script that expects 1 passed into it, however, if 1 is not
supplied I would like to use a default value and not stop to prompt the
user. I am playing around with new_value and select nvl('1','foo') from
dual...etc...and nothing seems to be working.
Thanks,
Ethan Post
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: Post, Ethan
INET: [EMAIL PROTECTED]
Fat City Network Services-- 858-538-5051 http://www.fatcity.com
San Diego, California-- Mailing list and web hosting services
-
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from). You may
also send the HELP command for other information (like subscribing).
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author:
INET: [EMAIL PROTECTED]
Fat City Network Services-- 858-538-5051 http://www.fatcity.com
San Diego, California-- Mailing list and web hosting services
-
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from). You may
also send the HELP command for other information (like subscribing).
RE: ORA-01031: insufficient privileges
Wow. That is a perfectly logical and amazingly simple explanation I would never have thought of. I am in awe. Jim Chris, The answer may be in what occurs below the water. Remember that RESOURCE is a bit special (here for Oracle 5 compatibility reasons), that it is one of the two (I think) 'roles' which cannot be granted to a role and that the reason I suspect it cannot be is that it grants 'unlimited tablespace' underhand. An hypothesis might be that RESOURCE was granted and then UNLIMITED TABLESPACE revoked by a DBA unwilling to see the user pollute any tablespace. It behaves like what you witnessed : SQL create user demo identified by demo; User created. SQL grant create session to demo; Grant succeeded. SQL grant resource to demo with admin option; Grant succeeded. SQL create user demo2 identified by demo2; User created. SQL revoke unlimited tablespace from demo; Revoke succeeded. SQL connect demo/demo Connected. SQL grant resource to demo2; grant resource to demo2 * ERROR at line 1: ORA-01031: insufficient privileges -- Regards, Stephane Faroult Oriole Software -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Stephane Faroult INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Conboy, Jim INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Re: Please help, comment required urgently
Barb, There's nothing in the STATSPACK report itself that mentions 25% or 16% or whatever, so your math skills are just fine... :-) --- It is the YAPP report (obtained by uploading STATSPACK report to full-functionality YAPP processor within www.oraperf.com) that shows these breakdowns. The YAPP report first trims out the time belonging to idle wait-events, with the time for the remaining wait-events plus CPU times from V$SYSSTAT comprising total response-time. This total breaks first down into CPU Time (a.k.a. processing time) and Wait Time (a.k.a. remaining non-idle wait-events). For the YAPP report resulting from the STATSPACK report provided by George, the first breakdown of CPU/Wait was roughly 57% vs 43%, if I remember correctly (I no longer have the email or the report). Drilling down on the CPU Time (i.e. 57%) provided further breakdown into Parse Time (showed less than 1%), Recursive CPU Time (showed less than 1%), and Other CPU Time (showed greater than 99%). So, 99% of the 57% of total response-time was due to Other CPU Time. Drilling down into the details of Other CPU Time showed a top SQL report sorted by buffer gets (a.k.a. logical reads) where one particular SQL statement accounted for about 55% of all buffer gets. So, 57% times 99% times 55% equals roughly 25% as I did it in my head... ...so that's where I got the 25% of total response-time number from... Going in the other direction, remember that 43% of total response-time was Wait Time. Drilling down on that showed the Latch Free wait-event consuming about 40% of all wait-time. Drilling down on the Latch free wait-event showed the cache buffers chains latch consuming 99% of all latch free wait-events. So, 43% times 40% times 99% equals roughly 16% (again, when I do it in my head)... ...and that's where I got the 16% of total response-time number from... --- None of this talk about breakdowns and drilling down makes any sense whatsoever unless you are looking at a YAPP report in front of you. To obtain one, you first have to register for a free account on www.oraperf.com. Once you have obtained a free account, then log in and follow the instructions to upload the STATSPACK report to the website. The website will return an HTML document which is the YAPP report. It is a fully hyperlinked document, so my comments above about drilling down should make more sense when you see the report... Additionally, in the left-hand navigation bar there is a link for white papers. There are only 5-6 white papers currently available, but the first two are the most useful. The first is the YAPP Methodology paper written by Anjo Kolk et al. The second is about using STATSPACK to obtain a YAPP report, by Bjorn Engsig. That's pretty much what we've been discussing here... --- As Jared had commented earlier, this report is the probably the best thing since sliced bread. It is very much a waste of time to attempt to make heads or tails of the standard STATSPACK or BSTAT/ESTAT reports. Instead, upload the text files for those reports up to www.oraperf.com and read a report that makes sense... Hope this helps... -Tim P.S.Again, big thanks to Anjo and his friends for all the hard work in putting together and maintaining this website... - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Friday, October 18, 2002 12:12 PM HELP (I'm an idiot) OK, never claimed math was my strong suit. Why is this 25% of total response time? (Truthfully, I don't really understand the column heading of total.. Here are the top SQL statements ordered by physical reads per execute: Statement Executes Physical Reads Hash Value Of Total Reads/Execute SELECT T5.CONFL 903549033 608.01 3509998681 47.31 % Barb -- From: Tim Gorman[SMTP:[EMAIL PROTECTED]] Reply To: [EMAIL PROTECTED] Sent: Friday, October 18, 2002 7:53 AM To: Multiple recipients of list ORACLE-L Subject: Re: Please help, comment required urgently George, Two things jump out together: * The SQL statement with hash value = 3509998681 is consuming about 25% of the total response-time (i.e. total processing plus total wait) on the system. This SQL statement is executing 900 times during the one-hour sample period... * Waits on the cache buffers chains are consuming another 16% of total response-time With these two things consuming 41% of everything consumed by the database instance during this time period, there is no chance that anything else is more important... Chances are excellent that these two things are related. Since the SQL statement has over 329m buffer gets and about 0.5m buffer cache-misses (i.e. physical reads) to it's credit, this indicates a buffer-cache hit-ratio of over 99.7%, which is sure proof that something is seriously wrong! :-) My guess is that the query is using an inappropriate and/or
*Revised--Oracle DBA with Data Warehouse experience needed in
Established company in San Antonio Texas needs an Oracle DBA with solid Data Warehouse experience to join its IT team. *Candidates living in Texas will be given proirity however some relocation assistance may be available for the right candidate. PLEASE Do Not send your resume for this position UNLESS you have the qualifications for this position. Please 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 H-1B candidates please. Requirements: -Bachelors degree in Computer Science or related business discipline. -Must Have several years experience as an Oracle DBA with Datawarehousing skills. -Performance tuning experience. -Unix platform experience required. -SQL/PLSQL skills. -Works well in groups/teamwork a must. -EXCELLENT communication skills. -U.S. citizenship or permanent residency. The base salary range depends on experience...but in the 70-75K range. Note: San Antonio is a very low cost of living area. For immediate consideration, please send your resume as a Word attachment to: OraStaff, Inc. Email: [EMAIL PROTECTED] Please use job code: One/San Antonio/DBA/Data W./Vicki ph: 1-800 -549-8502 All Submissions are handled in confidence. *We pay referral fees. So please contact us 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. -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: OraStaff INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: Specify Rollback for an Export
Dennis- The export is from a production system and the import is onto a development/reporting system, so the import time is not very important. I am using a QUERY parameter to limit the amount of data, but it is still doing a full table scan to get the rows. I will think about how I can paralleize this operation. I have also increased the BUFFER parameter to 10MB. The export is running right now, so I will know how successful it is in the morning. Thanks again for your help and interest. Erik -Original Message- Sent: Friday, October 18, 2002 3:39 PM To: Multiple recipients of list ORACLE-L Erik - Here is another thought for you. If the table you are exporting is that large, how long will it take you to import it somewhere? Import can be several times longer than export, since it is doing inserts rather than just a query. Tables that take hours to export can take days to import. What is your goal with this export? One thought is to break the export into several files by exporting based on a SQL statement. Then you can either just export the data you need or if you need it all, on a multi-CPU system you can run several import jobs at the same time. Dennis Williams DBA, 40%OCP Lifetouch, Inc. [EMAIL PROTECTED] -Original Message- Sent: Friday, October 18, 2002 2:03 PM To: Multiple recipients of list ORACLE-L Thank you all for your feedback. I will try again tonight with the following changes 1) take all rb segments off line except the large one 2) set the buffer to 1000 (was default before). I will let you know how things went (or didnt) on Monday morning. Thanks again. Erik -Original Message- Sent: Friday, October 18, 2002 10:14 AM To: Multiple recipients of list ORACLE-L I am performing an export of a set of very large tables. I am not using CONSISTENT mode. The exports are failing due to snapshot too old errors. The application uses many small rollback segments, but has one large one for big transactions. Is it possible to force the export to use the large segment without taking all the others (except System) offline? I have seen this question asked on the list before, but did not find a definitive follow-up answer. Thanks Erik -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Erik Williams INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Erik Williams INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: DENNIS WILLIAMS INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Erik Williams INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: CA's AutoSys
Title: RE: CA's AutoSys I think we may be talking about the same place Terry, aren't we? Matt Adams -Original Message- From: Ball, Terry [mailto:[EMAIL PROTECTED]] Sent: Friday, October 18, 2002 4:09 PM To: Multiple recipients of list ORACLE-L Subject: RE: CA's AutoSys Oh, I don't know about that. At one of the places you used to work, they _finally_ got it to work, but it did need at least one full-time, dedicated employee to monitor, maintain and massage it. Terry Ball, DBA Birch Telecom Work: 816-300-1335 FAX: 816-300-1800 -Original Message- Sent: Friday, October 18, 2002 11:19 AM To: Multiple recipients of list ORACLE-L Run Screaming in the opposite direction. Product was a semi-disaster at two previous job sites. Failed to run jobs when scheduled, failed to detected when dependent jobs failed. Just plain didn't work well. Of course this was when Platinum owned the product, who knows, maybe CA improved it. (although I would doubt it). Matt Adams - GE Appliances - [EMAIL PROTECTED] Write a poem about a haircut! But lofty, noble, tragic, full of love, treachery, retribution, quiet heroism in the face of certain doom! Six lines, cleverly rhymed, and every word beginning with the letter s! -Original Message- Sent: Friday, October 18, 2002 11:50 AM To: Multiple recipients of list ORACLE-L To All, I'll admit this is somewhat off-topic, but since the project is going to handle all scheduled batch jobs that bang on our PeopleSoft Oracle database, I figured I'd ask if anyone has an opinion preferably from experience using the product. The Complete A^^holes are here today to do a demo install so any input you all have would be appreciated. Thanks in advance Dick Goulet -- Please see the official ORACLE-L FAQ: http://www.orafaq.com http://www.orafaq.com -- Author: INET: [EMAIL PROTECTED] Fat City Network Services -- 858-538-5051 http://www.fatcity.com http://www.fatcity.com San Diego, California -- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Ball, Terry INET: [EMAIL PROTECTED] Fat City Network Services -- 858-538-5051 http://www.fatcity.com San Diego, California -- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
