DBA_EXTENTS problem
Dear List, When I query dba_extents , Iam getting response after half an hour to 40 minutes, but when I query any other dictionary view it is spontaneous. Everything else is fine in the database and there are no problems, except the above problem. Iam not getting any clue how to fix this. Iam thinking of running catalog.sql and catproc.sql as a last resort. Iam not sure it would fix the problem. Please let me know if there is any other way to fix this problem. Thanks, Babu -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Janardhana Babu Donga INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (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: DBA_EXTENTS problem
Sounds like a fragmentation problem. This will help you identify the segments with a large number of fragments. They are good candidates for reorganization. HTH, Beth select a.tablespace_name as tablespace, cast(a.segment_name as char(30)) as segment, a.partition_name as partition, count(1) as count, decode(b.extent_management,'LOCAL','Y','N') as LMT from dba_extents a, dba_tablespaces b where a.tablespace_name = b.tablespace_name having count(1) 1 group by a.tablespace_name, a.segment_name, a.partition_name, decode(b.extent_management,'LOCAL','Y','N') order by count(1) desc -Original Message- Sent: Thursday, May 09, 2002 1:59 PM To: Multiple recipients of list ORACLE-L Dear List, When I query dba_extents , Iam getting response after half an hour to 40 minutes, but when I query any other dictionary view it is spontaneous. Everything else is fine in the database and there are no problems, except the above problem. Iam not getting any clue how to fix this. Iam thinking of running catalog.sql and catproc.sql as a last resort. Iam not sure it would fix the problem. Please let me know if there is any other way to fix this problem. Thanks, Babu -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Janardhana Babu Donga INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Seefelt, Beth INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Re: OT - Teradata
[EMAIL PROTECTED] wrote: Exactly. ;) Jared geez, guys, you know he's going to believe every word you say.;-) -- -- Bill Shrek Thater ORACLE DBA [EMAIL PROTECTED] You gotta program like you don't need the money, You gotta compile like you'll never get hurt, You gotta run like there's nobody watching, It's gotta come from the heart if you want it to work. LISP: To call a spade a thpade. -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: bill thater INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (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: Cloning Question
Scott - I think that Rachel offered better advice, and I'm sure you've read her note by now. I have some experience with missing files when cloning a database :-), but I haven't had the rollback tablespace missing, and that would be different. One thought would be to create an extra rollback tablespace on production, so that when you perform the clone operation, Oracle still has some rollback segments available. Dennis Williams DBA Lifetouch, Inc. [EMAIL PROTECTED] -Original Message- Sent: Thursday, May 09, 2002 12:03 PM To: Multiple recipients of list ORACLE-L Dennis, No. I did a create controlfile set database... I commented out the rollback_segments line in the init.ora and removed the reference to the file in the create controlfile statement. With no reference (that I know of) to the rollback segments or tablespace, I thought that the database should have come up, then I could create a new one. I must have missed something, but I don't know what that was. DENNIS WILLIAMS wrote: Scott - Let's see if I understand specifically what you did. You ran the CREATE DATABASE script that was generated from your production system. Now, since the datafile for rollback tablespace isn't there, Oracle will come up without it. Once your new instance was up, then you did a DROP TABLESPACE on the rollback tablespace, followed by a CREATE TABLESPACE. I've done that scenario quite a few times on Oracle 8.1.6, just yesterday, as a matter of fact (that was a temp tablespace). If those weren't your steps, then provide more details. Dennis Williams DBA Lifetouch, Inc. [EMAIL PROTECTED] -Original Message- Sent: Thursday, May 09, 2002 8:48 AM To: Multiple recipients of list ORACLE-L I spent a long time trying to clone our production data warehouse into test, to refresh it. I do this frequently, as both a way to refresh test instances and to test the backups. After fighting through 2 bad backups, I finally got a good one. However, the problem I had was that the production rollback tablespace is larger than the disk on test. My thought was that I could restore everything except the rollback tablespace, which is not the way I usually do it. I commented out the rollback_segments line in the init.ora and created a script that would create a new rollback tablespace and rollback segments, smaller than those in production. This didn't work, even though Oracle support said it should have. When I did the clone, the database would not open. I kept getting the following error: ORA-00600: internal error code, arguments: [25012], [1], [2], [], [], [], [], [] Does anyone know what I was doing wrong? I can't figure it out, and Oracle support couldn't figure it out, either. I was able to get the database up by getting more disk added, so that there was enough space to restore the rollback tablespace file. Thank you. -- Scott Canaan ([EMAIL PROTECTED]) (585) 475-7886 Life is like a sewer, what you get out of it depends on what you put into it - Tom Lehrer -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Scott Canaan INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: DENNIS WILLIAMS INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Scott Canaan ([EMAIL PROTECTED]) (585) 475-7886 Life is like a sewer, what you get out of it depends on what you put into it - Tom Lehrer -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Scott Canaan INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the
WAITS
Hi I checked wait in my database and found the following are top 5 waits. Event Waits single-task message 1,411,047 log file sync 326,222 SQL*Net more data to client 7,363,877 control file parallel write27,674 latch free107,486 Any suggestions from group.what to do? Thx -Seema _ Chat with friends online, try MSN Messenger: http://messenger.msn.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Seema Singh INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (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: DBA_EXTENTS problem
Babu, We had a similiar problem, and setting optimizer_mode = choose in our session solved it. Something to do with optimizer and DD access. Give that a try. I had the same problem with DBA_INDEXES and that fixed it. hth, Jack --- Janardhana Babu Donga [EMAIL PROTECTED] wrote: Dear List, When I query dba_extents , Iam getting response after half an hour to 40 minutes, but when I query any other dictionary view it is spontaneous. Everything else is fine in the database and there are no problems, except the above problem. Iam not getting any clue how to fix this. Iam thinking of running catalog.sql and catproc.sql as a last resort. Iam not sure it would fix the problem. Please let me know if there is any other way to fix this problem. Thanks, Babu -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Janardhana Babu Donga INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). __ Do You Yahoo!? Yahoo! Shopping - Mother's Day is May 12th! http://shopping.yahoo.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Jack Silvey INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: DBA_EXTENTS problem
Babu, Do you have any locally managed tablespaces? I'm wondering if this could be caused by scanning for extents within LMTs. Regards, Mike Hately -Original Message- Sent: 09 May 2002 18:58 To: Multiple recipients of list ORACLE-L Dear List, When I query dba_extents , Iam getting response after half an hour to 40 minutes, but when I query any other dictionary view it is spontaneous. Everything else is fine in the database and there are no problems, except the above problem. Iam not getting any clue how to fix this. Iam thinking of running catalog.sql and catproc.sql as a last resort. Iam not sure it would fix the problem. Please let me know if there is any other way to fix this problem. Thanks, Babu This email and any attached to it are confidential and intended only for the individual or entity to which it is addressed. If you are not the intended recipient, please let us know by telephoning or emailing the sender. You should also delete the email and any attachment from your systems and should not copy the email or any attachment or disclose their content to any other person or entity. The views expressed here are not necessarily those of Churchill Insurance Group plc or its affiliates or subsidiaries. Thank you. Churchill Insurance Group plc. Company Registration Number - 2280426. England. Registered Office: Churchill Court, Westmoreland Road, Bromley, Kent BR1 1DP. -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Hately Mike INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (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: DBA_EXTENTS problem
Babu - Wow, never had that one. Of course, I don't use that table much. How many rows are in this table? Mine has 12,937 rows, and is kinda slow to respond compared to the other system tables. Do you have a test database to compare with? Is it possible that your system tablespace has become badly fragmented? What is your system load, is it possibly a contention problem of some type. Why not run STATSPACK (or utlbstat) while you do this query and see what is being waited for? Why don't I shut up and send this? Dennis Williams DBA Lifetouch, Inc. [EMAIL PROTECTED] -Original Message- Sent: Thursday, May 09, 2002 12:58 PM To: Multiple recipients of list ORACLE-L Dear List, When I query dba_extents , Iam getting response after half an hour to 40 minutes, but when I query any other dictionary view it is spontaneous. Everything else is fine in the database and there are no problems, except the above problem. Iam not getting any clue how to fix this. Iam thinking of running catalog.sql and catproc.sql as a last resort. Iam not sure it would fix the problem. Please let me know if there is any other way to fix this problem. Thanks, Babu -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Janardhana Babu Donga INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: DENNIS WILLIAMS INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Re: Cloning Question
I thought there was a rollback segment in system that would be there. It should be enough to get things started. After all, there isn't a separate one when you create a database, until you create it. DENNIS WILLIAMS wrote: Scott - I think that Rachel offered better advice, and I'm sure you've read her note by now. I have some experience with missing files when cloning a database :-), but I haven't had the rollback tablespace missing, and that would be different. One thought would be to create an extra rollback tablespace on production, so that when you perform the clone operation, Oracle still has some rollback segments available. Dennis Williams DBA Lifetouch, Inc. [EMAIL PROTECTED] -Original Message- Sent: Thursday, May 09, 2002 12:03 PM To: Multiple recipients of list ORACLE-L Dennis, No. I did a create controlfile set database... I commented out the rollback_segments line in the init.ora and removed the reference to the file in the create controlfile statement. With no reference (that I know of) to the rollback segments or tablespace, I thought that the database should have come up, then I could create a new one. I must have missed something, but I don't know what that was. DENNIS WILLIAMS wrote: Scott - Let's see if I understand specifically what you did. You ran the CREATE DATABASE script that was generated from your production system. Now, since the datafile for rollback tablespace isn't there, Oracle will come up without it. Once your new instance was up, then you did a DROP TABLESPACE on the rollback tablespace, followed by a CREATE TABLESPACE. I've done that scenario quite a few times on Oracle 8.1.6, just yesterday, as a matter of fact (that was a temp tablespace). If those weren't your steps, then provide more details. Dennis Williams DBA Lifetouch, Inc. [EMAIL PROTECTED] -Original Message- Sent: Thursday, May 09, 2002 8:48 AM To: Multiple recipients of list ORACLE-L I spent a long time trying to clone our production data warehouse into test, to refresh it. I do this frequently, as both a way to refresh test instances and to test the backups. After fighting through 2 bad backups, I finally got a good one. However, the problem I had was that the production rollback tablespace is larger than the disk on test. My thought was that I could restore everything except the rollback tablespace, which is not the way I usually do it. I commented out the rollback_segments line in the init.ora and created a script that would create a new rollback tablespace and rollback segments, smaller than those in production. This didn't work, even though Oracle support said it should have. When I did the clone, the database would not open. I kept getting the following error: ORA-00600: internal error code, arguments: [25012], [1], [2], [], [], [], [], [] Does anyone know what I was doing wrong? I can't figure it out, and Oracle support couldn't figure it out, either. I was able to get the database up by getting more disk added, so that there was enough space to restore the rollback tablespace file. Thank you. -- Scott Canaan ([EMAIL PROTECTED]) (585) 475-7886 Life is like a sewer, what you get out of it depends on what you put into it - Tom Lehrer -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Scott Canaan INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: DENNIS WILLIAMS INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Scott Canaan ([EMAIL PROTECTED]) (585) 475-7886 Life is like a sewer, what you get out of it depends on what you put into it - Tom Lehrer -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Scott Canaan INET: [EMAIL PROTECTED] Fat City
Re: DBA_EXTENTS problem
that's interesting... since the data dictionary is NOT analyzed, setting optimizer_mode=choose would force the query against dba_extents to RULE which it what it is supposed to be doing anyway, Hm. The question now is, what is the optimizer_mode set to when the problem happens? Did any of the data dictionary tables get accidentally analyzed? And -- how many extents are in use in the database? Could it just be a symptom of missized tables and indexes so that the number of extents is way high? Although Oracle is *supposed* to allow unlimited extents, in practice anything higher than 4096 extents in an object (at least in 8i) tends to slow things down. |+--- || | || | || jack_silvey@y| || ahoo.com | || | || 05/09/2002 | || 02:18 PM | || Please | || respond to | || ORACLE-L | || | |+--- | || | To: [EMAIL PROTECTED] | | cc: (bcc: Rachel Carmichael) | | Subject: Re: DBA_EXTENTS problem | | Babu, We had a similiar problem, and setting optimizer_mode = choose in our session solved it. Something to do with optimizer and DD access. Give that a try. I had the same problem with DBA_INDEXES and that fixed it. hth, Jack --- Janardhana Babu Donga [EMAIL PROTECTED] wrote: Dear List, When I query dba_extents , Iam getting response after half an hour to 40 minutes, but when I query any other dictionary view it is spontaneous. Everything else is fine in the database and there are no problems, except the above problem. Iam not getting any clue how to fix this. Iam thinking of running catalog.sql and catproc.sql as a last resort. Iam not sure it would fix the problem. Please let me know if there is any other way to fix this problem. Thanks, Babu -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Janardhana Babu Donga INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). __ Do You Yahoo!? Yahoo! Shopping - Mother's Day is May 12th! http://shopping.yahoo.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Jack Silvey INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: RE: Good DBA vs. Bad DBA
-Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]] Sent: Thursday, May 09, 2002 12:47 PM To: Multiple recipients of list ORACLE-L Subject: Re:RE: Good DBA vs. Bad DBA snip Now I've also got some real good SA's who likewise do not appreciate system crashes so we've put a lot of time effort into crash proofing things as much as possible. That means servers with redundant CPU's, power supplies, etc... Full disk mirroring with three not two mirrors. Connectivity software and scripts with our UPS system so we shutdown nicely before power runs out. And on top of all that a standby generator. So the servers and disks are fully protected. snip fully protected is awfully ambiguous. There was at least one company that thought it's data was fully protected because they had hot swap systems in both of the World Trade Center towers. Rich Jesse System/Database Administrator [EMAIL PROTECTED] Quad/Tech International, Sussex, WI USA -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Jesse, Rich INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: DBA_EXTENTS problem
The Optimizer_mode is already set to CHOOSE. Any other ideas? Thanks, --Babu -Original Message- Sent: Thursday, May 09, 2002 11:18 AM To: Multiple recipients of list ORACLE-L Babu, We had a similiar problem, and setting optimizer_mode = choose in our session solved it. Something to do with optimizer and DD access. Give that a try. I had the same problem with DBA_INDEXES and that fixed it. hth, Jack --- Janardhana Babu Donga [EMAIL PROTECTED] wrote: Dear List, When I query dba_extents , Iam getting response after half an hour to 40 minutes, but when I query any other dictionary view it is spontaneous. Everything else is fine in the database and there are no problems, except the above problem. Iam not getting any clue how to fix this. Iam thinking of running catalog.sql and catproc.sql as a last resort. Iam not sure it would fix the problem. Please let me know if there is any other way to fix this problem. Thanks, Babu -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Janardhana Babu Donga INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). __ Do You Yahoo!? Yahoo! Shopping - Mother's Day is May 12th! http://shopping.yahoo.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Jack Silvey INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Janardhana Babu Donga INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (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: Good DBA vs. Bad DBA
Jerry, Aw, come on! Try handling a radar radome on a B-4 stand in a 10 MPH wind, now that's fun!! Dick Goulet Reply Separator Author: Whittle Jerome Contr NCI [EMAIL PROTECTED] Date: 5/9/2002 9:47 AM Database crashes don't worry me too much. Just some extra work (or looking for work if it was my fault.) I was an aircraft mechanic for over 20 years. Nothing like hearing that an aircraft you recently worked on crashed killing all on board! Happened to me three times. Thankfully it wasn't my fault. Or having coworkers die on the job from accidents. That happened twice. I became a DBA in part because (1) it pays better, (2) much less stressful, and (3) I don't have to change 250 lb wheel and tire assemblies in the snow on midnight shift! Jerry Whittle ACIFICS DBA NCI Information Systems Inc. [EMAIL PROTECTED] 618-622-4145 -Original Message- Sinardy Xing [EMAIL PROTECTED] Sent by: [EMAIL PROTECTED] 05/09/2002 12:48 AM Please respond to ORACLE-L Do you guys still afraid of database crash ? Or I just too sensitive or no confidence !DOCTYPE HTML PUBLIC -//W3C//DTD HTML 3.2//EN HTML HEAD TITLERE: Good DBA vs. Bad DBA/TITLE /HEAD BODY !-- Converted from text/rtf format -- PSPAN LANG=en-usFONT COLOR=#FF SIZE=2 FACE=ArialDatabase crashes don't worry me too much. Just some extra work (or looking for work if it was my fault.)/FONT/SPAN /P PSPAN LANG=en-usFONT COLOR=#FF SIZE=2 FACE=ArialI was an aircraft mechanic for over 20 years. Nothing like hearing that an aircraft you recently worked on crashed killing all on board! Happened to me three times. Thankfully it wasn't my fault. Or having coworkers die on the job from accidents. That happened twice./FONT/SPAN/P PSPAN LANG=en-usFONT COLOR=#FF SIZE=2 FACE=ArialI became a DBA in part because (1) it pays better, (2) much less stressful, and (3) I don't have to change 250 lb wheel and tire assemblies in the snownbsp; on midnight shift!/FONT/SPAN/P PSPAN LANG=en-usFONT FACE=ArialJerry Whittle/FONT/SPAN BRSPAN LANG=en-usFONT FACE=ArialACIFICS DBA/FONT/SPAN BRSPAN LANG=en-usFONT FACE=ArialNCI Information Systems Inc./FONT/SPAN BRSPAN LANG=en-usFONT FACE=Arial[EMAIL PROTECTED]/FONT/SPAN BRSPAN LANG=en-usFONT FACE=Arial618-622-4145/FONT/SPAN /P UL PSPAN LANG=en-usFONT SIZE=1 FACE=Arial-Original Message-/FONT/SPAN BRSPAN LANG=en-usFONT SIZE=2 FACE=Arialquot;Sinardy Xingquot; lt;[EMAIL PROTECTED]gt;/FONT/SPAN BRSPAN LANG=en-usFONT SIZE=2 FACE=ArialSent by: [EMAIL PROTECTED]/FONT/SPAN BRSPAN LANG=en-usFONT SIZE=2 FACE=Arial05/09/2002 12:48 AM/FONT/SPAN BRSPAN LANG=en-usFONT SIZE=2 FACE=ArialPlease respond to ORACLE-L/FONT/SPAN /P PSPAN LANG=en-usFONT SIZE=2 FACE=ArialDo you guys still afraid of database crash ? Or I just too sensitive or no /FONT/SPAN BRSPAN LANG=en-usFONT SIZE=2 FACE=Arialconfidence/FONT/SPAN /P /UL /BODY /HTML -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: DBA_EXTENTS problem
There are no LMTs in the database. Pl. let me know if you have any other ideas. Thanks, -- Babu -Original Message- Sent: Thursday, May 09, 2002 11:29 AM To: Multiple recipients of list ORACLE-L Babu, Do you have any locally managed tablespaces? I'm wondering if this could be caused by scanning for extents within LMTs. Regards, Mike Hately -Original Message- Sent: 09 May 2002 18:58 To: Multiple recipients of list ORACLE-L Dear List, When I query dba_extents , Iam getting response after half an hour to 40 minutes, but when I query any other dictionary view it is spontaneous. Everything else is fine in the database and there are no problems, except the above problem. Iam not getting any clue how to fix this. Iam thinking of running catalog.sql and catproc.sql as a last resort. Iam not sure it would fix the problem. Please let me know if there is any other way to fix this problem. Thanks, Babu This email and any attached to it are confidential and intended only for the individual or entity to which it is addressed. If you are not the intended recipient, please let us know by telephoning or emailing the sender. You should also delete the email and any attachment from your systems and should not copy the email or any attachment or disclose their content to any other person or entity. The views expressed here are not necessarily those of Churchill Insurance Group plc or its affiliates or subsidiaries. Thank you. Churchill Insurance Group plc. Company Registration Number - 2280426. England. Registered Office: Churchill Court, Westmoreland Road, Bromley, Kent BR1 1DP. -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Hately Mike INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (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: Janardhana Babu Donga INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (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: WAITS
Seema - The SQL*Net more data to client means Oracle is waiting for the client to send something back so Oracle can do something more. I looked single-task message up on google and received: single-task message When running single task, this event indicates that the session waits for the client side of the executable. Wait Time: Total elapsed time that this session spent in the user application. Parameters: none So, these might be a problem, or not, depending on other factors in your system. For example, we have one program that runs in under a second on the local network, but takes more than a minute over the wide-area network. That would be an example where client waits are a problem with the application architecture. Do you have a problem, or are you just doing some standard analysis? Dennis Williams DBA Lifetouch, Inc. [EMAIL PROTECTED] -Original Message- Sent: Thursday, May 09, 2002 1:19 PM To: Multiple recipients of list ORACLE-L Hi I checked wait in my database and found the following are top 5 waits. Event Waits single-task message 1,411,047 log file sync 326,222 SQL*Net more data to client 7,363,877 control file parallel write27,674 latch free107,486 Any suggestions from group.what to do? Thx -Seema _ Chat with friends online, try MSN Messenger: http://messenger.msn.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Seema Singh INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: DENNIS WILLIAMS INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: DBA_EXTENTS problem
This is definitely not a fragmentation problem. I have just created a new table create table x1 (col1 number) tablespace data_ts; and run: select * from dba_extents where segment_name = 'X1'; It is still sitting there. Hope to get response after 30 minutes. Any other ideas?? Thanks, -- Babu -Original Message- Sent: Thursday, May 09, 2002 11:15 AM To: Multiple recipients of list ORACLE-L Sounds like a fragmentation problem. This will help you identify the segments with a large number of fragments. They are good candidates for reorganization. HTH, Beth select a.tablespace_name as tablespace, cast(a.segment_name as char(30)) as segment, a.partition_name as partition, count(1) as count, decode(b.extent_management,'LOCAL','Y','N') as LMT from dba_extents a, dba_tablespaces b where a.tablespace_name = b.tablespace_name having count(1) 1 group by a.tablespace_name, a.segment_name, a.partition_name, decode(b.extent_management,'LOCAL','Y','N') order by count(1) desc -Original Message- Sent: Thursday, May 09, 2002 1:59 PM To: Multiple recipients of list ORACLE-L Dear List, When I query dba_extents , Iam getting response after half an hour to 40 minutes, but when I query any other dictionary view it is spontaneous. Everything else is fine in the database and there are no problems, except the above problem. Iam not getting any clue how to fix this. Iam thinking of running catalog.sql and catproc.sql as a last resort. Iam not sure it would fix the problem. Please let me know if there is any other way to fix this problem. Thanks, Babu -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Janardhana Babu Donga INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Seefelt, Beth INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Janardhana Babu Donga INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (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: WAITS
There is a vital piece of information missing, namely how long was the accumulated wait time for each wait, and over what period of time? Jared Seema Singh [EMAIL PROTECTED] Sent by: [EMAIL PROTECTED] 05/09/2002 11:18 AM Please respond to ORACLE-L To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] cc: Subject:WAITS Hi I checked wait in my database and found the following are top 5 waits. Event Waits single-task message 1,411,047 log file sync 326,222 SQL*Net more data to client 7,363,877 control file parallel write27,674 latch free107,486 Any suggestions from group.what to do? Thx -Seema _ Chat with friends online, try MSN Messenger: http://messenger.msn.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Seema Singh INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Re: Good DBA vs. Bad DBA
[EMAIL PROTECTED] wrote: Nope. We _know_ the conspiracies are real... and so are the black helicopters. -- -- Bill Shrek Thater ORACLE DBA [EMAIL PROTECTED] You gotta program like you don't need the money, You gotta compile like you'll never get hurt, You gotta run like there's nobody watching, It's gotta come from the heart if you want it to work. LISP: To call a spade a thpade. -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: bill thater INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (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: WAITS
what does netstat -i tell you, on both client and server. On Thu, May 09, 2002 at 10:18:31AM -0800, Seema Singh wrote: Hi I checked wait in my database and found the following are top 5 waits. Event Waits single-task message 1,411,047 log file sync 326,222 SQL*Net more data to client 7,363,877 control file parallel write27,674 latch free107,486 Any suggestions from group.what to do? Thx -Seema _ Chat with friends online, try MSN Messenger: http://messenger.msn.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Seema Singh INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- === Ray Stell [EMAIL PROTECTED] (540) 231-4109 KE4TJC28^D -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Ray Stell INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (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: DBA_EXTENTS problem
Babu, What does this query return? select count(*) from dba_extents; Jared Janardhana Babu Donga [EMAIL PROTECTED] Sent by: [EMAIL PROTECTED] 05/09/2002 10:58 AM Please respond to ORACLE-L To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] cc: Subject:DBA_EXTENTS problem Dear List, When I query dba_extents , Iam getting response after half an hour to 40 minutes, but when I query any other dictionary view it is spontaneous. Everything else is fine in the database and there are no problems, except the above problem. Iam not getting any clue how to fix this. Iam thinking of running catalog.sql and catproc.sql as a last resort. Iam not sure it would fix the problem. Please let me know if there is any other way to fix this problem. Thanks, Babu -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Janardhana Babu Donga INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Re: Cloning Question
In some versions of Oracle, you need a SECOND rollback segment created in the SYSTEM tablespace in order to create another tablespace. It can't hurt to add it (you can drop it immediately after you get the rollback tablespace created) but it could be the solution. Since you didn't include the version of the database you are working with, we have to be somewhat generic in our answers Rachel |+--- || | || | || srcdco@ritvax| || .rit.edu | || | || 05/09/2002 | || 02:28 PM | || Please | || respond to | || ORACLE-L | || | |+--- | || | To: [EMAIL PROTECTED] | | cc: (bcc: Rachel Carmichael) | | Subject: Re: Cloning Question| | I thought there was a rollback segment in system that would be there. It should be enough to get things started. After all, there isn't a separate one when you create a database, until you create it. DENNIS WILLIAMS wrote: Scott - I think that Rachel offered better advice, and I'm sure you've read her note by now. I have some experience with missing files when cloning a database :-), but I haven't had the rollback tablespace missing, and that would be different. One thought would be to create an extra rollback tablespace on production, so that when you perform the clone operation, Oracle still has some rollback segments available. Dennis Williams DBA Lifetouch, Inc. [EMAIL PROTECTED] -Original Message- Sent: Thursday, May 09, 2002 12:03 PM To: Multiple recipients of list ORACLE-L Dennis, No. I did a create controlfile set database... I commented out the rollback_segments line in the init.ora and removed the reference to the file in the create controlfile statement. With no reference (that I know of) to the rollback segments or tablespace, I thought that the database should have come up, then I could create a new one. I must have missed something, but I don't know what that was. DENNIS WILLIAMS wrote: Scott - Let's see if I understand specifically what you did. You ran the CREATE DATABASE script that was generated from your production system. Now, since the datafile for rollback tablespace isn't there, Oracle will come up without it. Once your new instance was up, then you did a DROP TABLESPACE on the rollback tablespace, followed by a CREATE TABLESPACE. I've done that scenario quite a few times on Oracle 8.1.6, just yesterday, as a matter of fact (that was a temp tablespace). If those weren't your steps, then provide more details. Dennis Williams DBA Lifetouch, Inc. [EMAIL PROTECTED] -Original Message- Sent: Thursday, May 09, 2002 8:48 AM To: Multiple recipients of list ORACLE-L I spent a long time trying to clone our production data warehouse into test, to refresh it. I do this frequently, as both a way to refresh test instances and to test the backups. After fighting through 2 bad backups, I finally got a good one. However, the problem I had was that the production rollback tablespace is larger than the disk on test. My thought was that I could restore everything except the rollback tablespace, which is not the way I usually do it. I commented out the rollback_segments line in the init.ora and created a script that would create a new rollback tablespace and rollback segments, smaller than those in production. This didn't work, even though Oracle support said it should have. When I did the clone, the database would not open. I kept getting the following error: ORA-00600: internal error code, arguments: [25012], [1], [2], [], [], [], [], [] Does anyone know what I was doing wrong? I can't figure it out, and Oracle support couldn't figure it out, either. I was able to get the database up by getting more disk added, so that there was enough space to restore the rollback tablespace file. Thank you. -- Scott Canaan ([EMAIL PROTECTED]) (585) 475-7886 Life is like a sewer, what you get out of it depends on what you put into it - Tom Lehrer -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Scott Canaan INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note
Re: formating 10046 (level 12) trace file
I did write an awk script to summarize the wait events in a 10046 trace
file. It lists the SQL, and sums the count and time of the waits for the
SQL.
Henry
# Script for analyzing Oracle Trace files with WAIT statistics
# Usage: wait_scan.awk filename
# Written:Henry Poras
# 5/16/00
# Modified: 12/3/01 Initially assumes all wait states for a cursor are
between
# parse statements.
#
#
nawk '# need nawk,
not awk
BEGIN{N=
PARSE_FLAG=0 # PARSE_FLAG
= 0 (normal state)
printf(\n\n%-35s %-12s %-18s\n\n, # PARSE_FLAG
= 1 (previous line PARSING)
WAIT EVENT, # OF TIMES, ELAPSED TIME (sec)) # print
column headers
}
{if (PARSE_FLAG==1) # if previous
line started
{SQL[N]=$0 # with
PARSING, print
PARSE_FLAG=0 # the SQL.
N=
}
}
/^PARSING/ {FS=
N=$4
sub(#,,N)
if (N in SQL)
prinfo(N)
PARSE_FLAG=1
}
/^WAIT/ {FS=#| nam=|ela=|p1=
N=$2
sub(:,,N)
PARSE_FLAG=2
n_wait[N,$3] += 1
ela_wait[N,$3] += $4
}
END {for (N in SQL){ # Print Wait
statistics for final
printf \n\n\n%s\n\n, SQL[N] # SQL
statement in file
for (k in n_wait) {
split(k,arg,SUBSEP)
if (arg[1]==N n_wait[k]!=0) {
printf %-35s %-12s %12.2f\n,
arg[2],n_wait[k],ela_wait[k]/100
n_wait[k]=0
ela_wait[k]=0
}
}
printf \n\n
}
for (k in n_wait) {
split(k,arg,SUBSEP)
if (n_wait[k] != 0) {
printf %-35s %-12s %12.2f\n,
arg[2],n_wait[k],ela_wait[k]/100
n_wait[k]=0
ela_wait[k]=0
}
}
}
function prinfo(N, k) {
printf \n\n\n%s\n\n, SQL[N]
for (k in n_wait){
split(k,arg,SUBSEP)
if (arg[1]==N n_wait[k]!=0) {
printf %-35s %-12s %12.2f\n,
arg[2],n_wait[k],ela_wait[k]/100
n_wait[k]=0
ela_wait[k]=0
}
}
}
' $1
- Original Message -
To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED]
Sent: Wednesday, May 08, 2002 12:08 PM
Is there a commercial tool that will do the same thing
as the tool from hotsos? I am sort of angry with oracle
for not providing such tool to all of it's users. I'm more
inclined to test my abilities as a DBA (and those have been put to
test once or twice) then to pay $50/month to a 3rd party company
whose only advantage is that it has access to the information
and the tool that I do not have. If the information was available
to me, I could have used a little bit of flex/bison in conjunction
with OCI to write such a thing.
If I have to buy the tool, so be it, but then I want to use it
whenever I want, without the monthly fee. This, with all due respect
looks like milking a gullible cash cow and, with all due respect, I
don't like that.
I think that oracle should make public the information needed for such a
tool so that the rest of us who know how to put together a yacc grammar
can at least take a shot at writing such a tool.
-Original Message-
From: Gaja Krishna Vaidyanatha [mailto:[EMAIL PROTECTED]]
Sent: Tuesday, May 07, 2002 9:13 PM
To: Multiple recipients of list ORACLE-L
Subject: Re: formating 10046 (level 12) trace file
Mohammed,
You can look at 2 known options :-
1) The Hotsos Profiler at
http://www.hotsos.com/products/profiler/
2) ITRProf on http://www.ubtools.com/main.html
Hope that helps,
Gaja
--- [EMAIL PROTECTED] wrote:
Hello,
I was wondering if there is any tool or script which
can get the SQL and its
associated bind values from 10046 (level 12) trace
file and format it in a
readable way..
Any hints will be highly appreciated..
Thanks in advance.
Mohammed Ahsanuddin
Oracle DBA
--
Please see the official ORACLE-L FAQ:
http://www.orafaq.com
--
Author:
INET: [EMAIL PROTECTED]
Fat City Network Services-- (858) 538-5051 FAX:
(858) 538-5051
San Diego, California-- Public Internet
access / Mailing Lists
To REMOVE yourself from this mailing list, send an
E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of
'ListGuru') and in
the message BODY, include a line containing: UNSUB
ORACLE-L
(or the name of
Re: How to backup a data warehouse?
Hi, If my memory is good, it was in 1998-99 with Oracle 8, we were using stored proc with dynamic pl/sql. I do not recall that it was taking long. Everything was done with dynamic pl/sql : tablespace creation, partition creation,... Our partitions were not big, between 200M and 800M. Stéphane --- [EMAIL PROTECTED] a écrit : Stephane, So, what process did you use to switch over a non-read-only partition to read-only on a monthly basis? How quickly were you able to make the switch? How large were your partitions? Thanks, Cherie paquette stephane stephane_paquette@ To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] yahoo.comcc: Sent by: Subject: Re: How to backup a data warehouse? [EMAIL PROTECTED] 05/08/02 01:49 PM Please respond to ORACLE-L I've tested a setup like the one Tim is describing. We tested backuping with RMAN using incremental and doing conventional hot backup. We were partitionned by month and each partition was in its own tablespace. Only the current month was active so the rest was in read only mode. Since volume was not too big , we were still able to do a nice clean full backup during the weekends. --- Tim Gorman [EMAIL PROTECTED] a écrit : The short answer is that it becomes important to make use of the features of 1) range-partitioning, 2) read-only tablespaces, and 3) incremental backups with RMAN, and 4) a honking big tape library using media-management software. Range-partitioning allows tables and indexes to be spread across multiple tablespaces, usually by time. As data ages, inserts/updates/deletes tends to cease; it is usually the newest data that has insert/update/delete activity upon it. Therefore, as data ages, you can set the tablespace in which the partitions are located to read-only. As tablespaces are set to read-only, you can remove them from the regular backup list. It is important to take 2-3 additional backups for archival retention after setting to read-only, but the regularly scheduled backups can now ignore those tablespaces. Generally, I recommend partitioning according to your loading scheme (i.e. daily loads, thus daily partitions) and storing those partitions in tablespaces according to your scheme for setting them read-only (i.e. if setting read-only on a quarterly basis after aged 6 months, then create quarterly tablespaces to house all of the partitions from all partitioned objects in that quarter). The advantages of RMAN's incremental backup mechanisms should be obvious, but its usefulness depends on the nature of the application. At the very least, incremental backups will supplement the effects of the partitioning/read-only-tablespace scheme illustrated above. Using RMAN's incremental backup mechanism, at worst you would be getting the equivalent of a level-0 or full backup, if every block was modified during every backup cycle. I hope that would be an unlikely scenario, though... - Original Message - From: Terrian, Tom To: Multiple recipients of list ORACLE-L Sent: Wednesday, May 08, 2002 9:38 AM Subject: How to backup a data warehouse? How do you guys backup your data warehouses? Our warehouse is suppose to top out around 3.5TB. It seems that the traditional hot/cold backup methods will not be able to keep up. How do you guys do it? Thanks, Tom Terrian = Stéphane Paquette DBA Oracle, consultant entrepôt de données Oracle DBA, datawarehouse consultant [EMAIL PROTECTED]
RE: Oracle Presentation for Beginners!
Any one got this message? Forbidden You don't have permission to access /~melody/courses/Spring2002Backup/BackupPPTS/les on this server. --- Sherman, Edward [EMAIL PROTECTED] wrote: Here's the slides! SQL/PL-SQL http://www.ecst.csuchico.edu/~melody/courses/Spg2001OraclePLSQL/PLSQL8iPPTS/ Default.htm Architecture and Admin http://www.ecst.csuchico.edu/~melody/courses/Fall2001CSCI379/TOC.html Performance Tuning http://www.ecst.csuchico.edu/~melody/courses/Spring2002PerfTune/TOC.html Backup and Recovery http://www.ecst.csuchico.edu/~melody/courses/Spring2002Backup/BackupPPTS/les 01_files/v3_document.htm http://www.ecst.csuchico.edu/~melody/courses/Spring2002Backup/BackupPPTS/les 02_files/v3_document.htm http://www.ecst.csuchico.edu/~melody/courses/Spring2002Backup/BackupPPTS/les 03_files/v3_document.htm And here's a gal after my own heart! http://www.ecst.csuchico.edu/~melody/ Thanks Melody!!! Ed -Original Message- Sent: Monday, May 06, 2002 5:09 AM To: Multiple recipients of list ORACLE-L Hello all, because I am an human being I am also looking for the easiest solution :-) So, I need to have a training session concerning basic oracle features. I wonder if anyone of you know place/site where I could download some powerpoint slides. Or if you have your own, pls. send me a copy ... Any help will be highly appreciated. Thanks km the lazy dba -- __ Ota itsellesi luotettava kotimainen email http://www.jippii.fi/ Tutustu samalla netin parhaaseen pelipaikkaan JIPPIIGAMESIIN. -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). * * * * * Freedom of Information Act Notice * * * * * The information in this email is subject to the record protection mandated by 5 United States Code 552 (b) (4) and relevant judicial opinions. -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Sherman, Edward INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). __ Do You Yahoo!? Yahoo! Shopping - Mother's Day is May 12th! http://shopping.yahoo.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Steven Joshua INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (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: DBA_EXTENTS problem
Jared, It is taking 30 to 40 minutes to respond. I just issued again and is sitting there. Iam sure it would respond after 30 minutes as happenned many times. I would E-Mail after getting the response. Thanks, -- Janardhana Babu -Original Message- Sent: Thursday, May 09, 2002 11:54 AM To: Multiple recipients of list ORACLE-L Babu, What does this query return? select count(*) from dba_extents; Jared Janardhana Babu Donga [EMAIL PROTECTED] Sent by: [EMAIL PROTECTED] 05/09/2002 10:58 AM Please respond to ORACLE-L To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] cc: Subject:DBA_EXTENTS problem Dear List, When I query dba_extents , Iam getting response after half an hour to 40 minutes, but when I query any other dictionary view it is spontaneous. Everything else is fine in the database and there are no problems, except the above problem. Iam not getting any clue how to fix this. Iam thinking of running catalog.sql and catproc.sql as a last resort. Iam not sure it would fix the problem. Please let me know if there is any other way to fix this problem. Thanks, Babu -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Janardhana Babu Donga INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Janardhana Babu Donga INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (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: DBA_EXTENTS problem
Dennis, It is happenning for any table in the database. I have just created a new table: create table x1 (col1 number) tablespace data_ts, inserted one row and run: select * from dba_extents where segment_name = 'X1'; It is still sitting there. The response to any other dictionary view is spontaneous except this dba_extents, which is confusing me. I issued: select * from dba_objects where ..., select * from dba_segments where etc. They are all responding fine. There are not many users at the moment. System tablespace is 500M size and 300MB is free. DB is 40Gig in size. It is on HP_UX 11.0 and Oracle 8.1.7.2.0 (64bit) database. Any other ideas? Thanks, -- Babu -Original Message- Sent: Thursday, May 09, 2002 11:29 AM To: Multiple recipients of list ORACLE-L Babu - Wow, never had that one. Of course, I don't use that table much. How many rows are in this table? Mine has 12,937 rows, and is kinda slow to respond compared to the other system tables. Do you have a test database to compare with? Is it possible that your system tablespace has become badly fragmented? What is your system load, is it possibly a contention problem of some type. Why not run STATSPACK (or utlbstat) while you do this query and see what is being waited for? Why don't I shut up and send this? Dennis Williams DBA Lifetouch, Inc. [EMAIL PROTECTED] -Original Message- Sent: Thursday, May 09, 2002 12:58 PM To: Multiple recipients of list ORACLE-L Dear List, When I query dba_extents , Iam getting response after half an hour to 40 minutes, but when I query any other dictionary view it is spontaneous. Everything else is fine in the database and there are no problems, except the above problem. Iam not getting any clue how to fix this. Iam thinking of running catalog.sql and catproc.sql as a last resort. Iam not sure it would fix the problem. Please let me know if there is any other way to fix this problem. Thanks, Babu -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Janardhana Babu Donga INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: DENNIS WILLIAMS INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Janardhana Babu Donga INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (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: Working with Oracle Designer
I've been looking for that in Designer , where it is ? I've used a lot Power*AMC in the past and it has that feature . We're using Oracle Designer 6.5.52.1.0 TIA --- [EMAIL PROTECTED] a écrit : Stephane, We have the same objects, etc. in both dev and prod. We only have a single database defined. When we get to prod, we'll have to edit the tablespace physical storage definition (in just one place) to change the datafile definitions. It is possible to use different methods to generate DDL from the same definition.One method generates DDL that containts storage definitions and one does not. You don't actually need to change the way anything is defined in Designer to switch back and forth between the two methods. Cherie paquette stephane stephane_paquette@ To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] yahoo.comcc: Sent by: Subject: Re: Working with Oracle Designer [EMAIL PROTECTED] 05/08/02 04:03 PM Please respond to ORACLE-L So you're generating specific DDL for each environments. Do you have 1 db in Designer per Oracle database ? Also, we have a requirement to be able to generate the DDL without any tablespaces and storage clause so the developers can do some prototyping in their own space. The way we will handle that is to create a user no_storage for each user and to do table implementation without tablespace. Anybody working like that ? --- [EMAIL PROTECTED] a écrit : Stephane, We are doing exactly that with Designer 6i. We are able to generate all the DDL except for public synonyms. No solution found for that. Otherwise, it is working well in our QA environment. Haven't used it yet to generate prod but that is coming next month. Cherie Machler Oracle DBA Gelco Information Network paquette stephane stephane_paquette@ To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] yahoo.comcc: Sent by: Subject: Working with Oracle Designer [EMAIL PROTECTED] 05/08/02 01:24 PM Please respond to ORACLE-L Hi, We're discussing ways of working with Designer. We convinced developpers to use the check in/check out stuff and each application has its many workareas. Someone here would like to press a button and have all the DDL generetad for at least 2 environments : prod and dev (including datafiles path and sizing). Of course the physical files layout/sizing are different in dev, test, QA, prod and maintenance environments. One way we tought, is for each application, to have a logical DB where we put all the schemas, tables, and tablespaces. For the same application, we created 2 physicals DB , lets called them dev and prod where we created the tablespaces with a storage definition. The storage definition would match the physical caracteristics of the db. So, when generating for dev, we use the dev physical layout and when for prod we use the prod physical layout. Anybody working like that ? Other approach ? TIA = Stéphane Paquette DBA Oracle, consultant entrepôt de données Oracle DBA, datawarehouse consultant [EMAIL PROTECTED] ___ Do You Yahoo!? -- Une adresse @yahoo.fr gratuite et en français ! === message truncated === =
RE: DBA_EXTENTS problem
I have recently changed the analyze script. Earliar it was dbms_utility.analyze_schema(...) statement, It is now changed to dbms_stats.gather_database_stats(); Is this a problem? The response time for select count(*) from dba_extents is also 30 minutes. It is not specific to any table. Thanks, -- Babu -Original Message- [mailto:[EMAIL PROTECTED]] Sent: Thursday, May 09, 2002 11:36 AM To: Multiple recipients of list ORACLE-L that's interesting... since the data dictionary is NOT analyzed, setting optimizer_mode=choose would force the query against dba_extents to RULE which it what it is supposed to be doing anyway, Hm. The question now is, what is the optimizer_mode set to when the problem happens? Did any of the data dictionary tables get accidentally analyzed? And -- how many extents are in use in the database? Could it just be a symptom of missized tables and indexes so that the number of extents is way high? Although Oracle is *supposed* to allow unlimited extents, in practice anything higher than 4096 extents in an object (at least in 8i) tends to slow things down. |+--- || | || | || jack_silvey@y| || ahoo.com | || | || 05/09/2002 | || 02:18 PM | || Please | || respond to | || ORACLE-L | || | |+--- | || | To: [EMAIL PROTECTED] | | cc: (bcc: Rachel Carmichael) | | Subject: Re: DBA_EXTENTS problem | | Babu, We had a similiar problem, and setting optimizer_mode = choose in our session solved it. Something to do with optimizer and DD access. Give that a try. I had the same problem with DBA_INDEXES and that fixed it. hth, Jack --- Janardhana Babu Donga [EMAIL PROTECTED] wrote: Dear List, When I query dba_extents , Iam getting response after half an hour to 40 minutes, but when I query any other dictionary view it is spontaneous. Everything else is fine in the database and there are no problems, except the above problem. Iam not getting any clue how to fix this. Iam thinking of running catalog.sql and catproc.sql as a last resort. Iam not sure it would fix the problem. Please let me know if there is any other way to fix this problem. Thanks, Babu -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Janardhana Babu Donga INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). __ Do You Yahoo!? Yahoo! Shopping - Mother's Day is May 12th! http://shopping.yahoo.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Jack Silvey INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Janardhana Babu Donga INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego,
RE: DBA_EXTENTS problem
Jared, It is taking 30 to 40 minutes to respond. I just issued again and is sitting there. Iam sure it would respond after 30 minutes as happenned many times. Thanks, -- Janardhana Babu -Original Message- Sent: Thursday, May 09, 2002 10:45 AM To: [EMAIL PROTECTED] Cc: [EMAIL PROTECTED] Babu, What does this query return? select count(*) from dba_extents; Jared Janardhana Babu Donga [EMAIL PROTECTED] Sent by: [EMAIL PROTECTED] 05/09/2002 10:58 AM Please respond to ORACLE-L To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] cc: Subject:DBA_EXTENTS problem Dear List, When I query dba_extents , Iam getting response after half an hour to 40 minutes, but when I query any other dictionary view it is spontaneous. Everything else is fine in the database and there are no problems, except the above problem. Iam not getting any clue how to fix this. Iam thinking of running catalog.sql and catproc.sql as a last resort. Iam not sure it would fix the problem. Please let me know if there is any other way to fix this problem. Thanks, Babu -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Janardhana Babu Donga INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (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: Janardhana Babu Donga INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (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: Async I/O question
Thanks Anjo/John: Anjo, I agree, Unknown297 might be a post/wait call asking for KAIO. John, you have to configure AIO servers separately using SMIT AIO. (And possiblly bounce the server?)...If you are going to use Threaded Async I/O with FS, If you want to use Kernelized Async I/O withRaw devices (my case), you should notuse AIO servers. (as a matter of fact you can't use them) Thanks again for the answers. DC - Original Message - From: Anjo Kolk To: Multiple recipients of list ORACLE-L Sent: Wednesday, May 08, 2002 3:08 PM Subject: Re: Async I/O question Could the unknown297 call be for post/wait (postwait driver). As that is what is missing in the trace. Anjo. Diego Cutrone wrote: Hi list.Oracle 7.3.4.5IBM 4.3.3Raw devices based datafilesuse_async_io=TRUEWhile the database was processing some long update, I traced the LGWR proccess with SCTRACE utility (like truss in solaris or HP) and I got the following dump:LGWR proccessunknown297(0x1, 0x2ff0cfa8, 0x30, 0x2ff0cfa0, 0x2ff0ed00) - 0 times(0x2ff20fd8) - 77507556 (0x49eabe4) 0x2ff20fd8: : 0015 01e1 || times(0x2ff21068) - 77507556 (0x49eabe4) 0x2ff21068: : 0015 01e1 || times(0x2ff212c8) - 77507556 (0x49eabe4) 0x2ff212c8: : 0015 01e1 || times(0x2ff212c8) - 77507556 (0x49eabe4) 0x2ff212c8: : 0015 01e1 || times(0x2ff21018) - 77507556 (0x49eabe4) 0x2ff21018: : 0015 01e1 || times(0x2ff21068) - 77507556 (0x49eabe4) 0x2ff21068: : 0015 01e1 || unknown297(0x1, 0x2ff0cfa8, 0x3, 0x2ff0cfa0, 0x2ff0e058) - 0 times(0x2ff20fd8) - 77507557 (0x49eabe5) 0x2ff20fd8: : 0015 01e1 || times(0x2ff21068) - 77507557 (0x49eabe5) 0x2ff21068: : 0015 01e1 ||and it keeps going.I asume that unknown297() system call is the writting call here, as times() unknown297() and are the only calls that appearin the trace.After this testing I did the same trace in anOracle database 7.3.4.5File system based datafilesAIX 4.3.3 (same box)use_async_io = TRUEAnd I got the following output:LGWR proccessunknown297(0xa, 0, 0x4000982c, 0x1, 0x20034c78) - -1 EINTR(4) (Interrupted system call) times(0x2ff21438) - 45035290 (0x2af2f1a) 0x2ff21438: : 002d 0175 |...-...u| times(0x2ff215c8) - 45035290 (0x2af2f1a) 0x2ff215c8: : 002d 0175 |...-...u| kwaitpid(0x2ff21520, -1, 0x5, 0x) - -1 ECHILD(10) (No child processes) kill(27736, SIG 0(0)) - 0 times(0x2ff215c8) - 45035290 (0x2af2f1a) 0x2ff215c8: : 002d 0175 |...-...u| times(0x2ff21478) - 45035290 (0x2af2f1a) 0x2ff21478: : 002d 0175 |...-...u| unknown297(0x12c, 0, 0x4000982c, 0x1, 0x20034c78) [unknown297] - 0 times(0x2ff21438) - 45035477 (0x2af2fd5) 0x2ff21438: : 002d 0175 |...-...u| times(0x2ff21398) - 45035477 (0x2af2fd5) 0x2ff21398: : 002d 0175 |...-...u| times(0x2ff21398) - 45035477 (0x2af2fd5) 0x2ff21398: : 002d 0175 |...-...u| times(0x2ff210e8) - 45035477 (0x2af2fd5) 0x2ff210e8: : 002d 0175 |...-...u| times(0x2ff21138) - 45035477 (0x2af2fd5) 0x2ff21138: : 002d 0175 |...-...u| kwrite(15, 0x4213be00, 55296) - 55296 (0xd800) times(0x2ff210a8) - 45035479 (0x2af2fd7) 0x2ff210a8: : 002d 0175 |...-...u| times(0x2ff21138) - 45035479 (0x2af2fd7) 0x2ff21138: : 002d 0175 |...-...u| unknown297(0xa650, 0xc08, 0, 0x5, 0x420315b4) - 0 times(0x2ff21398) - 45035479 (0x2af2fd7) 0x2ff21398: : 002d 0175 |...-...u| times(0x2ff21398) - 45035479 (0x2af2fd7) 0x2ff21398: : 002d 0175 |...-...u| times(0x2ff210e8) - 45035479 (0x2af2fd7) 0x2ff210e8: : 002d 0175 |...-...u| times(0x2ff21138) - 45035479 (0x2af2fd7) 0x2ff21138: : 002d 0175 |...-...u| kwrite(15, 0x42149600, 59904) - 59904 (0xea00) kwrite(15, 0x4213, 48128) - 48128 (0xbc00)As you can see kwrite, klseek, etc are used here.My questions are:Is unknown297 system call related
Re: WAITS
Well why do you want to do something ? To look busy ? Or are there complaints about response times or throughput problems ? Anjo. Seema Singh wrote: Hi I checked wait in my database and found the following are top 5 waits. Event Waits single-task message 1,411,047 log file sync 326,222 SQL*Net more data to client 7,363,877 control file parallel write27,674 latch free107,486 Any suggestions from group.what to do? Thx -Seema _ Chat with friends online, try MSN Messenger: http://messenger.msn.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Seema Singh INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Anjo Kolk INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Installing oracle 9.0.1 on Sparc
Hi, I am installing oracle 9.0.1 on Sun Sparc Solaris 5.6, I have the next error: make: Fatal error: Command failed for target `ntcontab.o' /usr/ccs/bin/make -f ins_net_client.mk nnfgt.o ORACLE_HOME =/u02/app/oracle/prod uct/9.0.1 (if [ -d /u02/app/oracle/product/9.0.1/lib32 ] ; then \ /usr/ccs/bin/make -f /u02/app/oracle/product/9.0.1/network/lib/ins_net_client. mk \ nnfgt.o-32bit; fi) (if [ assemble = assemble ] ; then \ /u02/app/oracle/product/9.0.1/bin/gennfgt nnfgt.s ;\ /usr/ccs/bin/as -P -o nnfgt.o nnfgt.s ;\ rm -f /u02/app/oracle/product/9.0.1/lib32/nnfgt.o ;\ mv nnfgt.o /u02/app/oracle/product/9.0.1/lib32 ;\ /usr/ccs/bin/ar rv /u02/app/oracle/product/9.0.1/lib32/libn9.a \ /u02/app/oracle/product/9.0.1/lib32/nnfgt.o ; fi) ar: writing /u02/app/oracle/product/9.0.1/lib32/libn9.a r - nnfgt.o (if [ assemble = compile ] ; then \ /u02/app/oracle/product/9.0.1/bin/gennfgt nnfgt.c ;\ cc -c nnfgt.c ;\ rm -f /u02/app/oracle/product/9.0.1/lib/nnfgt.o ;\ mv nnfgt.o /u02/app/oracle/product/9.0.1/lib ;\ /usr/ccs/bin/ar rv /u02/app/oracle/product/9.0.1/lib/libn9.a /u02/app/ oracle/product/9.0.1/lib/nnfgt.o ; fi) (if [ assemble = assemble ] ; then \ ... Some idea? -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Natalia Laracca INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (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: Good DBA vs. Bad DBA
sh... _They'll_ hear you. -Original Message- Sent: Thursday, May 09, 2002 2:49 PM To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] wrote: Nope. We _know_ the conspiracies are real... and so are the black helicopters. -- -- Bill Shrek Thater ORACLE DBA [EMAIL PROTECTED] You gotta program like you don't need the money, You gotta compile like you'll never get hurt, You gotta run like there's nobody watching, It's gotta come from the heart if you want it to work. LISP: To call a spade a thpade. -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: bill thater INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (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: Speaks, Chuck W. INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: Oracle Presentation for Beginners!
Yes, I got that one too. I suspect that the current courses are locked to all but current students? Ron -Original Message- From: Steven Joshua [SMTP:[EMAIL PROTECTED]] Sent: Thursday, May 09, 2002 3:00 PM To: Multiple recipients of list ORACLE-L Subject: RE: Oracle Presentation for Beginners! Any one got this message? Forbidden You don't have permission to access /~melody/courses/Spring2002Backup/BackupPPTS/les on this server. --- Sherman, Edward [EMAIL PROTECTED] wrote: Here's the slides! SQL/PL-SQL http://www.ecst.csuchico.edu/~melody/courses/Spg2001OraclePLSQL/PLSQL8iPPT S/ Default.htm Architecture and Admin http://www.ecst.csuchico.edu/~melody/courses/Fall2001CSCI379/TOC.html Performance Tuning http://www.ecst.csuchico.edu/~melody/courses/Spring2002PerfTune/TOC.html Backup and Recovery http://www.ecst.csuchico.edu/~melody/courses/Spring2002Backup/BackupPPTS/l es 01_files/v3_document.htm http://www.ecst.csuchico.edu/~melody/courses/Spring2002Backup/BackupPPTS/l es 02_files/v3_document.htm http://www.ecst.csuchico.edu/~melody/courses/Spring2002Backup/BackupPPTS/l es 03_files/v3_document.htm And here's a gal after my own heart! http://www.ecst.csuchico.edu/~melody/ Thanks Melody!!! Ed -Original Message- Sent: Monday, May 06, 2002 5:09 AM To: Multiple recipients of list ORACLE-L Hello all, because I am an human being I am also looking for the easiest solution :-) So, I need to have a training session concerning basic oracle features. I wonder if anyone of you know place/site where I could download some powerpoint slides. Or if you have your own, pls. send me a copy ... Any help will be highly appreciated. Thanks km the lazy dba -- __ Ota itsellesi luotettava kotimainen email http://www.jippii.fi/ Tutustu samalla netin parhaaseen pelipaikkaan JIPPIIGAMESIIN. -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). * * * * * Freedom of Information Act Notice * * * * * The information in this email is subject to the record protection mandated by 5 United States Code 552 (b) (4) and relevant judicial opinions. -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Sherman, Edward INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). __ Do You Yahoo!? Yahoo! Shopping - Mother's Day is May 12th! http://shopping.yahoo.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Steven Joshua INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (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: Morton, Ronald D INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing:
RE: DBA_EXTENTS problem
Yes, that's bad. You have analyzed the data dictionary tables. You need to delete the statistics for them. -Original Message- Sent: Thursday, May 09, 2002 3:04 PM To: Multiple recipients of list ORACLE-L I have recently changed the analyze script. Earliar it was dbms_utility.analyze_schema(...) statement, It is now changed to dbms_stats.gather_database_stats(); Is this a problem? The response time for select count(*) from dba_extents is also 30 minutes. It is not specific to any table. Thanks, -- Babu -Original Message- [mailto:[EMAIL PROTECTED]] Sent: Thursday, May 09, 2002 11:36 AM To: Multiple recipients of list ORACLE-L that's interesting... since the data dictionary is NOT analyzed, setting optimizer_mode=choose would force the query against dba_extents to RULE which it what it is supposed to be doing anyway, Hm. The question now is, what is the optimizer_mode set to when the problem happens? Did any of the data dictionary tables get accidentally analyzed? And -- how many extents are in use in the database? Could it just be a symptom of missized tables and indexes so that the number of extents is way high? Although Oracle is *supposed* to allow unlimited extents, in practice anything higher than 4096 extents in an object (at least in 8i) tends to slow things down. |+--- || | || | || jack_silvey@y| || ahoo.com | || | || 05/09/2002 | || 02:18 PM | || Please | || respond to | || ORACLE-L | || | |+--- | || | To: [EMAIL PROTECTED] | | cc: (bcc: Rachel Carmichael) | | Subject: Re: DBA_EXTENTS problem | | Babu, We had a similiar problem, and setting optimizer_mode = choose in our session solved it. Something to do with optimizer and DD access. Give that a try. I had the same problem with DBA_INDEXES and that fixed it. hth, Jack --- Janardhana Babu Donga [EMAIL PROTECTED] wrote: Dear List, When I query dba_extents , Iam getting response after half an hour to 40 minutes, but when I query any other dictionary view it is spontaneous. Everything else is fine in the database and there are no problems, except the above problem. Iam not getting any clue how to fix this. Iam thinking of running catalog.sql and catproc.sql as a last resort. Iam not sure it would fix the problem. Please let me know if there is any other way to fix this problem. Thanks, Babu -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Janardhana Babu Donga INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). __ Do You Yahoo!? Yahoo! Shopping - Mother's Day is May 12th! http://shopping.yahoo.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Jack Silvey INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like
RE: Oracle Presentation for Beginners!
Aw. Looks like it got shutdown? :( And the URL you stated was the truncated version, but even the full URL gets this message. They probably weren't prepared for the grunt of the list hitting 'em... Rich Jesse System/Database Administrator [EMAIL PROTECTED] Quad/Tech International, Sussex, WI USA -Original Message- From: Steven Joshua [mailto:[EMAIL PROTECTED]] Sent: Thursday, May 09, 2002 2:00 PM To: Multiple recipients of list ORACLE-L Subject: RE: Oracle Presentation for Beginners! Any one got this message? Forbidden You don't have permission to access /~melody/courses/Spring2002Backup/BackupPPTS/les on this server. -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Jesse, Rich INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: DBA_EXTENTS problem
what does select count(*) from uet$ return? -Original Message- Sent: Thursday, May 09, 2002 2:54 PM To: Multiple recipients of list ORACLE-L Dennis, It is happenning for any table in the database. I have just created a new table: create table x1 (col1 number) tablespace data_ts, inserted one row and run: select * from dba_extents where segment_name = 'X1'; It is still sitting there. The response to any other dictionary view is spontaneous except this dba_extents, which is confusing me. I issued: select * from dba_objects where ..., select * from dba_segments where etc. They are all responding fine. There are not many users at the moment. System tablespace is 500M size and 300MB is free. DB is 40Gig in size. It is on HP_UX 11.0 and Oracle 8.1.7.2.0 (64bit) database. Any other ideas? Thanks, -- Babu -Original Message- Sent: Thursday, May 09, 2002 11:29 AM To: Multiple recipients of list ORACLE-L Babu - Wow, never had that one. Of course, I don't use that table much. How many rows are in this table? Mine has 12,937 rows, and is kinda slow to respond compared to the other system tables. Do you have a test database to compare with? Is it possible that your system tablespace has become badly fragmented? What is your system load, is it possibly a contention problem of some type. Why not run STATSPACK (or utlbstat) while you do this query and see what is being waited for? Why don't I shut up and send this? Dennis Williams DBA Lifetouch, Inc. [EMAIL PROTECTED] -Original Message- Sent: Thursday, May 09, 2002 12:58 PM To: Multiple recipients of list ORACLE-L Dear List, When I query dba_extents , Iam getting response after half an hour to 40 minutes, but when I query any other dictionary view it is spontaneous. Everything else is fine in the database and there are no problems, except the above problem. Iam not getting any clue how to fix this. Iam thinking of running catalog.sql and catproc.sql as a last resort. Iam not sure it would fix the problem. Please let me know if there is any other way to fix this problem. Thanks, Babu -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Janardhana Babu Donga INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: DENNIS WILLIAMS INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Janardhana Babu Donga INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Seefelt, Beth INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: RE: Good DBA vs. Bad DBA
Or flying an F-16 in Afghanistan at night... Regards, Patrice Boivin Systems Analyst (Oracle Certified DBA) E-Mail: [EMAIL PROTECTED] -Original Message- Sent: Thursday, May 09, 2002 3:39 PM To: Multiple recipients of list ORACLE-L Jerry, Aw, come on! Try handling a radar radome on a B-4 stand in a 10 MPH wind, now that's fun!! Dick Goulet Reply Separator Author: Whittle Jerome Contr NCI [EMAIL PROTECTED] Date: 5/9/2002 9:47 AM Database crashes don't worry me too much. Just some extra work (or looking for work if it was my fault.) I was an aircraft mechanic for over 20 years. Nothing like hearing that an aircraft you recently worked on crashed killing all on board! Happened to me three times. Thankfully it wasn't my fault. Or having coworkers die on the job from accidents. That happened twice. I became a DBA in part because (1) it pays better, (2) much less stressful, and (3) I don't have to change 250 lb wheel and tire assemblies in the snow on midnight shift! Jerry Whittle ACIFICS DBA NCI Information Systems Inc. [EMAIL PROTECTED] 618-622-4145 -Original Message- Sinardy Xing [EMAIL PROTECTED] Sent by: [EMAIL PROTECTED] 05/09/2002 12:48 AM Please respond to ORACLE-L Do you guys still afraid of database crash ? Or I just too sensitive or no confidence !DOCTYPE HTML PUBLIC -//W3C//DTD HTML 3.2//EN HTML HEAD TITLERE: Good DBA vs. Bad DBA/TITLE /HEAD BODY !-- Converted from text/rtf format -- PSPAN LANG=en-usFONT COLOR=#FF SIZE=2 FACE=ArialDatabase crashes don't worry me too much. Just some extra work (or looking for work if it was my fault.)/FONT/SPAN /P PSPAN LANG=en-usFONT COLOR=#FF SIZE=2 FACE=ArialI was an aircraft mechanic for over 20 years. Nothing like hearing that an aircraft you recently worked on crashed killing all on board! Happened to me three times. Thankfully it wasn't my fault. Or having coworkers die on the job from accidents. That happened twice./FONT/SPAN/P PSPAN LANG=en-usFONT COLOR=#FF SIZE=2 FACE=ArialI became a DBA in part because (1) it pays better, (2) much less stressful, and (3) I don't have to change 250 lb wheel and tire assemblies in the snownbsp; on midnight shift!/FONT/SPAN/P PSPAN LANG=en-usFONT FACE=ArialJerry Whittle/FONT/SPAN BRSPAN LANG=en-usFONT FACE=ArialACIFICS DBA/FONT/SPAN BRSPAN LANG=en-usFONT FACE=ArialNCI Information Systems Inc./FONT/SPAN BRSPAN LANG=en-usFONT FACE=Arial[EMAIL PROTECTED]/FONT/SPAN BRSPAN LANG=en-usFONT FACE=Arial618-622-4145/FONT/SPAN /P UL PSPAN LANG=en-usFONT SIZE=1 FACE=Arial-Original Message-/FONT/SPAN BRSPAN LANG=en-usFONT SIZE=2 FACE=Arialquot;Sinardy Xingquot; lt;[EMAIL PROTECTED]gt;/FONT/SPAN BRSPAN LANG=en-usFONT SIZE=2 FACE=ArialSent by: [EMAIL PROTECTED]/FONT/SPAN BRSPAN LANG=en-usFONT SIZE=2 FACE=Arial05/09/2002 12:48 AM/FONT/SPAN BRSPAN LANG=en-usFONT SIZE=2 FACE=ArialPlease respond to ORACLE-L/FONT/SPAN /P PSPAN LANG=en-usFONT SIZE=2 FACE=ArialDo you guys still afraid of database crash ? Or I just too sensitive or no /FONT/SPAN BRSPAN LANG=en-usFONT SIZE=2 FACE=Arialconfidence/FONT/SPAN /P /UL /BODY /HTML -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- 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 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (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: DBA_EXTENTS problem
or more accurately - select count(*) from sys.uet$; -Original Message- Sent: Thursday, May 09, 2002 2:03 PM To: '[EMAIL PROTECTED]' what does select count(*) from uet$ return? -Original Message- Sent: Thursday, May 09, 2002 2:54 PM To: Multiple recipients of list ORACLE-L Dennis, It is happenning for any table in the database. I have just created a new table: create table x1 (col1 number) tablespace data_ts, inserted one row and run: select * from dba_extents where segment_name = 'X1'; It is still sitting there. The response to any other dictionary view is spontaneous except this dba_extents, which is confusing me. I issued: select * from dba_objects where ..., select * from dba_segments where etc. They are all responding fine. There are not many users at the moment. System tablespace is 500M size and 300MB is free. DB is 40Gig in size. It is on HP_UX 11.0 and Oracle 8.1.7.2.0 (64bit) database. Any other ideas? Thanks, -- Babu -Original Message- Sent: Thursday, May 09, 2002 11:29 AM To: Multiple recipients of list ORACLE-L Babu - Wow, never had that one. Of course, I don't use that table much. How many rows are in this table? Mine has 12,937 rows, and is kinda slow to respond compared to the other system tables. Do you have a test database to compare with? Is it possible that your system tablespace has become badly fragmented? What is your system load, is it possibly a contention problem of some type. Why not run STATSPACK (or utlbstat) while you do this query and see what is being waited for? Why don't I shut up and send this? Dennis Williams DBA Lifetouch, Inc. [EMAIL PROTECTED] -Original Message- Sent: Thursday, May 09, 2002 12:58 PM To: Multiple recipients of list ORACLE-L Dear List, When I query dba_extents , Iam getting response after half an hour to 40 minutes, but when I query any other dictionary view it is spontaneous. Everything else is fine in the database and there are no problems, except the above problem. Iam not getting any clue how to fix this. Iam thinking of running catalog.sql and catproc.sql as a last resort. Iam not sure it would fix the problem. Please let me know if there is any other way to fix this problem. Thanks, Babu -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Janardhana Babu Donga INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: DENNIS WILLIAMS INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Janardhana Babu Donga INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Seefelt, Beth INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Re:RE: RE: Good DBA vs. Bad DBA
True enough. Allow me to say that since I've got all of the drives mirrored, I'm fairly immune to a single drive failure. BTW: There was one stock broker in the World Trade Center that I've heard of who was impacted by the previous years bombing subsequent database crash. So they put one of their mirrors in Houston Tx. using EMC's products. Now that IS protected!! Dick Goulet Reply Separator Author: Jesse; Rich [EMAIL PROTECTED] Date: 5/9/2002 10:36 AM -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]] Sent: Thursday, May 09, 2002 12:47 PM To: Multiple recipients of list ORACLE-L Subject: Re:RE: Good DBA vs. Bad DBA snip Now I've also got some real good SA's who likewise do not appreciate system crashes so we've put a lot of time effort into crash proofing things as much as possible. That means servers with redundant CPU's, power supplies, etc... Full disk mirroring with three not two mirrors. Connectivity software and scripts with our UPS system so we shutdown nicely before power runs out. And on top of all that a standby generator. So the servers and disks are fully protected. snip fully protected is awfully ambiguous. There was at least one company that thought it's data was fully protected because they had hot swap systems in both of the World Trade Center towers. Rich Jesse System/Database Administrator [EMAIL PROTECTED] Quad/Tech International, Sussex, WI USA -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Jesse, Rich INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (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: DBA_EXTENTS problem
I got the response from the count(*) query. It has returned 4855 and took nearly 30 minutes to respond. Thanks, -- Babu -Original Message- Sent: Thursday, May 09, 2002 11:54 AM To: Multiple recipients of list ORACLE-L Babu, What does this query return? select count(*) from dba_extents; Jared Janardhana Babu Donga [EMAIL PROTECTED] Sent by: [EMAIL PROTECTED] 05/09/2002 10:58 AM Please respond to ORACLE-L To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] cc: Subject:DBA_EXTENTS problem Dear List, When I query dba_extents , Iam getting response after half an hour to 40 minutes, but when I query any other dictionary view it is spontaneous. Everything else is fine in the database and there are no problems, except the above problem. Iam not getting any clue how to fix this. Iam thinking of running catalog.sql and catproc.sql as a last resort. Iam not sure it would fix the problem. Please let me know if there is any other way to fix this problem. Thanks, Babu -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Janardhana Babu Donga INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Janardhana Babu Donga INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (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: DBA_EXTENTS problem
Is it possible that some of the system owned tables were accidently analyzed? You might try running: select table_name from dba_tables where owner='SYS' and last_analyzed is not null; -Original Message- Sent: Thursday, May 09, 2002 1:59 PM To: Multiple recipients of list ORACLE-L Dear List, When I query dba_extents , Iam getting response after half an hour to 40 minutes, but when I query any other dictionary view it is spontaneous. Everything else is fine in the database and there are no problems, except the above problem. Iam not getting any clue how to fix this. Iam thinking of running catalog.sql and catproc.sql as a last resort. Iam not sure it would fix the problem. Please let me know if there is any other way to fix this problem. Thanks, Babu -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Janardhana Babu Donga INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Miller, Jay INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Re: Raw device backup script
Thank you Connor, but I need a script that backups over 20 raw devices datafiles to a TAPE without using a FS to store these datafiles first. TIA ___ Do You Yahoo!? Yahoo! Messenger Comunicación instantánea gratis con tu gente. http://messenger.yahoo.es -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: =?iso-8859-1?q?Pablo=20Rodriguez?= INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: Oracle Presentation for Beginners!
Yep. I believe you have to be a registered member of the class to see the class material. Ron ROR mª¿ªm [EMAIL PROTECTED] 05/09/02 02:59PM Any one got this message? Forbidden You don't have permission to access /~melody/courses/Spring2002Backup/BackupPPTS/les on this server. --- Sherman, Edward [EMAIL PROTECTED] wrote: Here's the slides! SQL/PL-SQL http://www.ecst.csuchico.edu/~melody/courses/Spg2001OraclePLSQL/PLSQL8iPPTS/ Default.htm Architecture and Admin http://www.ecst.csuchico.edu/~melody/courses/Fall2001CSCI379/TOC.html Performance Tuning http://www.ecst.csuchico.edu/~melody/courses/Spring2002PerfTune/TOC.html Backup and Recovery http://www.ecst.csuchico.edu/~melody/courses/Spring2002Backup/BackupPPTS/les 01_files/v3_document.htm http://www.ecst.csuchico.edu/~melody/courses/Spring2002Backup/BackupPPTS/les 02_files/v3_document.htm http://www.ecst.csuchico.edu/~melody/courses/Spring2002Backup/BackupPPTS/les 03_files/v3_document.htm And here's a gal after my own heart! http://www.ecst.csuchico.edu/~melody/ Thanks Melody!!! Ed -Original Message- Sent: Monday, May 06, 2002 5:09 AM To: Multiple recipients of list ORACLE-L Hello all, because I am an human being I am also looking for the easiest solution :-) So, I need to have a training session concerning basic oracle features. I wonder if anyone of you know place/site where I could download some powerpoint slides. Or if you have your own, pls. send me a copy ... Any help will be highly appreciated. Thanks km the lazy dba -- __ Ota itsellesi luotettava kotimainen email http://www.jippii.fi/ Tutustu samalla netin parhaaseen pelipaikkaan JIPPIIGAMESIIN. -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). * * * * * Freedom of Information Act Notice * * * * * The information in this email is subject to the record protection mandated by 5 United States Code 552 (b) (4) and relevant judicial opinions. -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Sherman, Edward INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). __ Do You Yahoo!? Yahoo! Shopping - Mother's Day is May 12th! http://shopping.yahoo.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Steven Joshua INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Ron Rogers INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: Oracle Presentation for Beginners!
You are not using the full url, check the next line for the rest of the url 01_files/v3_document.htm you need les01_files/v3_document.htm -Original Message- Sent: May 9, 2002 12:00 PM To: Multiple recipients of list ORACLE-L Any one got this message? Forbidden You don't have permission to access /~melody/courses/Spring2002Backup/BackupPPTS/les on this server. --- Sherman, Edward [EMAIL PROTECTED] wrote: Here's the slides! SQL/PL-SQL http://www.ecst.csuchico.edu/~melody/courses/Spg2001OraclePLSQL/PLSQL8iPPTS/ Default.htm Architecture and Admin http://www.ecst.csuchico.edu/~melody/courses/Fall2001CSCI379/TOC.html Performance Tuning http://www.ecst.csuchico.edu/~melody/courses/Spring2002PerfTune/TOC.html Backup and Recovery http://www.ecst.csuchico.edu/~melody/courses/Spring2002Backup/BackupPPTS/les 01_files/v3_document.htm http://www.ecst.csuchico.edu/~melody/courses/Spring2002Backup/BackupPPTS/les 02_files/v3_document.htm http://www.ecst.csuchico.edu/~melody/courses/Spring2002Backup/BackupPPTS/les 03_files/v3_document.htm And here's a gal after my own heart! http://www.ecst.csuchico.edu/~melody/ Thanks Melody!!! Ed -Original Message- Sent: Monday, May 06, 2002 5:09 AM To: Multiple recipients of list ORACLE-L Hello all, because I am an human being I am also looking for the easiest solution :-) So, I need to have a training session concerning basic oracle features. I wonder if anyone of you know place/site where I could download some powerpoint slides. Or if you have your own, pls. send me a copy ... Any help will be highly appreciated. Thanks km the lazy dba -- __ Ota itsellesi luotettava kotimainen email http://www.jippii.fi/ Tutustu samalla netin parhaaseen pelipaikkaan JIPPIIGAMESIIN. -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). * * * * * Freedom of Information Act Notice * * * * * The information in this email is subject to the record protection mandated by 5 United States Code 552 (b) (4) and relevant judicial opinions. -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Sherman, Edward INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). __ Do You Yahoo!? Yahoo! Shopping - Mother's Day is May 12th! http://shopping.yahoo.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Steven Joshua INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (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: Browett, Darren INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (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: DBA_EXTENTS problem
I used to run the following analyze every week: dbms_utility.analyze_schema(...) statement which included SYSTEM schema, Two weeks back it was changed to dbms_stats.gather_database_stats(); Is there anyway to de-analyze SYSTEM schema? Thanks, -- Babu -Original Message- Sent: Thursday, May 09, 2002 11:04 AM To: 'Janardhana Babu Donga' Babu - Among the suggestions you have received, the one that seems to have the most merit is whether any of your system tables have been analyzed. Were I in your position, I would check that out. Dennis Williams DBA Lifetouch, Inc. [EMAIL PROTECTED] -Original Message- Sent: Thursday, May 09, 2002 12:51 PM To: '[EMAIL PROTECTED]' Cc: DENNIS WILLIAMS Dennis, It is happenning for any table in the database. I have just created a new table: create table x1 (col1 number) tablespace data_ts, inserted one row and run: select * from dba_extents where segment_name = 'X1'; It is still sitting there. The response to any other dictionary view is spontaneous except this dba_extents, which is confusing me. I issued: select * from dba_objects where ..., select * from dba_segments where etc. They are all responding fine. There are not many users at the moment. System tablespace is 500M size and 300MB is free. DB is 40Gig in size. It is on HP_UX 11.0 and Oracle 8.1.7.2.0 (64bit) database. Any other ideas? Thanks, -- Babu -Original Message- Sent: Thursday, May 09, 2002 11:29 AM To: Multiple recipients of list ORACLE-L Babu - Wow, never had that one. Of course, I don't use that table much. How many rows are in this table? Mine has 12,937 rows, and is kinda slow to respond compared to the other system tables. Do you have a test database to compare with? Is it possible that your system tablespace has become badly fragmented? What is your system load, is it possibly a contention problem of some type. Why not run STATSPACK (or utlbstat) while you do this query and see what is being waited for? Why don't I shut up and send this? Dennis Williams DBA Lifetouch, Inc. [EMAIL PROTECTED] -Original Message- Sent: Thursday, May 09, 2002 12:58 PM To: Multiple recipients of list ORACLE-L Dear List, When I query dba_extents , Iam getting response after half an hour to 40 minutes, but when I query any other dictionary view it is spontaneous. Everything else is fine in the database and there are no problems, except the above problem. Iam not getting any clue how to fix this. Iam thinking of running catalog.sql and catproc.sql as a last resort. Iam not sure it would fix the problem. Please let me know if there is any other way to fix this problem. Thanks, Babu -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Janardhana Babu Donga INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: DENNIS WILLIAMS INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Janardhana Babu Donga INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Re: Oracle Presentation for Beginners!
Me too. JP On Thu 9. May 2002 20:59, you wrote: Any one got this message? Forbidden You don't have permission to access /~melody/courses/Spring2002Backup/BackupPPTS/les on this server. --- Sherman, Edward [EMAIL PROTECTED] wrote: Here's the slides! SQL/PL-SQL http://www.ecst.csuchico.edu/~melody/courses/Spg2001OraclePLSQL/PLSQL8iPPTS / Default.htm Architecture and Admin http://www.ecst.csuchico.edu/~melody/courses/Fall2001CSCI379/TOC.html Performance Tuning http://www.ecst.csuchico.edu/~melody/courses/Spring2002PerfTune/TOC.html Backup and Recovery http://www.ecst.csuchico.edu/~melody/courses/Spring2002Backup/BackupPPTS/le s 01_files/v3_document.htm http://www.ecst.csuchico.edu/~melody/courses/Spring2002Backup/BackupPPTS/le s 02_files/v3_document.htm http://www.ecst.csuchico.edu/~melody/courses/Spring2002Backup/BackupPPTS/le s 03_files/v3_document.htm And here's a gal after my own heart! http://www.ecst.csuchico.edu/~melody/ Thanks Melody!!! Ed -Original Message- Sent: Monday, May 06, 2002 5:09 AM To: Multiple recipients of list ORACLE-L Hello all, because I am an human being I am also looking for the easiest solution :-) So, I need to have a training session concerning basic oracle features. I wonder if anyone of you know place/site where I could download some powerpoint slides. Or if you have your own, pls. send me a copy ... Any help will be highly appreciated. Thanks km the lazy dba -- __ Ota itsellesi luotettava kotimainen email http://www.jippii.fi/ Tutustu samalla netin parhaaseen pelipaikkaan JIPPIIGAMESIIN. -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). * * * * * Freedom of Information Act Notice * * * * * The information in this email is subject to the record protection mandated by 5 United States Code 552 (b) (4) and relevant judicial opinions. -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Sherman, Edward INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). __ Do You Yahoo!? Yahoo! Shopping - Mother's Day is May 12th! http://shopping.yahoo.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Jan Pruner INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Re: too low optimizer_index_cost_adj causing bizarre index choice
There seems to be a perfectly good theoretical reason
for this. But it would be interesting to know:
Number of blocks below HWM
Setting for db_file_multiblock_read_count
Maximum usable value for db_file_mbrc
The cost given by Explain Plan for the tablescan
before I confuse the issue further by expounding a
hypothesis that may be totally misleading.
Jonathan Lewis
http://www.jlcomp.demon.co.uk
Author of:
Practical Oracle 8i: Building Efficient Databases
Next Seminar - Australia - July/August
http://www.jlcomp.demon.co.uk/seminar.html
Host to The Co-Operative Oracle Users' FAQ
http://www.jlcomp.demon.co.uk/faq/ind_faq.html
-Original Message-
To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED]
Date: 09 May 2002 14:30
|Good morning listers,
|
|Some weeks ago I noticed a query plan that was using a
|bizarre index choice and opened a TAR. Here is that
|query:
|
|select DSS_EMPR.V_CLAIM_EMPR_HX.AMT_COPAY from
|DSS_EMPR.V_CLAIM_EMPR_HX where
|DSS_EMPR.V_CLAIM_EMPR_HX.BATCH_NUM between
|to_date('2000-01-01','-mm-dd') and
|to_date('2000-01-31','-mm-dd');
|
|Here is the query plan:
|
| SELECT STATEMENT (all_rows) Cost
|(4924,636953,12739060)
|
| 1 0 1 2
|TABLE ACCESS (analyzed) DSS_EMPR T_CLAIM_EMPR_HX
|(by index rowid) Cost (4924,636953,12739060)
|
| 2 1 1
| BITMAP CONVERSION(to rowids)
|
| 3 2 1
|BITMAP INDEX DSS_EMPR X_CLAIM_EMPR_HX_N01
|(full scan)
|
|
|The index x_claim_empr_hx_n01 is on the phmcy_gid
|column of the t_claim_empr_hx table.
|
|Now, here is the crux of the matter: phmcy_gid is
|referred to nowhere in the query, not in select nor in
|where nor even in order by.
|
|Worked through the tar with Oracle and they advised
|that Oracle can and will cost ALL indexes during a
|plan parse and eval, so it became a matter of
|discovering why the index was being incorrectly
|costed.
|
|Remembered that our optimizer_index_cost_adj was set
|to 1 (don't ask). When I upped this value to 2 or more
|and reran the query, it returned the appropriate FTS
|plan.
|
|hth,
|
|Jack Silvey
|
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: Jonathan Lewis
INET: [EMAIL PROTECTED]
Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051
San Diego, California-- Public Internet access / Mailing Lists
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from). You may
also send the HELP command for other information (like subscribing).
Asinine security workarounds in Oracle, Part XXXXIII
So, a developer asks me Why can't I grant user CHARLIE access to my view in schema ALPHA? I look in our 8.1.7.2 DB and see this setup: Schema ALPHA: -- Simple table. CREATE TABLE my_table (col1 VARCHAR2(10), col2 VARCHAR2(10); -- View to join ALPHA table to BRAVO table. CREATE OR REPLACE VIEW my_view AS SELECT a.col1, b.col2 FROM my_table a, BRAVO.their_table b WHERE a.col1 = b.col1; Schema BRAVO: -- Simple table. CREATE TABLE their_table (col1 VARCHAR2(10), col2 VARCHAR2(10); -- Let everyone see it. GRANT SELECT ON their_table TO ALPHA; GRANT SELECT ON their_table TO CHARLIE; When I connect as ALPHA and try to GRANT SELECT ON my_view TO CHARLIE, I get: ORA-1720, GRANT OPTION does not exist for BRAVO.THEIR_TABLE Huh? Who cares? Everyone already has SELECT access to it! Who cares if ALPHA does not have the WITH GRANT OPTION? So, as a workaround, I do this while connected as ALPHA: -- Remove foreign tables from view. CREATE OR REPLACE VIEW my_view AS SELECT a.col1 FROM my_table a; -- Grant access. GRANT SELECT ON my_view TO CHARLIE; -- Replace view with original source. CREATE OR REPLACE VIEW my_view AS SELECT a.col1, b.col2 FROM my_table a, BRAVO.their_table b WHERE a.col1 = b.col1; And it all works now! Does anyone have a better way to do this, other than the GRANT OPTION, as Oracle Support states? Rich Jesse System/Database Administrator [EMAIL PROTECTED] Quad/Tech International, Sussex, WI USA Disclaimer: I'm cranky, it's beer day, and it's over 3 hours till beer. http://www.westbend.net/~legoman -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Jesse, Rich INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: DBA_EXTENTS problem
It returned 4855. The response is immediate. Count(*) from dba_extents also returned 4855 but took nearly 30 minutes. Thanks, -- Babu -Original Message- Sent: Thursday, May 09, 2002 12:14 PM To: Multiple recipients of list ORACLE-L what does select count(*) from uet$ return? -Original Message- Sent: Thursday, May 09, 2002 2:54 PM To: Multiple recipients of list ORACLE-L Dennis, It is happenning for any table in the database. I have just created a new table: create table x1 (col1 number) tablespace data_ts, inserted one row and run: select * from dba_extents where segment_name = 'X1'; It is still sitting there. The response to any other dictionary view is spontaneous except this dba_extents, which is confusing me. I issued: select * from dba_objects where ..., select * from dba_segments where etc. They are all responding fine. There are not many users at the moment. System tablespace is 500M size and 300MB is free. DB is 40Gig in size. It is on HP_UX 11.0 and Oracle 8.1.7.2.0 (64bit) database. Any other ideas? Thanks, -- Babu -Original Message- Sent: Thursday, May 09, 2002 11:29 AM To: Multiple recipients of list ORACLE-L Babu - Wow, never had that one. Of course, I don't use that table much. How many rows are in this table? Mine has 12,937 rows, and is kinda slow to respond compared to the other system tables. Do you have a test database to compare with? Is it possible that your system tablespace has become badly fragmented? What is your system load, is it possibly a contention problem of some type. Why not run STATSPACK (or utlbstat) while you do this query and see what is being waited for? Why don't I shut up and send this? Dennis Williams DBA Lifetouch, Inc. [EMAIL PROTECTED] -Original Message- Sent: Thursday, May 09, 2002 12:58 PM To: Multiple recipients of list ORACLE-L Dear List, When I query dba_extents , Iam getting response after half an hour to 40 minutes, but when I query any other dictionary view it is spontaneous. Everything else is fine in the database and there are no problems, except the above problem. Iam not getting any clue how to fix this. Iam thinking of running catalog.sql and catproc.sql as a last resort. Iam not sure it would fix the problem. Please let me know if there is any other way to fix this problem. Thanks, Babu -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Janardhana Babu Donga INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: DENNIS WILLIAMS INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Janardhana Babu Donga INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Seefelt, Beth INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). --
RE: DBA_EXTENTS problem
I thought using DBMS_STATS is much more efficient than the old method. I have recently read an article about it in Oracle magazine, Jan/Feb 2002 Page 32, then I changed the analyze script. I would appreciate if anyone sends me a script to delete the SYS/SYSTEM statistics. Thanks, -- Babu -Original Message- [mailto:[EMAIL PROTECTED]] Sent: Thursday, May 09, 2002 11:20 AM To: [EMAIL PROTECTED] I *think* there was a bug where it would also analyze SYS when you gathered database stats... why did you go from analyzing at the schema level to analyzing the entire database? You will need to delete the stats if they are there for any object owned by SYS. since I've never done this, I don't have a script to delete the stats... I know I've seen it posted to the list.. anyone have a copy? Rachel |+--- || | || | || jbdonga@ucdav| || is.edu | || | || 05/09/2002 | || 02:01 PM | || | |+--- | || | To: [EMAIL PROTECTED] | | cc: Rachel Carmichael@Sony_Music | | Subject: RE: DBA_EXTENTS problem | | I have recently changed the analyze script. Earliar it was dbms_utility.analyze_schema(...) statement, It is now changed to dbms_stats.gather_database_stats(); Is this a problem? The response time for select count(*) from dba_extents is also 30 minutes. It is not specific to any table. Thanks, -- Babu -Original Message- [mailto:[EMAIL PROTECTED]] Sent: Thursday, May 09, 2002 11:36 AM To: Multiple recipients of list ORACLE-L that's interesting... since the data dictionary is NOT analyzed, setting optimizer_mode=choose would force the query against dba_extents to RULE which it what it is supposed to be doing anyway, Hm. The question now is, what is the optimizer_mode set to when the problem happens? Did any of the data dictionary tables get accidentally analyzed? And -- how many extents are in use in the database? Could it just be a symptom of missized tables and indexes so that the number of extents is way high? Although Oracle is *supposed* to allow unlimited extents, in practice anything higher than 4096 extents in an object (at least in 8i) tends to slow things down. |+--- || | || | || jack_silvey@y| || ahoo.com | || | || 05/09/2002 | || 02:18 PM | || Please | || respond to | || ORACLE-L | || | |+--- | || | To: [EMAIL PROTECTED] | | cc: (bcc: Rachel Carmichael) | | Subject: Re: DBA_EXTENTS problem | | Babu, We had a similiar problem, and setting optimizer_mode = choose in our session solved it. Something to do with optimizer and DD access. Give that a try. I had the same problem with DBA_INDEXES and that fixed it. hth, Jack --- Janardhana Babu Donga [EMAIL PROTECTED] wrote: Dear List, When I query dba_extents , Iam getting response after half an hour to 40 minutes, but when I query any other dictionary view it is spontaneous. Everything else is fine in the database and there are no problems, except the above problem. Iam not getting any clue how to fix this. Iam thinking of running catalog.sql and catproc.sql as a last resort. Iam not sure it would fix the problem. Please let me know if there is any other way to fix this problem. Thanks, Babu -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Janardhana Babu Donga INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). __ Do You Yahoo!? Yahoo! Shopping - Mother's Day is May 12th!
RE: DBA_EXTENTS problem
It responded with 197 tables. Is it not the correct way to analyze? dbms_stats.gather_database_stats(); I have recently been using the above statement to analyze the database. Thanks, --Babu -Original Message- Sent: Thursday, May 09, 2002 12:24 PM To: Multiple recipients of list ORACLE-L Is it possible that some of the system owned tables were accidently analyzed? You might try running: select table_name from dba_tables where owner='SYS' and last_analyzed is not null; -Original Message- Sent: Thursday, May 09, 2002 1:59 PM To: Multiple recipients of list ORACLE-L Dear List, When I query dba_extents , Iam getting response after half an hour to 40 minutes, but when I query any other dictionary view it is spontaneous. Everything else is fine in the database and there are no problems, except the above problem. Iam not getting any clue how to fix this. Iam thinking of running catalog.sql and catproc.sql as a last resort. Iam not sure it would fix the problem. Please let me know if there is any other way to fix this problem. Thanks, Babu -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Janardhana Babu Donga INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Miller, Jay INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Janardhana Babu Donga INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (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: DBA_EXTENTS problem
Jack, Output from show parameter optim: Optimizer_cache_optimal_size 102400 optimizer_features_enable 8.1.7 optimizer_index_caching 0 optimizer_index_cost_adj 100 optimizer_max_permutations8 optimizer_modechoose optimizer_percent_parallel0 Immediate response for select /*+RULE*/ count(1) from dba_extents Count(1) - 4855 select count(1) from dba_extents is still sitting there and has not yet responded. Thanks, -- Babu -Original Message- Sent: Thursday, May 09, 2002 12:41 PM To: Multiple recipients of list ORACLE-L Babu, issue this in your sqlplus session: show parameter optim and then this: alter session set optimizer_mode=choose; and then this: select /*+ RULE */ count(1) from dba_extents; and then this: select count(1) from dba_extents; and let us know what happens. Rachel: we were running all_rows in our dwh and it was slowing down dd access. not sure why, choose and all_rows are kissing cousins. true, dd is supposed to be rule and with no stats. Have heard of cases where dbms_statistics analyzed the sys schema while doing a analyze_database, which is one reason I stopped using it. Might be the problem here? We will find out. hth, jack --- Janardhana Babu Donga [EMAIL PROTECTED] wrote: I have recently changed the analyze script. Earliar it was dbms_utility.analyze_schema(...) statement, It is now changed to dbms_stats.gather_database_stats(); Is this a problem? The response time for select count(*) from dba_extents is also 30 minutes. It is not specific to any table. Thanks, -- Babu -Original Message- [mailto:[EMAIL PROTECTED]] Sent: Thursday, May 09, 2002 11:36 AM To: Multiple recipients of list ORACLE-L that's interesting... since the data dictionary is NOT analyzed, setting optimizer_mode=choose would force the query against dba_extents to RULE which it what it is supposed to be doing anyway, Hm. The question now is, what is the optimizer_mode set to when the problem happens? Did any of the data dictionary tables get accidentally analyzed? And -- how many extents are in use in the database? Could it just be a symptom of missized tables and indexes so that the number of extents is way high? Although Oracle is *supposed* to allow unlimited extents, in practice anything higher than 4096 extents in an object (at least in 8i) tends to slow things down. |+--- || | || | || jack_silvey@y| || ahoo.com | || | || 05/09/2002 | || 02:18 PM | || Please | || respond to | || ORACLE-L | || | |+--- | | | | To: [EMAIL PROTECTED] | | cc: (bcc: Rachel Carmichael) | | Subject: Re: DBA_EXTENTS problem | | Babu, We had a similiar problem, and setting optimizer_mode = choose in our session solved it. Something to do with optimizer and DD access. Give that a try. I had the same problem with DBA_INDEXES and that fixed it. hth, Jack --- Janardhana Babu Donga [EMAIL PROTECTED] wrote: Dear List, When I query dba_extents , Iam getting response after half an hour to 40 minutes, but when I query any other dictionary view it is spontaneous. Everything else is fine in the database and there are no problems, except the above problem. Iam not getting any clue how to fix this. Iam thinking of running catalog.sql and catproc.sql as a last resort. Iam not sure it would fix the problem. Please let me know if there is any other way to fix this problem. Thanks, Babu -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Janardhana Babu Donga INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). __ Do You Yahoo!? Yahoo! Shopping - Mother's Day is May 12th! http://shopping.yahoo.com -- Please see the official ORACLE-L FAQ:
Re: DBA_EXTENTS problem
dbms_stats.gather_database_stats has a bug, it analyzes SYS objects.
From Metalink:
Bug:1422285 is a severity 3 bug that was logged for the
dbms_stats.gather_database_stats collecting statistics on the data
dictionary objects (FET$ was the example). This bug is fixed in 9i. A
workaround is to execute dbms_stats.gather_database_statistics +
dbms_stats.delete_schema_stats('SYS').
Janardhana Babu Donga wrote:
I have recently changed the analyze script. Earliar it was
dbms_utility.analyze_schema(...) statement, It is now changed to
dbms_stats.gather_database_stats();
Is this a problem?
The response time for select count(*) from dba_extents is also 30 minutes.
It is not specific to any table.
Thanks,
-- Babu
-Original Message-
[mailto:[EMAIL PROTECTED]]
Sent: Thursday, May 09, 2002 11:36 AM
To: Multiple recipients of list ORACLE-L
that's interesting... since the data dictionary is NOT analyzed, setting
optimizer_mode=choose would force the query against dba_extents to RULE
which it
what it is supposed to be doing anyway, Hm. The question now is, what is the
optimizer_mode set to when the problem happens? Did any of the data
dictionary
tables get accidentally analyzed? And -- how many extents are in use in the
database? Could it just be a symptom of missized tables and indexes so that
the
number of extents is way high?
Although Oracle is *supposed* to allow unlimited extents, in practice
anything
higher than 4096 extents in an object (at least in 8i) tends to slow things
down.
|+---
|| |
|| |
|| jack_silvey@y|
|| ahoo.com |
|| |
|| 05/09/2002 |
|| 02:18 PM |
|| Please |
|| respond to |
|| ORACLE-L |
|| |
|+---
|
||
| To: [EMAIL PROTECTED] |
| cc: (bcc: Rachel Carmichael) |
| Subject: Re: DBA_EXTENTS problem |
|
Babu,
We had a similiar problem, and setting optimizer_mode
= choose in our session solved it. Something to do
with optimizer and DD access. Give that a try. I had
the same problem with DBA_INDEXES and that fixed it.
hth,
Jack
--- Janardhana Babu Donga [EMAIL PROTECTED] wrote:
Dear List,
When I query dba_extents , Iam getting response
after half an hour to 40
minutes, but when I query any other dictionary view
it is spontaneous.
Everything else is fine in the database and there
are no problems, except
the above problem. Iam not getting any clue how to
fix this. Iam thinking of
running catalog.sql and catproc.sql as a last
resort. Iam not sure it would
fix the problem. Please let me know if there is any
other way to fix this
problem.
Thanks,
Babu
--
Please see the official ORACLE-L FAQ:
http://www.orafaq.com
--
Author: Janardhana Babu Donga
INET: [EMAIL PROTECTED]
Fat City Network Services-- (858) 538-5051 FAX:
(858) 538-5051
San Diego, California-- Public Internet
access / Mailing Lists
To REMOVE yourself from this mailing list, send an
E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of
'ListGuru') and in
the message BODY, include a line containing: UNSUB
ORACLE-L
(or the name of mailing list you want to be removed
from). You may
also send the HELP command for other information
(like subscribing).
__
Do You Yahoo!?
Yahoo! Shopping - Mother's Day is May 12th!
http://shopping.yahoo.com
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: Jack Silvey
INET: [EMAIL PROTECTED]
Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051
San Diego, California-- Public Internet access / Mailing Lists
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from). You may
also send the HELP command for other information (like subscribing).
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author:
INET: [EMAIL PROTECTED]
Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051
San Diego, California-- Public Internet access / Mailing Lists
RE: DBA_EXTENTS problem
Babu, issue this in your sqlplus session: show parameter optim and then this: alter session set optimizer_mode=choose; and then this: select /*+ RULE */ count(1) from dba_extents; and then this: select count(1) from dba_extents; and let us know what happens. Rachel: we were running all_rows in our dwh and it was slowing down dd access. not sure why, choose and all_rows are kissing cousins. true, dd is supposed to be rule and with no stats. Have heard of cases where dbms_statistics analyzed the sys schema while doing a analyze_database, which is one reason I stopped using it. Might be the problem here? We will find out. hth, jack --- Janardhana Babu Donga [EMAIL PROTECTED] wrote: I have recently changed the analyze script. Earliar it was dbms_utility.analyze_schema(...) statement, It is now changed to dbms_stats.gather_database_stats(); Is this a problem? The response time for select count(*) from dba_extents is also 30 minutes. It is not specific to any table. Thanks, -- Babu -Original Message- [mailto:[EMAIL PROTECTED]] Sent: Thursday, May 09, 2002 11:36 AM To: Multiple recipients of list ORACLE-L that's interesting... since the data dictionary is NOT analyzed, setting optimizer_mode=choose would force the query against dba_extents to RULE which it what it is supposed to be doing anyway, Hm. The question now is, what is the optimizer_mode set to when the problem happens? Did any of the data dictionary tables get accidentally analyzed? And -- how many extents are in use in the database? Could it just be a symptom of missized tables and indexes so that the number of extents is way high? Although Oracle is *supposed* to allow unlimited extents, in practice anything higher than 4096 extents in an object (at least in 8i) tends to slow things down. |+--- || | || | || jack_silvey@y| || ahoo.com | || | || 05/09/2002 | || 02:18 PM | || Please | || respond to | || ORACLE-L | || | |+--- | | | | To: [EMAIL PROTECTED] | | cc: (bcc: Rachel Carmichael) | | Subject: Re: DBA_EXTENTS problem | | Babu, We had a similiar problem, and setting optimizer_mode = choose in our session solved it. Something to do with optimizer and DD access. Give that a try. I had the same problem with DBA_INDEXES and that fixed it. hth, Jack --- Janardhana Babu Donga [EMAIL PROTECTED] wrote: Dear List, When I query dba_extents , Iam getting response after half an hour to 40 minutes, but when I query any other dictionary view it is spontaneous. Everything else is fine in the database and there are no problems, except the above problem. Iam not getting any clue how to fix this. Iam thinking of running catalog.sql and catproc.sql as a last resort. Iam not sure it would fix the problem. Please let me know if there is any other way to fix this problem. Thanks, Babu -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Janardhana Babu Donga INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). __ Do You Yahoo!? Yahoo! Shopping - Mother's Day is May 12th! http://shopping.yahoo.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Jack Silvey INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other
RE: DBA_EXTENTS problem
Babu,
While you're waiting, run this query to see what you're waiting on.
Jared
select
s.username username,
e.event event,
s.sid,
e.p1text,
e.p1,
e.p2text,
e.p2,
e.wait_time,
e.seconds_in_wait,
e.state
from v$session s, v$session_wait e
where s.username is not null
and s.sid = e.sid
and s.username like upper('uusername')
-- skip sqlnet idle session messages
and e.event not like '%message%client'
order by s.username, upper(e.event);
Janardhana Babu Donga [EMAIL PROTECTED]
05/09/2002 11:03 AM
To: '[EMAIL PROTECTED]' [EMAIL PROTECTED]
cc: '[EMAIL PROTECTED]' [EMAIL PROTECTED]
Subject:RE: DBA_EXTENTS problem
Jared,
It is taking 30 to 40 minutes to respond. I just issued again and is
sitting
there. Iam sure it would respond after 30 minutes as happenned many times.
I would E-Mail after getting the response.
Thanks,
-- Janardhana Babu
-Original Message-
Sent: Thursday, May 09, 2002 11:54 AM
To: Multiple recipients of list ORACLE-L
Babu,
What does this query return?
select count(*) from dba_extents;
Jared
Janardhana Babu Donga [EMAIL PROTECTED]
Sent by: [EMAIL PROTECTED]
05/09/2002 10:58 AM
Please respond to ORACLE-L
To: Multiple recipients of list ORACLE-L
[EMAIL PROTECTED]
cc:
Subject:DBA_EXTENTS problem
Dear List,
When I query dba_extents , Iam getting response after half an hour to 40
minutes, but when I query any other dictionary view it is spontaneous.
Everything else is fine in the database and there are no problems, except
the above problem. Iam not getting any clue how to fix this. Iam thinking
of
running catalog.sql and catproc.sql as a last resort. Iam not sure it
would
fix the problem. Please let me know if there is any other way to fix this
problem.
Thanks,
Babu
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: Janardhana Babu Donga
INET: [EMAIL PROTECTED]
Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051
San Diego, California-- Public Internet access / Mailing Lists
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from). You may
also send the HELP command for other information (like subscribing).
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author:
INET: [EMAIL PROTECTED]
Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051
San Diego, California-- Public Internet access / Mailing Lists
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from). You may
also send the HELP command for other information (like subscribing).
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author:
INET: [EMAIL PROTECTED]
Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051
San Diego, California-- Public Internet access / Mailing Lists
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from). You may
also send the HELP command for other information (like subscribing).
I have a black hole in my database
Oracle Version 8.1.7.3 64-bit OS Solaris 8 10/1 Server platform Sunfire 38000 Client Platform WIN2k I have somehow created a black hole in my database. While installing designer 6i my process freezes while creating the RM_ELEMENTS view. I do not have any errors in my alert logs. If I create a new SQL*PLUS session it hangs before logging in. If I bring up DBA Studios it hangs. My only recover so far has been to shutdown abort the instance. If I run the 'cdapirvw.sql' script (this creates the views) it goes to end of job successfully. No errors. If I then run something simple like 'select count(*) from rm_elements' the instance hangs. If I attempt to grant rm_elements to a role the instance hangs. I can descibe rm_elements successfully. I can drop it. The create view statement for rm_elements is incredibly long. here is an example CREATE OR REPLACE FORCE VIEW rm_elements AS SELECT sdd_a1.changed_by ,sdd_a1.created_by ,sdd_a1.date_changed ,sdd_a1.date_created ,sdd_a1.irid id ,sdd_a1.notm number_of_times_modified ,sdd_a1.types FROM sdd_a1 sdd_a1 UNION ALL SELECT sdd_a2.changed_by ,sdd_a2.created_by ,sdd_a2.date_changed ,sdd_a2.date_created ,sdd_a2.irid id ,sdd_a2.notm number_of_times_modified ,sdd_a2.types FROM sdd_a10 sdd_a2 UNION ALL SELECT sdd_a3.changed_by ,sdd_a3.created_by ,sdd_a3.date_changed ,sdd_a3.date_created ,sdd_a3.irid id ,sdd_a3.notm number_of_times_modified ,sdd_a3.types FROM sdd_a11 sdd_a3 There are about 30 or 40 unions at least in this view. The command is a couple of 100 lines long. Has anyone had a problem like this or a suggestion I could pursue? I am working with Oracle support but have not had a resolution yet. -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Alec Macdonell INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (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: DBA_EXTENTS problem
Yes, I ran the query from the SYS schema and it returned the same number:4855, the same as count(*) from dba_extents. -- Babu -Original Message- Sent: Thursday, May 09, 2002 12:14 PM To: Multiple recipients of list ORACLE-L or more accurately - select count(*) from sys.uet$; -Original Message- Sent: Thursday, May 09, 2002 2:03 PM To: '[EMAIL PROTECTED]' what does select count(*) from uet$ return? -Original Message- Sent: Thursday, May 09, 2002 2:54 PM To: Multiple recipients of list ORACLE-L Dennis, It is happenning for any table in the database. I have just created a new table: create table x1 (col1 number) tablespace data_ts, inserted one row and run: select * from dba_extents where segment_name = 'X1'; It is still sitting there. The response to any other dictionary view is spontaneous except this dba_extents, which is confusing me. I issued: select * from dba_objects where ..., select * from dba_segments where etc. They are all responding fine. There are not many users at the moment. System tablespace is 500M size and 300MB is free. DB is 40Gig in size. It is on HP_UX 11.0 and Oracle 8.1.7.2.0 (64bit) database. Any other ideas? Thanks, -- Babu -Original Message- Sent: Thursday, May 09, 2002 11:29 AM To: Multiple recipients of list ORACLE-L Babu - Wow, never had that one. Of course, I don't use that table much. How many rows are in this table? Mine has 12,937 rows, and is kinda slow to respond compared to the other system tables. Do you have a test database to compare with? Is it possible that your system tablespace has become badly fragmented? What is your system load, is it possibly a contention problem of some type. Why not run STATSPACK (or utlbstat) while you do this query and see what is being waited for? Why don't I shut up and send this? Dennis Williams DBA Lifetouch, Inc. [EMAIL PROTECTED] -Original Message- Sent: Thursday, May 09, 2002 12:58 PM To: Multiple recipients of list ORACLE-L Dear List, When I query dba_extents , Iam getting response after half an hour to 40 minutes, but when I query any other dictionary view it is spontaneous. Everything else is fine in the database and there are no problems, except the above problem. Iam not getting any clue how to fix this. Iam thinking of running catalog.sql and catproc.sql as a last resort. Iam not sure it would fix the problem. Please let me know if there is any other way to fix this problem. Thanks, Babu -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Janardhana Babu Donga INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: DENNIS WILLIAMS INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Janardhana Babu Donga INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Seefelt, Beth INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L
RE: DBA_EXTENTS problem
It is waiting on the event: db file scattered read.
Thanks,
--Babu
-Original Message-
Sent: Thursday, May 09, 2002 11:33 AM
To: [EMAIL PROTECTED]
Cc: Janardhana Babu Donga
Babu,
While you're waiting, run this query to see what you're waiting on.
Jared
select
s.username username,
e.event event,
s.sid,
e.p1text,
e.p1,
e.p2text,
e.p2,
e.wait_time,
e.seconds_in_wait,
e.state
from v$session s, v$session_wait e
where s.username is not null
and s.sid = e.sid
and s.username like upper('uusername')
-- skip sqlnet idle session messages
and e.event not like '%message%client'
order by s.username, upper(e.event);
Janardhana Babu Donga [EMAIL PROTECTED]
05/09/2002 11:03 AM
To: '[EMAIL PROTECTED]' [EMAIL PROTECTED]
cc: '[EMAIL PROTECTED]' [EMAIL PROTECTED]
Subject:RE: DBA_EXTENTS problem
Jared,
It is taking 30 to 40 minutes to respond. I just issued again and is
sitting
there. Iam sure it would respond after 30 minutes as happenned many times.
I would E-Mail after getting the response.
Thanks,
-- Janardhana Babu
-Original Message-
Sent: Thursday, May 09, 2002 11:54 AM
To: Multiple recipients of list ORACLE-L
Babu,
What does this query return?
select count(*) from dba_extents;
Jared
Janardhana Babu Donga [EMAIL PROTECTED]
Sent by: [EMAIL PROTECTED]
05/09/2002 10:58 AM
Please respond to ORACLE-L
To: Multiple recipients of list ORACLE-L
[EMAIL PROTECTED]
cc:
Subject:DBA_EXTENTS problem
Dear List,
When I query dba_extents , Iam getting response after half an hour to 40
minutes, but when I query any other dictionary view it is spontaneous.
Everything else is fine in the database and there are no problems, except
the above problem. Iam not getting any clue how to fix this. Iam thinking
of
running catalog.sql and catproc.sql as a last resort. Iam not sure it
would
fix the problem. Please let me know if there is any other way to fix this
problem.
Thanks,
Babu
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: Janardhana Babu Donga
INET: [EMAIL PROTECTED]
Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051
San Diego, California-- Public Internet access / Mailing Lists
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from). You may
also send the HELP command for other information (like subscribing).
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author:
INET: [EMAIL PROTECTED]
Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051
San Diego, California-- Public Internet access / Mailing Lists
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from). You may
also send the HELP command for other information (like subscribing).
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: Janardhana Babu Donga
INET: [EMAIL PROTECTED]
Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051
San Diego, California-- Public Internet access / Mailing Lists
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from). You may
also send the HELP command for other information (like subscribing).
optimizer not using function based indexes
Hi,
We are trying to use function based indexes in our application but it looks like
optimizer is not using them.
So we created a sample table emp with 3 columns empno,ename,sal and populate
this table with 10 rows and created function based index on upper(ename).
we try to run simple query like below and optimzer is not using index:
select sal from emp
where upper(ename)=upper('abc98');
Even we use index hint optimizer is still not using index.
1) table is analyzed as compute after creating index.
2) query rewrite is granted to user
3) query_rewrite_enabled=true.
4) optimzer mode is choose.
5) optimer should use index as there is no way full scan can be faster than index in
this case as i tried to create simple
index on ename and remove upper function from where clause of query..
What can be the possible reason that optimzer is not using index??
Thanks
--Harvinder
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: Harvinder Singh
INET: [EMAIL PROTECTED]
Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051
San Diego, California-- Public Internet access / Mailing Lists
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(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: Clone Database
Ken, The opening was stated incorrectly. HR doesn't know the difference between a DBA a DA. We are looking for someone to do logical design data modeling, not to administrator a database. The platform is Oracle. Bill GentryDBAAllina Health SystemMinneapolis, MN 55403612-775-1190[EMAIL PROTECTED] - Original Message - From: KENNETH JANUSZ To: Multiple recipients of list ORACLE-L Sent: Wednesday, May 08, 2002 3:03 PM Subject: Re: Clone Database Bill: I noticed that you are with Allina. I'm currently looking for an Oracle DBA (or related) position. I noticed on your web site that Allina has an opening fora DBA-Intermed. Business Unit: System Office. Req.#: 046690. Would you know if this is an Oracle DB or otherwise? The description doesn't name the DB. Thanks, Ken Janusz - Original Message - From: Bill Gentry To: Multiple recipients of list ORACLE-L Sent: Wednesday, May 08, 2002 12:40 PM Subject: Re: Clone Database I also have a 70 Gb production database and have to clone it to several test instances. I just delete all the test files, copy all the .dbf files over from a cold backup [or files archives from a hot backup], and rebuild the controlfiles/log files. Takes about 2 hours. Bill GentryDBAAllina Health SystemMinneapolis, MN 55403612-775-1190[EMAIL PROTECTED] - Original Message - From: Ramon E. Estevez To: Multiple recipients of list ORACLE-L Sent: Tuesday, May 07, 2002 9:28 AM Subject: Clone Database I have to refresh a development database every day with the production DB. The size of the DB is 70GB and import last like 7 hours. Does anyone in the list has the steps to clone a database, not using import. TIA Ramon E. Estevez
Re: Cloning Question
I'm sorry, and I should know better. We are on 8.1.7.0.0 on Sun Solaris 2.6. [EMAIL PROTECTED] wrote: In some versions of Oracle, you need a SECOND rollback segment created in the SYSTEM tablespace in order to create another tablespace. It can't hurt to add it (you can drop it immediately after you get the rollback tablespace created) but it could be the solution. Since you didn't include the version of the database you are working with, we have to be somewhat generic in our answers Rachel |+--- || | || | || srcdco@ritvax| || .rit.edu | || | || 05/09/2002 | || 02:28 PM | || Please | || respond to | || ORACLE-L | || | |+--- | || | To: [EMAIL PROTECTED] | | cc: (bcc: Rachel Carmichael) | | Subject: Re: Cloning Question| | I thought there was a rollback segment in system that would be there. It should be enough to get things started. After all, there isn't a separate one when you create a database, until you create it. DENNIS WILLIAMS wrote: Scott - I think that Rachel offered better advice, and I'm sure you've read her note by now. I have some experience with missing files when cloning a database :-), but I haven't had the rollback tablespace missing, and that would be different. One thought would be to create an extra rollback tablespace on production, so that when you perform the clone operation, Oracle still has some rollback segments available. Dennis Williams DBA Lifetouch, Inc. [EMAIL PROTECTED] -Original Message- Sent: Thursday, May 09, 2002 12:03 PM To: Multiple recipients of list ORACLE-L Dennis, No. I did a create controlfile set database... I commented out the rollback_segments line in the init.ora and removed the reference to the file in the create controlfile statement. With no reference (that I know of) to the rollback segments or tablespace, I thought that the database should have come up, then I could create a new one. I must have missed something, but I don't know what that was. DENNIS WILLIAMS wrote: Scott - Let's see if I understand specifically what you did. You ran the CREATE DATABASE script that was generated from your production system. Now, since the datafile for rollback tablespace isn't there, Oracle will come up without it. Once your new instance was up, then you did a DROP TABLESPACE on the rollback tablespace, followed by a CREATE TABLESPACE. I've done that scenario quite a few times on Oracle 8.1.6, just yesterday, as a matter of fact (that was a temp tablespace). If those weren't your steps, then provide more details. Dennis Williams DBA Lifetouch, Inc. [EMAIL PROTECTED] -Original Message- Sent: Thursday, May 09, 2002 8:48 AM To: Multiple recipients of list ORACLE-L I spent a long time trying to clone our production data warehouse into test, to refresh it. I do this frequently, as both a way to refresh test instances and to test the backups. After fighting through 2 bad backups, I finally got a good one. However, the problem I had was that the production rollback tablespace is larger than the disk on test. My thought was that I could restore everything except the rollback tablespace, which is not the way I usually do it. I commented out the rollback_segments line in the init.ora and created a script that would create a new rollback tablespace and rollback segments, smaller than those in production. This didn't work, even though Oracle support said it should have. When I did the clone, the database would not open. I kept getting the following error: ORA-00600: internal error code, arguments: [25012], [1], [2], [], [], [], [], [] Does anyone know what I was doing wrong? I can't figure it out, and Oracle support couldn't figure it out, either. I was able to get the database up by getting more disk added, so that there was enough space to restore the rollback tablespace file. Thank you. -- Scott Canaan ([EMAIL PROTECTED]) (585) 475-7886 Life is like a sewer, what you get out of it depends on what you put into it - Tom Lehrer -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Scott Canaan INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California
RE: Oracle Presentation for Beginners!
This was not always the case. We must have flooded the server and they shut off public access. There are other sites that have the slides. The slides are actually provided BY Oracle as part of some educational deal (ie. pre-marketing campaign!). But I have found these on the net in the past. Next time I see them, I will download 'em. Also, when this discussion first occurred, I was able to get up on the site and view everything. Hannah -Original Message- From: [EMAIL PROTECTED]@SUNGARD On Behalf Of Ron Rogers [EMAIL PROTECTED] Sent: Thursday, May 09, 2002 3:45 PM To: Multiple recipients of list ORACLE-L Subject: RE: Oracle Presentation for Beginners! Yep. I believe you have to be a registered member of the class to see the class material. Ron ROR mª¿ªm [EMAIL PROTECTED] 05/09/02 02:59PM Any one got this message? Forbidden You don't have permission to access /~melody/courses/Spring2002Backup/BackupPPTS/les on this server. Ed . -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: DBA_EXTENTS problem
I have just now deleted the 'SYS' schema statistics and executed select
count(*) from dba_extents, and there is no improvement. It is still sitting
there.
Thanks,
--Babu
-Original Message-
Sent: Thursday, May 09, 2002 12:45 PM
To: Multiple recipients of list ORACLE-L
dbms_stats.gather_database_stats has a bug, it analyzes SYS objects.
From Metalink:
Bug:1422285 is a severity 3 bug that was logged for the
dbms_stats.gather_database_stats collecting statistics on the data
dictionary objects (FET$ was the example). This bug is fixed in 9i. A
workaround is to execute dbms_stats.gather_database_statistics +
dbms_stats.delete_schema_stats('SYS').
Janardhana Babu Donga wrote:
I have recently changed the analyze script. Earliar it was
dbms_utility.analyze_schema(...) statement, It is now changed to
dbms_stats.gather_database_stats();
Is this a problem?
The response time for select count(*) from dba_extents is also 30 minutes.
It is not specific to any table.
Thanks,
-- Babu
-Original Message-
[mailto:[EMAIL PROTECTED]]
Sent: Thursday, May 09, 2002 11:36 AM
To: Multiple recipients of list ORACLE-L
that's interesting... since the data dictionary is NOT analyzed, setting
optimizer_mode=choose would force the query against dba_extents to RULE
which it
what it is supposed to be doing anyway, Hm. The question now is, what is
the
optimizer_mode set to when the problem happens? Did any of the data
dictionary
tables get accidentally analyzed? And -- how many extents are in use in
the
database? Could it just be a symptom of missized tables and indexes so
that
the
number of extents is way high?
Although Oracle is *supposed* to allow unlimited extents, in practice
anything
higher than 4096 extents in an object (at least in 8i) tends to slow
things
down.
|+---
|| |
|| |
|| jack_silvey@y|
|| ahoo.com |
|| |
|| 05/09/2002 |
|| 02:18 PM |
|| Please |
|| respond to |
|| ORACLE-L |
|| |
|+---
|
||
| To: [EMAIL PROTECTED] |
| cc: (bcc: Rachel Carmichael) |
| Subject: Re: DBA_EXTENTS problem |
|
Babu,
We had a similiar problem, and setting optimizer_mode
= choose in our session solved it. Something to do
with optimizer and DD access. Give that a try. I had
the same problem with DBA_INDEXES and that fixed it.
hth,
Jack
--- Janardhana Babu Donga [EMAIL PROTECTED] wrote:
Dear List,
When I query dba_extents , Iam getting response
after half an hour to 40
minutes, but when I query any other dictionary view
it is spontaneous.
Everything else is fine in the database and there
are no problems, except
the above problem. Iam not getting any clue how to
fix this. Iam thinking of
running catalog.sql and catproc.sql as a last
resort. Iam not sure it would
fix the problem. Please let me know if there is any
other way to fix this
problem.
Thanks,
Babu
--
Please see the official ORACLE-L FAQ:
http://www.orafaq.com
--
Author: Janardhana Babu Donga
INET: [EMAIL PROTECTED]
Fat City Network Services-- (858) 538-5051 FAX:
(858) 538-5051
San Diego, California-- Public Internet
access / Mailing Lists
To REMOVE yourself from this mailing list, send an
E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of
'ListGuru') and in
the message BODY, include a line containing: UNSUB
ORACLE-L
(or the name of mailing list you want to be removed
from). You may
also send the HELP command for other information
(like subscribing).
__
Do You Yahoo!?
Yahoo! Shopping - Mother's Day is May 12th!
http://shopping.yahoo.com
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: Jack Silvey
INET: [EMAIL PROTECTED]
Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051
San Diego, California-- Public Internet access / Mailing Lists
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from). You may
also send the HELP command for other information (like subscribing).
--
Please see the official
RE: DBA_EXTENTS problem
It is waiting on the event: db file scattered read
Thanks,
--Babu
-Original Message-
Sent: Thursday, May 09, 2002 12:41 PM
To: Multiple recipients of list ORACLE-L
Babu,
While you're waiting, run this query to see what you're waiting on.
Jared
select
s.username username,
e.event event,
s.sid,
e.p1text,
e.p1,
e.p2text,
e.p2,
e.wait_time,
e.seconds_in_wait,
e.state
from v$session s, v$session_wait e
where s.username is not null
and s.sid = e.sid
and s.username like upper('uusername')
-- skip sqlnet idle session messages
and e.event not like '%message%client'
order by s.username, upper(e.event);
Janardhana Babu Donga [EMAIL PROTECTED]
05/09/2002 11:03 AM
To: '[EMAIL PROTECTED]' [EMAIL PROTECTED]
cc: '[EMAIL PROTECTED]' [EMAIL PROTECTED]
Subject:RE: DBA_EXTENTS problem
Jared,
It is taking 30 to 40 minutes to respond. I just issued again and is
sitting
there. Iam sure it would respond after 30 minutes as happenned many times.
I would E-Mail after getting the response.
Thanks,
-- Janardhana Babu
-Original Message-
Sent: Thursday, May 09, 2002 11:54 AM
To: Multiple recipients of list ORACLE-L
Babu,
What does this query return?
select count(*) from dba_extents;
Jared
Janardhana Babu Donga [EMAIL PROTECTED]
Sent by: [EMAIL PROTECTED]
05/09/2002 10:58 AM
Please respond to ORACLE-L
To: Multiple recipients of list ORACLE-L
[EMAIL PROTECTED]
cc:
Subject:DBA_EXTENTS problem
Dear List,
When I query dba_extents , Iam getting response after half an hour to 40
minutes, but when I query any other dictionary view it is spontaneous.
Everything else is fine in the database and there are no problems, except
the above problem. Iam not getting any clue how to fix this. Iam thinking
of
running catalog.sql and catproc.sql as a last resort. Iam not sure it
would
fix the problem. Please let me know if there is any other way to fix this
problem.
Thanks,
Babu
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: Janardhana Babu Donga
INET: [EMAIL PROTECTED]
Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051
San Diego, California-- Public Internet access / Mailing Lists
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from). You may
also send the HELP command for other information (like subscribing).
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author:
INET: [EMAIL PROTECTED]
Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051
San Diego, California-- Public Internet access / Mailing Lists
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from). You may
also send the HELP command for other information (like subscribing).
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author:
INET: [EMAIL PROTECTED]
Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051
San Diego, California-- Public Internet access / Mailing Lists
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(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: Janardhana Babu Donga
INET: [EMAIL PROTECTED]
Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051
San Diego, California-- Public Internet access / Mailing Lists
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(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: Raw device backup script
but I need a script that backups over 20 raw devices
datafiles to a TAPE without using a FS to store these
datafiles first.
To a single tape or multiple ones? If it's a single tape
then you are in for a real fun time trying to restore the
stuff...
This is skeletal but should give you a reasonable starting
point for a working backup:
#!/usr/bin/perl -w
$\ = \n;
$, = \n\t;
use Carp;
my @rawvolz =
qw(
/dev/foo
/dev/bar
);
sub run
{
for my $cmd ( @_ )
{
print $$: system($cmd);
if( my $exit = system($cmd) )
{
print STDERR: coredump if $exit 128;
if( $exit 0 )
{
croak $$: failed system call: $!;
}
elsif( my $status = $exit 8 )
{
croak $$: Nonzero exit: $exit;
}
elsif( my $signal = $exit 0xFF )
{
croak $$: Zapped by SIG-$signal;
}
}
}
}
sub mt
{
for my $cmd ( @_ )
{
if( system mt $cmd 0 )
{
croak $$: Failed system call: $!;
}
elsif( $? == 1 )
{
croak $$: Invalid tape device: $ENV{TAPE};
}
elsif( $? == 2 )
{
croak $$: Tape operation failed: $!;
}
else
{
croak $$: Flaky exit from mt: $!;
}
}
}
print $$: Checking tape: $ENV{TAPE};
# if these don't work there's little chance the backups will.
mt 'erase', 'status';
# ok., some hope, start the fireworks...
print $$: Backing up:, @rawvolz;
my $blocksize = 1024 * 1024 * 80; # whatever
my $file = 0;
for my $raw ( @rawvolz )
{
# keep track of the file order for later restore.
print $$: $raw : . ++$file;
run qq{dd if='$raw' of='$ENV{TAPE}' obs='$blocksize'};
mt 'eof';
}
# avoid cinches in the tape and make sure not to overwrite
# a valid backup with a new one if someone forgets to change
# the tape.
mt 'retension', 'rewoffl';
# this isn't a module
0
__END__
--
Steven Lembark 2930 W. Palmer
Workhorse Computing Chicago, IL 60647
+1 800 762 1582
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: Steven Lembark
INET: [EMAIL PROTECTED]
Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051
San Diego, California-- Public Internet access / Mailing Lists
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from). You may
also send the HELP command for other information (like subscribing).
RE: DBA_EXTENTS problem
analyzing SYSTEM is not the same as analyzing SYS, which is what you have done
by using gather_database_stats
run dbms_stats.delete_schema_stats('SYS') to remove the statistics
then either always run the delete after the gather_database_stats or switch to
gather_schema_stats
|+---
|| |
|| |
|| jbdonga@ucdav|
|| is.edu |
|| |
|| 05/09/2002 |
|| 03:41 PM |
|| Please |
|| respond to |
|| ORACLE-L |
|| |
|+---
|
||
| To: [EMAIL PROTECTED] |
| cc: (bcc: Rachel Carmichael) |
| Subject: RE: DBA_EXTENTS problem |
|
I used to run the following analyze every week:
dbms_utility.analyze_schema(...) statement which included SYSTEM schema,
Two weeks back it was changed to
dbms_stats.gather_database_stats();
Is there anyway to de-analyze SYSTEM schema?
Thanks,
-- Babu
-Original Message-
Sent: Thursday, May 09, 2002 11:04 AM
To: 'Janardhana Babu Donga'
Babu - Among the suggestions you have received, the one that seems to have
the most merit is whether any of your system tables have been analyzed. Were
I in your position, I would check that out.
Dennis Williams
DBA
Lifetouch, Inc.
[EMAIL PROTECTED]
-Original Message-
Sent: Thursday, May 09, 2002 12:51 PM
To: '[EMAIL PROTECTED]'
Cc: DENNIS WILLIAMS
Dennis,
It is happenning for any table in the database. I have just created a new
table:
create table x1 (col1 number) tablespace data_ts, inserted one row and run:
select * from dba_extents where segment_name = 'X1'; It is still sitting
there. The response to any other dictionary view is spontaneous except this
dba_extents, which is confusing me.
I issued: select * from dba_objects where ..., select * from dba_segments
where etc. They are all responding fine.
There are not many users at the moment. System tablespace is 500M size and
300MB is free. DB is 40Gig in size. It is on HP_UX 11.0 and Oracle 8.1.7.2.0
(64bit) database.
Any other ideas?
Thanks,
-- Babu
-Original Message-
Sent: Thursday, May 09, 2002 11:29 AM
To: Multiple recipients of list ORACLE-L
Babu - Wow, never had that one. Of course, I don't use that table much. How
many rows are in this table? Mine has 12,937 rows, and is kinda slow to
respond compared to the other system tables. Do you have a test database to
compare with? Is it possible that your system tablespace has become badly
fragmented? What is your system load, is it possibly a contention problem of
some type. Why not run STATSPACK (or utlbstat) while you do this query and
see what is being waited for? Why don't I shut up and send this?
Dennis Williams
DBA
Lifetouch, Inc.
[EMAIL PROTECTED]
-Original Message-
Sent: Thursday, May 09, 2002 12:58 PM
To: Multiple recipients of list ORACLE-L
Dear List,
When I query dba_extents , Iam getting response after half an hour to 40
minutes, but when I query any other dictionary view it is spontaneous.
Everything else is fine in the database and there are no problems, except
the above problem. Iam not getting any clue how to fix this. Iam thinking of
running catalog.sql and catproc.sql as a last resort. Iam not sure it would
fix the problem. Please let me know if there is any other way to fix this
problem.
Thanks,
Babu
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: Janardhana Babu Donga
INET: [EMAIL PROTECTED]
Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051
San Diego, California-- Public Internet access / Mailing Lists
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from). You may
also send the HELP command for other information (like subscribing).
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: DENNIS WILLIAMS
INET: [EMAIL PROTECTED]
Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051
San Diego, California-- Public Internet access / Mailing Lists
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from). You may
also send the
WindowsXP and JInitiator
I tried to see whether JInitiator would be certified for XP, the only item I found on MetaLink stated that Jinitiator was still at version 1.1.8.14, no news re. XP yet. JInitiator doesn't appear in the Certification matrix. Am going to check the technet software download site, maybe they have a note there, but I was wondering if anyone knows the status of XP and oracle JInitiator. I know XP doesn't come with a JVM, Sun posted a download link for that. 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 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (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: DBA_EXTENTS problem
I have deleted the statistics from 'SYS' and SYSTEM' as suggested by Suzy
and made sure that they were gone by issuing the query:
select table_name,owner
from dba_tables
where owner in ('SYS','SYSTEM')
and last_analyzed is not null;
It returned no rows this time. But why is my query: select count(*) from
dba_extents is still sitting there? Is there anything else I should do?
Restart the DB/something??
Thanks for any response.
-- Babu
-Original Message-
[mailto:[EMAIL PROTECTED]]
Sent: Thursday, May 09, 2002 1:19 PM
To: Multiple recipients of list ORACLE-L
I knew someone on the list would have it :) thanks Suzy.
actually, I'd do dbms_stats.delete_schema_stats('SYS') this time through to
fix
the problem and then change the dbms_stats command to
dbms_stats.gather_schema_stats instead of database
|+---
|| |
|| |
|| lvordos@qwest|
|| .com |
|| |
|| 05/09/2002 |
|| 03:44 PM |
|| Please |
|| respond to |
|| ORACLE-L |
|| |
|+---
|
||
| To: [EMAIL PROTECTED] |
| cc: (bcc: Rachel Carmichael) |
| Subject: Re: DBA_EXTENTS problem |
|
dbms_stats.gather_database_stats has a bug, it analyzes SYS objects.
From Metalink:
Bug:1422285 is a severity 3 bug that was logged for the
dbms_stats.gather_database_stats collecting statistics on the data
dictionary objects (FET$ was the example). This bug is fixed in 9i. A
workaround is to execute dbms_stats.gather_database_statistics +
dbms_stats.delete_schema_stats('SYS').
Janardhana Babu Donga wrote:
I have recently changed the analyze script. Earliar it was
dbms_utility.analyze_schema(...) statement, It is now changed to
dbms_stats.gather_database_stats();
Is this a problem?
The response time for select count(*) from dba_extents is also 30 minutes.
It is not specific to any table.
Thanks,
-- Babu
-Original Message-
[mailto:[EMAIL PROTECTED]]
Sent: Thursday, May 09, 2002 11:36 AM
To: Multiple recipients of list ORACLE-L
that's interesting... since the data dictionary is NOT analyzed, setting
optimizer_mode=choose would force the query against dba_extents to RULE
which it
what it is supposed to be doing anyway, Hm. The question now is, what is
the
optimizer_mode set to when the problem happens? Did any of the data
dictionary
tables get accidentally analyzed? And -- how many extents are in use in
the
database? Could it just be a symptom of missized tables and indexes so
that
the
number of extents is way high?
Although Oracle is *supposed* to allow unlimited extents, in practice
anything
higher than 4096 extents in an object (at least in 8i) tends to slow
things
down.
|+---
|| |
|| |
|| jack_silvey@y|
|| ahoo.com |
|| |
|| 05/09/2002 |
|| 02:18 PM |
|| Please |
|| respond to |
|| ORACLE-L |
|| |
|+---
|
||
| To: [EMAIL PROTECTED] |
| cc: (bcc: Rachel Carmichael) |
| Subject: Re: DBA_EXTENTS problem |
|
Babu,
We had a similiar problem, and setting optimizer_mode
= choose in our session solved it. Something to do
with optimizer and DD access. Give that a try. I had
the same problem with DBA_INDEXES and that fixed it.
hth,
Jack
--- Janardhana Babu Donga [EMAIL PROTECTED] wrote:
Dear List,
When I query dba_extents , Iam getting response
after half an hour to 40
minutes, but when I query any other dictionary view
it is spontaneous.
Everything else is fine in the database and there
are no problems, except
the above problem. Iam not getting any clue how to
fix this. Iam thinking of
running catalog.sql and catproc.sql as a last
resort. Iam not sure it would
fix the problem. Please let me know if there is any
other way to fix this
problem.
Thanks,
Babu
--
Please see the official ORACLE-L FAQ:
http://www.orafaq.com
--
Author: Janardhana Babu Donga
INET: [EMAIL PROTECTED]
Fat City Network Services-- (858) 538-5051 FAX:
(858) 538-5051
San
Re: optimizer not using function based indexes
you don't need the upper(ename) in your where clause... that forces oracle to
NOT use an index
|+-
|| |
|| |
|| Harvinder.Singh@met|
|| ratech.com |
|| |
|| 05/09/2002 04:18 PM|
|| Please respond to |
|| ORACLE-L |
|| |
|+-
|
||
| To: [EMAIL PROTECTED] |
| cc: (bcc: Rachel Carmichael) |
| Subject: optimizer not using function|
| based indexes|
|
Hi,
We are trying to use function based indexes in our application but it looks like
optimizer is not using them.
So we created a sample table emp with 3 columns empno,ename,sal and populate
this table with 10 rows and created function based index on upper(ename).
we try to run simple query like below and optimzer is not using index:
select sal from emp
where upper(ename)=upper('abc98');
Even we use index hint optimizer is still not using index.
1) table is analyzed as compute after creating index.
2) query rewrite is granted to user
3) query_rewrite_enabled=true.
4) optimzer mode is choose.
5) optimer should use index as there is no way full scan can be faster than
index in this case as i tried to create simple
index on ename and remove upper function from where clause of query..
What can be the possible reason that optimzer is not using index??
Thanks
--Harvinder
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: Harvinder Singh
INET: [EMAIL PROTECTED]
Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051
San Diego, California-- Public Internet access / Mailing Lists
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from). You may
also send the HELP command for other information (like subscribing).
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author:
INET: [EMAIL PROTECTED]
Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051
San Diego, California-- Public Internet access / Mailing Lists
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from). You may
also send the HELP command for other information (like subscribing).
RE: Oracle Presentation for Beginners!
Looks like she took away the permissions. I never took a class here. I stumbled on the site several months ago using the Google search engine with the search string Using Oracle Blocks Efficiently. Back then that site was the first one returned by Google. Sorry the links were so short lived. I never thought the presentation would go away so soon. Ed -Original Message- Sent: Thursday, May 09, 2002 3:41 PM To: Multiple recipients of list ORACLE-L You are not using the full url, check the next line for the rest of the url 01_files/v3_document.htm you need les01_files/v3_document.htm -Original Message- Sent: May 9, 2002 12:00 PM To: Multiple recipients of list ORACLE-L Any one got this message? Forbidden You don't have permission to access /~melody/courses/Spring2002Backup/BackupPPTS/les on this server. --- Sherman, Edward [EMAIL PROTECTED] wrote: Here's the slides! SQL/PL-SQL http://www.ecst.csuchico.edu/~melody/courses/Spg2001OraclePLSQL/PLSQL8iPPTS/ Default.htm Architecture and Admin http://www.ecst.csuchico.edu/~melody/courses/Fall2001CSCI379/TOC.html Performance Tuning http://www.ecst.csuchico.edu/~melody/courses/Spring2002PerfTune/TOC.html Backup and Recovery http://www.ecst.csuchico.edu/~melody/courses/Spring2002Backup/BackupPPTS/les 01_files/v3_document.htm http://www.ecst.csuchico.edu/~melody/courses/Spring2002Backup/BackupPPTS/les 02_files/v3_document.htm http://www.ecst.csuchico.edu/~melody/courses/Spring2002Backup/BackupPPTS/les 03_files/v3_document.htm And here's a gal after my own heart! http://www.ecst.csuchico.edu/~melody/ Thanks Melody!!! Ed -Original Message- Sent: Monday, May 06, 2002 5:09 AM To: Multiple recipients of list ORACLE-L Hello all, because I am an human being I am also looking for the easiest solution :-) So, I need to have a training session concerning basic oracle features. I wonder if anyone of you know place/site where I could download some powerpoint slides. Or if you have your own, pls. send me a copy ... Any help will be highly appreciated. Thanks km the lazy dba -- __ Ota itsellesi luotettava kotimainen email http://www.jippii.fi/ Tutustu samalla netin parhaaseen pelipaikkaan JIPPIIGAMESIIN. -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). * * * * * Freedom of Information Act Notice * * * * * The information in this email is subject to the record protection mandated by 5 United States Code 552 (b) (4) and relevant judicial opinions. -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Sherman, Edward INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). __ Do You Yahoo!? Yahoo! Shopping - Mother's Day is May 12th! http://shopping.yahoo.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Steven Joshua INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (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: Browett, Darren INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists
RE: Oracle Presentation for Beginners!
God Bless google.com and the American higher educational system! http://www.sdsc.edu/~oracle/training/intro/syllabus.html This one, you'll have to do the HTML conversion yourself, though... Rich Jesse System/Database Administrator [EMAIL PROTECTED] Quad/Tech International, Sussex, WI USA -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]] Sent: Thursday, May 09, 2002 3:27 PM To: Multiple recipients of list ORACLE-L Subject: RE: Oracle Presentation for Beginners! This was not always the case. We must have flooded the server and they shut off public access. There are other sites that have the slides. The slides are actually provided BY Oracle as part of some educational deal (ie. pre-marketing campaign!). But I have found these on the net in the past. Next time I see them, I will download 'em. Also, when this discussion first occurred, I was able to get up on the site and view everything. Hannah -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Jesse, Rich INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Re: WindowsXP and JInitiator
I'm running 1.1.8.14 on XP Pro to run Developer 6 forms via the web. Joe Joe LaCascio Oracle DBA, Unix Administrator Wheaton College, MA 508.286.3405 On Thu, 9 May 2002, Boivin, Patrice J wrote: I tried to see whether JInitiator would be certified for XP, the only item I found on MetaLink stated that Jinitiator was still at version 1.1.8.14, no news re. XP yet. JInitiator doesn't appear in the Certification matrix. Am going to check the technet software download site, maybe they have a note there, but I was wondering if anyone knows the status of XP and oracle JInitiator. I know XP doesn't come with a JVM, Sun posted a download link for that. 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 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (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: Joe LaCascio INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (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: too low optimizer_index_cost_adj causing bizarre index choice
Hi Johnathan,
here is the skinny:
db_file_multiblock_read_count = 64
number of rows=15m
blocks=251071
empty_blocks=0
db_block_size=16384
total plan cost=4924
tablescan cost = 4924
Jack
--- Jonathan Lewis [EMAIL PROTECTED]
wrote:
There seems to be a perfectly good theoretical
reason
for this. But it would be interesting to know:
Number of blocks below HWM
Setting for db_file_multiblock_read_count
Maximum usable value for db_file_mbrc
The cost given by Explain Plan for the tablescan
before I confuse the issue further by expounding a
hypothesis that may be totally misleading.
Jonathan Lewis
http://www.jlcomp.demon.co.uk
Author of:
Practical Oracle 8i: Building Efficient Databases
Next Seminar - Australia - July/August
http://www.jlcomp.demon.co.uk/seminar.html
Host to The Co-Operative Oracle Users' FAQ
http://www.jlcomp.demon.co.uk/faq/ind_faq.html
-Original Message-
To: Multiple recipients of list ORACLE-L
[EMAIL PROTECTED]
Date: 09 May 2002 14:30
|Good morning listers,
|
|Some weeks ago I noticed a query plan that was
using a
|bizarre index choice and opened a TAR. Here is that
|query:
|
|select DSS_EMPR.V_CLAIM_EMPR_HX.AMT_COPAY from
|DSS_EMPR.V_CLAIM_EMPR_HX where
|DSS_EMPR.V_CLAIM_EMPR_HX.BATCH_NUM between
|to_date('2000-01-01','-mm-dd') and
|to_date('2000-01-31','-mm-dd');
|
|Here is the query plan:
|
| SELECT STATEMENT (all_rows) Cost
|(4924,636953,12739060)
|
| 1 0 1 2
|TABLE ACCESS (analyzed) DSS_EMPR
T_CLAIM_EMPR_HX
|(by index rowid) Cost (4924,636953,12739060)
|
| 2 1 1
| BITMAP CONVERSION(to rowids)
|
| 3 2 1
|BITMAP INDEX DSS_EMPR X_CLAIM_EMPR_HX_N01
|(full scan)
|
|
|The index x_claim_empr_hx_n01 is on the phmcy_gid
|column of the t_claim_empr_hx table.
|
|Now, here is the crux of the matter: phmcy_gid is
|referred to nowhere in the query, not in select nor
in
|where nor even in order by.
|
|Worked through the tar with Oracle and they advised
|that Oracle can and will cost ALL indexes during a
|plan parse and eval, so it became a matter of
|discovering why the index was being incorrectly
|costed.
|
|Remembered that our optimizer_index_cost_adj was
set
|to 1 (don't ask). When I upped this value to 2 or
more
|and reran the query, it returned the appropriate
FTS
|plan.
|
|hth,
|
|Jack Silvey
|
--
Please see the official ORACLE-L FAQ:
http://www.orafaq.com
--
Author: Jonathan Lewis
INET: [EMAIL PROTECTED]
Fat City Network Services-- (858) 538-5051 FAX:
(858) 538-5051
San Diego, California-- Public Internet
access / Mailing Lists
To REMOVE yourself from this mailing list, send an
E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of
'ListGuru') and in
the message BODY, include a line containing: UNSUB
ORACLE-L
(or the name of mailing list you want to be removed
from). You may
also send the HELP command for other information
(like subscribing).
__
Do You Yahoo!?
Yahoo! Shopping - Mother's Day is May 12th!
http://shopping.yahoo.com
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: Jack Silvey
INET: [EMAIL PROTECTED]
Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051
San Diego, California-- Public Internet access / Mailing Lists
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from). You may
also send the HELP command for other information (like subscribing).
Re: WindowsXP and JInitiator
(Embedded image moved to file: pic28253.gif) Certify - Certification Matrix: Oracle JInitiator (for E-Business Suite) on Microsoft Windows XP Client Certifications OS Product Certified With Version Status Addtl. Info. Components Other Install Issue Professional 1.1.8.16 E-Business Suite 11i Certified Yes None Yes None Professional 1.1.8.16 E-Business Suite 11.0.3 Projected Yes N/A
Re: DBA_EXTENTS problem
Try this: dbms_stats.delete_database_stats();
Janardhana Babu Donga wrote:
I have deleted the statistics from 'SYS' and SYSTEM' as suggested by Suzy
and made sure that they were gone by issuing the query:
select table_name,owner
from dba_tables
where owner in ('SYS','SYSTEM')
and last_analyzed is not null;
It returned no rows this time. But why is my query: select count(*) from
dba_extents is still sitting there? Is there anything else I should do?
Restart the DB/something??
Thanks for any response.
-- Babu
-Original Message-
[mailto:[EMAIL PROTECTED]]
Sent: Thursday, May 09, 2002 1:19 PM
To: Multiple recipients of list ORACLE-L
I knew someone on the list would have it :) thanks Suzy.
actually, I'd do dbms_stats.delete_schema_stats('SYS') this time through to
fix
the problem and then change the dbms_stats command to
dbms_stats.gather_schema_stats instead of database
|+---
|| |
|| |
|| lvordos@qwest|
|| .com |
|| |
|| 05/09/2002 |
|| 03:44 PM |
|| Please |
|| respond to |
|| ORACLE-L |
|| |
|+---
|
||
| To: [EMAIL PROTECTED] |
| cc: (bcc: Rachel Carmichael) |
| Subject: Re: DBA_EXTENTS problem |
|
dbms_stats.gather_database_stats has a bug, it analyzes SYS objects.
From Metalink:
Bug:1422285 is a severity 3 bug that was logged for the
dbms_stats.gather_database_stats collecting statistics on the data
dictionary objects (FET$ was the example). This bug is fixed in 9i. A
workaround is to execute dbms_stats.gather_database_statistics +
dbms_stats.delete_schema_stats('SYS').
Janardhana Babu Donga wrote:
I have recently changed the analyze script. Earliar it was
dbms_utility.analyze_schema(...) statement, It is now changed to
dbms_stats.gather_database_stats();
Is this a problem?
The response time for select count(*) from dba_extents is also 30 minutes.
It is not specific to any table.
Thanks,
-- Babu
-Original Message-
[mailto:[EMAIL PROTECTED]]
Sent: Thursday, May 09, 2002 11:36 AM
To: Multiple recipients of list ORACLE-L
that's interesting... since the data dictionary is NOT analyzed, setting
optimizer_mode=choose would force the query against dba_extents to RULE
which it
what it is supposed to be doing anyway, Hm. The question now is, what is
the
optimizer_mode set to when the problem happens? Did any of the data
dictionary
tables get accidentally analyzed? And -- how many extents are in use in
the
database? Could it just be a symptom of missized tables and indexes so
that
the
number of extents is way high?
Although Oracle is *supposed* to allow unlimited extents, in practice
anything
higher than 4096 extents in an object (at least in 8i) tends to slow
things
down.
|+---
|| |
|| |
|| jack_silvey@y|
|| ahoo.com |
|| |
|| 05/09/2002 |
|| 02:18 PM |
|| Please |
|| respond to |
|| ORACLE-L |
|| |
|+---
|
||
| To: [EMAIL PROTECTED] |
| cc: (bcc: Rachel Carmichael) |
| Subject: Re: DBA_EXTENTS problem |
|
Babu,
We had a similiar problem, and setting optimizer_mode
= choose in our session solved it. Something to do
with optimizer and DD access. Give that a try. I had
the same problem with DBA_INDEXES and that fixed it.
hth,
Jack
--- Janardhana Babu Donga [EMAIL PROTECTED] wrote:
Dear List,
When I query dba_extents , Iam getting response
after half an hour to 40
minutes, but when I query any other dictionary view
it is spontaneous.
Everything else is fine in the database and there
are no problems, except
the above problem. Iam not getting any clue how to
fix this. Iam thinking of
running catalog.sql and catproc.sql as a last
resort. Iam not sure it would
fix the problem. Please let me know if there is any
other way to fix this
problem.
Thanks,
Babu
Re: Working with Oracle Designer
I've found out how to generate without the storage clause. --- paquette stephane [EMAIL PROTECTED] a écrit : I've been looking for that in Designer , where it is ? I've used a lot Power*AMC in the past and it has that feature . We're using Oracle Designer 6.5.52.1.0 TIA --- [EMAIL PROTECTED] a écrit : Stephane, We have the same objects, etc. in both dev and prod. We only have a single database defined. When we get to prod, we'll have to edit the tablespace physical storage definition (in just one place) to change the datafile definitions. It is possible to use different methods to generate DDL from the same definition.One method generates DDL that containts storage definitions and one does not. You don't actually need to change the way anything is defined in Designer to switch back and forth between the two methods. Cherie paquette stephane stephane_paquette@ To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] yahoo.comcc: Sent by: Subject: Re: Working with Oracle Designer [EMAIL PROTECTED] 05/08/02 04:03 PM Please respond to ORACLE-L So you're generating specific DDL for each environments. Do you have 1 db in Designer per Oracle database ? Also, we have a requirement to be able to generate the DDL without any tablespaces and storage clause so the developers can do some prototyping in their own space. The way we will handle that is to create a user no_storage for each user and to do table implementation without tablespace. Anybody working like that ? --- [EMAIL PROTECTED] a écrit : Stephane, We are doing exactly that with Designer 6i. We are able to generate all the DDL except for public synonyms. No solution found for that. Otherwise, it is working well in our QA environment. Haven't used it yet to generate prod but that is coming next month. Cherie Machler Oracle DBA Gelco Information Network paquette stephane stephane_paquette@ To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] yahoo.com cc: Sent by: Subject: Working with Oracle Designer [EMAIL PROTECTED] 05/08/02 01:24 PM Please respond to ORACLE-L Hi, We're discussing ways of working with Designer. We convinced developpers to use the check in/check out stuff and each application has its many workareas. Someone here would like to press a button and have all the DDL generetad for at least 2 environments : === message truncated === = Stéphane Paquette DBA Oracle, consultant entrepôt de données Oracle DBA, datawarehouse consultant [EMAIL PROTECTED] ___ Do You Yahoo!? -- Une adresse @yahoo.fr gratuite et en français ! Yahoo! Mail : http://fr.mail.yahoo.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: =?iso-8859-1?q?paquette=20stephane?= INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send
RE: optimizer not using function based indexes
that's why we are using function based indexes...
-Original Message-
[mailto:[EMAIL PROTECTED]]
Sent: Thursday, May 09, 2002 4:49 PM
To: Multiple recipients of list ORACLE-L
you don't need the upper(ename) in your where clause... that forces oracle to
NOT use an index
|+-
|| |
|| |
|| Harvinder.Singh@met|
|| ratech.com |
|| |
|| 05/09/2002 04:18 PM|
|| Please respond to |
|| ORACLE-L |
|| |
|+-
|
||
| To: [EMAIL PROTECTED] |
| cc: (bcc: Rachel Carmichael) |
| Subject: optimizer not using function|
| based indexes|
|
Hi,
We are trying to use function based indexes in our application but it looks like
optimizer is not using them.
So we created a sample table emp with 3 columns empno,ename,sal and populate
this table with 10 rows and created function based index on upper(ename).
we try to run simple query like below and optimzer is not using index:
select sal from emp
where upper(ename)=upper('abc98');
Even we use index hint optimizer is still not using index.
1) table is analyzed as compute after creating index.
2) query rewrite is granted to user
3) query_rewrite_enabled=true.
4) optimzer mode is choose.
5) optimer should use index as there is no way full scan can be faster than
index in this case as i tried to create simple
index on ename and remove upper function from where clause of query..
What can be the possible reason that optimzer is not using index??
Thanks
--Harvinder
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: Harvinder Singh
INET: [EMAIL PROTECTED]
Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051
San Diego, California-- Public Internet access / Mailing Lists
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from). You may
also send the HELP command for other information (like subscribing).
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author:
INET: [EMAIL PROTECTED]
Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051
San Diego, California-- Public Internet access / Mailing Lists
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from). You may
also send the HELP command for other information (like subscribing).
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: Harvinder Singh
INET: [EMAIL PROTECTED]
Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051
San Diego, California-- Public Internet access / Mailing Lists
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(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: optimizer not using function based indexes
Another requirement is that your compatibility init.ora parameter must
be set to 8.1.7 or higher, I believe, but check the doco.
If that doesn't fix it, then trying using a hint to force the use of
that index. If it doesn't work with the hint, then you know you have an
environment problem that is preventing the use of function-based
indexes. If it does work with the hint, then you know that function
based is working but you have to figure out why the optimizer is not
choosing it.
HTH,
Beth
-Original Message-
Sent: Thursday, May 09, 2002 4:19 PM
To: Multiple recipients of list ORACLE-L
Hi,
We are trying to use function based indexes in our application but it
looks like
optimizer is not using them.
So we created a sample table emp with 3 columns empno,ename,sal and
populate
this table with 10 rows and created function based index on
upper(ename).
we try to run simple query like below and optimzer is not using index:
select sal from emp
where upper(ename)=upper('abc98');
Even we use index hint optimizer is still not using index.
1) table is analyzed as compute after creating index.
2) query rewrite is granted to user
3) query_rewrite_enabled=true.
4) optimzer mode is choose.
5) optimer should use index as there is no way full scan can be faster
than index in this case as i tried to create simple
index on ename and remove upper function from where clause of query..
What can be the possible reason that optimzer is not using index??
Thanks
--Harvinder
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: Harvinder Singh
INET: [EMAIL PROTECTED]
Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051
San Diego, California-- Public Internet access / Mailing Lists
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from). You may
also send the HELP command for other information (like subscribing).
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: Seefelt, Beth
INET: [EMAIL PROTECTED]
Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051
San Diego, California-- Public Internet access / Mailing Lists
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from). You may
also send the HELP command for other information (like subscribing).
RE: DBA_EXTENTS problem
Babu, Your query is using cost algorithms to access the data dictionary, and that is why it is so slow. The RULE hint makes it use rule-based optimization, which is the way that the data dictionary is supposed to be accessed. If you remove all stats from the data dictionary, your query should be fast again. If it doesn't, just use the RULE hint in all your data dictionary queries. Do make sure that ALL of the stats are gone from your data dictionary tables. hth, Jack --- Janardhana Babu Donga [EMAIL PROTECTED] wrote: Jack, Output from show parameter optim: Optimizer_cache_optimal_size 102400 optimizer_features_enable 8.1.7 optimizer_index_caching 0 optimizer_index_cost_adj 100 optimizer_max_permutations8 optimizer_modechoose optimizer_percent_parallel0 Immediate response for select /*+RULE*/ count(1) from dba_extents Count(1) - 4855 select count(1) from dba_extents is still sitting there and has not yet responded. Thanks, -- Babu -Original Message- Sent: Thursday, May 09, 2002 12:41 PM To: Multiple recipients of list ORACLE-L Babu, issue this in your sqlplus session: show parameter optim and then this: alter session set optimizer_mode=choose; and then this: select /*+ RULE */ count(1) from dba_extents; and then this: select count(1) from dba_extents; and let us know what happens. Rachel: we were running all_rows in our dwh and it was slowing down dd access. not sure why, choose and all_rows are kissing cousins. true, dd is supposed to be rule and with no stats. Have heard of cases where dbms_statistics analyzed the sys schema while doing a analyze_database, which is one reason I stopped using it. Might be the problem here? We will find out. hth, jack --- Janardhana Babu Donga [EMAIL PROTECTED] wrote: I have recently changed the analyze script. Earliar it was dbms_utility.analyze_schema(...) statement, It is now changed to dbms_stats.gather_database_stats(); Is this a problem? The response time for select count(*) from dba_extents is also 30 minutes. It is not specific to any table. Thanks, -- Babu -Original Message- [mailto:[EMAIL PROTECTED]] Sent: Thursday, May 09, 2002 11:36 AM To: Multiple recipients of list ORACLE-L that's interesting... since the data dictionary is NOT analyzed, setting optimizer_mode=choose would force the query against dba_extents to RULE which it what it is supposed to be doing anyway, Hm. The question now is, what is the optimizer_mode set to when the problem happens? Did any of the data dictionary tables get accidentally analyzed? And -- how many extents are in use in the database? Could it just be a symptom of missized tables and indexes so that the number of extents is way high? Although Oracle is *supposed* to allow unlimited extents, in practice anything higher than 4096 extents in an object (at least in 8i) tends to slow things down. |+--- || | || | || jack_silvey@y| || ahoo.com | || | || 05/09/2002 | || 02:18 PM | || Please | || respond to | || ORACLE-L | || | |+--- | | | | To: [EMAIL PROTECTED] | | cc: (bcc: Rachel Carmichael) | | Subject: Re: DBA_EXTENTS problem | | Babu, We had a similiar problem, and setting optimizer_mode = choose in our session solved it. Something to do with optimizer and DD access. Give that a try. I had the same problem with DBA_INDEXES and that fixed it. hth, Jack --- Janardhana Babu Donga [EMAIL PROTECTED] wrote: Dear List, When I query dba_extents , Iam getting response after half an hour to 40 minutes, but when I query any other dictionary view it is spontaneous. Everything else is fine in the database and there are no problems, except the above problem. Iam not getting any clue how to fix this. Iam thinking of running catalog.sql and catproc.sql as a last resort. Iam not sure it would fix the problem. Please let me know if there is any other way to fix this problem. Thanks, Babu === message truncated === __ Do You Yahoo!? Yahoo! Shopping - Mother's Day is May 12th! http://shopping.yahoo.com --
RE: DBA_EXTENTS problem
Do you have a lot of locally managed tablespaces? Doing a read from
dba_extents can cause quite an I/O with locally managed tablespaces.
My 2 cents.
Raj
Janardhana
Babu Donga To: Multiple recipients of list ORACLE-L
[EMAIL PROTECTED]
jbdonga@ucdacc:
vis.edu Subject: RE: DBA_EXTENTS problem
Sent by:
root@fatcity.
com
May 09, 2002
04:38 PM
Please
respond to
ORACLE-L
I have deleted the statistics from 'SYS' and SYSTEM' as suggested by Suzy
and made sure that they were gone by issuing the query:
select table_name,owner
from dba_tables
where owner in ('SYS','SYSTEM')
and last_analyzed is not null;
It returned no rows this time. But why is my query: select count(*) from
dba_extents is still sitting there? Is there anything else I should do?
Restart the DB/something??
Thanks for any response.
-- Babu
-Original Message-
[mailto:[EMAIL PROTECTED]]
Sent: Thursday, May 09, 2002 1:19 PM
To: Multiple recipients of list ORACLE-L
I knew someone on the list would have it :) thanks Suzy.
actually, I'd do dbms_stats.delete_schema_stats('SYS') this time through to
fix
the problem and then change the dbms_stats command to
dbms_stats.gather_schema_stats instead of database
|+---
|| |
|| |
|| lvordos@qwest|
|| .com |
|| |
|| 05/09/2002 |
|| 03:44 PM |
|| Please |
|| respond to |
|| ORACLE-L |
|| |
|+---
|
||
| To: [EMAIL PROTECTED] |
| cc: (bcc: Rachel Carmichael) |
| Subject: Re: DBA_EXTENTS problem |
|
dbms_stats.gather_database_stats has a bug, it analyzes SYS objects.
From Metalink:
Bug:1422285 is a severity 3 bug that was logged for the
dbms_stats.gather_database_stats collecting statistics on the data
dictionary objects (FET$ was the example). This bug is fixed in 9i. A
workaround is to execute dbms_stats.gather_database_statistics +
dbms_stats.delete_schema_stats('SYS').
Janardhana Babu Donga wrote:
I have recently changed the analyze script. Earliar it was
dbms_utility.analyze_schema(...) statement, It is now changed to
dbms_stats.gather_database_stats();
Is this a problem?
The response time for select count(*) from dba_extents is also 30
minutes.
It is not specific to any table.
Thanks,
-- Babu
-Original Message-
[mailto:[EMAIL PROTECTED]]
Sent: Thursday, May 09, 2002 11:36 AM
To: Multiple recipients of list ORACLE-L
that's interesting... since the data dictionary is NOT analyzed, setting
optimizer_mode=choose would force the query against dba_extents to RULE
which it
what it is supposed to be doing anyway, Hm. The question now is, what is
the
optimizer_mode set to when the problem happens? Did any of the data
dictionary
tables get accidentally
RE: optimizer not using function based indexes
I thought that's what told the optimizer to use the FBI? Rich Jesse System/Database Administrator [EMAIL PROTECTED] Quad/Tech International, Sussex, WI USA -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]] Sent: Thursday, May 09, 2002 3:49 PM To: Multiple recipients of list ORACLE-L Subject: Re: optimizer not using function based indexes you don't need the upper(ename) in your where clause... that forces oracle to NOT use an index -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Jesse, Rich INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: DBA_EXTENTS problem .... Solved
Thanks for all those who helped me in fixing the problem.
I deleted the SYS schema statistics using
dbms_stats.delete_schema_stats('SYS') and I need to restart the database.
Iam changing my analyze script from database analyze to schema analyze.
Thanks,
--Babu
-Original Message-
Sent: Thursday, May 09, 2002 2:06 PM
To: Multiple recipients of list ORACLE-L
Try this: dbms_stats.delete_database_stats();
Janardhana Babu Donga wrote:
I have deleted the statistics from 'SYS' and SYSTEM' as suggested by Suzy
and made sure that they were gone by issuing the query:
select table_name,owner
from dba_tables
where owner in ('SYS','SYSTEM')
and last_analyzed is not null;
It returned no rows this time. But why is my query: select count(*) from
dba_extents is still sitting there? Is there anything else I should do?
Restart the DB/something??
Thanks for any response.
-- Babu
-Original Message-
[mailto:[EMAIL PROTECTED]]
Sent: Thursday, May 09, 2002 1:19 PM
To: Multiple recipients of list ORACLE-L
I knew someone on the list would have it :) thanks Suzy.
actually, I'd do dbms_stats.delete_schema_stats('SYS') this time through
to
fix
the problem and then change the dbms_stats command to
dbms_stats.gather_schema_stats instead of database
|+---
|| |
|| |
|| lvordos@qwest|
|| .com |
|| |
|| 05/09/2002 |
|| 03:44 PM |
|| Please |
|| respond to |
|| ORACLE-L |
|| |
|+---
|
||
| To: [EMAIL PROTECTED] |
| cc: (bcc: Rachel Carmichael) |
| Subject: Re: DBA_EXTENTS problem |
|
dbms_stats.gather_database_stats has a bug, it analyzes SYS objects.
From Metalink:
Bug:1422285 is a severity 3 bug that was logged for the
dbms_stats.gather_database_stats collecting statistics on the data
dictionary objects (FET$ was the example). This bug is fixed in 9i. A
workaround is to execute dbms_stats.gather_database_statistics +
dbms_stats.delete_schema_stats('SYS').
Janardhana Babu Donga wrote:
I have recently changed the analyze script. Earliar it was
dbms_utility.analyze_schema(...) statement, It is now changed to
dbms_stats.gather_database_stats();
Is this a problem?
The response time for select count(*) from dba_extents is also 30
minutes.
It is not specific to any table.
Thanks,
-- Babu
-Original Message-
[mailto:[EMAIL PROTECTED]]
Sent: Thursday, May 09, 2002 11:36 AM
To: Multiple recipients of list ORACLE-L
that's interesting... since the data dictionary is NOT analyzed, setting
optimizer_mode=choose would force the query against dba_extents to RULE
which it
what it is supposed to be doing anyway, Hm. The question now is, what is
the
optimizer_mode set to when the problem happens? Did any of the data
dictionary
tables get accidentally analyzed? And -- how many extents are in use in
the
database? Could it just be a symptom of missized tables and indexes so
that
the
number of extents is way high?
Although Oracle is *supposed* to allow unlimited extents, in practice
anything
higher than 4096 extents in an object (at least in 8i) tends to slow
things
down.
|+---
|| |
|| |
|| jack_silvey@y|
|| ahoo.com |
|| |
|| 05/09/2002 |
|| 02:18 PM |
|| Please |
|| respond to |
|| ORACLE-L |
|| |
|+---
|
||
| To: [EMAIL PROTECTED] |
| cc: (bcc: Rachel Carmichael) |
| Subject: Re: DBA_EXTENTS problem |
|
Babu,
We had a similiar problem, and setting optimizer_mode
= choose in our session solved it. Something to do
with optimizer and DD access. Give that a try. I had
the same problem with DBA_INDEXES and that fixed it.
hth,
Jack
--- Janardhana Babu Donga [EMAIL PROTECTED] wrote:
Dear List,
When I query dba_extents , Iam getting response
after half an hour to 40
minutes, but when I query any other dictionary view
it is
RE: optimizer not using function based indexes
I respectfully disagree. In order to use a function based index
you do need the upper. If optimizer doesn't pick it up straigt
away, he should give a hand with a hint (/*+ INDEX(alias,index) */)
-Original Message-
From: [EMAIL PROTECTED]
[mailto:[EMAIL PROTECTED]]
Sent: Thursday, May 09, 2002 4:49 PM
To: Multiple recipients of list ORACLE-L
Subject: Re: optimizer not using function based indexes
you don't need the upper(ename) in your where clause... that
forces oracle to
NOT use an index
|+-
|| |
|| |
|| Harvinder.Singh@met|
|| ratech.com |
|| |
|| 05/09/2002 04:18 PM|
|| Please respond to |
|| ORACLE-L |
|| |
|+-
|
||
| To: [EMAIL PROTECTED] |
| cc: (bcc: Rachel Carmichael) |
| Subject: optimizer not using function|
| based indexes|
|
Hi,
We are trying to use function based indexes in our
application but it looks like
optimizer is not using them.
So we created a sample table emp with 3 columns
empno,ename,sal and populate
this table with 10 rows and created function based index
on upper(ename).
we try to run simple query like below and optimzer is not using index:
select sal from emp
where upper(ename)=upper('abc98');
Even we use index hint optimizer is still not using index.
1) table is analyzed as compute after creating index.
2) query rewrite is granted to user
3) query_rewrite_enabled=true.
4) optimzer mode is choose.
5) optimer should use index as there is no way full scan can
be faster than
index in this case as i tried to create simple
index on ename and remove upper function from where clause of query..
What can be the possible reason that optimzer is not using index??
Thanks
--Harvinder
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: Harvinder Singh
INET: [EMAIL PROTECTED]
Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051
San Diego, California-- Public Internet access / Mailing Lists
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from). You may
also send the HELP command for other information (like subscribing).
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author:
INET: [EMAIL PROTECTED]
Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051
San Diego, California-- Public Internet access / Mailing Lists
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from). You may
also send the HELP command for other information (like subscribing).
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: Gogala, Mladen
INET: [EMAIL PROTECTED]
Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051
San Diego, California-- Public Internet access / Mailing Lists
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from). You may
also send the HELP command for other information (like subscribing).
RE: Installing oracle 9.0.1 on Sparc
Natalia, follow the 9i Installation Guide very carefully, especially the parts about setting up the DBA user and group accounts, permissions, etc. Also, on UNIX you have to set the kernel parameters in /etc/system, among other things. All of this is in the Installation Guide. HTH, david David B. Wagoner Database Administrator Arsenal Digital Solutions Worldwide, Inc. 8000 Regency Parkway, Suite 110 Cary, NC 27511-8582 Office (919) 466-6723 Pager [EMAIL PROTECTED] Fax (919) 466-6783 http://www.arsenaldigital.com/ *** NOTICE *** This e-mail message is confidential, intended only for the named recipient(s) above and may contain information that is privileged, work product or exempt from disclosure under applicable law. If you have received this message in error, or are not the named recipient(s), please immediately notify the sender by phone or email and delete this e-mail message from your computer. Thank you. -Original Message- Sent: Thursday, May 09, 2002 3:09 PM To: Multiple recipients of list ORACLE-L Hi, I am installing oracle 9.0.1 on Sun Sparc Solaris 5.6, I have the next error: make: Fatal error: Command failed for target `ntcontab.o' /usr/ccs/bin/make -f ins_net_client.mk nnfgt.o ORACLE_HOME =/u02/app/oracle/prod uct/9.0.1 (if [ -d /u02/app/oracle/product/9.0.1/lib32 ] ; then \ /usr/ccs/bin/make -f /u02/app/oracle/product/9.0.1/network/lib/ins_net_client. mk \ nnfgt.o-32bit; fi) (if [ assemble = assemble ] ; then \ /u02/app/oracle/product/9.0.1/bin/gennfgt nnfgt.s ;\ /usr/ccs/bin/as -P -o nnfgt.o nnfgt.s ;\ rm -f /u02/app/oracle/product/9.0.1/lib32/nnfgt.o ;\ mv nnfgt.o /u02/app/oracle/product/9.0.1/lib32 ;\ /usr/ccs/bin/ar rv /u02/app/oracle/product/9.0.1/lib32/libn9.a \ /u02/app/oracle/product/9.0.1/lib32/nnfgt.o ; fi) ar: writing /u02/app/oracle/product/9.0.1/lib32/libn9.a r - nnfgt.o (if [ assemble = compile ] ; then \ /u02/app/oracle/product/9.0.1/bin/gennfgt nnfgt.c ;\ cc -c nnfgt.c ;\ rm -f /u02/app/oracle/product/9.0.1/lib/nnfgt.o ;\ mv nnfgt.o /u02/app/oracle/product/9.0.1/lib ;\ /usr/ccs/bin/ar rv /u02/app/oracle/product/9.0.1/lib/libn9.a /u02/app/ oracle/product/9.0.1/lib/nnfgt.o ; fi) (if [ assemble = assemble ] ; then \ ... Some idea? -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Natalia Laracca INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (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: David Wagoner INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (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: optimizer not using function based indexes
I think the point Rachel made is the query should instead be:
where ename=upper('abc98');
Harvinder Singh wrote:
that's why we are using function based indexes...
-Original Message-
[mailto:[EMAIL PROTECTED]]
Sent: Thursday, May 09, 2002 4:49 PM
To: Multiple recipients of list ORACLE-L
you don't need the upper(ename) in your where clause... that forces oracle to
NOT use an index
|+-
|| |
|| |
|| Harvinder.Singh@met|
|| ratech.com |
|| |
|| 05/09/2002 04:18 PM|
|| Please respond to |
|| ORACLE-L |
|| |
|+-
|
||
| To: [EMAIL PROTECTED] |
| cc: (bcc: Rachel Carmichael) |
| Subject: optimizer not using function|
| based indexes|
|
Hi,
We are trying to use function based indexes in our application but it looks like
optimizer is not using them.
So we created a sample table emp with 3 columns empno,ename,sal and populate
this table with 10 rows and created function based index on upper(ename).
we try to run simple query like below and optimzer is not using index:
select sal from emp
where upper(ename)=upper('abc98');
Even we use index hint optimizer is still not using index.
1) table is analyzed as compute after creating index.
2) query rewrite is granted to user
3) query_rewrite_enabled=true.
4) optimzer mode is choose.
5) optimer should use index as there is no way full scan can be faster than
index in this case as i tried to create simple
index on ename and remove upper function from where clause of query..
What can be the possible reason that optimzer is not using index??
Thanks
--Harvinder
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: Harvinder Singh
INET: [EMAIL PROTECTED]
Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051
San Diego, California-- Public Internet access / Mailing Lists
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from). You may
also send the HELP command for other information (like subscribing).
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author:
INET: [EMAIL PROTECTED]
Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051
San Diego, California-- Public Internet access / Mailing Lists
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from). You may
also send the HELP command for other information (like subscribing).
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: Harvinder Singh
INET: [EMAIL PROTECTED]
Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051
San Diego, California-- Public Internet access / Mailing Lists
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from). You may
also send the HELP command for other information (like subscribing).
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: Suzy Vordos
INET: [EMAIL PROTECTED]
Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051
San Diego, California-- Public Internet access / Mailing Lists
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from). You may
also send the HELP command for other information (like subscribing).
RE: optimizer not using function based indexes
and stats have to have been collected on the index and base table as well
just checked the 9i docs... I was wrong you do need the upper on the column, my
bad.. but you need the stats, the compatibility set (as Beth says)
|+--
|| |
|| |
|| Beth.Seefelt@tet|
|| leyusa.com |
|| |
|| 05/09/2002 05:24|
|| PM |
|| Please respond |
|| to ORACLE-L |
|| |
|+--
|
||
| To: [EMAIL PROTECTED] |
| cc: (bcc: Rachel Carmichael) |
| Subject: RE: optimizer not using function|
| based indexes|
|
Another requirement is that your compatibility init.ora parameter must
be set to 8.1.7 or higher, I believe, but check the doco.
If that doesn't fix it, then trying using a hint to force the use of
that index. If it doesn't work with the hint, then you know you have an
environment problem that is preventing the use of function-based
indexes. If it does work with the hint, then you know that function
based is working but you have to figure out why the optimizer is not
choosing it.
HTH,
Beth
-Original Message-
Sent: Thursday, May 09, 2002 4:19 PM
To: Multiple recipients of list ORACLE-L
Hi,
We are trying to use function based indexes in our application but it
looks like
optimizer is not using them.
So we created a sample table emp with 3 columns empno,ename,sal and
populate
this table with 10 rows and created function based index on
upper(ename).
we try to run simple query like below and optimzer is not using index:
select sal from emp
where upper(ename)=upper('abc98');
Even we use index hint optimizer is still not using index.
1) table is analyzed as compute after creating index.
2) query rewrite is granted to user
3) query_rewrite_enabled=true.
4) optimzer mode is choose.
5) optimer should use index as there is no way full scan can be faster
than index in this case as i tried to create simple
index on ename and remove upper function from where clause of query..
What can be the possible reason that optimzer is not using index??
Thanks
--Harvinder
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: Harvinder Singh
INET: [EMAIL PROTECTED]
Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051
San Diego, California-- Public Internet access / Mailing Lists
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from). You may
also send the HELP command for other information (like subscribing).
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: Seefelt, Beth
INET: [EMAIL PROTECTED]
Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051
San Diego, California-- Public Internet access / Mailing Lists
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from). You may
also send the HELP command for other information (like subscribing).
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author:
INET: [EMAIL PROTECTED]
Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051
San Diego, California-- Public Internet access / Mailing Lists
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from). You may
also send the HELP command for other information (like subscribing).
RE: Oracle Presentation for Beginners!
SAME MESSAGE --- Browett, Darren [EMAIL PROTECTED] wrote: You are not using the full url, check the next line for the rest of the url 01_files/v3_document.htm you need les01_files/v3_document.htm -Original Message- Sent: May 9, 2002 12:00 PM To: Multiple recipients of list ORACLE-L Any one got this message? Forbidden You don't have permission to access /~melody/courses/Spring2002Backup/BackupPPTS/les on this server. --- Sherman, Edward [EMAIL PROTECTED] wrote: Here's the slides! SQL/PL-SQL http://www.ecst.csuchico.edu/~melody/courses/Spg2001OraclePLSQL/PLSQL8iPPTS/ Default.htm Architecture and Admin http://www.ecst.csuchico.edu/~melody/courses/Fall2001CSCI379/TOC.html Performance Tuning http://www.ecst.csuchico.edu/~melody/courses/Spring2002PerfTune/TOC.html Backup and Recovery http://www.ecst.csuchico.edu/~melody/courses/Spring2002Backup/BackupPPTS/les 01_files/v3_document.htm http://www.ecst.csuchico.edu/~melody/courses/Spring2002Backup/BackupPPTS/les 02_files/v3_document.htm http://www.ecst.csuchico.edu/~melody/courses/Spring2002Backup/BackupPPTS/les 03_files/v3_document.htm And here's a gal after my own heart! http://www.ecst.csuchico.edu/~melody/ Thanks Melody!!! Ed -Original Message- Sent: Monday, May 06, 2002 5:09 AM To: Multiple recipients of list ORACLE-L Hello all, because I am an human being I am also looking for the easiest solution :-) So, I need to have a training session concerning basic oracle features. I wonder if anyone of you know place/site where I could download some powerpoint slides. Or if you have your own, pls. send me a copy ... Any help will be highly appreciated. Thanks km the lazy dba -- __ Ota itsellesi luotettava kotimainen email http://www.jippii.fi/ Tutustu samalla netin parhaaseen pelipaikkaan JIPPIIGAMESIIN. -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). * * * * * Freedom of Information Act Notice * * * * * The information in this email is subject to the record protection mandated by 5 United States Code 552 (b) (4) and relevant judicial opinions. -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Sherman, Edward INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). __ Do You Yahoo!? Yahoo! Shopping - Mother's Day is May 12th! http://shopping.yahoo.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Steven Joshua INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (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: Browett, Darren INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from 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: OT - Teradata
Have they included a DECODE function yet? - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Thursday, May 09, 2002 11:51 AM Exactly. ;) Jared Anjo Kolk [EMAIL PROTECTED] Sent by: [EMAIL PROTECTED] 05/09/2002 10:03 AM Please respond to ORACLE-L To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] cc: Subject:Re: OT - Teradata Great product. Very good for OLTP ! Very dynamic partitioning schemes ! A real winner ;-) ;;-)) Anjo. Andrey Bronfin wrote: Dear gurus , sorry for the off topic posting. One of our customers wants to start using Teradata for some of their products. That means that I need to learn it, quickly. I would like to ask for any inputs you can provide regarding Teradata - your thoughts , impressions, URLs, any materials. Thanks a lot in advance. DBAndrey * 03-9254520 * 058-548133 * mailto:[EMAIL PROTECTED] -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Andrey Bronfin INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Anjo Kolk INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Tim Gorman INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: Oracle Presentation for Beginners!
My site has some stuff up at http://www.freetechnicaltraining.com/Oracle/Free_OCP/Architecture_and_Admini stration/ I have not had time to work on this stuff so the site will be coming down in a few weeks. Creating training with audio and stuff is pretty easy just very time consuming. Use products like Camtasia (www.techsmith.com) or the free Real Audio Producer (www.real.com good luck finding it :) ) and Powerpoint. Ethan Post perotdba (AIM), epost1 (Yahoo) -Original Message- Sent: Thursday, May 09, 2002 4:06 PM To: Multiple recipients of list ORACLE-L God Bless google.com and the American higher educational system! http://www.sdsc.edu/~oracle/training/intro/syllabus.html This one, you'll have to do the HTML conversion yourself, though... Rich Jesse System/Database Administrator [EMAIL PROTECTED] Quad/Tech International, Sussex, WI USA -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Post, Ethan INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (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: optimizer not using function based indexes
I take it back... the query was correct as originally written. Just
tried similar queries on my database and the FBI was used for:
upper(ename)=upper('abc98')
upper(ename)='ABC98'
It's been a LONG week...
[EMAIL PROTECTED] wrote:
and stats have to have been collected on the index and base table as well
just checked the 9i docs... I was wrong you do need the upper on the column, my
bad.. but you need the stats, the compatibility set (as Beth says)
|+--
|| |
|| |
|| Beth.Seefelt@tet|
|| leyusa.com |
|| |
|| 05/09/2002 05:24|
|| PM |
|| Please respond |
|| to ORACLE-L |
|| |
|+--
|
||
| To: [EMAIL PROTECTED] |
| cc: (bcc: Rachel Carmichael) |
| Subject: RE: optimizer not using function|
| based indexes|
|
Another requirement is that your compatibility init.ora parameter must
be set to 8.1.7 or higher, I believe, but check the doco.
If that doesn't fix it, then trying using a hint to force the use of
that index. If it doesn't work with the hint, then you know you have an
environment problem that is preventing the use of function-based
indexes. If it does work with the hint, then you know that function
based is working but you have to figure out why the optimizer is not
choosing it.
HTH,
Beth
-Original Message-
Sent: Thursday, May 09, 2002 4:19 PM
To: Multiple recipients of list ORACLE-L
Hi,
We are trying to use function based indexes in our application but it
looks like
optimizer is not using them.
So we created a sample table emp with 3 columns empno,ename,sal and
populate
this table with 10 rows and created function based index on
upper(ename).
we try to run simple query like below and optimzer is not using index:
select sal from emp
where upper(ename)=upper('abc98');
Even we use index hint optimizer is still not using index.
1) table is analyzed as compute after creating index.
2) query rewrite is granted to user
3) query_rewrite_enabled=true.
4) optimzer mode is choose.
5) optimer should use index as there is no way full scan can be faster
than index in this case as i tried to create simple
index on ename and remove upper function from where clause of query..
What can be the possible reason that optimzer is not using index??
Thanks
--Harvinder
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: Harvinder Singh
INET: [EMAIL PROTECTED]
Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051
San Diego, California-- Public Internet access / Mailing Lists
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from). You may
also send the HELP command for other information (like subscribing).
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: Seefelt, Beth
INET: [EMAIL PROTECTED]
Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051
San Diego, California-- Public Internet access / Mailing Lists
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from). You may
also send the HELP command for other information (like subscribing).
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author:
INET: [EMAIL PROTECTED]
Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051
San Diego, California-- Public Internet access / Mailing Lists
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from). You may
also send the HELP command for other information (like subscribing).
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: Suzy Vordos
INET: [EMAIL PROTECTED]
Fat
Re: Good HR vs. Bad HR...
I think enough has been said already - I didn't intend to name the company at all. Actually, I don't think that I said that all the managers were incompetent. (A select few in the wrong places perhaps.) Since the cat is out of the bag though, I will try to end this here and now. I had a number of specific and well-documented complaints, so I fired off this kamikaze resignation letter - straight through three layers of management, even cc'ing one of the two owners of the company. The original formatting was better, but here it is in ASCII. --- [...] --- It is demoralizing also to see a parade of experts, gurus, and geniuses hand off botched, half-baked, poorly designed, and dysfunctional systems, then get major promotions and/or commendations. Case in point: The CIS applications were handed over to Wanda Kelley and I as upper management mourned the loss of their Oracle genius (a direct quote). Well this genius left us a system with almost no data integrity - there were only two foreign keys for 44 tables. Only about 70% of the tables even had a primary key. There were no check constraints or triggers to enforce data integrity. Nothing was enforced in the application. And the application handled extremely few of the business requirements. For example, the application does not function properly unless LOGONID in the EMPLOYEE table is unique. There was no such uniqueness constraint. Furthermore, the application uses where upper(LOGONID) = :some_variable, so a uniqueness constraint alone is insufficient - there also needs to be a method to enforce uppercase only on this column on inserts and updates. There was none. One could enter a new employee record, then immediately query for it, exactly as entered, and not find it! Both copies of the Oracle control file were in the same directory. The online redo logs were not mirrored. While these and other basic errors and omissions were obvious to us untrusted flunkies, the genius overlooked them. The users were connecting to the database as the SYSTEM user (with the default password MANAGER of course). His project plan included installing SQL*Net on hundreds of PC's using WCSGCOPY. This doesn't work, all it does is remove the ORACLE_HOME directory and replace it with files copied from a server. It doesn't update the registry and it doesn't consider that there may already be an Oracle client on the machine (typically, there is - and it includes things that this method would delete, like the Oracle forms runtime). This last item became an issue when I first heard of it less than a week before the implementation date. (Incidentally, that was the first time that most of us even heard about this project.) We tested a variation of this method for CIT client installations and determined that it was impractical. How does one do such a poor job and yet convince everyone they are a genius? Is perception is truly everything here? To further compound the problem, we were never allowed to question or even review anything - not the application, not the database, and not the schema. The whole mess was a well-guarded secret until the very Friday afternoon that this consultant's contract was over and he left the company. At that time, it was turned over to us. We very quickly discovered that almost nothing actually worked. We were told that it absolutely had to go live Monday morning and that the deadline was totally inflexible. Then, in a rather nasty and threatening tone, we were told that we would just have to work over the weekend to fix it and install the Oracle client on 400 PCs. Since the only way to actually fix this disaster was to redesign and rewrite significant portions of it, we insisted that this plan wasn't realistic. How could we be expected to repair nine months of damage in a single weekend - in our spare time while also doing 400 client installations? The response was that we were simply not team players. --- Another significant aspect of my dissatisfaction is that management quite frequently fails to understand (or even question) the usefulness of tasks assigned. I have been assigned many tasks that were said to be urgent and critical only to have the results discarded when completed. I have several classic examples… Case in point: During the very early stages of Telephony development, Steve Flott and I were assigned the task of writing data injectors to populate the Telephony tables for RACP testing. We were told this was of paramount importance and had to be done in less than two weeks by doing whatever it takes. After we started and saw how immature the design was, we questioned whether this task was premature. The design was changing as fast as we could write code. We worked 60-70 hours weeks until it was done. Every time we were ostensibly finished we were given more extensive design changes and told to rewrite the code to fit the new design. We did this twice. After we finished the
script to show heirarchical list of object dependencies for a given object
Greetings listers,
Ever worry about wrinkles, loss of hair, and bad
breath?
Well, the following script can't help you with THAT,
but it CAN show you a heirarchical list of objects
that depend upon the given object! Just pass in
1=owner and 2=object name, and viola! No more
changing objects without knowing what depends on them
(and might break). (The reason for the temp table is
that you can't do recursive self-join sql on complex
views.) This can be a very useful script if you make
production database changes.
prompt
prompt objects depending on 1. 2:
prompt
set termout off
set head off
set verify off
drop table depends;
create table depends as select * from
sys.dba_dependencies;
set termout on
select '*'||lpad(' ',level+3)||type||'
'||owner||'.'||name
from depends
connect by prior owner = referenced_owner
and prior name = referenced_name
and prior type = referenced_type
start with referenced_owner = upper('1')
and referenced_name = upper('2')
and owner is not null
/
good luck,
jack silvey
__
Do You Yahoo!?
Yahoo! Shopping - Mother's Day is May 12th!
http://shopping.yahoo.com
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: Jack Silvey
INET: [EMAIL PROTECTED]
Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051
San Diego, California-- Public Internet access / Mailing Lists
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from). You may
also send the HELP command for other information (like subscribing).
Server upgrade NT4 - W2K Oracle implications
We have an 8.1.7 SE database on a server which is currently NT4 SP6. The System Admin group wish to upgrade the server to W2K and propose doing so by recreating the C: partitition. Our Oracle software resides on E: (same physical disk) and database files on other disks/partitions. Are there any implications for the Oracle software and database for this upgrade. Are there any steps I need to take pre or post OS upgrade?. In particular I'm wondering about implications for the registry. Any advice/help would be appreciated. - Seán O' Neill Organon (Ireland) Ltd. [subscribed: digest mode] This message, including attached files, may contain confidential information and is intended only for the use by the individual and/or the entity to which it is addressed. Any unauthorized use, dissemination of, or copying of the information contained herein is not allowed and may lead to irreparable harm and damage for which you may be held liable. If you receive this message in error or if it is intended for someone else please notify the sender by returning this e-mail immediately and delete the message. -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: O'Neill, Sean INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: DBA_EXTENTS problem
Babu,
execute dbms_utility.analyze_schema('system','delete') this would do.
-Shaibal
From: Janardhana Babu Donga [EMAIL PROTECTED]
Reply-To: [EMAIL PROTECTED]
To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED]
Subject: RE: DBA_EXTENTS problem
Date: Thu, 09 May 2002 11:41:16 -0800
I used to run the following analyze every week:
statement which included SYSTEM schema,
Two weeks back it was changed to
dbms_stats.gather_database_stats();
Is there anyway to de-analyze SYSTEM schema?
Thanks,
-- Babu
-Original Message-
Sent: Thursday, May 09, 2002 11:04 AM
To: 'Janardhana Babu Donga'
Babu - Among the suggestions you have received, the one that seems to have
the most merit is whether any of your system tables have been analyzed.
Were
I in your position, I would check that out.
Dennis Williams
DBA
Lifetouch, Inc.
[EMAIL PROTECTED]
-Original Message-
Sent: Thursday, May 09, 2002 12:51 PM
To: '[EMAIL PROTECTED]'
Cc: DENNIS WILLIAMS
Dennis,
It is happenning for any table in the database. I have just created a new
table:
create table x1 (col1 number) tablespace data_ts, inserted one row and
run:
select * from dba_extents where segment_name = 'X1'; It is still sitting
there. The response to any other dictionary view is spontaneous except this
dba_extents, which is confusing me.
I issued: select * from dba_objects where ..., select * from dba_segments
where etc. They are all responding fine.
There are not many users at the moment. System tablespace is 500M size and
300MB is free. DB is 40Gig in size. It is on HP_UX 11.0 and Oracle
8.1.7.2.0
(64bit) database.
Any other ideas?
Thanks,
-- Babu
-Original Message-
Sent: Thursday, May 09, 2002 11:29 AM
To: Multiple recipients of list ORACLE-L
Babu - Wow, never had that one. Of course, I don't use that table much. How
many rows are in this table? Mine has 12,937 rows, and is kinda slow to
respond compared to the other system tables. Do you have a test database to
compare with? Is it possible that your system tablespace has become badly
fragmented? What is your system load, is it possibly a contention problem
of
some type. Why not run STATSPACK (or utlbstat) while you do this query and
see what is being waited for? Why don't I shut up and send this?
Dennis Williams
DBA
Lifetouch, Inc.
[EMAIL PROTECTED]
-Original Message-
Sent: Thursday, May 09, 2002 12:58 PM
To: Multiple recipients of list ORACLE-L
Dear List,
When I query dba_extents , Iam getting response after half an hour to 40
minutes, but when I query any other dictionary view it is spontaneous.
Everything else is fine in the database and there are no problems, except
the above problem. Iam not getting any clue how to fix this. Iam thinking
of
running catalog.sql and catproc.sql as a last resort. Iam not sure it would
fix the problem. Please let me know if there is any other way to fix this
problem.
Thanks,
Babu
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: Janardhana Babu Donga
INET: [EMAIL PROTECTED]
Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051
San Diego, California-- Public Internet access / Mailing Lists
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from). You may
also send the HELP command for other information (like subscribing).
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: DENNIS WILLIAMS
INET: [EMAIL PROTECTED]
Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051
San Diego, California-- Public Internet access / Mailing Lists
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from). You may
also send the HELP command for other information (like subscribing).
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: Janardhana Babu Donga
INET: [EMAIL PROTECTED]
Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051
San Diego, California-- Public Internet access / Mailing Lists
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from). You may
also send the HELP command for other information (like subscribing).
_
Chat with friends online,
RE: Transactions per second
Your
environment is much different from our test which was one-tier, but did
use Oracle 9i. We had no indexes on the partition in which the
insert was taking place. We did no special tuning other than to balance the I/O
load. We employed Veritas and its direct IO capability. That's
direct not quick. We used Oracle's OCiDirectPath
capabilities.
The
production system only has to do 500 tps routinely and 1000 tps peak in its
initial configuration. So when the Controls folks came back
and said they had achieved 15,000 tps. I wasn't going to muck with
the system.
N.B. There is a bug in the Oracle 9i version of the OCI direct path
functions which has additional requirements to that 8i
version.
Here's
some more information about it:
From: David Mitchell 04-Jan-02 00:23
Subject: Re : Crash in OCIDirPathPrepare on Solaris and Oracle9i
I'll present a few reasons why I believe this is a 9i regression bug:
* The Oracle8i client doesn't have this problem.
* The way it fails:
* If the schema is required, OCIDirPathPrepare() should fail with an error. A
crash/core dump is an ... unconventional way of indicating an
error.
* Looking at the stack trace, the crash appears to occur in code where it's
getting table-type info. Perhaps it's making the assumption that
there's always a schema name:
[12] sigacthandler(0xb, 0xf9f81dc0, 0xf9f7f220, 0xfdbec9ac, 0xf9f7f4d8,
0xf9f81dc0), at 0xfdbd8644
called from signal handler with signal 11 (SIGSEGV) --
[13] kpudpxp_genCaseSensName(0x0, 0x0, 0x5734c8, 0xf9f7f778, 0x1f, 0x2), at
0xfadba9cc
[14] kpudpxp_setTblObjType(0x5724ac, 0x5ad050, 0x5ad0bc, 0xf9f81840, 0x48,
0x572fcc), at 0xfadbad58
[15] kpudpxp_ctxPrepare(0x573454, 0x5ad050, 0x5ad0bc, 0xfb4126d0, 0x5734c8,
0x5ad0bc), at 0xfadb1590
[16] OCIDirPathPrepare(0x573454, 0xfc8b5a30, 0x5ad0bc, 0xfc8b1720, 0x5ad0bc,
0x5ad050), at 0xfc87cd94
* Finally, there's the documentation (from
http://download-west.oracle.com/otndoc/oracle9i/901_doc/appdev.901/a89857/ociaahan.htm#453382):
OCI_ATTR_SCHEMA_NAME
Mode
READ/WRITE
Description
Name of the schema where the table being loaded resides. If not specified,
the schema defaults to that of the connected user.
Attribute Datatype
text **/text *
I can work-around it by setting an empty ("") schema, but since the behavior
is contrary to the documentation it seems to be a bug.
-Original Message-From: [EMAIL PROTECTED]
[mailto:[EMAIL PROTECTED]]Sent: Thursday, May 09, 2002 4:08
AMTo: Multiple recipients of list ORACLE-LSubject: RE:
Transactions per secondHi, From the messages below
I understand that some of you are having big transactions per second
requirements in the application. We are also developing an application that
requires 5000 TPS, can anybody suggest how to get the size of the TPS and also
how I can test for the number of Transaction per second. We are using a 3 tier architecture, Java client. EJB
and Oracle 9i. I would be very
useful if some help is available in this regards Regards Prem
Chandran N
"MacGregor, Ian A."
[EMAIL PROTECTED] Sent by: [EMAIL PROTECTED]
05/09/02 04:53 AM Please respond to ORACLE-L
To:
Multiple recipients of list ORACLE-L
[EMAIL PROTECTED] cc:
Subject:RE: Transactions per
secondWhere does that theoretical limit come from? We've
done 15,000 tps here using OCIDirectWrite calls on amachine with
4, 450 MHzCPU's an a-1000 and some internal
disks. The transactions were small about 150 bytes
max. There was no network involved, and no queries were
being run against the database. The 15,000 tps figure comes
from our accelerator controls department which is
testingOracle'sfeasibilityto store information on
theaccelerator's status. Ian
MacGregor Stanford Linear
Accelerator Center [EMAIL PROTECTED]
-Original Message- From: Tim Gorman
[mailto:[EMAIL PROTECTED]] Sent: Wednesday, May 08, 2002 11:39 AM To: Multiple recipients of list ORACLE-L
Subject: Re: Transactions per
second Not that it's relevant
to this question, but I understandthe theoretical limit is 16,384
tps.This affects the sizing of integers used for SCN base,
wrap, seq# in the block headers, I guess... - Original Message - From: Rick Stephenson
To: Multiple recipients of list ORACLE-L Sent: Wednesday, May 08, 2002 11:30 AM Subject: FW: Transactions per
second I have a developer
that asked "how many transactions per second can Oracle handle?"
I would assume that the number of
transactions depends on the size of the transactions, number of CPUs,
memory, etc. Is there a
guideline to follow when guesstimating something like this, or is it
just trial an error to find out whether it can handle the
Re: Improving Oracle Performance
Sankar, For starters, SQL set autotrace trace explain Now run your SQL and you'll get an explain plan. Send another email to the list showing both: the SQL statement, and the explain plan. These may reveal your problem quickly. - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Thursday, May 09, 2002 6:08 AM Hi, We are using Oracle 8.1.5 on solaris 2.7.We have a table with 70 columns. My problem is when i try to insert/update data into the table,it is taking a very long time[45secs to update 40rows].Is there any methods to improve the updation/access speed other than Table partitions. Thanks Regards, Sankar -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: P.V.Sankar INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (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: Greg Moore INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Re: Good DBA vs. Bad DBA
Outstanding response! Don Granaman [certified skeptic] - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Thursday, May 09, 2002 9:13 AM no - DBAs would be skeptical of conspiracy theories. -Original Message- Sent: Thursday, May 09, 2002 8:23 AM To: Multiple recipients of list ORACLE-L I wonder, do DBA's tend to lean more toward being conspiracy theorists then??? RF -Original Message- Sent: Wednesday, May 08, 2002 8:18 PM To: Multiple recipients of list ORACLE-L Dennis, I think you nailed it. I was a skeptic before I was a DBA. Jared DENNIS WILLIAMS [EMAIL PROTECTED] Sent by: [EMAIL PROTECTED] 05/08/2002 08:58 AM Please respond to ORACLE-L To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] cc: Subject:RE: Good DBA vs. Bad DBA Mladen - What hasn't Dilbert had to say about managers? Read his books if you need more. Along this topic, someone provided the answer to why do technical people like Dilbert's opinions so much?. Their response was that because we are hired because we are intelligent and educated (well I'm still fooling them), we are hard to manage. We tend not to blindly trust authority figures, but to be a little skeptical. In fact, sometimes I think a DBA is a professional skeptic. Dennis Williams DBA Lifetouch, Inc. [EMAIL PROTECTED] -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Freeman, Robert INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (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: STEVE OLLIG INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (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: Don Granaman INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (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: too low optimizer_index_cost_adj causing bizarre index choice
Undocumented behavior:
setting optimizer_index_cost_adj = 1 is simply begging the optimizer to use
any index, just kidding :}
I think it's simply b/c the total cost which is = cost of sequential read *
number of reported rows in the index (adjusted by optimizer_index_cost_adj)
is the lowest calculated one.
Regards,
Waleed
-Original Message-
Sent: Thursday, May 09, 2002 5:06 PM
To: Multiple recipients of list ORACLE-L
choice
Hi Johnathan,
here is the skinny:
db_file_multiblock_read_count = 64
number of rows=15m
blocks=251071
empty_blocks=0
db_block_size=16384
total plan cost=4924
tablescan cost = 4924
Jack
--- Jonathan Lewis [EMAIL PROTECTED]
wrote:
There seems to be a perfectly good theoretical
reason
for this. But it would be interesting to know:
Number of blocks below HWM
Setting for db_file_multiblock_read_count
Maximum usable value for db_file_mbrc
The cost given by Explain Plan for the tablescan
before I confuse the issue further by expounding a
hypothesis that may be totally misleading.
Jonathan Lewis
http://www.jlcomp.demon.co.uk
Author of:
Practical Oracle 8i: Building Efficient Databases
Next Seminar - Australia - July/August
http://www.jlcomp.demon.co.uk/seminar.html
Host to The Co-Operative Oracle Users' FAQ
http://www.jlcomp.demon.co.uk/faq/ind_faq.html
-Original Message-
To: Multiple recipients of list ORACLE-L
[EMAIL PROTECTED]
Date: 09 May 2002 14:30
|Good morning listers,
|
|Some weeks ago I noticed a query plan that was
using a
|bizarre index choice and opened a TAR. Here is that
|query:
|
|select DSS_EMPR.V_CLAIM_EMPR_HX.AMT_COPAY from
|DSS_EMPR.V_CLAIM_EMPR_HX where
|DSS_EMPR.V_CLAIM_EMPR_HX.BATCH_NUM between
|to_date('2000-01-01','-mm-dd') and
|to_date('2000-01-31','-mm-dd');
|
|Here is the query plan:
|
| SELECT STATEMENT (all_rows) Cost
|(4924,636953,12739060)
|
| 1 0 1 2
|TABLE ACCESS (analyzed) DSS_EMPR
T_CLAIM_EMPR_HX
|(by index rowid) Cost (4924,636953,12739060)
|
| 2 1 1
| BITMAP CONVERSION(to rowids)
|
| 3 2 1
|BITMAP INDEX DSS_EMPR X_CLAIM_EMPR_HX_N01
|(full scan)
|
|
|The index x_claim_empr_hx_n01 is on the phmcy_gid
|column of the t_claim_empr_hx table.
|
|Now, here is the crux of the matter: phmcy_gid is
|referred to nowhere in the query, not in select nor
in
|where nor even in order by.
|
|Worked through the tar with Oracle and they advised
|that Oracle can and will cost ALL indexes during a
|plan parse and eval, so it became a matter of
|discovering why the index was being incorrectly
|costed.
|
|Remembered that our optimizer_index_cost_adj was
set
|to 1 (don't ask). When I upped this value to 2 or
more
|and reran the query, it returned the appropriate
FTS
|plan.
|
|hth,
|
|Jack Silvey
|
--
Please see the official ORACLE-L FAQ:
http://www.orafaq.com
--
Author: Jonathan Lewis
INET: [EMAIL PROTECTED]
Fat City Network Services-- (858) 538-5051 FAX:
(858) 538-5051
San Diego, California-- Public Internet
access / Mailing Lists
To REMOVE yourself from this mailing list, send an
E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of
'ListGuru') and in
the message BODY, include a line containing: UNSUB
ORACLE-L
(or the name of mailing list you want to be removed
from). You may
also send the HELP command for other information
(like subscribing).
__
Do You Yahoo!?
Yahoo! Shopping - Mother's Day is May 12th!
http://shopping.yahoo.com
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: Jack Silvey
INET: [EMAIL PROTECTED]
Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051
San Diego, California-- Public Internet access / Mailing Lists
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from). You may
also send the HELP command for other information (like subscribing).
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: Khedr, Waleed
INET: [EMAIL PROTECTED]
Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051
San Diego, California-- Public Internet access / Mailing Lists
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from). You
cannot dsiplay bfile data-URGENT
I am not able to display the bmp stored in the
BFILE.Ifollowed the following steps.I have been following the
NOTE from metalink and the oracle app dev
guide.
CREATE TABLE empbfiles (id NUMBER PRIMARY KEY,
empname
VARCHAR2(20),
photo BFILE); / CREATE OR REPLACE DIRECTORY
empbfiles AS '/u02/ora';
/
INSERT INTO empbfiles VALUES (1,
'Smith',bfilename('EMPBFILES', 'smith.bmp'));
/
select count(*) from empbfiles where
id=1;
/
COUNT(*)--
1
I am using the following procedure(got this from
the oracle app devlopers guide) to display the bfile data
CREATE OR REPLACE PROCEDURE displayBFILE_proc
ISLob_loc BFILE;Buffer RAW(1024);Amount BINARY_INTEGER :=
1024;Position INTEGER := 1;BEGIN/* Select the LOB: */SELECT
photo INTO Lob_locFROM empbfiles WHERE id = 1;/* Opening the BFILE:
*/DBMS_LOB.OPEN (Lob_loc, DBMS_LOB.LOB_READONLY);LOOPDBMS_LOB.READ
(Lob_loc, Amount, Position, Buffer);/* Display the buffer contents:
*/DBMS_OUTPUT.PUT_LINE(utl_raw.cast_to_varchar2(Buffer));Position :=
Position + Amount;END LOOP;/* Closing the BFILE: */DBMS_LOB.CLOSE
(Lob_loc);END;
when I execute the above procedure I am getting the
following error
ERROR at line 1:ORA-01403: no data
foundORA-06512: at "SYS.DBMS_LOB", line 656ORA-06512: at
"HHNEWBUILD.DISPLAYBFILE_PROC", line 13ORA-06512: at line 1
What am i missing or doing wrong.
Ravi
