Re: [Q] wait time on stat
Do you have any LOBs defined with the nocache nologging attribute ? Regards Jonathan Lewis http://www.jlcomp.demon.co.uk The educated person is not the person who can answer the questions, but the person who can question the answers -- T. Schick Jr Next public appearances: Jan 29th 2004 UKOUG Unix SIG - v$ and x$ March 2004 Hotsos Symposium - The Burden of Proof March 2004 Charlotte NC OUG - CBO Tutorial April 2004 Iceland One-day tutorials: http://www.jlcomp.demon.co.uk/tutorial.html Three-day seminar: see http://www.jlcomp.demon.co.uk/seminar.html UK___February UK___June The Co-operative Oracle Users' FAQ http://www.jlcomp.demon.co.uk/faq/ind_faq.html - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Wednesday, January 28, 2004 8:09 PM WE have ORACLE 9.2.0.3 database run under Win2000. I run statspack and found Top 5 timed event. Look like control file parallel write and redo file parallel write take lots time. How to fix? Top 5 Timed Events ~~ % Total Event WaitsTime (s) Ela Time --- control file parallel write 5,499 1,14190.37 CPU time 97 7.70 log file parallel write 1,305 12 .95 db file parallel write 162 6 .44 log file switch completion 10 2 .16 - Wait Events for DB: 9IDEV Instance: 9idev Snaps: 5 -6 - s - second - cs - centisecond - 100th of a second - ms - millisecond -1000th of a second - us - microsecond - 100th of a second - ordered by wait time desc, waits desc (idle events last) Avg Total Wait waitWaits Event Waits Timeouts Time (s) (ms) /txn -- -- -- control file parallel write 5,499 0 1,141207211.5 log file parallel write 1,305 1,299 12 9 50.2 db file parallel write162 0 6 34 6.2 log file switch completion 10 0 2204 0.4 control file sequential read3,827 0 2 0147.2 db file sequential read 176 0 1 7 6.8 direct path write 92 0 1 6 3.5 log file sync 14 0 0 33 0.5 log file single write 20 0 0 13 0.8 log file sequential read 35 0 0 6 1.3 direct path read 92 0 0 2 3.5 SQL*Net break/reset to clien 44 0 0 0 1.7 SQL*Net more data to client 7 0 0 0 0.3 async disk IO 4 0 0 0 0.2 virtual circuit status 6,826496 19,650 2879262.5 wakeup time manager 530530 19,179 36187 20.4 SQL*Net message from client 6,457 0 12,084 1871248.3 jobq slave wait 402381 1,227 3051 15.5 SQL*Net message to client 6,458 0 0 0248.4 - Background Wait Events for DB: 9IDEV Instance: 9idev Snaps: 5 -6 - ordered by wait time desc, waits desc (idle events last) __ Do you Yahoo!? Yahoo! SiteBuilder - Free web site building tool. Try it! http://webhosting.yahoo.com/ps/sb/ -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: dba1 mcc INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ:
RE: [Q] wait time on stat
-Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of Mladen Gogala Sent: 29 January 2004 01:54 To: Multiple recipients of list ORACLE-L Subject: Re: [Q] wait time on stat Defaulty Windows installation usually creates log files of 20M. The person who posted the problem explicitely mentioned Windows platform. Size of 20M is so small that the database will start to checkpoint like crazy when you start using it for real. On 9.2 at least (might be 9.0.1 as well but that isn't in use here) each of the available template databases used by dbca comes with 3 redo logs of 100m each by default. Don't know what the default is if you don't specify a size in the create database statement (never been that daft) but that wouldn't be windows specific. Niall -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Niall Litchfield INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Re: [Q] wait time on stat
Hi! Do you have any LOBs defined with the nocache nologging attribute ? This was what I initially thought would be the problem, but then I thought, in case of nocache lobs you should see direct path writes (lob) a lot... Tanel. -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Tanel Poder INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: UNION ALL Query: Riddle
I have the same problem like you Rajesh, the query also gives different rowcount each time executed eventhough there's no one updating base tables, in my opinion it's because of the sorting operation (your group by clause). In my case after I remove some group functions, the result goes well. Also I reduce the use of order by clause where it's not needed. I still haven't found the exact solution to this problem. But just now I've tried to decrease the sort area size parameter value (I think I oversize it), and run the query again, the result goes stable with the problematic query but it runs slower. I haven't tried intensively, I try to do that tomorrow. Meanwhile if, there's any of the Gurus can give us clearer explanation, please do so... Thank you all in advance. Regards, Wendry. -Original Message- Pillai, Rajesh Sent: Thursday, January 29, 2004 2:24 AM To: Multiple recipients of list ORACLE-L Hi Jared, Thanks for your response. different results mean that number of records are different sometimes, and sometimes the some of the quantities are not correct. Your help is really appreciated. Thanks, Rajesh -Original Message- Sent: Tuesday, January 27, 2004 2:29 PM To: Multiple recipients of list ORACLE-L Q: What does different results mean? Different row count? Completely different data? Partially different data? Some columns have incorrect value? What about doing it without the parallel hints? The tables aren't so big that it would take a long time to find out. Jared Pillai, Rajesh [EMAIL PROTECTED] Sent by: [EMAIL PROTECTED] 01/27/2004 01:09 PM Please respond to ORACLE-L To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] cc: Subject: UNION ALL Query: Riddle Hi All, The following query is giving different results in each run. I assure that no data modified between consecutive runs - INSERT /* append parallel (z,8) */ INTO some_table (SELECT /*parallel (a,8) */ a.item, a.loc, SUM(a.qty_type_1), SUM(a.qty_type_2) FROM (select /*parallel (x,8) */ item, loc, qty_type_1, to_number(NULL) from table_a x UNION ALL select /*parallel (y,8) */ item, loc, to_number(NULL), qty_type_2 from table_b y ) a GROUP BY a.item, a.loc); Additional info - Number of records in table_a and table_b is around 3M and 6M. SQL select * from v$version; BANNER Oracle8i Enterprise Edition Release 8.1.7.2.0 - Production PL/SQL Release 8.1.7.2.0 - Production CORE 8.1.7.0.0 Production TNS for Solaris: Version 8.1.7.2.0 - Production NLSRTL Version 3.4.1.0.0 - Production I would appreciate any help in solving this mystery and all hints are welcome. Thanks, Rajesh Pillai -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Pillai, Rajesh INET: [EMAIL PROTECTED] Fat City Network Services -- 858-538-5051 http://www.fatcity.com San Diego, California -- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Wendry INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the
Re: [Q] wait time on stat
Mladen, Here I will humbly disagree with you. It is not fault of Windows but the person who created the database and defined tiny size log files while creation. If he used custom database option he can define whatever size he wants. These options are same for both Windows and Unix. Regards Rafiq Reply-To: [EMAIL PROTECTED] To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Date: Wed, 28 Jan 2004 17:54:26 -0800 Defaulty Windows installation usually creates log files of 20M. The person who posted the problem explicitely mentioned Windows platform. Size of 20M is so small that the database will start to checkpoint like crazy when you start using it for real. The number that I gave is my rule of thumb. It's not to big to frighten the boss (alltogether just a gig or two) and it's big enough to solve checkpointing problem. This rule of thumb number is derived from experience. Yes, of course, I am guessing. There is not enough information to investigate the problem. I believe that he was asking us to guess. I did. On 01/28/2004 08:14:25 PM, Jared Still wrote: How did you determine the size of the logs? Or are you just guessing that they are significantly less than 250m? Could just be a very busy database that needs redo and controlfile on faster disk. Jared On Wed, 2004-01-28 at 12:24, Mladen Gogala wrote: Increase redo logs to 250M each. You're checkpointing. On 01/28/2004 03:09:26 PM, dba1 mcc wrote: WE have ORACLE 9.2.0.3 database run under Win2000. I run statspack and found Top 5 timed event. Look like control file parallel write and redo file parallel write take lots time. How to fix? Top 5 Timed Events ~~ % Total Event WaitsTime (s) Ela Time --- control file parallel write 5,499 1,14190.37 CPU time 97 7.70 log file parallel write 1,305 12 .95 db file parallel write 162 6 .44 log file switch completion 10 2 .16 - Wait Events for DB: 9IDEV Instance: 9idev Snaps: 5 -6 - s - second - cs - centisecond - 100th of a second - ms - millisecond -1000th of a second - us - microsecond - 100th of a second - ordered by wait time desc, waits desc (idle events last) Avg Total Wait waitWaits Event Waits Timeouts Time (s) (ms) /txn -- -- -- control file parallel write 5,499 0 1,141207211.5 log file parallel write 1,305 1,299 12 9 50.2 db file parallel write162 0 6 34 6.2 log file switch completion 10 0 2204 0.4 control file sequential read3,827 0 2 0147.2 db file sequential read 176 0 1 7 6.8 direct path write 92 0 1 6 3.5 log file sync 14 0 0 33 0.5 log file single write 20 0 0 13 0.8 log file sequential read 35 0 0 6 1.3 direct path read 92 0 0 2 3.5 SQL*Net break/reset to clien 44 0 0 0 1.7 SQL*Net more data to client 7 0 0 0 0.3 async disk IO 4 0 0 0 0.2 virtual circuit status 6,826496 19,650 2879262.5 wakeup time manager 530530 19,179 36187 20.4 SQL*Net message from client 6,457 0 12,084 1871248.3 jobq slave wait 402381 1,227 3051 15.5 SQL*Net message to client 6,458 0 0 0248.4 - Background Wait Events for DB: 9IDEV Instance: 9idev Snaps: 5 -6 - ordered by wait time desc, waits desc (idle events last) __ Do you Yahoo!? Yahoo! SiteBuilder - Free web site building tool. Try it! http://webhosting.yahoo.com/ps/sb/ -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: dba1 mcc INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL
RE: !!Please Read - Oracle-L is moving!!
the OT list is significantly smaller than this list and I clean addresses every 6 months or so. It's not easy being a list owner, Jared has my utmost respect for the work it takes to manage this huge list. It took me 3 seconds to send the first message to subscribe and less than that to verify the subscription. Gee, that was way too much work. NOT --- [EMAIL PROTECTED] wrote: I must disagree . . . this resource is worth MUCH more than the minimal effort it took :-) But if it clears out the dead subscribers, perhaps we should list addresses every 6 months ;-) Babette Turner-Underwood work: [EMAIL PROTECTED] home: [EMAIL PROTECTED] 954-3752 (Mon - Fri 7am - 3pm) -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of [EMAIL PROTECTED] Sent: 2004-01-28 12:25 PM To: Multiple recipients of list ORACLE-L Subject: RE: !!Please Read - Oracle-L is moving!! may i just say - this resource is worth the minimal effort it took -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] Behalf Of [EMAIL PROTECTED] Sent: Wednesday, January 28, 2004 12:09 PM To: Multiple recipients of list ORACLE-L Subject: RE: !!Please Read - Oracle-L is moving!! You sir, have obviously never done this. I have. :) Nor read my first post on the matter. No, it would not be easier, not by a long shot. This is free service, so my thinking is, share the workload. Jared [EMAIL PROTECTED] Sent by: [EMAIL PROTECTED] 01/27/2004 11:49 PM Please respond to ORACLE-L To:Multiple recipients of list ORACLE-L [EMAIL PROTECTED] cc: Subject:RE: !!Please Read - Oracle-L is moving!! Hi List Manger- Couldn't a immigration of our subscribed accounts been the most logical and error free option ? All this fire would have been avaoided. CSW Simon. __ Do you Yahoo!? Yahoo! SiteBuilder - Free web site building tool. Try it! http://webhosting.yahoo.com/ps/sb/ -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Rachel Carmichael INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Rachel Carmichael INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: !!Please Read - Oracle-L is moving!!
Oops ... Had a typo in the second sentence It should have read But if it clears out the dead subscribers, perhaps we should CHANGE list addresses every 6 months ;-) I was AGREEING that the changing list addresses was a minimal amount of work compared to the amount that we get back out of this list resource. I think Jared has been doing a great job. Not only as a listowner but often at times as a list moderator. HOW does he find time to read ALL of those e-mails? - Babette -Original Message- Sent: 2004-01-28 2:00 PM To: Multiple recipients of list ORACLE-L the OT list is significantly smaller than this list and I clean addresses every 6 months or so. It's not easy being a list owner, Jared has my utmost respect for the work it takes to manage this huge list. It took me 3 seconds to send the first message to subscribe and less than that to verify the subscription. Gee, that was way too much work. NOT --- [EMAIL PROTECTED] wrote: I must disagree . . . this resource is worth MUCH more than the minimal effort it took :-) But if it clears out the dead subscribers, perhaps we should list addresses every 6 months ;-) Babette Turner-Underwood work: [EMAIL PROTECTED] home: [EMAIL PROTECTED] 954-3752 (Mon - Fri 7am - 3pm) -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of [EMAIL PROTECTED] Sent: 2004-01-28 12:25 PM To: Multiple recipients of list ORACLE-L Subject: RE: !!Please Read - Oracle-L is moving!! may i just say - this resource is worth the minimal effort it took -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] Behalf Of [EMAIL PROTECTED] Sent: Wednesday, January 28, 2004 12:09 PM To: Multiple recipients of list ORACLE-L Subject: RE: !!Please Read - Oracle-L is moving!! You sir, have obviously never done this. I have. :) Nor read my first post on the matter. No, it would not be easier, not by a long shot. This is free service, so my thinking is, share the workload. Jared [EMAIL PROTECTED] Sent by: [EMAIL PROTECTED] 01/27/2004 11:49 PM Please respond to ORACLE-L To:Multiple recipients of list ORACLE-L [EMAIL PROTECTED] cc: Subject:RE: !!Please Read - Oracle-L is moving!! Hi List Manger- Couldn't a immigration of our subscribed accounts been the most logical and error free option ? All this fire would have been avaoided. CSW Simon. __ Do you Yahoo!? Yahoo! SiteBuilder - Free web site building tool. Try it! http://webhosting.yahoo.com/ps/sb/ -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Rachel Carmichael INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: [EMAIL PROTECTED] INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing
RE: !!Please Read - Oracle-L is moving!!
I was agreeing with you. :) --- [EMAIL PROTECTED] wrote: Oops ... Had a typo in the second sentence It should have read But if it clears out the dead subscribers, perhaps we should CHANGE list addresses every 6 months ;-) I was AGREEING that the changing list addresses was a minimal amount of work compared to the amount that we get back out of this list resource. I think Jared has been doing a great job. Not only as a listowner but often at times as a list moderator. HOW does he find time to read ALL of those e-mails? - Babette -Original Message- Sent: 2004-01-28 2:00 PM To: Multiple recipients of list ORACLE-L the OT list is significantly smaller than this list and I clean addresses every 6 months or so. It's not easy being a list owner, Jared has my utmost respect for the work it takes to manage this huge list. It took me 3 seconds to send the first message to subscribe and less than that to verify the subscription. Gee, that was way too much work. NOT --- [EMAIL PROTECTED] wrote: I must disagree . . . this resource is worth MUCH more than the minimal effort it took :-) But if it clears out the dead subscribers, perhaps we should list addresses every 6 months ;-) Babette Turner-Underwood work: [EMAIL PROTECTED] home: [EMAIL PROTECTED] 954-3752 (Mon - Fri 7am - 3pm) -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of [EMAIL PROTECTED] Sent: 2004-01-28 12:25 PM To: Multiple recipients of list ORACLE-L Subject: RE: !!Please Read - Oracle-L is moving!! may i just say - this resource is worth the minimal effort it took -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] Behalf Of [EMAIL PROTECTED] Sent: Wednesday, January 28, 2004 12:09 PM To: Multiple recipients of list ORACLE-L Subject: RE: !!Please Read - Oracle-L is moving!! You sir, have obviously never done this. I have. :) Nor read my first post on the matter. No, it would not be easier, not by a long shot. This is free service, so my thinking is, share the workload. Jared [EMAIL PROTECTED] Sent by: [EMAIL PROTECTED] 01/27/2004 11:49 PM Please respond to ORACLE-L To:Multiple recipients of list ORACLE-L [EMAIL PROTECTED] cc: Subject:RE: !!Please Read - Oracle-L is moving!! Hi List Manger- Couldn't a immigration of our subscribed accounts been the most logical and error free option ? All this fire would have been avaoided. CSW Simon. __ Do you Yahoo!? Yahoo! SiteBuilder - Free web site building tool. Try it! http://webhosting.yahoo.com/ps/sb/ -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Rachel Carmichael INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: [EMAIL PROTECTED] INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). __ Do you Yahoo!? Yahoo! SiteBuilder - Free web site building tool. Try it! http://webhosting.yahoo.com/ps/sb/ -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Rachel Carmichael INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services
RE: !!Please Read - Oracle-L is moving!!
Phew, I would hate to have the Oracle Goddess in disagreement with me. I have already had a bad enough day Arguing with one of the other DBAs about how long a recovery took. (It was just testing that recovery would work... Proof of concept... Not testing for recovery timing AAAarrgh!) - Babette -Original Message- Sent: 2004-01-28 2:34 PM To: Multiple recipients of list ORACLE-L I was agreeing with you. :) --- [EMAIL PROTECTED] wrote: Oops ... Had a typo in the second sentence It should have read But if it clears out the dead subscribers, perhaps we should CHANGE list addresses every 6 months ;-) I was AGREEING that the changing list addresses was a minimal amount of work compared to the amount that we get back out of this list resource. I think Jared has been doing a great job. Not only as a listowner but often at times as a list moderator. HOW does he find time to read ALL of those e-mails? - Babette -Original Message- Sent: 2004-01-28 2:00 PM To: Multiple recipients of list ORACLE-L the OT list is significantly smaller than this list and I clean addresses every 6 months or so. It's not easy being a list owner, Jared has my utmost respect for the work it takes to manage this huge list. It took me 3 seconds to send the first message to subscribe and less than that to verify the subscription. Gee, that was way too much work. NOT --- [EMAIL PROTECTED] wrote: I must disagree . . . this resource is worth MUCH more than the minimal effort it took :-) But if it clears out the dead subscribers, perhaps we should list addresses every 6 months ;-) Babette Turner-Underwood work: [EMAIL PROTECTED] home: [EMAIL PROTECTED] 954-3752 (Mon - Fri 7am - 3pm) -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of [EMAIL PROTECTED] Sent: 2004-01-28 12:25 PM To: Multiple recipients of list ORACLE-L Subject: RE: !!Please Read - Oracle-L is moving!! may i just say - this resource is worth the minimal effort it took -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] Behalf Of [EMAIL PROTECTED] Sent: Wednesday, January 28, 2004 12:09 PM To: Multiple recipients of list ORACLE-L Subject: RE: !!Please Read - Oracle-L is moving!! You sir, have obviously never done this. I have. :) Nor read my first post on the matter. No, it would not be easier, not by a long shot. This is free service, so my thinking is, share the workload. Jared [EMAIL PROTECTED] Sent by: [EMAIL PROTECTED] 01/27/2004 11:49 PM Please respond to ORACLE-L To:Multiple recipients of list ORACLE-L [EMAIL PROTECTED] cc: Subject:RE: !!Please Read - Oracle-L is moving!! Hi List Manger- Couldn't a immigration of our subscribed accounts been the most logical and error free option ? All this fire would have been avaoided. CSW Simon. __ Do you Yahoo!? Yahoo! SiteBuilder - Free web site building tool. Try it! http://webhosting.yahoo.com/ps/sb/ -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Rachel Carmichael INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: [EMAIL PROTECTED] INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Re: !!Please Read - Oracle-L is moving!!
I really do not understand all this topic. I just sent subscribe, reply to the message that I received and that's all. Oh, yes, I had to update the e-mail address in my favorites. Another minute down the drain. Jared has enough work to do, without having to what is a minimal effort from you. Yechiel Adar Mehish - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Thursday, January 22, 2004 8:49 AM Dear Oracle-L subscriber, Due to changing circumstances, the Oracle-L mailing list has found a new home. Fatcity.com has graciously hosted Oracle-L for several years, and I thank Bruce Bergman for his hard work on our behalf, but the time has come to move to a new host. Oracle-L will be hosted by freelists.org, effective immediately. In the past when this list has moved, I used a list of subscribers to automatically subscribe people to the new address, thinking I was providing a service. To avoid dealing with irate users that forgot they had subscribed (where does their mail go?) and their attorneys (don't care to hear from them again ) this new list will be 100% opt in. What this means is that you will need to subscribe to the new address if you wish to remain on the Oracle-L mailing list. This email is being sent to you once individually, and will also appear in the regular Oracle-L traffic. After a period of time (2 weeks or so) [EMAIL PROTECTED] will be shut down. At this time, I don't know for how long the Oracle-L archives at fatcity.com will be available. Instruction for subscribing to the new list are at the end of the message. I look forward to seeing you all at [EMAIL PROTECTED] Jared Still -- to subscribe: send email to [EMAIL PROTECTED] with 'subscribe' in the Subject field OR Subscribe via the web site - http://www.freelists.org/login.html To send email to the list, use this address: [EMAIL PROTECTED] You can unsubscribe from [EMAIL PROTECTED] by: send email to [EMAIL PROTECTED] with 'unsubscribe' in the Subject field OR Unsubscribe via the web site - http://www.freelists.org/login.html Documentation - http://www.freelists.org/help/. Searchable archives - http://www.freelists.org/archives/oracle-l -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Jared Still INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Yechiel Adar INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
[Q] sql loader problem while load record more than one line???
We are migrate from MS Access to ORACLE(9.2.0.4) use SQL*Loader. The problem we have are some of MS Access dump records (ASCII output) have to several lines(EOL before end of record). For example: control file: load data infile 'data.asc' into table test fields terminated by ','optionally enclosed by '' TRAILING NULLCOLS ( ID, comment ) Data.asc file: 101, September 1, 2003 - Labor Day November 27, 2003 - Thanksgiving Day November 28, 2003 - Day after Thanksgiving December 24, 2003 - Christmas Eve December 25, 2003 - Christmas Day January 1, 2004 - New Year's Day January 19, 2004 - Martin Luther Ki Does there has way to pass around? Thanks. __ Do you Yahoo!? Yahoo! SiteBuilder - Free web site building tool. Try it! http://webhosting.yahoo.com/ps/sb/ -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: dba1 mcc INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: [Q] sql loader problem while load record more than one line??
This is a hack but ... you might want to look into ftp-ing a file to Unix, run a 'tr' or 'sed' to get rid of the EOL character. Thanks, Nikhil -Original Message- Sent: Thursday, January 29, 2004 10:34 AM To: Multiple recipients of list ORACLE-L We are migrate from MS Access to ORACLE(9.2.0.4) use SQL*Loader. The problem we have are some of MS Access dump records (ASCII output) have to several lines(EOL before end of record). For example: control file: load data infile 'data.asc' into table test fields terminated by ','optionally enclosed by '' TRAILING NULLCOLS ( ID, comment ) Data.asc file: 101, September 1, 2003 - Labor Day November 27, 2003 - Thanksgiving Day November 28, 2003 - Day after Thanksgiving December 24, 2003 - Christmas Eve December 25, 2003 - Christmas Day January 1, 2004 - New Year's Day January 19, 2004 - Martin Luther Ki Does there has way to pass around? Thanks. __ Do you Yahoo!? Yahoo! SiteBuilder - Free web site building tool. Try it! http://webhosting.yahoo.com/ps/sb/ -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: dba1 mcc INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Nikhil Khimani INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Re: [Q] sql loader problem while load record more than one line???
So, why don't you use migration workbench, when it's available? On 01/29/2004 10:34:27 AM, dba1 mcc wrote: We are migrate from MS Access to ORACLE(9.2.0.4) use SQL*Loader. The problem we have are some of MS Access dump records (ASCII output) have to several lines(EOL before end of record). For example: control file: load data infile 'data.asc' into table test fields terminated by ','optionally enclosed by '' TRAILING NULLCOLS ( ID, comment ) Data.asc file: 101, September 1, 2003 - Labor Day November 27, 2003 - Thanksgiving Day November 28, 2003 - Day after Thanksgiving December 24, 2003 - Christmas Eve December 25, 2003 - Christmas Day January 1, 2004 - New Year's Day January 19, 2004 - Martin Luther Ki Does there has way to pass around? Thanks. __ Do you Yahoo!? Yahoo! SiteBuilder - Free web site building tool. Try it! http://webhosting.yahoo.com/ps/sb/ -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: dba1 mcc INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Mladen Gogala INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: measuring TPM
And these would be CPM (commits per minute) and RPM (rollbacks per minute). If you really want a transaction, you have to code it yourself, otherwise all you can get is CPM and RPM. If your transactions_per_minute ( or commits_per_minute) is low use this handy script to bump it up. create table my_dual as select * from dual / begin for i in 1 .. 1000 loop insert into my_dual values(i); commit; delete from my_dual; commit; end loop; end; / add salt and pepper to taste, serve with nice red wine, enjoy. Raj Rajendra dot Jamadagni at nospamespn dot com All Views expressed in this email are strictly personal. QOTD: Any clod can have facts, having an opinion is an art ! -Original Message- Mladen Gogala Sent: Wednesday, January 28, 2004 11:09 AM To: Multiple recipients of list ORACLE-L Well, as you are well aware of, you cannot measure without impacting. I know of the following methods: 1) Turn on auditing, count all transactions from dba_audit_trail table within a day and divide by the number of minutes in 9 hours. That will give you an average TPM number during the working hours. The problem is that auditing will impact the transaction rate. 2) Pick a single user, a chosen average Joe (or Josephine, to to avoid accusations for gender bias), create a logon trigger which will record user commits from v$sesstat and that will be the number of transactions. Divide by the number of minutes and multiply by the number of users on your system. The problem with this method is that it is usually very hard to pick up an average overall user of the system, so the whole thing is performed by department. 3) Count user commits in v$sysstat, which will count them system-wide. Divide by period. -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Jamadagni, Rajendra INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: [Q] sql loader problem while load record more than one line??
Thank you for answer. I did not said clearly. Most records are fine. ONly some records have this problem. re-transfer from PC to UNIX will not fix problem. --- Nikhil Khimani [EMAIL PROTECTED] wrote: This is a hack but ... you might want to look into ftp-ing a file to Unix, run a 'tr' or 'sed' to get rid of the EOL character. Thanks, Nikhil -Original Message- Sent: Thursday, January 29, 2004 10:34 AM To: Multiple recipients of list ORACLE-L We are migrate from MS Access to ORACLE(9.2.0.4) use SQL*Loader. The problem we have are some of MS Access dump records (ASCII output) have to several lines(EOL before end of record). For example: control file: load data infile 'data.asc' into table test fields terminated by ','optionally enclosed by '' TRAILING NULLCOLS ( ID, comment ) Data.asc file: 101, September 1, 2003 - Labor Day November 27, 2003 - Thanksgiving Day November 28, 2003 - Day after Thanksgiving December 24, 2003 - Christmas Eve December 25, 2003 - Christmas Day January 1, 2004 - New Year's Day January 19, 2004 - Martin Luther Ki Does there has way to pass around? Thanks. __ Do you Yahoo!? Yahoo! SiteBuilder - Free web site building tool. Try it! http://webhosting.yahoo.com/ps/sb/ -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: dba1 mcc INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Nikhil Khimani INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (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! SiteBuilder - Free web site building tool. Try it! http://webhosting.yahoo.com/ps/sb/ -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: dba1 mcc INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: What to look for in STATSPACK report
I still go to www.oraperf.com and it still greets me with the same interface and provides the same services that it did before Raj Rajendra dot Jamadagni at nospamespn dot com All Views expressed in this email are strictly personal. QOTD: Any clod can have facts, having an opinion is an art ! -Original Message- Hemant K Chitale Sent: Wednesday, January 28, 2004 9:59 AM To: Multiple recipients of list ORACLE-L There certainly _was_ a site called http://oraperf.veritas.com Hemant At 05:59 AM 27-01-04 -0800, you wrote: Anjo So what was the deal with oraperf.veritas.com if you don't mind my asking? I subscribed to it sometime before Christmas, but when I went to use it a week or so ago, it had disappeared and I had to (re)subscribe to www.oraperf.com. -- David Lord -Original Message- From: Anjo Kolk [mailto:[EMAIL PROTECTED] Sent: 27 January 2004 13:29 To: Multiple recipients of list ORACLE-L Subject: RE: What to look for in STATSPACK report No, the server is in my basement. Anjo. -Original Message- Rachel Carmichael Sent: Tuesday, January 27, 2004 11:44 AM To: Multiple recipients of list ORACLE-L that's pretty definitive. :) I did say retaining permanent ownership Is Veritas hosting it for you? --- Anjo Kolk [EMAIL PROTECTED] wrote: No, It is mine! Anjo. -Original Message- Rachel Carmichael Sent: Friday, January 23, 2004 11:49 AM To: Multiple recipients of list ORACLE-L well, I can't get to the site at the moment to test it.. if I remember correctly, Anjo said he had leased it to Veritas for a couple of years, while retaining permanent ownership. On the other hand, he's on this list, he can confirm or deny that himself! --- Mogens_Nxrgaard [EMAIL PROTECTED] wrote: Hi Tim, Are you sure it's still owned by Veritas? Doesn't look that way when I checked it just now. Mogens Tim Gorman wrote: Helmut, Register with http://www.oraperf.com; and run those STATSPACK reports through the YAPP analyzer, which will reformat them in such a way that they make sense. All of the ratio stuff on the STATSPACK report is ignored by the YAPP analyzer, and instead the reformatting looks at things from the standpoint of response-time analysis, as described in the white papers at http://www.oraperf.com/whitepapers.html;. Yes, I know OraPerf is now owned by Veritas and the real URLs are different, but it'll always be just good old oraperf.com hopefully, no matter who Anjo works for... :-) Hope this helps... -Tim on 1/18/04 11:24 PM, Daiminger, Helmut at [EMAIL PROTECTED] wrote: Hi! We want to introduce a performance monitoring policy here. We are using the STATSPACK utility. What are sections in statspack reports to look for? What are threshold numbers for these values? Does anybody have any power points or papers about it? This is 9.2 on HP-UX. Thanks, Helmut -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: =?ISO-8859-1?Q?Mogens_N=F8rgaard?= INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (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! SiteBuilder - Free web site building tool. Try it! http://webhosting.yahoo.com/ps/sb/ -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Rachel Carmichael INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ:
stored_outline issues
I have been trying to get stored outlines to work.It seems simple enough, but it is working the exact oppositeas I think it should. Anyone else use these? I am on 8.1.7.4 on HP-UX 11.00 In a nutshell, I verify my query is using the correct explain plan. I grant 'create any outline' to the users. Then, I do: alter system set use_stored_outlines = true ALTER SESSION SET CREATE_STORED_OUTLINES=true;select * from edi_monitor_vw;ALTER SESSION SET CREATE_STORED_OUTLINES=false; I can see the outline in DBA_OUTLINES and also in outln.ol$ If I open another session and run the query, it is NOT using the correct explain plan. If I 'alter system set use_stored_outlines = false;' then the query uses the correct plan. I also noticed that outline_category in v$sql is NULL. Does this mean that they outline is not being used? I assume not. Thanks all, John John Fedock "K" Line America, Inc. www.kline.com * [EMAIL PROTECTED]
RE: FW: pl/sql array processing?
There is no simple way, What is important for you ... value of the element or the index of the element in the pl/sql table? Looks like the index of the element is important for you. Tell us again what is the problem? is it that you have too many array elements? where does the time goes? it should be in the execution of the function .. not in navigating from one element to next. What version of oracle? Raj Rajendra dot Jamadagni at nospamespn dot com All Views expressed in this email are strictly personal. QOTD: Any clod can have facts, having an opinion is an art ! -Original Message- Guang Mei Sent: Tuesday, January 27, 2004 2:59 PM To: Multiple recipients of list ORACLE-L Sorry I did not make it clear that the number I used here (1, 9, 15,99) are just examples, the actual element index is a varible and they are not continuous. Yes, refTbl can be defined into a package. I guess what I am asking is if there is a way in pl/sql to do something like -- FORALL array element indexes (they are non-continuous) call a package function (parameter: element index) -- end for without looping the array. -- orginal code: declare type numTbl is table of number index by binary_integer; refTblnumTbl; i number; str varchar2(30); begin refTbl (1) := 1; refTbl (9) := 1; refTbl(15) := 1; refTbl(99) := 1; i := refTbl.first; while i is not null loop dbms_output.put_line ('i=' || i); str:= my_package.function(i); i := refTbl.next(i); end loop; end; / Guang -Original Message- Mladen Gogala Sent: Tuesday, January 27, 2004 2:29 PM To: Multiple recipients of list ORACLE-L On 01/27/2004 02:09:25 PM, Jesse, Rich wrote: Couldn't the declarations be put into a package? We've done this in order to maintain values for the life of the session. Yes, they could, I didn't see it in this example. -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Mladen Gogala INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Guang Mei INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Jamadagni, Rajendra INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Re: [Q] wait time /lob def
Jonathan / Tanel: I, however, AM having this problem. Didn't know where to look till I saw this message. (I love this list!) I've yet to capture the sid (and therefore track back to the table) where the 'direct path write' occurs. Definitions for the tables were supplied by the vendor. When I look at at the lobs, the definitions are mixed. Most are nocache, logging yes. some are no/no I don't see much on metalink about this -- just a couple of generic articles on lob storage. Should I change the lobs to cache/logging across the board? Thanks for any insight. Barb Table NameCach LOG --- CONTACTGROUP NO YES CONTENT NO YES IMAGEBINARY NO YES IMAGEHEADER NO YES IMAGEROW NO NO IMPORTIN NO NO TF_NEW_CONTENTNO YES XMLS_DICTIONARY NO YES XMLS_SALESSUPPORT NO NO XMLS_STYLEPARTYES YES --- Tanel Poder [EMAIL PROTECTED] wrote: Hi! Do you have any LOBs defined with the nocache nologging attribute ? This was what I initially thought would be the problem, but then I thought, in case of nocache lobs you should see direct path writes (lob) a lot... Tanel. -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Tanel Poder INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (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! SiteBuilder - Free web site building tool. Try it! http://webhosting.yahoo.com/ps/sb/ -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Barbara Baker INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Re: [Q] wait time /lob def
There is a note in one of the manuals about nologging lobs (or nocache lob, I forget which) that points out that the unrecoverable SCN for file that holds the LOB has to be updated in the control file whenever the LOB is updated. If you actually have a performance problem because of this - i.e. if lots of people/processes are running slowly because they are waiting on control file writes - then you might want to make the LOB cache/loggong. But control file writes are not inherently a bad thing to be blocked. Of course, if the LOBs are quite large, then the time taken to write the LOB may be far greater than the time taken to update the controlfile - which would make any concerns about the controlfile update irrelevant. So there is no 'obvious' correct answer to your question, without examing your current activity. The note (which I think Steve Adams' also has on his website) mentions an event that can be set to stop the controlfile update when the LOB is updated. This may not be a good idea, though, as it may affect some aspects of recoverability. If you do make the LOB 'cached', then remember that all reads and writes go through the db_block_buffer, which could affect all the other I/O activity adversely, so you might want to consider putting the LOBs into a tablespace with a non-standard block size so that the LOB activity doesn't affect the rest of the cache. (You do also have the option in more recent versions of refining the caching properties so the LOB can be readcache only, writecache only or read/write cache or nocache, I believe). Regards Jonathan Lewis http://www.jlcomp.demon.co.uk The educated person is not the person who can answer the questions, but the person who can question the answers -- T. Schick Jr Next public appearances: Jan 29th 2004 UKOUG Unix SIG - v$ and x$ March 2004 Hotsos Symposium - The Burden of Proof March 2004 Charlotte NC OUG - CBO Tutorial April 2004 Iceland One-day tutorials: http://www.jlcomp.demon.co.uk/tutorial.html Three-day seminar: see http://www.jlcomp.demon.co.uk/seminar.html UK___February UK___June The Co-operative Oracle Users' FAQ http://www.jlcomp.demon.co.uk/faq/ind_faq.html - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Thursday, January 29, 2004 5:04 PM Jonathan / Tanel: I, however, AM having this problem. Didn't know where to look till I saw this message. (I love this list!) I've yet to capture the sid (and therefore track back to the table) where the 'direct path write' occurs. Definitions for the tables were supplied by the vendor. When I look at at the lobs, the definitions are mixed. Most are nocache, logging yes. some are no/no I don't see much on metalink about this -- just a couple of generic articles on lob storage. Should I change the lobs to cache/logging across the board? Thanks for any insight. Barb -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Jonathan Lewis INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Re: [Q] sql loader problem while load record more than one line???
You may want to add CONTINUEIF clause and try the load again ... infile 'data.asc' CONTINUEIF NEXT (1) != '' into table test ... For complete reference, see this URL: http://download-west.oracle.com/docs/cd/B10501_01/server.920/a96652/ch05.htm#1005518 -- Thanks, Krishna ~~ NOTICE: This email message is for the sole use of the intended recipient(s) and may contain confidential and privileged information. Any unauthorized review, use, disclosure or distribution is prohibited. If you are not the intended recipient, please contact the sender by reply email and destroy all copies of the original message. ~~ dba1 mcc wrote: We are migrate from MS Access to ORACLE(9.2.0.4) use SQL*Loader. The problem we have are some of MS Access dump records (ASCII output) have to several lines(EOL before end of record). For example: control file: load data infile 'data.asc' into table test fields terminated by ','optionally enclosed by '' TRAILING NULLCOLS ( ID, comment ) Data.asc file: 101, September 1, 2003 - Labor Day November 27, 2003 - Thanksgiving Day November 28, 2003 - Day after Thanksgiving December 24, 2003 - Christmas Eve December 25, 2003 - Christmas Day January 1, 2004 - New Year's Day January 19, 2004 - Martin Luther Ki Does there has way to pass around? Thanks. -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Krishna Kakatur INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: [Fwd: ! READ THIS - Oracle-L@fatcity.com is shutting down as of
RIP old friend! Dick Goulet Senior Oracle DBA Oracle Certified 8i DBA -Original Message- Sent: Thursday, January 29, 2004 12:24 PM To: Multiple recipients of list ORACLE-L A repost of a previous message. This is the last day for this address folks. --- [EMAIL PROTECTED] is shutting down as of 1/31/2004 If you have already subscribed to the new location for Oracle-L, then you may discard this message. To those of you still reading: [EMAIL PROTECTED] will be offline as of 1/31/2004. You will not longer receive posts from Oracle-L as of 1/31/2004, and you will not be able to post to [EMAIL PROTECTED] as of 1/31/2004. The last day for this address is 1/30/2004. The new address is [EMAIL PROTECTED], and instructions for subscribing to the new address may be found at the following URL: http://www.cybcon.com/~jkstill/util/maillist/maillist.html Thanks for reading. Jared ( List Owner ) -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Jared Still INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Goulet, Dick INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
[Fwd: ! READ THIS - Oracle-L@fatcity.com is shutting down as of
A repost of a previous message. This is the last day for this address folks. --- [EMAIL PROTECTED] is shutting down as of 1/31/2004 If you have already subscribed to the new location for Oracle-L, then you may discard this message. To those of you still reading: [EMAIL PROTECTED] will be offline as of 1/31/2004. You will not longer receive posts from Oracle-L as of 1/31/2004, and you will not be able to post to [EMAIL PROTECTED] as of 1/31/2004. The last day for this address is 1/30/2004. The new address is [EMAIL PROTECTED], and instructions for subscribing to the new address may be found at the following URL: http://www.cybcon.com/~jkstill/util/maillist/maillist.html Thanks for reading. Jared ( List Owner ) -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Jared Still INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: UNION ALL Query: Riddle
If what you are describing is completely accurate, ( no DML, change S_A_S fixes the problem ) then it would appear you have encountered a bug. A search on MetaLink is in order, and failing that, you need to open a TAR. Jared On Thu, 2004-01-29 at 04:59, Wendry wrote: I have the same problem like you Rajesh, the query also gives different rowcount each time executed eventhough there's no one updating base tables, in my opinion it's because of the sorting operation (your group by clause). In my case after I remove some group functions, the result goes well. Also I reduce the use of order by clause where it's not needed. I still haven't found the exact solution to this problem. But just now I've tried to decrease the sort area size parameter value (I think I oversize it), and run the query again, the result goes stable with the problematic query but it runs slower. I haven't tried intensively, I try to do that tomorrow. Meanwhile if, there's any of the Gurus can give us clearer explanation, please do so... Thank you all in advance. Regards, Wendry. -Original Message- Pillai, Rajesh Sent: Thursday, January 29, 2004 2:24 AM To: Multiple recipients of list ORACLE-L Hi Jared, Thanks for your response. different results mean that number of records are different sometimes, and sometimes the some of the quantities are not correct. Your help is really appreciated. Thanks, Rajesh -Original Message- Sent: Tuesday, January 27, 2004 2:29 PM To: Multiple recipients of list ORACLE-L Q: What does different results mean? Different row count? Completely different data? Partially different data? Some columns have incorrect value? What about doing it without the parallel hints? The tables aren't so big that it would take a long time to find out. Jared Pillai, Rajesh [EMAIL PROTECTED] Sent by: [EMAIL PROTECTED] 01/27/2004 01:09 PM Please respond to ORACLE-L To:Multiple recipients of list ORACLE-L [EMAIL PROTECTED] cc: Subject:UNION ALL Query: Riddle Hi All, The following query is giving different results in each run. I assure that no data modified between consecutive runs - INSERT /* append parallel (z,8) */ INTO some_table (SELECT /*parallel (a,8) */ a.item, a.loc, SUM(a.qty_type_1), SUM(a.qty_type_2) FROM (select /*parallel (x,8) */ item, loc, qty_type_1, to_number(NULL) from table_a x UNION ALL select /*parallel (y,8) */ item, loc, to_number(NULL), qty_type_2 from table_b y ) a GROUP BY a.item, a.loc); Additional info - Number of records in table_a and table_b is around 3M and 6M. SQL select * from v$version; BANNER Oracle8i Enterprise Edition Release 8.1.7.2.0 - Production PL/SQL Release 8.1.7.2.0 - Production CORE8.1.7.0.0 Production TNS for Solaris: Version 8.1.7.2.0 - Production NLSRTL Version 3.4.1.0.0 - Production I would appreciate any help in solving this mystery and all hints are welcome. Thanks, Rajesh Pillai -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Pillai, Rajesh INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Jared Still INET: [EMAIL PROTECTED] Fat City
Re: merge command ???
Can't do it (until version 10) Regards Jonathan Lewis http://www.jlcomp.demon.co.uk The educated person is not the person who can answer the questions, but the person who can question the answers -- T. Schick Jr Next public appearances: Jan 29th 2004 UKOUG Unix SIG - v$ and x$ March 2004 Hotsos Symposium - The Burden of Proof March 2004 Charlotte NC OUG - CBO Tutorial April 2004 Iceland One-day tutorials: http://www.jlcomp.demon.co.uk/tutorial.html Three-day seminar: see http://www.jlcomp.demon.co.uk/seminar.html UK___February UK___June The Co-operative Oracle Users' FAQ http://www.jlcomp.demon.co.uk/faq/ind_faq.html - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Wednesday, January 28, 2004 4:54 PM Hi, for Merge command, how to specify if matched, not to do anything, I tried NULL, not working. Thank you! MERGE INTO caption c3 USING [EMAIL PROTECTED] c1 ON (c3.caption_id = c1.caption_id) WHEN MATCHED THEN NULL -- don't need to do anything when matched! WHEN NOT MATCHED THEN INSERT (c3.CAPTION_ID, c3.CAPTION_NAME, c3.VISIBILITY_ID, c3.MOD_DATE, c3.MOD_USER) VALUES (c1.CAPTION_ID, c1.CAPTION_NAME, c1.VISIBILITY_ID, c1.MOD_DATE, c1.MOD_USER); __ Do you Yahoo!? Yahoo! SiteBuilder - Free web site building tool. Try it! http://webhosting.yahoo.com/ps/sb/ -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Janet Linsy INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Jonathan Lewis INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Date Format: Mystery
Hi All, See the following - 1) select to_char(sysdate,'-MM-DD HH24:MI:SS AM') from dual; result = 2004-01-29 12:52:06 PM 2) select to_char(sysdate,'-MM-DD HH24:MI: AM') from dual; result = 2004-01-29 12:52:2020 PM 3) select to_char(sysdate,'-MM-DD HH24:MI:SS AM') from dual; ORA-01821: date format not recognized 4) select to_char(sysdate,'-MM-DD HH24:MI: AM') from dual; ORA-01821: date format not recognized 5) select to_char(sysdate,'-MM-DD HH24:MI:SS AM') from dual; result = 2004-01-29 12:53:4643946439 PM What does it mean? If I want to see the 10th part of the second or 100th part of the second, Is it poosible? I would appreciate all your hints. Thanks, Rajesh -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Pillai, Rajesh INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Re: [Q] wait time /lob def
That is an interesting use of an alternate block size Jonathan. Jonathan Lewis [EMAIL PROTECTED] Sent by: [EMAIL PROTECTED] 01/29/2004 09:59 AM Please respond to ORACLE-L To:Multiple recipients of list ORACLE-L [EMAIL PROTECTED] cc: Subject:Re: [Q] wait time /lob def There is a note in one of the manuals about nologging lobs (or nocache lob, I forget which) that points out that the unrecoverable SCN for file that holds the LOB has to be updated in the control file whenever the LOB is updated. If you actually have a performance problem because of this - i.e. if lots of people/processes are running slowly because they are waiting on control file writes - then you might want to make the LOB cache/loggong. But control file writes are not inherently a bad thing to be blocked. Of course, if the LOBs are quite large, then the time taken to write the LOB may be far greater than the time taken to update the controlfile - which would make any concerns about the controlfile update irrelevant. So there is no 'obvious' correct answer to your question, without examing your current activity. The note (which I think Steve Adams' also has on his website) mentions an event that can be set to stop the controlfile update when the LOB is updated. This may not be a good idea, though, as it may affect some aspects of recoverability. If you do make the LOB 'cached', then remember that all reads and writes go through the db_block_buffer, which could affect all the other I/O activity adversely, so you might want to consider putting the LOBs into a tablespace with a non-standard block size so that the LOB activity doesn't affect the rest of the cache. (You do also have the option in more recent versions of refining the caching properties so the LOB can be readcache only, writecache only or read/write cache or nocache, I believe). Regards Jonathan Lewis http://www.jlcomp.demon.co.uk
Starting Database from OEM 9.2 gives ORA-12500 Failed to start a dedicated ....
Hey all, Following is the environment. Windows 2000 Oracle Enterprise 9.2, OEM 9.2 I have two 9.2 database installed on my system. OEM is configured properly. One of the database is in automatic startmode and this DB contains OEM repository. I start the machine, open OEM, login to OEM and then try to start the other database, but it does not start the DB, instead I get ORA-12500 Failed to start a dedicated OEM doc says that with OEM, you can start/shutdown the database using OEM. Can someone point to me what I am doing wrong. I am using sys username/password for the startup of the second database. Thanks. -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Dharminder Softhome INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Re[2]: merge command ???
Hi, for Merge command, how to specify if matched, not to do anything, I tried NULL, not working. Thank you! MERGE INTO caption c3 USING [EMAIL PROTECTED] c1 ON (c3.caption_id = c1.caption_id) WHEN MATCHED THEN NULL -- don't need to do anything when matched! WHEN NOT MATCHED THEN INSERT (c3.CAPTION_ID, Forgive me for asking, but if you don't want to handle both cases, then why not just write an INSERT statement to begin with? Best regards, Jonathan Gennick --- Brighten the corner where you are http://Gennick.com * 906.387.1698 * mailto:[EMAIL PROTECTED] Join the Oracle-article list and receive one article on Oracle technologies per month by email. To join, visit http://four.pairlist.net/mailman/listinfo/oracle-article, or send email to [EMAIL PROTECTED] and include the word subscribe in either the subject or body. -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Jonathan Gennick INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Re: [Q] wait time /lob def
Jonathan, about which version are you talking here? (You do also have the option in more recent versions of refining the caching properties so the LOB can be readcache only, writecache only or read/write cache or nocache, I believe). I haven't found a way to explicitly set read or write caching for LOBs in system level, although I've done some research on them lately (on 10g as well). Or are you talking about OCI LOB caching here? Tanel. -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Tanel Poder INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Re: Date Format: Mystery
Rajesh, A look into the docs might help you: In date-format SS means seconds as of the seconds 00-59 in a minute. S means seconds since midnight, thus 0 - 86399 Compiling the statement the longest part is recocnized first. So: SS give 06 in your first example. gives 20, but twice: 2020 SS consists of the S part, followed by an unrecocnized single S: error consists of S, followed by SS, followed by an unrecognized S: error SS is S S, so the result is 46439 46439. Regards, Carel-Jan At 10:29 PM 1/29/2004, you wrote: Hi All, See the following - 1) select to_char(sysdate,'-MM-DD HH24:MI:SS AM') from dual; result = 2004-01-29 12:52:06 PM 2) select to_char(sysdate,'-MM-DD HH24:MI: AM') from dual; result = 2004-01-29 12:52:2020 PM 3) select to_char(sysdate,'-MM-DD HH24:MI:SS AM') from dual; ORA-01821: date format not recognized 4) select to_char(sysdate,'-MM-DD HH24:MI: AM') from dual; ORA-01821: date format not recognized 5) select to_char(sysdate,'-MM-DD HH24:MI:SS AM') from dual; result = 2004-01-29 12:53:4643946439 PM What does it mean? If I want to see the 10th part of the second or 100th part of the second, Is it poosible? I would appreciate all your hints. Thanks, Rajesh -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Pillai, Rajesh INET: [EMAIL PROTECTED] Fat City Network Services -- 858-538-5051 http://www.fatcity.com San Diego, California -- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). === If you think education is expensive, try ignorance. (Derek Bok) === DBA!ert, Independent Oracle Consultancy Kastanjelaan 61C 2743 BX Waddinxveen The Netherlands tel. +31 (0) 182 640 428 fax +31 (0) 182 640 429 mobile+31 (0) 653 911 950 e-mail [EMAIL PROTECTED]
RE: Date Format: Mystery
Carel-Jan, This explanation helps. Thanks a lot. Could you also answer if displaying centiseconds or milliseconds or 1/10th of a second in oracle is possible or not Thanks, Rajesh -Original Message-From: Carel-Jan Engel [mailto:[EMAIL PROTECTED]Sent: Thursday, January 29, 2004 4:29 PMTo: Multiple recipients of list ORACLE-LSubject: Re: Date Format: MysteryRajesh,A look into the docs might help you:In date-format SS means seconds as of the seconds 00-59 in a minute.S means seconds since midnight, thus 0 - 86399Compiling the statement the longest part is recocnized first.So:SS give 06 in your first example. gives 20, but twice: 2020SS consists of the S part, followed by an unrecocnized single S: error consists of S, followed by SS, followed by an unrecognized S: errorSS is S S, so the result is 46439 46439.Regards, Carel-JanAt 10:29 PM 1/29/2004, you wrote: Hi All,See the following - 1) select to_char(sysdate,'-MM-DD HH24:MI:SS AM') from dual;result = 2004-01-29 12:52:06 PM2) select to_char(sysdate,'-MM-DD HH24:MI: AM') from dual;result = 2004-01-29 12:52:2020 PM3) select to_char(sysdate,'-MM-DD HH24:MI:SS AM') from dual;ORA-01821: date format not recognized4) select to_char(sysdate,'-MM-DD HH24:MI: AM') from dual;ORA-01821: date format not recognized5) select to_char(sysdate,'-MM-DD HH24:MI:SS AM') from dual;result = 2004-01-29 12:53:4643946439 PMWhat does it mean? If I want to see the 10th part of the second or 100th part of the second, Is it poosible?I would appreciate all your hints.Thanks,Rajesh-- Please see the official ORACLE-L FAQ: http://www.orafaq.net-- Author: Pillai, Rajesh INET: [EMAIL PROTECTED]Fat City Network Services -- 858-538-5051 http://www.fatcity.comSan Diego, California -- Mailing list and web hosting services-To REMOVE yourself from this mailing list, send an E-Mail messageto: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and inthe message BODY, include a line containing: UNSUB ORACLE-L(or the name of mailing list you want to be removed from). You mayalso send the HELP command for other information (like subscribing). ===If you think education is expensive, try ignorance. (Derek Bok)===DBA!ert, Independent Oracle Consultancy Kastanjelaan 61C2743 BX WaddinxveenThe Netherlandstel. +31 (0) 182 640 428fax +31 (0) 182 640 429mobile+31 (0) 653 911 950e-mail [EMAIL PROTECTED]
Re: [Q] wait time /lob def
Comments below. - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Thursday, January 29, 2004 7:04 PM Jonathan / Tanel: I, however, AM having this problem. Didn't know where to look till I saw this message. (I love this list!) So do I ;) I've yet to capture the sid (and therefore track back to the table) where the 'direct path write' occurs. Definitions for the tables were supplied by the vendor. When I look at at the lobs, the definitions are mixed. Most are nocache, logging yes. some are no/no I don't see much on metalink about this -- just a couple of generic articles on lob storage. If you are up to attending RMOUG Training days in few weeks, then I'll be speaking about LOB performance tuning there, you're most welcome ;) Should I change the lobs to cache/logging across the board? Well, if you set all your LOBs to CACHE (cached LOBs are always logged), then you'll be generating more redo for current nocache nologging LOBs as well. Also, your buffer cache gets hit, especially if your LOBs are big. One possibility is setting event 10359 at level 1 to what Jonathan also referred, then your direct writes won't cause controlfile updates anymore. But when you have set this event, you can't rely on UNRECOVERABLE_CHANGE# in your V$DATAFILE anymore, for determining whether a datafile should be backed up due nologging operations in it. If you should ever need to restore a file containing NOLOGGING NOCACHE LOBs, then any inconsistent blocks will be marked corrupt (block sqn=0xFF) since there was no redo information logged for them. In that case you have update the corrupt LOBs to nulls for example to reclaim the space in LOB segment. Note that having few corrupt LOB items in a LOB segment won't affect other, healthy LOB items, you just have to have some kind of error handling mechanism implemented when you hit a corrupt item. Note that updates to LOB indexes are always cached and logged, despite the NOLOGGING setting. Tanel. Thanks for any insight. Barb Table NameCach LOG --- CONTACTGROUP NO YES CONTENT NO YES IMAGEBINARY NO YES IMAGEHEADER NO YES IMAGEROW NO NO IMPORTIN NO NO TF_NEW_CONTENTNO YES XMLS_DICTIONARY NO YES XMLS_SALESSUPPORT NO NO XMLS_STYLEPARTYES YES --- Tanel Poder [EMAIL PROTECTED] wrote: Hi! Do you have any LOBs defined with the nocache nologging attribute ? This was what I initially thought would be the problem, but then I thought, in case of nocache lobs you should see direct path writes (lob) a lot... Tanel. -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Tanel Poder INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (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! SiteBuilder - Free web site building tool. Try it! http://webhosting.yahoo.com/ps/sb/ -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Barbara Baker INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Tanel Poder INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Re: [Q] wait time /lob def
Thanks, Tanel. Yes, I'll be at RMOUG. I'll be in the front row for your presentation. Again, thanks for the info. Barb --- Tanel Poder [EMAIL PROTECTED] wrote: Comments below. - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Thursday, January 29, 2004 7:04 PM Jonathan / Tanel: I, however, AM having this problem. Didn't know where to look till I saw this message. (I love this list!) So do I ;) I've yet to capture the sid (and therefore track back to the table) where the 'direct path write' occurs. Definitions for the tables were supplied by the vendor. When I look at at the lobs, the definitions are mixed. Most are nocache, logging yes. some are no/no I don't see much on metalink about this -- just a couple of generic articles on lob storage. If you are up to attending RMOUG Training days in few weeks, then I'll be speaking about LOB performance tuning there, you're most welcome ;) Should I change the lobs to cache/logging across the board? Well, if you set all your LOBs to CACHE (cached LOBs are always logged), then you'll be generating more redo for current nocache nologging LOBs as well. Also, your buffer cache gets hit, especially if your LOBs are big. One possibility is setting event 10359 at level 1 to what Jonathan also referred, then your direct writes won't cause controlfile updates anymore. But when you have set this event, you can't rely on UNRECOVERABLE_CHANGE# in your V$DATAFILE anymore, for determining whether a datafile should be backed up due nologging operations in it. If you should ever need to restore a file containing NOLOGGING NOCACHE LOBs, then any inconsistent blocks will be marked corrupt (block sqn=0xFF) since there was no redo information logged for them. In that case you have update the corrupt LOBs to nulls for example to reclaim the space in LOB segment. Note that having few corrupt LOB items in a LOB segment won't affect other, healthy LOB items, you just have to have some kind of error handling mechanism implemented when you hit a corrupt item. Note that updates to LOB indexes are always cached and logged, despite the NOLOGGING setting. Tanel. Thanks for any insight. Barb Table NameCach LOG --- CONTACTGROUP NO YES CONTENT NO YES IMAGEBINARY NO YES IMAGEHEADER NO YES IMAGEROW NO NO IMPORTIN NO NO TF_NEW_CONTENTNO YES XMLS_DICTIONARY NO YES XMLS_SALESSUPPORT NO NO XMLS_STYLEPARTYES YES --- Tanel Poder [EMAIL PROTECTED] wrote: Hi! Do you have any LOBs defined with the nocache nologging attribute ? This was what I initially thought would be the problem, but then I thought, in case of nocache lobs you should see direct path writes (lob) a lot... Tanel. -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Tanel Poder INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (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! SiteBuilder - Free web site building tool. Try it! http://webhosting.yahoo.com/ps/sb/ -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Barbara Baker INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Tanel Poder INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT