RE: UTL_FILE question
Title: Message I would create a new version of the procedure that took the date from and to as parameters and output a file named appropriately for the date range. You would then just call this 24 times. Niall -Original Message-From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of ViktorSent: 31 January 2004 00:24To: Multiple recipients of list ORACLE-LSubject: UTL_FILE question Hello, Ihave a procedure that open a cursor anddumps some data for last 24 monthsto a file. The date range is static condition definedinside the cursor. Procedure is working great, but Iwhat I'm havingproblemfiguring outisif there is a way to create not one file with all the data, but multiple files with monthly sets of data. This way data formonth1 is created as filename_012002.txt and so forth. Thanks much! Viktor Do you Yahoo!?Yahoo! SiteBuilder - Free web site building tool. Try it!
SV: Date Format: Mystery
Title: Meddelelse Rajesh, SYSDATE is of datatype DATE (that's what the documentation says), i.e. it contains century, year, month, day, hour, minute and second (without decimals). I have made a little test. FirstIdump a SYSDATE to see the internal representation. Then Icreate a table with a SYSDATE anddump it. SQL select to_char(sysdate, '.MM.DD HH24:MI:SS'), dump(sysdate) 2 from dual; TO_CHAR(SYSDATE,'YYYDUMP(SYSDATE)--2004.01.30 08:13:16Typ=13 Len=8: 7,212,1,30,8,13,16,0 SQL create table testdate as 2 select sysdate d 3 from dual; Table created. SQL select to_char(d, '.MM.DD HH24:MI:SS'), dump(d) 2 from testdate; TO_CHAR(D,'.MM.DDUMP(D)--2004.01.30 08:13:16Typ=12 Len=7: 120,104,1,30,9,14,17 Certainly som conversion is going on here. This might be the reason why there has been confusion about 7 or 8 bytes in a DATE datatype. To answer your question, it is possible to deal with fractions of seconds, byt you need to use theTIMESTAMP datatype. The function SYSTIMESTAMP could be useful. Be aware that Oracle supports 9 decimals, but not all hardware platforms do. Therefore the results might be inaccurate. SQL select to_char(systimestamp, '.MM.DD HH24:MI:SS.S'), dump(systimestamp) 2 from dual; TO_CHAR(SYSTIMESTAMP,'.MM-DUMP(SYSTIMESTAMP)--2004.01.30 08:13:16.295961616Typ=188 Len=20: 7,212,1,30,7,13,16,0,6,14,35,240,1,0,5,46,0,0,32,32 Regards Jesper Haure Nørrevang -Oprindelig meddelelse-Fra: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] På vegne af Pillai, RajeshSendt: 30. januar 2004 01:59Til: Multiple recipients of list ORACLE-LEmne: 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
Tanel, Oracle 9-2 SQL Ref manual. Lob storage clause. The options for lobs on creation are: CACHE NO CACHE CACHE READS I don't think the last one appeared until 9.2 I was wrong about caching only writes, though - one of the joys of trying to quote everything from memory. The CACHE READS option means that the LOB goes into the buffer cache for reads, but not for writes. Pity, really, because I'd quite like to see it the other way around. 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: Friday, January 30, 2004 12:19 AM 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: 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] wait time /lob def
It's just one example of my general suggestion that messing about with block sizes rarely has any direct performance benefit. But if you can put something out of the way where it can't do so much damage then the performance of everything else might benefit. 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 11:04 PM That is an interesting use of an alternate block size Jonathan. -- 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] wait time /lob def
Hi Jonathan, I don't think the last one appeared until 9.2 I was wrong about caching only writes, though - one of the joys of trying to quote everything from memory. Thank you for this note, I had somehow missed this important improvement. The CACHE READS option means that the LOB goes into the buffer cache for reads, but not for writes. Pity, really, because I'd quite like to see it the other way around. It guess it has to do with the kernel cache layer, that it's probably not feasible to start modifying cache layer code the way it wouldn't log any changes? Anyway, some testing needs to be done now :) Actually CACHE READS is exactly what I need for a current project, the incoming LOB feed is too high to have them logging, but the app can somewhat benefit from LOB caching in buffer cache. I relieved write performance problem by using OCI LOB caching and batch writes for incoming content feed (and the app is gonna run on a dedicated write-cache enabled Hitachi 9980V as well). Thanks, Tanel. 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: Friday, January 30, 2004 12:19 AM 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: 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). -- 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).
SV: Date Format: Mystery
Title: Meddelelse Sorry! I was mixingformat-masks. The last SQL should read (i.e. instead of ): select to_char(systimestamp, '.MM.DD HH24:MI:SS.'), dump(systimestamp) from dual; /Jesper -Oprindelig meddelelse-Fra: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] På vegne af Jesper Haure NorrevangSendt: 30. januar 2004 08:24Til: Multiple recipients of list ORACLE-LEmne: SV: Date Format: Mystery Rajesh, SYSDATE is of datatype DATE (that's what the documentation says), i.e. it contains century, year, month, day, hour, minute and second (without decimals). I have made a little test. FirstIdump a SYSDATE to see the internal representation. Then Icreate a table with a SYSDATE anddump it. SQL select to_char(sysdate, '.MM.DD HH24:MI:SS'), dump(sysdate) 2 from dual; TO_CHAR(SYSDATE,'YYYDUMP(SYSDATE)--2004.01.30 08:13:16Typ=13 Len=8: 7,212,1,30,8,13,16,0 SQL create table testdate as 2 select sysdate d 3 from dual; Table created. SQL select to_char(d, '.MM.DD HH24:MI:SS'), dump(d) 2 from testdate; TO_CHAR(D,'.MM.DDUMP(D)--2004.01.30 08:13:16Typ=12 Len=7: 120,104,1,30,9,14,17 Certainly som conversion is going on here. This might be the reason why there has been confusion about 7 or 8 bytes in a DATE datatype. To answer your question, it is possible to deal with fractions of seconds, byt you need to use theTIMESTAMP datatype. The function SYSTIMESTAMP could be useful. Be aware that Oracle supports 9 decimals, but not all hardware platforms do. Therefore the results might be inaccurate. SQL select to_char(systimestamp, '.MM.DD HH24:MI:SS.S'), dump(systimestamp) 2 from dual; TO_CHAR(SYSTIMESTAMP,'.MM-DUMP(SYSTIMESTAMP)--2004.01.30 08:13:16.295961616Typ=188 Len=20: 7,212,1,30,7,13,16,0,6,14,35,240,1,0,5,46,0,0,32,32 Regards Jesper Haure Nørrevang -Oprindelig meddelelse-Fra: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] På vegne af Pillai, RajeshSendt: 30. januar 2004 01:59Til: Multiple recipients of list ORACLE-LEmne: 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
Re: Re: [Q] wait time /lob def
i apologize if someone posted this answer(i missed it if you did), but why do cached lobs require writes on the control file? and jonathan said that isnt 'necessarily' bad. is that because there isnt alot of I/O? so caching lobs are primarily useful for read only or read 'mostly' LOBs? From: Jonathan Lewis [EMAIL PROTECTED] Date: 2004/01/30 Fri AM 04:09:25 EST To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Subject: Re: [Q] wait time /lob def Tanel, Oracle 9-2 SQL Ref manual. Lob storage clause. The options for lobs on creation are: CACHE NO CACHE CACHE READS I don't think the last one appeared until 9.2 I was wrong about caching only writes, though - one of the joys of trying to quote everything from memory. The CACHE READS option means that the LOB goes into the buffer cache for reads, but not for writes. Pity, really, because I'd quite like to see it the other way around. 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: Friday, January 30, 2004 12:19 AM 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: 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). -- 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: Re: [Q] wait time /lob def
Hi! i apologize if someone posted this answer(i missed it if you did), but why do cached lobs require writes on the control file? NOCACHE NOLOGGING LOBs require writes to controlfile, because last nologging operation to a datafile has to be reflected somewhere, for being able to determine which files need to be backed up (since recovery information is not logged for nologging operations). Another option would be to use NOCACHE LOGGING operations, that way no controlfile updates are needed, but I do not recommend it, because NOCACHE LOGGING LOBs require the writing of the whole LOB segment block to redo, even if you modify just one byte in it. CACHE LOGGING is more efficient way in sense of redo generation, that way only changed bytes will be recorded. and jonathan said that isnt 'necessarily' bad. is that because there isnt alot of I/O? If you don't spend too much time waiting on these events, then yes it's not bad at all. so caching lobs are primarily useful for read only or read 'mostly' LOBs? They are useful in many cases, for write LOBs as well in some cases, but the problem is that they always generate redo, which might not be acceptable in high data load environments. IMO, if you can use a combination of disk array with write cache, asynch IO, LOB buffering in client side and write batching, then NOCACHE NOLOGGING LOBs can meet acceptable performance constraints and if you use CACHE READ NOLOGGING LOBs (which seem to be available even from 8.1.6), then your performance will be just fine. Of course, there is always cases with special requirements/contstraints, so YMMAFV (your mileage may, and will vary). Tanel. - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Friday, January 30, 2004 2:49 PM From: Jonathan Lewis [EMAIL PROTECTED] Date: 2004/01/30 Fri AM 04:09:25 EST To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Subject: Re: [Q] wait time /lob def Tanel, Oracle 9-2 SQL Ref manual. Lob storage clause. The options for lobs on creation are: CACHE NO CACHE CACHE READS I don't think the last one appeared until 9.2 I was wrong about caching only writes, though - one of the joys of trying to quote everything from memory. The CACHE READS option means that the LOB goes into the buffer cache for reads, but not for writes. Pity, really, because I'd quite like to see it the other way around. 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: Friday, January 30, 2004 12:19 AM 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: 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). -- 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
Re: SV: Date Format: Mystery
Friday, January 30, 2004, 2:24:25 AM, Jesper Haure Norrevang ([EMAIL PROTECTED]) wrote: JHN Certainly som conversion is going on here. This might be the reason why JHN there has been confusion about 7 or 8 bytes in a DATE datatype. That's really interesting, that switch between 7 and 8 bytes. Oracle's docs, I believe in the OCI manual, do show a seven-byte format. JHN To answer your question, it is possible to deal with fractions of seconds, JHN byt you need to use the TIMESTAMP datatype. The function SYSTIMESTAMP could JHN be useful. Be aware that Oracle supports 9 decimals, but not all hardware JHN platforms do. Related to this, just because a platform returns, say, six digits, does not mean it increments on that last digit. Instead of: 21.01 seconds 21.02 seconds ... The best your platform does might look like: 21.01 seconds 21.000801 seconds 21.001601 seconds ... I just made these numbers up, but hopefully they give the idea. I'm still curious about that seven versus eight byte thing with SYSDATE. 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: Starting Database from OEM 9.2 gives ORA-12500 Failed to start a
Go to the SERVICES and start the service. Dharminder SofthomeTo: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] dharminder cc: @softhome.net Subject: Starting Database from OEM 9.2 gives ORA-12500 Failed to start a dedicated Sent by: ml-errors 01/29/2004 06:14 PM Please respond to ORACLE-L 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). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Thomas Day 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).
Trigger question
Hallo, I would like to write the code to atrigger that makes insert into another table inly if there is the condition PREFERED_NUMBER = 1 and TELEPHONE_TYPE_ID in (1,2, 5) How can write this in a simple way? Thanks in advance. Roland -- 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 list you want to be removed from). You may also send the HELP command for other information (like subscribing).
dba_jobs nls_env
How does dba_jobs decide what it's nls_env values are?. They seem to vary according to whether I queue the job from a windows client or from unix. John -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: John Dunn 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: SV: Date Format: Mystery
Hi, From Note: 69028.1 on Metalink The datatype returned is 13 and not 12, the external DATE datatype. This occurs because we rely on the TO_DATE function! External datatype 13 is an internal c-structure whose length varies depending on how the c-compiler represents the structure. Note that the Len= value is 8 and not 7. Type 13 is not a part of the published 3GL interfaces for Oracle and is used for date calculations mainly within PL/SQL operations. Note that the same result can be seen when DUMPing the value SYSDATE. Garry Gillies Database Administrator Business Systems Weir Pumps Ltd 149 Newlands Road, Cathcart, Glasgow, G44 4EX T: +44 0141 308 3982 F: +44 0141 633 1147 E: [EMAIL PROTECTED] Jonathan Gennick [EMAIL PROTECTED]To: Multiple recipients of list ORACLE-L .com [EMAIL PROTECTED] Sent by: cc: [EMAIL PROTECTED]Subject: Re: SV: Date Format: Mystery .com 30/01/04 13:44 Please respond to ORACLE-L Friday, January 30, 2004, 2:24:25 AM, Jesper Haure Norrevang ([EMAIL PROTECTED]) wrote: JHN Certainly som conversion is going on here. This might be the reason why JHN there has been confusion about 7 or 8 bytes in a DATE datatype. That's really interesting, that switch between 7 and 8 bytes. Oracle's docs, I believe in the OCI manual, do show a seven-byte format. JHN To answer your question, it is possible to deal with fractions of seconds, JHN byt you need to use the TIMESTAMP datatype. The function SYSTIMESTAMP could JHN be useful. Be aware that Oracle supports 9 decimals, but not all hardware JHN platforms do. Related to this, just because a platform returns, say, six digits, does not mean it increments on that last digit. Instead of: 21.01 seconds 21.02 seconds ... The best your platform does might look like: 21.01 seconds 21.000801 seconds 21.001601 seconds ... I just made these numbers up, but hopefully they give the idea. I'm still curious about that seven versus eight byte thing with SYSDATE. 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). CONFIDENTIAL: The information contained in this email (including any attachments) is confidential, subject to copyright and for the use of the intended recipient only. If you are not the intended recipient please delete this message after notifying the sender. Unauthorised retention, alteration or distribution of this email is forbidden and may be actionable. Attachments are opened at your own risk and you are advised to scan incoming email for viruses before opening any attached files. We give no guarantee that any communication is virus-free and accept no responsibility for virus contamination or other system loss or damage
Re: Trigger question
On 01/30/2004 09:29:26 AM, [EMAIL PROTECTED] wrote: Hallo, I would like to write the code to atrigger that makes insert into another table inly if there is the condition Oracle9i Application Developer's Guide - Fundamentals, Ch. 15. - Using Triggers. There are examples there, too. -- 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: Starting Database from OEM 9.2 gives ORA-12500 Failed to start a
Thomas, That is the real problem. I can start the database using Windows 2000 OracleServiceSID. But I want to start up the database using OEM only. Thanks. -Original Message- Thomas Day Sent: Friday, January 30, 2004 9:19 AM To: Multiple recipients of list ORACLE-L start a Go to the SERVICES and start the service. Dharminder SofthomeTo: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] dharminder cc: @softhome.net Subject: Starting Database from OEM 9.2 gives ORA-12500 Failed to start a dedicated Sent by: ml-errors 01/29/2004 06:14 PM Please respond to ORACLE-L 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). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Thomas Day 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: 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: Starting Database from OEM 9.2 gives ORA-12500 Failed to star
Dharminder, Changes the service to AutoStart when the machine reboots. As Thomas said, the NT service has to be running for you to be able to start the database. The service will stay running even if you shut the database down. Think of the service as a backgrouond placeholder for the database. It needs to be running for the database to run, but you can still shut the database down as normal and keep the background service running. hope this helps. Tom Mercadante Oracle Certified Professional -Original Message- Sent: Friday, January 30, 2004 10:34 AM To: Multiple recipients of list ORACLE-L start a Thomas, That is the real problem. I can start the database using Windows 2000 OracleServiceSID. But I want to start up the database using OEM only. Thanks. -Original Message- Thomas Day Sent: Friday, January 30, 2004 9:19 AM To: Multiple recipients of list ORACLE-L start a Go to the SERVICES and start the service. Dharminder SofthomeTo: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] dharminder cc: @softhome.net Subject: Starting Database from OEM 9.2 gives ORA-12500 Failed to start a dedicated Sent by: ml-errors 01/29/2004 06:14 PM Please respond to ORACLE-L 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). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Thomas Day 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: 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). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Mercadante, Thomas F 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
Jared Still scribbled on the wall in glitter crayon: A repost of a previous message. This is the last day for this address folks. good-bye fatcity my old friend, won't get to talk to you again... -- Bill Shrek Thater ORACLE DBA I'm going to work my ticket if I can... -- Gilwell song [EMAIL PROTECTED] This planet has -- or rather had -- a problem, which was this: most of the people living on it were unhappy for pretty much of the time. Many solutions were suggested for this problem, but most of these were largely concerned with the movements of small green pieces of paper, which is odd because on the whole it wasn't the small green pieces of paper that were unhappy. -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Thater, William 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
last post(its kinda like first post on slashdot.org) joe Thater, William wrote: Jared Still scribbled on the wall in glitter crayon: A repost of a previous message. This is the last day for this address folks. good-bye fatcity my old friend, won't get to talk to you again... -- Bill Shrek Thater ORACLE DBA I'm going to work my ticket if I can... -- Gilwell song [EMAIL PROTECTED] This planet has -- or rather had -- a problem, which was this: most of the people living on it were unhappy for pretty much of the time. Many solutions were suggested for this problem, but most of these were largely concerned with the movements of small green pieces of paper, which is odd because on the whole it wasn't the small green pieces of paper that were unhappy. -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Joe Testa 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
So, is anyone posting on the new list? I believe that I'm subscribed there but I'm only getting fatcity posts. -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Thomas Day 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
Jared, I think you should ask the maintainers of orafaq.com to update their information about oracle-l as well, it's the first hit in google when you search for oracle-l. Tanel. - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Friday, January 30, 2004 5:44 PM Jared Still scribbled on the wall in glitter crayon: A repost of a previous message. This is the last day for this address folks. good-bye fatcity my old friend, won't get to talk to you again... -- Bill Shrek Thater ORACLE DBA I'm going to work my ticket if I can... -- Gilwell song [EMAIL PROTECTED] This planet has -- or rather had -- a problem, which was this: most of the people living on it were unhappy for pretty much of the time. Many solutions were suggested for this problem, but most of these were largely concerned with the movements of small green pieces of paper, which is odd because on the whole it wasn't the small green pieces of paper that were unhappy. -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Thater, William 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).
Changes to RULE based optimizer between Oracle8 and 9i
We move an application that uses OPTIMIZER_MODE=RULE from Oracle8 to 9i. Most of it is fine, but there are two queries that have a very different execution plan. In one case, the execution time increases from less than a minute to more than an hour. Neither query uses any of the new Oracle 9i features. My understanding is that the Rule optimizer code has not changed, except to account for new features like IOT's. Has anyone else seen this type of behavior? Keith MooreOracle Certified Professional972-431-5126[EMAIL PROTECTED] The information transmitted is intended only for the person or entity to which it is addressed and may contain confidential and/or privileged material. If the reader of this message is not the intended recipient, you are hereby notified that your access is unauthorized, and any review, dissemination, distribution or copying of this message including any attachments is strictly prohibited. If you are not the intended recipient, please contact the sender and delete the material from any computer.
RE: Changes to RULE based optimizer between Oracle8 and 9i
Title: Message Keith, To my knowledge, RBO has not changed. That includes NOT dealing w/ IOT. So, I think if you execute a query that references an IOT, you'll implicitly invoke the CBO. That would explain why the plan is changing. So, did you change a table from heap to IOT when you moved to 9i? If so, that's what's invoking CBO. -Mark PS fatcity is going away, use freelists.org! Mark J. Bobak Oracle DBA ProQuest Company Ann Arbor, MI "Imagination was given to man to compensate him for what he is not, and a sense of humor was provided to console him for what he is." --Horace Walpole -Original Message-From: Keith Moore [mailto:[EMAIL PROTECTED] Sent: Friday, January 30, 2004 11:54 AMTo: Multiple recipients of list ORACLE-LSubject: Changes to RULE based optimizer between Oracle8 and 9i We move an application that uses OPTIMIZER_MODE=RULE from Oracle8 to 9i. Most of it is fine, but there are two queries that have a very different execution plan. In one case, the execution time increases from less than a minute to more than an hour. Neither query uses any of the new Oracle 9i features. My understanding is that the Rule optimizer code has not changed, except to account for new features like IOT's. Has anyone else seen this type of behavior? Keith MooreOracle Certified Professional972-431-5126[EMAIL PROTECTED]
Re: [Fwd: ! READ THIS - Oracle-L@fatcity.com is shutting down as
Thanks Tanel, Frank is on top of it already. Jared Tanel Poder [EMAIL PROTECTED] Sent by: [EMAIL PROTECTED] 01/30/2004 08:24 AM Please respond to ORACLE-L To:Multiple recipients of list ORACLE-L [EMAIL PROTECTED] cc: Subject:Re: [Fwd: ! READ THIS - [EMAIL PROTECTED] is shutting down as Jared, I think you should ask the maintainers of orafaq.com to update their information about oracle-l as well, it's the first hit in google when you search for oracle-l. Tanel. - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Friday, January 30, 2004 5:44 PM Jared Still scribbled on the wall in glitter crayon: A repost of a previous message. This is the last day for this address folks. good-bye fatcity my old friend, won't get to talk to you again... -- Bill Shrek Thater ORACLE DBA I'm going to work my ticket if I can... -- Gilwell song [EMAIL PROTECTED] This planet has -- or rather had -- a problem, which was this: most of the people living on it were unhappy for pretty much of the time. Many solutions were suggested for this problem, but most of these were largely concerned with the movements of small green pieces of paper, which is odd because on the whole it wasn't the small green pieces of paper that were unhappy. -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Thater, William 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: Starting Database from OEM 9.2 gives ORA-12500 Failed to star
The Windows service implements Oracle's VOS ( virtual OS ) This is not noticed on *versions, as there is no separate step. See James Morle's book 'Scaling Oracle 8i'. Jared Mercadante, Thomas F [EMAIL PROTECTED] Sent by: [EMAIL PROTECTED] 01/30/2004 07:44 AM Please respond to ORACLE-L To:Multiple recipients of list ORACLE-L [EMAIL PROTECTED] cc: Subject:RE: Starting Database from OEM 9.2 gives ORA-12500 Failed to star Dharminder, Changes the service to AutoStart when the machine reboots. As Thomas said, the NT service has to be running for you to be able to start the database. The service will stay running even if you shut the database down. Think of the service as a backgrouond placeholder for the database. It needs to be running for the database to run, but you can still shut the database down as normal and keep the background service running. hope this helps. Tom Mercadante Oracle Certified Professional -Original Message- Sent: Friday, January 30, 2004 10:34 AM To: Multiple recipients of list ORACLE-L start a Thomas, That is the real problem. I can start the database using Windows 2000 OracleServiceSID. But I want to start up the database using OEM only. Thanks. -Original Message- Thomas Day Sent: Friday, January 30, 2004 9:19 AM To: Multiple recipients of list ORACLE-L start a Go to the SERVICES and start the service. Dharminder SofthomeTo: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] dharminder cc: @softhome.net Subject: Starting Database from OEM 9.2 gives ORA-12500 Failed to start a dedicated Sent by: ml-errors 01/29/2004 06:14 PM Please respond to ORACLE-L 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). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Thomas Day 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: 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). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Mercadante, Thomas F 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
Re: Starting Database from OEM 9.2 gives ORA-12500 Failed to start a dedicated ....
There is not enough memory to start a dedicated connection. sochange the server mode from dedicated to shared, (you can do it change some parameters in init_sid.ora file see oracle documentation). If you are going to change the server mode to shared and you have two databaseon the same machine, thenyou have toassign one listener port for each database. hope this helps Mauricio Vélez Oracle DBA Dharminder Softhome [EMAIL PROTECTED] wrote: Hey all,Following is the environment.Windows 2000Oracle Enterprise 9.2,OEM 9.2I 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 OEMrepository.I start the machine, open OEM, login to OEM and then try to start the otherdatabase, but it does not start the DB, instead I getORA-12500 Failed to start a dedicatedOEM 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 sysusername/password for the startup of the second database.Thanks.-- Please see the official ORACLE-L FAQ: http://www.orafaq.net-- Author: Dharminder SofthomeINET: [EMAIL PROTECTED]Fat City Network Services -- 858-538-5051 http://www.fatcity.comSan Diego, California -- Mailing list and web hosting services-To REMOVEE 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). Do you Yahoo!? Yahoo! SiteBuilder - Free web site building tool. Try it!
RE: Changes to RULE based optimizer between Oracle8 and 9i
Logic says one thing, experience says another. Question one, are the tables analyzed? If so, since RBO is deprecated in 9i even setting OPTIMIZER_MODE=RULE will invoke the CBO. Dick GouletSenior Oracle DBAOracle Certified 8i DBA -Original Message-From: Keith Moore [mailto:[EMAIL PROTECTED]Sent: Friday, January 30, 2004 11:54 AMTo: Multiple recipients of list ORACLE-LSubject: Changes to RULE based optimizer between Oracle8 and 9i We move an application that uses OPTIMIZER_MODE=RULE from Oracle8 to 9i. Most of it is fine, but there are two queries that have a very different execution plan. In one case, the execution time increases from less than a minute to more than an hour. Neither query uses any of the new Oracle 9i features. My understanding is that the Rule optimizer code has not changed, except to account for new features like IOT's. Has anyone else seen this type of behavior? Keith MooreOracle Certified Professional972-431-5126[EMAIL PROTECTED]
** v$log.status
Hi, In view v$log there is a column status. This changes from current (if the redo log is in use) to atcive then to inactive. Documentation says : ACTIVE: The log is active but is not the current log. It is needed for crash recovery. It may be in use for block recovery. It might or might not be archived. INACTIVE: The log is no longer needed for instance recovery. It may be in use for media recovery. It might or might not be archived. So if it is in 'ACTIVE' status: it means it is needed for crash/instance recovery? So what does it mean? That changes in this log are still not written to data files? What else? Is this related to delayed block cleanout etcor no connection? What factor affects how long it will bein ACTIVE state before going to INACTIVE? What can be changed to control how long it takes. Any trade off? Any detailed explanation on this will begreatly appreciated. Youcan mail me direct or to the list. Thank you. Do you Yahoo!? Yahoo! SiteBuilder - Free web site building tool. Try it!
Re: ** v$log.status
On 01/30/2004 01:24:26 PM, A Joshi wrote: Hi, In view v$log there is a column status. This changes from current (if the redo log is in use) to atcive then to inactive. Documentation says : ACTIVE: The log is active but is not the current log. It is needed for crash recovery. It may be in use for block recovery. It might or might not be archived. INACTIVE: The log is no longer needed for instance recovery. It may be in use for media recovery. It might or might not be archived. So if it is in 'ACTIVE' status : it means it is needed for crash/instance recovery? So what does it mean? That changes in this log are still not written to data files? What else? Is this related to delayed block cleanout etc or no connection? What factor affects how long it will be in ACTIVE state before going to INACTIVE? What can be changed to control how long it takes. Any trade off? Any detailed explanation on this will be greatly appreciated. You can mail me direct or to the list. Thank you. Status of active means that log has been switched, but not all of the changes are written to the disk. It is, essentially, saying that DBWR has some more work to do to catch up. The only log that is open by the instance is the one marked CURRENT. Here is an example: SQL select group#,status from v$log; GROUP# STATUS -- 1 CURRENT 2 INACTIVE 3 INACTIVE SQL alter system switch logfile; System altered. SQL select group#,status from v$log; GROUP# STATUS -- 1 ACTIVE 2 CURRENT 3 INACTIVE So, after the checkpoint, the CKPT has updated log file headers, corresponding markers and termination records are written to the file, data file headers have been updated and so has been the control file. DBWR still has some work to do because there are still unwritten blocks modified by transactions whose log records are in the ACTIVE file. DBWR will take its time to write them down. The only log file open by the instance is redo02: [EMAIL PROTECTED] root]# fuser /data/db/OraHome1/oradata/compldb/*.log /data/db/OraHome1/oradata/compldb/redo02.log: 6019 [EMAIL PROTECTED] root]# After another checkpoint, everyhing is back to normal: SQL alter system checkpoint 2 / System altered. SQL select group#,status from v$log; GROUP# STATUS -- 1 INACTIVE 2 CURRENT 3 INACTIVE -- 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: Starting Database from OEM 9.2 gives ORA-12500 Failed to start a dedicated ....
Mauricio I do not think it is a memory issue because I can startup the database using Windows 2000 OracleServiceSID service and it does not fail. Thanks. -Original Message-From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]On Behalf Of Mauricio VilezSent: Friday, January 30, 2004 12:39 PMTo: Multiple recipients of list ORACLE-LSubject: Re: Starting Database from OEM 9.2 gives ORA-12500 Failed to start a dedicated There is not enough memory to start a dedicated connection. sochange the server mode from dedicated to shared, (you can do it change some parameters in init_sid.ora file see oracle documentation). If you are going to change the server mode to shared and you have two databaseon the same machine, thenyou have toassign one listener port for each database. hope this helps Mauricio Vilez Oracle DBA Dharminder Softhome [EMAIL PROTECTED] wrote: Hey all,Following is the environment.Windows 2000Oracle Enterprise 9.2,OEM 9.2I 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 OEMrepository.I start the machine, open OEM, login to OEM and then try to start the otherdatabase, but it does not start the DB, instead I getORA-12500 Failed to start a dedicatedOEM 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 sysusername/password for the startup of the second database.Thanks.-- Please see the official ORACLE-L FAQ: http://www.orafaq.net-- Author: Dharminder SofthomeINET: [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). Do you Yahoo!?Yahoo! SiteBuilder - Free web site building tool. Try it!
RE: Starting Database from OEM 9.2 gives ORA-12500 Failed to star
Tom, As stated earlier, here the intention is to startup the database using OEM only and without using windows service. As for as I understand if the OracleServiceSID is properly setup and if startmode for the service is set to automatic then it will startup the database automatically once you boot the machine. Thanks. -Original Message- Mercadante, Thomas F Sent: Friday, January 30, 2004 10:44 AM To: Multiple recipients of list ORACLE-L star Dharminder, Changes the service to AutoStart when the machine reboots. As Thomas said, the NT service has to be running for you to be able to start the database. The service will stay running even if you shut the database down. Think of the service as a backgrouond placeholder for the database. It needs to be running for the database to run, but you can still shut the database down as normal and keep the background service running. hope this helps. Tom Mercadante Oracle Certified Professional -Original Message- Sent: Friday, January 30, 2004 10:34 AM To: Multiple recipients of list ORACLE-L start a Thomas, That is the real problem. I can start the database using Windows 2000 OracleServiceSID. But I want to start up the database using OEM only. Thanks. -Original Message- Thomas Day Sent: Friday, January 30, 2004 9:19 AM To: Multiple recipients of list ORACLE-L start a Go to the SERVICES and start the service. Dharminder SofthomeTo: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] dharminder cc: @softhome.net Subject: Starting Database from OEM 9.2 gives ORA-12500 Failed to start a dedicated Sent by: ml-errors 01/29/2004 06:14 PM Please respond to ORACLE-L 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). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Thomas Day 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: 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). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Mercadante, Thomas F 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
RE: Starting Database from OEM 9.2 gives ORA-12500 Failed to star
That's true. Dharminder SofthomeTo: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] dharminder cc: @softhome.net Subject: RE: Starting Database from OEM 9.2 gives ORA-12500 Failed to star Sent by: ml-errors 01/30/2004 02:19 PM Please respond to ORACLE-L Tom, As stated earlier, here the intention is to startup the database using OEM only and without using windows service. As for as I understand if the OracleServiceSID is properly setup and if startmode for the service is set to automatic then it will startup the database automatically once you boot the machine. Thanks. -Original Message- Mercadante, Thomas F Sent: Friday, January 30, 2004 10:44 AM To: Multiple recipients of list ORACLE-L star Dharminder, Changes the service to AutoStart when the machine reboots. As Thomas said, the NT service has to be running for you to be able to start the database. The service will stay running even if you shut the database down. Think of the service as a backgrouond placeholder for the database. It needs to be running for the database to run, but you can still shut the database down as normal and keep the background service running. hope this helps. Tom Mercadante Oracle Certified Professional -Original Message- Sent: Friday, January 30, 2004 10:34 AM To: Multiple recipients of list ORACLE-L start a Thomas, That is the real problem. I can start the database using Windows 2000 OracleServiceSID. But I want to start up the database using OEM only. Thanks. -Original Message- Thomas Day Sent: Friday, January 30, 2004 9:19 AM To: Multiple recipients of list ORACLE-L start a Go to the SERVICES and start the service. Dharminder SofthomeTo: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] dharminder cc: @softhome.net Subject: Starting Database from OEM 9.2 gives ORA-12500 Failed to start a dedicated Sent by: ml-errors 01/29/2004 06:14 PM Please respond to ORACLE-L 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
Re: ** v$log.status
There is a pretty good explanation in this usenet thread: http://groups.google.com/groups?hl=enlr=ie=UTF-8oe=UTF-8threadm=OoSWa.122%246d2.89%40newssvr22.news.prodigy.comrnum=1prev=/groups%3Fq%3Dv%2524log.status%26hl%3Den%26lr%3D%26ie%3DUTF-8%26oe%3DUTF-8%26selm%3DOoSWa.122%25246d2.89%2540newssvr22.news.prodigy.com%26rnum%3D1 Regards, Dave [EMAIL PROTECTED] wrote: Hi, In view v$log there is a column status. This changes from current (if the redo log is in use) to atcive then to inactive. Documentation says : # ACTIVE: The log is active but is not the current log. It is needed for crash recovery. It may be in use for block recovery. It might or might not be archived. # INACTIVE: The log is no longer needed for instance recovery. It may be in use for media recovery. It might or might not be archived. So if it is in 'ACTIVE' status : it means it is needed for crash/instance recovery? So what does it mean? That changes in this log are still not written to data files? What else? Is this related to delayed block cleanout etc or no connection? What factor affects how long it will be in ACTIVE state before going to INACTIVE? What can be changed to control how long it takes. Any trade off? Any detailed explanation on this will be greatly appreciated. You can mail me direct or to the list. Thank you. Do you Yahoo!? Yahoo! SiteBuilder - Free web site building tool. Try it! http://us.rd.yahoo.com/evt=21608/*http://webhosting.yahoo.com/ps/sb/ -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: David Hau 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: Starting Database from OEM 9.2 gives ORA-12500 Failed to star
Well, you can't. Windows is different from other systems in that you have these services that have to be running in the background. Once the services are running, you can use OEM to shutdown and startup the database. But the service has to be running first. Sorry. Have you looked at OEM Jobs? Maybe you can create an OEM job to start the service. The DOS command is: NET START OracleServiceSID and NET STOP OracleServiceSID Good Luck! Tom Mercadante Oracle Certified Professional -Original Message- Sent: Friday, January 30, 2004 2:20 PM To: Multiple recipients of list ORACLE-L star Tom, As stated earlier, here the intention is to startup the database using OEM only and without using windows service. As for as I understand if the OracleServiceSID is properly setup and if startmode for the service is set to automatic then it will startup the database automatically once you boot the machine. Thanks. -Original Message- Mercadante, Thomas F Sent: Friday, January 30, 2004 10:44 AM To: Multiple recipients of list ORACLE-L star Dharminder, Changes the service to AutoStart when the machine reboots. As Thomas said, the NT service has to be running for you to be able to start the database. The service will stay running even if you shut the database down. Think of the service as a backgrouond placeholder for the database. It needs to be running for the database to run, but you can still shut the database down as normal and keep the background service running. hope this helps. Tom Mercadante Oracle Certified Professional -Original Message- Sent: Friday, January 30, 2004 10:34 AM To: Multiple recipients of list ORACLE-L start a Thomas, That is the real problem. I can start the database using Windows 2000 OracleServiceSID. But I want to start up the database using OEM only. Thanks. -Original Message- Thomas Day Sent: Friday, January 30, 2004 9:19 AM To: Multiple recipients of list ORACLE-L start a Go to the SERVICES and start the service. Dharminder SofthomeTo: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] dharminder cc: @softhome.net Subject: Starting Database from OEM 9.2 gives ORA-12500 Failed to start a dedicated Sent by: ml-errors 01/29/2004 06:14 PM Please respond to ORACLE-L 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). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Thomas Day 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: 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
RE: Starting Database from OEM 9.2 gives ORA-12500 Failed to star
Dharminder, OracleServiceSID starts the database automatically, because by default the registry key ORA_SID_AUTOSTART IN HKEY_LOCAL_MACHINE\SOFTWARE\ORACLE\HOME0 is set to TRUE. If you don't want your OracleServiceSID to start the database automatically, change the value to FALSE. This way service will be still running, but you should be able to startup the database from OEM. Igor Neyman, OCP DBA [EMAIL PROTECTED] -Original Message- Dharminder Softhome Sent: Friday, January 30, 2004 2:20 PM To: Multiple recipients of list ORACLE-L star Tom, As stated earlier, here the intention is to startup the database using OEM only and without using windows service. As for as I understand if the OracleServiceSID is properly setup and if startmode for the service is set to automatic then it will startup the database automatically once you boot the machine. Thanks. -Original Message- Mercadante, Thomas F Sent: Friday, January 30, 2004 10:44 AM To: Multiple recipients of list ORACLE-L star Dharminder, Changes the service to AutoStart when the machine reboots. As Thomas said, the NT service has to be running for you to be able to start the database. The service will stay running even if you shut the database down. Think of the service as a backgrouond placeholder for the database. It needs to be running for the database to run, but you can still shut the database down as normal and keep the background service running. hope this helps. Tom Mercadante Oracle Certified Professional -Original Message- Sent: Friday, January 30, 2004 10:34 AM To: Multiple recipients of list ORACLE-L start a Thomas, That is the real problem. I can start the database using Windows 2000 OracleServiceSID. But I want to start up the database using OEM only. Thanks. -Original Message- Thomas Day Sent: Friday, January 30, 2004 9:19 AM To: Multiple recipients of list ORACLE-L start a Go to the SERVICES and start the service. Dharminder SofthomeTo: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] dharminder cc: @softhome.net Subject: Starting Database from OEM 9.2 gives ORA-12500 Failed to start a dedicated Sent by: ml-errors 01/29/2004 06:14 PM Please respond to ORACLE-L 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). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Thomas Day 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: 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
Re: ** v$log.status
Mladen, Thanks for info. So all the dirty blocks need to be written to disk after eachcheckpoint. After that is donethe status becomes 'INACTIVE'. Just that sometimes this is very unpredictable. My question : Ifa log switch always causes a implicit checkpoint then what is the need for this explicit checkpoint to be given? Thank you.Mladen Gogala [EMAIL PROTECTED] wrote: On 01/30/2004 01:24:26 PM, A Joshi wrote: Hi, In view v$log there is a column status. This changes from current (if the redo log is in use) to atcive then to inactive. Documentation says : ACTIVE: The log is active but is not the current log. It is needed for crash recovery. It may be in use for block recovery. It might or might not be archived. INACTIVE: The log is no longer needed for instance recovery. It may be in use for media recovery. It might or might not be archived. So if it is in 'ACTIVE' status : it means it is needed for crash/instance recovery? So what does it mean? That changes in this log are still not written to data files? What else? Is this related to delayed block cleanout etc or no connection? What factor affects how long it will be in ACTIVE state before going to INACTIVE? What can be changed to control how long it takes. Any trade off? Any detailed explanation on this will be greatly appreciated. You can mail me direct or to the list. Thank you.Status of active means that log has been switched, but not all of the changes are written to the disk. It is, essentially, saying that DBWRhas some more work to do to catch up. The only log that is open by theinstance is the one marked "CURRENT". Here is an example:SQL select group#,status from v$log;GROUP# STATUS-- 1 CURRENT2 INACTIVE3 INACTIVESQL alter system switch logfile;System altered.SQL select group#,status from v$log;GROUP# STATUS-- 1 ACTIVE2 CURRENT3 INACTIVESo, after the check! point, the CKPT has updated log file headers, corresponding markers and termination records are written to the file,data file headers have been updated and so has been the control file.DBWR still has some work to do because there are still unwritten blocks modified by transactions whose log records are in the "ACTIVE"file. DBWR will take its time to write them down.The only log file open by the instance is redo02:[EMAIL PROTECTED] root]# fuser /data/db/OraHome1/oradata/compldb/*.log/data/db/OraHome1/oradata/compldb/redo02.log: 6019[EMAIL PROTECTED] root]#After another checkpoint, everyhing is back to normal:SQL alter system checkpoint2 /System altered.SQL select group#,status from v$log;GROUP# STATUS-- 1 INACTIVE2 CURRENT3 INACTIVE-- Please see the official ORACLE-L FAQ: http://www.orafaq.net-- Author: Mladen GogalaINET: [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). Do you Yahoo!? Yahoo! SiteBuilder - Free web site building tool. Try it!
RE: Starting Database from OEM 9.2 gives ORA-12500 Failed to star
-Original Message- Igor Neyman OracleServiceSID starts the database automatically, because by default the registry key ORA_SID_AUTOSTART IN HKEY_LOCAL_MACHINE\SOFTWARE\ORACLE\HOME0 is set to TRUE. If you don't want your OracleServiceSID to start the database automatically, change the value to FALSE. This way service will be still running, but you should be able to startup the database from OEM. Igor Neyman, OCP DBA [EMAIL PROTECTED] Are you sure about that? This is the way I thought it worked with Oracle 8.1 and 9.2 (I just tried it again today using Oracle 8.1.7 on Windows 2000) If in the services control panel the database service has Startup Type Automatic, then the registry entry you mention will have ORA_sid_AUTOSTART TRUE, and when the machine is rebooted, the service will start up AND the instance will be started up. If you want to startup the instance manually on system reboot, you should set the Startup Type for the service to Manual (either in the Services control panel or the registry). Then once the machine is rebooted, you can 1) go to the Services control panel and start the Service, which will start the service and the instance OR 2) At a command prompt type in set ORACLE_SID=instance_name oradim -startup -sid %ORACLE_SID% which will start the service and the instance OR 3) At a command prompt type in set ORACLE_SID=instance_name oradim -startup -sid %ORACLE_SID% -starttype srvc which will start the service, then oradim -startup -sid %ORACLE_SID% -starttype inst which will start the instance OR 4) At a command prompt type in set ORACLE_SID=instance_name oradim -startup -sid %ORACLE_SID% -starttype srvc which will start the service, then sqlplus /nolog connect sys/password as sysdba startup which will start the instance I personally use method 4. -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Jacques Kilchoer 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: ** v$log.status
David Hau explained this much better and in much more clear fashion then me. Here is the most important part from the usenet thread he was referring you to: ** This makes sense if you think about where the various v$ dynamic performance views get their info from, and which Oracle background process is responsible for each task. First, note that: 1. v$log.status gets its redo log info from the *control file* 2. v$datafile_header.checkpoint_change# and checkpoint_time get their info from the *datafile headers*. Here's the sequence of events when a log switch happens: 1. LGWR switches to the next redo log file, changes the status of the previous redo log file from CURRENT to ACTIVE in the control file, and signals DBWR to do a checkpoint on the previous redo log file. 2. When DBWR finishes with the checkpoint, it signals CKPT to update datafile headers and update checkpoint info (only) in the control file. This is the info read by v$datafile_header.checkpoint_change# and checkpoint_time. Note that CKPT does not update redo log info in the control file. It only deals with checkpoint info, as its name implies. 3. When CKPT is done, it signals LGWR to update the redo log status in the control file from ACTIVE to INACTIVE. This is the info read by v$log.status. This update task is a low priority item for LGWR because the only process that cares about whether the redo log status is active or not is LGWR itself. The redo log status tells LGWR whether it can reuse a redo log file or not (i.e. whether checkpoint has completed on that redo log file.) That is, by delaying this operation, LGWR is not blocking the work of any other process. LGWR will update the redo log status in the control file when any of these occurs (and others too, that I don't know of): 1. when LGWR periodically checks for compliance with the LOG_CHECKPOINT_TIMEOUT parameter, which says that the checkpoint position should not lag behind the latest redo record by this amount of time. 2. when you issue a alter system checkpoint which is what you did. So if you want the redo log status to be updated more quickly to inactive after a checkpoint, one way to do it is to decrease the value of LOG_CHECKPOINT_TIMEOUT in init.ora. Cheers, Dave ** On 01/30/2004 03:44:29 PM, A Joshi wrote: Mladen, Thanks for info. So all the dirty blocks need to be written to disk after each checkpoint. After that is done the status becomes 'INACTIVE'. Just that sometimes this is very unpredictable. My question : If a log switch always causes a implicit checkpoint then what is the need for this explicit checkpoint to be given? Thank you. Mladen Gogala [EMAIL PROTECTED] wrote: On 01/30/2004 01:24:26 PM, A Joshi wrote: Hi, In view v$log there is a column status. This changes from current (if the redo log is in use) to atcive then to inactive. Documentation says : ACTIVE: The log is active but is not the current log. It is needed for crash recovery. It may be in use for block recovery. It might or might not be archived. INACTIVE: The log is no longer needed for instance recovery. It may be in use for media recovery. It might or might not be archived. So if it is in 'ACTIVE' status : it means it is needed for crash/instance recovery? So what does it mean? That changes in this log are still not written to data files? What else? Is this related to delayed block cleanout etc or no connection? What factor affects how long it will be in ACTIVE state before going to INACTIVE? What can be changed to control how long it takes. Any trade off? Any detailed explanation on this will be greatly appreciated. You can mail me direct or to the list. Thank you. Status of active means that log has been switched, but not all of the changes are written to the disk. It is, essentially, saying that DBWR has some more work to do to catch up. The only log that is open by the instance is the one marked CURRENT. Here is an example: SQL select group#,status from v$log; GROUP# STATUS -- 1 CURRENT 2 INACTIVE 3 INACTIVE SQL alter system switch logfile; System altered. SQL select group#,status from v$log; GROUP# STATUS -- 1 ACTIVE 2 CURRENT 3 INACTIVE So, after the checkpoint, the CKPT has updated log file headers, corresponding markers and termination records are written to the file, data file headers have been updated and so has been the control file. DBWR still has some work to do because there are still unwritten blocks modified by transactions whose log records are in the ACTIVE file. DBWR will take its time to write them down. The only log file open by the instance is redo02: [EMAIL PROTECTED] root]# fuser /data/db/OraHome1/oradata/compldb/*.log /data/db/OraHome1/oradata/compldb/redo02.log: 6019 [EMAIL PROTECTED] root]# After
RE: Starting Database from OEM 9.2 gives ORA-12500 Failed to star
Igor, What you have mentioned is right i.e. if I set ORA_SID_AUTOSTART to FALSE for the database in question and set OraceServiceSID startmode to Automatic then as windows starts it starts the OracleSID service (or I should say background services needed for the database to run and I believe I need to study about this more), but not the database. And then from OEM now I can startup the database. My impression was that OEM should be able to do all these tasks itself. Thanks to all others who have replied as well. -Original Message- Igor Neyman Sent: Friday, January 30, 2004 3:04 PM To: Multiple recipients of list ORACLE-L star Dharminder, OracleServiceSID starts the database automatically, because by default the registry key ORA_SID_AUTOSTART IN HKEY_LOCAL_MACHINE\SOFTWARE\ORACLE\HOME0 is set to TRUE. If you don't want your OracleServiceSID to start the database automatically, change the value to FALSE. This way service will be still running, but you should be able to startup the database from OEM. Igor Neyman, OCP DBA [EMAIL PROTECTED] -Original Message- Dharminder Softhome Sent: Friday, January 30, 2004 2:20 PM To: Multiple recipients of list ORACLE-L star Tom, As stated earlier, here the intention is to startup the database using OEM only and without using windows service. As for as I understand if the OracleServiceSID is properly setup and if startmode for the service is set to automatic then it will startup the database automatically once you boot the machine. Thanks. -Original Message- Mercadante, Thomas F Sent: Friday, January 30, 2004 10:44 AM To: Multiple recipients of list ORACLE-L star Dharminder, Changes the service to AutoStart when the machine reboots. As Thomas said, the NT service has to be running for you to be able to start the database. The service will stay running even if you shut the database down. Think of the service as a backgrouond placeholder for the database. It needs to be running for the database to run, but you can still shut the database down as normal and keep the background service running. hope this helps. Tom Mercadante Oracle Certified Professional -Original Message- Sent: Friday, January 30, 2004 10:34 AM To: Multiple recipients of list ORACLE-L start a Thomas, That is the real problem. I can start the database using Windows 2000 OracleServiceSID. But I want to start up the database using OEM only. Thanks. -Original Message- Thomas Day Sent: Friday, January 30, 2004 9:19 AM To: Multiple recipients of list ORACLE-L start a Go to the SERVICES and start the service. Dharminder SofthomeTo: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] dharminder cc: @softhome.net Subject: Starting Database from OEM 9.2 gives ORA-12500 Failed to start a dedicated Sent by: ml-errors 01/29/2004 06:14 PM Please respond to ORACLE-L 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). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Thomas Day 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
RE: Starting Database from OEM 9.2 gives ORA-12500 Failed to star
Yes, I'm sure. If in the services control panel you change OracleServiceSID starup properties, it does not affect ORA_SID_AUTOSTART key in HKEY_LOCAL_MACHINE\SOFTWARE\ORACLE\HOME0 (and does not change the way the database is started when the service is started). What it does, it changes the value of Start key in HKEY_LOCAL_MACHINE\CurrentControlSet\Services\OracleServiceSID (and the way service is started when the machine is rebooted - Automatic/Manual). So, if you have Start key in HKEY_LOCAL_MACHINE\CurrentControlSet\Services\OracleServiceSID set to 2 (which means automatic) and you have ORA_SID_AUTOSTART key in HKEY_LOCAL_MACHINE\SOFTWARE\ORACLE\HOME0 set to FALSE, then on reboot OracleServiceSID will start automatically, but the database will not startup. So, then (whenever you ready) you can do: set oracle_sid=instance_name sqlplus /nolog connect sys/password as sysdba startup which will startup the database. Igor Neyman, OCP DBA [EMAIL PROTECTED] -Original Message- Jacques Kilchoer Sent: Friday, January 30, 2004 4:04 PM To: Multiple recipients of list ORACLE-L star -Original Message- Igor Neyman OracleServiceSID starts the database automatically, because by default the registry key ORA_SID_AUTOSTART IN HKEY_LOCAL_MACHINE\SOFTWARE\ORACLE\HOME0 is set to TRUE. If you don't want your OracleServiceSID to start the database automatically, change the value to FALSE. This way service will be still running, but you should be able to startup the database from OEM. Igor Neyman, OCP DBA [EMAIL PROTECTED] Are you sure about that? This is the way I thought it worked with Oracle 8.1 and 9.2 (I just tried it again today using Oracle 8.1.7 on Windows 2000) If in the services control panel the database service has Startup Type Automatic, then the registry entry you mention will have ORA_sid_AUTOSTART TRUE, and when the machine is rebooted, the service will start up AND the instance will be started up. If you want to startup the instance manually on system reboot, you should set the Startup Type for the service to Manual (either in the Services control panel or the registry). Then once the machine is rebooted, you can 1) go to the Services control panel and start the Service, which will start the service and the instance OR 2) At a command prompt type in set ORACLE_SID=instance_name oradim -startup -sid %ORACLE_SID% which will start the service and the instance OR 3) At a command prompt type in set ORACLE_SID=instance_name oradim -startup -sid %ORACLE_SID% -starttype srvc which will start the service, then oradim -startup -sid %ORACLE_SID% -starttype inst which will start the instance OR 4) At a command prompt type in set ORACLE_SID=instance_name oradim -startup -sid %ORACLE_SID% -starttype srvc which will start the service, then sqlplus /nolog connect sys/password as sysdba startup which will start the instance I personally use method 4. -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Jacques Kilchoer 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: Igor Neyman 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: ** v$log.status
Thanks Mladen and David
It is great to get such detailed info.
If we take your example of three redo logs then you could have a situation where one is is 'CURRENT' status and two are in 'ACTIVE' status. As follows :
GROUP# STATUS-- 1 ACTIVE2 CURRENT3 ACTIVE
Now if group 2 ('CURRENT') fills up or if 'alter system switch logfile' command is given then it will try to go to the next one. Since the next one (in fact both) is 'ACTIVE' it cannot be allocated and made current.Right?So in that case will the database hang?Will it wait for the next sequenceredo log which is group 3 to get 'INACTIVE' or will itallocate group 1 if it becomes 'INACTIVE' before group 3.
Basically given limited space is it advisable to have many smaller redo log groups instead of Oracle suggested bigger redo logs which switch once an hour.
I agree it is unlikely and might as well just get extra space but the point is to put in the best possible setup and be ready for crazy activityinstead of just going by the 'thumb rule' of having one log switch per hour.
Thank youagain
Mladen Gogala [EMAIL PROTECTED] wrote:
David Hau explained this much better and in much more clearfashion then me. Here is the most important part from the usenetthread he was referring you to:**This makes sense if you think about where the various v$ dynamic performanceviews get their info from, and which Oracle background process isresponsible for each task. First, note that:1. v$log.status gets its redo log info from the *control file*2. v$datafile_header.checkpoint_change# and checkpoint_time get their infofrom the *datafile headers*.Here's the sequence of events when a log switch happens:1. LGWR switches to the next redo log file, changes the status of theprevious redo log file from CURRENT to ACTIVE in the control file, andsignals DBWR to do a checkpoint on the previous !
redo log
file.2. When DBWR finishes with the checkpoint, it signals CKPT to updatedatafile headers and update checkpoint info (only) in the control file.This is the info read by v$datafile_header.checkpoint_change# andcheckpoint_time. Note that CKPT does not update redo log info in thecontrol file. It only deals with checkpoint info, as its name implies.3. When CKPT is done, it signals LGWR to update the redo log status in thecontrol file from ACTIVE to INACTIVE. This is the info read byv$log.status. This update task is a low priority item for LGWR because theonly process that cares about whether the redo log status is active or notis LGWR itself. The redo log status tells LGWR whether it can reuse a redolog file or not (i.e. whether checkpoint has completed on that redo logfile.) That is, by delaying this operation, LGWR is not blocking the workof any other process.LGWR will update the redo log status i!
n the
control file when any of theseoccurs (and others too, that I don't know of):1. when LGWR periodically checks for compliance with theLOG_CHECKPOINT_TIMEOUT parameter, which says that the checkpoint positionshould not lag behind the latest redo record by this amount of time.2. when you issue a "alter system checkpoint" which is what you did.So if you want the redo log status to be updated more quickly to inactiveafter a checkpoint, one way to do it is to decrease the value ofLOG_CHECKPOINT_TIMEOUT in init.ora.Cheers,Dave**On 01/30/2004 03:44:29 PM, A Joshi wrote: Mladen, Thanks for info. So all the dirty blocks need to be written to disk after each checkpoint. After that is done the status becomes 'INACTIVE'. Just that sometimes this is very unpredictable. My question : If a log switch alway!
s causes
a implicit checkpoint then what is the need for this explicit checkpoint to be given? Thank you. Mladen Gogala <[EMAIL PROTECTED]>wrote: On 01/30/2004 01:24:26 PM, A Joshi wrote: Hi, In view v$log there is a column status. This changes from current (if the redo log is in use) to atcive then to inactive. Documentation says :ACTIVE: The log is active but is not the current log. It is needed for crash recovery. It may be in use for block recovery. It might or might not be archived. INACTIVE: The log is no longer needed for instance recovery. It may be in use for media recovery. It might or might not be archived. So if it is in 'ACTIVE' status : it means it is needed for crash/instance recover!
y? So
what does it mean? That changes in this log are still not written to data files? What else? Is this related to delayed block cleanout etc or no connection? What factor affects how long it will be in ACTIVE state before going to INACTIVE? What can be changed to control how long it takes. Any trade off? Any detailed explanation on this will be greatly appreciated. You can mail me direct or to the list. Thank you. Status of active means that log has been switched, but not all of the changes are
UTL_FILE question
Hello, Ihave a procedure that open a cursor anddumps some data for last 24 monthsto a file. The date range is static condition definedinside the cursor. Procedure is working great, but Iwhat I'm havingproblemfiguring outisif there is a way to create not one file with all the data, but multiple files with monthly sets of data. This way data formonth1 is created as filename_012002.txt and so forth. Thanks much! Viktor Do you Yahoo!? Yahoo! SiteBuilder - Free web site building tool. Try it!
Bye...
bye...
Re: Bye...
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 last post [EMAIL PROTECTED] wrote: | | bye... - -- Joseph S Testa Chief Technology Officer Data Management Consulting 614-791-9000 It's all about the CACHE -BEGIN PGP SIGNATURE- Version: GnuPG v1.2.1 (GNU/Linux) Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org iD8DBQFAGwhjR8fSap71V7YRAr2kAJ9mCheNbKDgqaxYv+PwCZfOwfUj6wCgkTco Ekwzd7uVojMNOewaxJ6k0dU= =+8Vo -END PGP SIGNATURE- -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Joe Testa 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: Bye...
NOO!! -Original Message- Sent: Friday, January 30, 2004 7:44 PM To: Multiple recipients of list ORACLE-L -BEGIN PGP SIGNED MESSAGE- Hash: SHA1 last post [EMAIL PROTECTED] wrote: | | bye... - -- Joseph S Testa Chief Technology Officer Data Management Consulting 614-791-9000 It's all about the CACHE -BEGIN PGP SIGNATURE- Version: GnuPG v1.2.1 (GNU/Linux) Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org iD8DBQFAGwhjR8fSap71V7YRAr2kAJ9mCheNbKDgqaxYv+PwCZfOwfUj6wCgkTco Ekwzd7uVojMNOewaxJ6k0dU= =+8Vo -END PGP SIGNATURE- -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Joe Testa 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: Chris Stephens 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: Bye...
It's the end of an era. How long was the list hosted here? -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Jacques Kilchoer 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).
ORA-01722 invalid number
I am running a query: select from WHERE COMPANY=2000 AND LOCATION='CDJ01' AND ITEMLOC.ACTIVE_STATUS='A' AND POVAGRMTLN.PROCURE_GROUP='SMAR' AND RTRIM(LTRIM(POVAGRMTLN.VEN_AGRMT_REF)) = 'COM1-21-LARROC NANCY' AND RTRIM(LTRIM(POVAGRMTLN.VENDOR))='21' and in the next part of the where I got this error:ORA-01722 invalid number AND RTRIM(LTRIM(POVAGRMTLN.VEN_AGRMT_REF)) = 'COM1-21-LARROC NANCY' If I write 'COM-21-LARROC NANCY' the error dissapear, I don't have any clue why this happen, I read about this error but the help don't seem to fit on this case. The POVAGRMTLN.VEN_AGRMT_REF field is char(30). ORA-01722 invalid number Cause: The attempted conversion of a character string to a number failed because the character string was not a valid numeric literal. Only numeric fields or character fields containing numeric data may be used in arithmetic functions or expressions. Only numeric fields may be added to or subtracted from dates. Action: Check the character strings in the function or _expression_. Check that they contain only numbers, a sign, a decimal point, and the character "E" or "e" and retry the operation.
Re: ** v$log.status
LGWR will always write to the redo-logs in a round-robin
fashion. So, on completing Group2, it would wait
for Group3 to be INACTIVE. {One exception is when you ADD a new Log
Group. If you added a new log
file without specifying a Group#, it would be allocated Group#4, would be
set to status UNUSED and would
get used before Group3 if LGWR is still currently in Group2}
In terms of disk space many smaller log groups is the same
requirement as few large log group.
Larger log groups are preferred by many DBAs so as to reduce the
frequency of the Log Switch Checkpoint.
{provided, of course, that LOG_CHECKPOINT_INTERVAL is set to higher than
the size of the log files}.
In scenarios where you have Standby Database and wish to propagate
Archive Logs frequently to
the Standby Site, you would have to accept frequent Log Switches
{however, this does not mean that
the Log Files have to be small, they could still be large}
Hemant
At 03:59 PM 30-01-04 -0800, you wrote:
Thanks Mladen and David
It is great to get such detailed info.
If we take your example of three redo logs then you could have a
situation where one is is 'CURRENT' status and two are in 'ACTIVE'
status. As follows :
GROUP# STATUS
--
1 ACTIVE
2 CURRENT
3 ACTIVE
Now if group 2 ('CURRENT') fills up or if 'alter system
switch logfile' command is given then it will try to go to the next one.
Since the next one (in fact both) is 'ACTIVE' it cannot be allocated and
made current. Right? So in that case will the database hang? Will it wait
for the next sequence redo log which is group 3 to get 'INACTIVE' or will
it allocate
group 1 if it becomes 'INACTIVE' before group 3.
Basically given limited space is it advisable to have many smaller redo
log groups instead of Oracle suggested bigger redo logs which switch once
an hour.
I agree it is unlikely and might as well just get extra space but the
point is to put in the best possible setup and be ready for crazy
activity instead of just going by the 'thumb rule' of having one log
switch per hour.
Thank you again
Mladen Gogala [EMAIL PROTECTED] wrote:
David Hau explained this much better and in much more clear
fashion then me. Here is the most important part from the usenet
thread he was referring you to:
**
This makes sense if you think about where the various v$ dynamic
performance
views get their info from, and which Oracle background process is
responsible for each task. First, note that:
1. v$log.status gets its redo log info from the *control file*
2. v$datafile_header.checkpoint_change# and checkpoint_time get their
info
from the *datafile headers*.
Here's the sequence of events when a log switch happens:
1. LGWR switches to the next redo log file, changes the status of
the
previous redo log file from CURRENT to ACTIVE in the control file,
and
signals DBWR to do a checkpoint on the previous ! redo log file.
2. When DBWR finishes with the checkpoint, it signals CKPT to
update
datafile headers and update checkpoint info (only) in the control
file.
This is the info read by v$datafile_header.checkpoint_change# and
checkpoint_time. Note that CKPT does not update redo log info in
the
control file. It only deals with checkpoint info, as its name
implies.
3. When CKPT is done, it signals LGWR to update the redo log status
in
the
control file from ACTIVE to INACTIVE. This is the info read by
v$log.status. This update task is a low priority item for LGWR
because
the
only process that cares about whether the redo log status is active
or
not
is LGWR itself. The redo log status tells LGWR whether it can reuse a
redo
log file or not (i.e. whether checkpoint has completed on that redo
log
file.) That is, by delaying this operation, LGWR is not blocking the
work
of any other process.
LGWR will update the redo log status i! n the control file when any
of
these
occurs (and others too, that I don't know of):
1. when LGWR periodically checks for compliance with the
LOG_CHECKPOINT_TIMEOUT parameter, which says that the checkpoint
position
should not lag behind the latest redo record by this amount of
time.
2. when you issue a alter system checkpoint which is what
you did.
So if you want the redo log status to be updated more quickly to
inactive
after a checkpoint, one way to do it is to decrease the value of
LOG_CHECKPOINT_TIMEOUT in init.ora.
Cheers,
Dave
**
On 01/30/2004 03:44:29 PM, A Joshi wrote:
Mladen,
Thanks for info. So all the dirty blocks need to be written to
disk
after each checkpoint. After that is done the status becomes
'INACTIVE'. Just that sometimes this is very unpredictable.
My question : If a log switch alway! s causes a implicit
checkpoint
then
what is the need for this explicit checkpoint to be given? Thank
you.
Mladen Gogala wrote:
On 01/30/2004 01:24:26 PM, A Joshi wrote:
Hi,
In view v$log there is a
RE: Bye...
Re: ORA-01722 invalid number
My guess would be that company is not a number but because you do compare it to a number company=2000 Oracle does an implicit conversion to_number(company)=2000 and that fails when it hits a row where company is not numeric. If my guess is right try company='2000' At 07:59 PM 1/30/2004, you wrote: I am running a query: select from WHERE COMPANY=2000 AND LOCATION='CDJ01' AND ITEMLOC.ACTIVE_STATUS='A' AND POVAGRMTLN.PROCURE_GROUP='SMAR' AND RTRIM(LTRIM(POVAGRMTLN.VEN_AGRMT_REF)) = 'COM1-21-LARROC NANCY' AND RTRIM(LTRIM(POVAGRMTLN.VENDOR))='21' and in the next part of the where I got this error:ORA-01722 invalid number AND RTRIM(LTRIM(POVAGRMTLN.VEN_AGRMT_REF)) = 'COM1-21-LARROC NANCY' If I write 'COM-21-LARROC NANCY' the error dissapear, I don't have any clue why this happen, I read about this error but the help don't seem to fit on this case. The POVAGRMTLN.VEN_AGRMT_REF field is char(30). ORA-01722 invalid number Cause: The attempted conversion of a character string to a number failed because the character string was not a valid numeric literal. Only numeric fields or character fields containing numeric data may be used in arithmetic functions or expressions. Only numeric fields may be added to or subtracted from dates. Action: Check the character strings in the function or expression. Check that they contain only numbers, a sign, a decimal point, and the character E or e and retry the operation. Wolfgang Breitling Oracle7, 8, 8i, 9i OCP DBA Centrex Consulting Corporation http://www.centrexcc.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Wolfgang Breitling INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Re: ORA-01722 invalid number
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 last post Wolfgang Breitling wrote: | My guess would be that company is not a number but because you do | compare it to a number company=2000 Oracle does an implicit | conversion to_number(company)=2000 and that fails when it hits a | row where company is not numeric. | If my guess is right try company='2000' | | At 07:59 PM 1/30/2004, you wrote: | | I am running a query: | select | from | WHERE COMPANY=2000 AND | LOCATION='CDJ01' AND ITEMLOC.ACTIVE_STATUS='A' AND | POVAGRMTLN.PROCURE_GROUP='SMAR' | AND RTRIM(LTRIM(POVAGRMTLN.VEN_AGRMT_REF)) = 'COM1-21-LARROC NANCY' | AND RTRIM(LTRIM(POVAGRMTLN.VENDOR))='21' | | and in the next part of the where I got this error:ORA-01722 | invalid number | | AND RTRIM(LTRIM(POVAGRMTLN.VEN_AGRMT_REF)) = 'COM1-21-LARROC NANCY' | | If I write 'COM-21-LARROC NANCY' the error dissapear, I don't have | any clue why this happen, I read about this error but the help | don't seem to fit on this case. | The POVAGRMTLN.VEN_AGRMT_REF field is char(30). | | ORA-01722 invalid number | | Cause: The attempted conversion of a character string to a number | failed because the character string was not a valid numeric | literal. Only numeric fields or character fields containing numeric | data may be used in arithmetic functions or expressions. Only | numeric fields may be added to or subtracted from dates. | | Action: Check the character strings in the function or expression. | Check that they contain only numbers, a sign, a decimal point, and | the character E or e and retry the operation. | | | Wolfgang Breitling | Oracle7, 8, 8i, 9i OCP DBA | Centrex Consulting Corporation | http://www.centrexcc.com - -- Joseph S Testa Chief Technology Officer Data Management Consulting 614-791-9000 It's all about the CACHE -BEGIN PGP SIGNATURE- Version: GnuPG v1.2.1 (GNU/Linux) Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org iD8DBQFAGyRxR8fSap71V7YRApzzAKCoA6lzRXR7hCWkZmSA0RW+DXvg/QCeNQPK WQEp4pYfX7j7JxYW8RMbeN8= =kTiZ -END PGP SIGNATURE- -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Joe Testa 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: Bye...
drop list ORACLE-[EMAIL PROTECTED] \rm r FATCITY bye -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of [EMAIL PROTECTED] Sent: Friday, January 30, 2004 5:24 PM To: Multiple recipients of list ORACLE-L Subject: Bye... bye...
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
RE: !!Please Read - Oracle-L is moving!!
Title: Message 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. -Original Message-From: Lord David [mailto:[EMAIL PROTECTED]Sent: Monday, January 26, 2004 11:09 AMTo: Multiple recipients of list ORACLE-LSubject: RE: !!Please Read - Oracle-L is moving!! One word - exchange. --David Lord -Original Message-From: Hemant K Chitale [mailto:[EMAIL PROTECTED] Sent: 23 January 2004 16:54To: Multiple recipients of list ORACLE-LSubject: RE: !!Please Read - Oracle-L is moving!!Why not stop using Outlook. I've been happy with Eudora for 1.5 years now.HemantHemantAt 07:54 AM 23-01-04 -0800, you wrote: Thanks guys! From: Kevin Toepke [mailto:[EMAIL PROTECTED]] Sent: Friday, January 23, 2004 9:30 AMTo: Multiple recipients of list ORACLE-LSubject: RE: !!Please Read - Oracle-L is moving!!Its easy to disable this "feature": Navigate to the Tools-Options menu Click the "Email Options" Button Uncheck the "Remove extra line breaks in plain text messages" checkbox Click Okay about 30 times and your're done!Kevin -Original Message- From: Lord David [mailto:[EMAIL PROTECTED]] Sent: Friday, January 23, 2004 9:14 AM To: Multiple recipients of list ORACLE-L Subject: RE: !!Please Read - Oracle-L is moving!! Bill The line breaks get removed from *incoming* mail, so I don't think it matters what your default new mail format is. I think its a new 'feature' in Outlook 2003 - I found this quote in the 'Whats new in Microsoft Office' in online help: - Extra line breaks automatically removed in messages Sometimes plain text messages that travel over the Internet acquire extra line breaks that make the message difficult to read. Outlook automatically removes the extra line breaks so it's easier to read the message. Ouch David Lord Senior DBA Iron Mountain (UK) Ltd Telephone: 029 2054 4000 Direct: 029 2054 4013 Fax: 029 2069 2464 Email: [EMAIL PROTECTED] -Original Message- From: Thater, William [mailto:[EMAIL PROTECTED]] Sent: 23 January 2004 13:24 To: Multiple recipients of list ORACLE-L Subject: RE: !!Please Read - Oracle-L is moving!! -Original Message- From: Lord David [mailto:[EMAIL PROTECTED]] Sent: Friday, January 23, 2004 3:14 AM To: Multiple recipients of list ORACLE-L Subject: RE: !!Please Read - Oracle-L is moving!! Tim Its something to do with outlook removing line breaks and thereby mangling the formatting of the command. In my Outlook, there is a message in the header of the mail saying something like 'Extra line breaks in this message were removed. To restore click here.' When I did click there and replied the subscription went through okay. What on earth lookout is doing removing line breaks I'm not sure. How does it decide which line breaks to remove? I couldn't find any way of stopping it doing this. well, it looks to me as if you're using HTML and/or Word for your email, and Outlook in it's infinite wisdom replaces line brakes with BR or whatever the hell Word uses. as to stopping it, i have no idea. -- Bill "Shrek" Thater ORACLE DBA "I'm going to work my ticket if I can..." -- Gilwell song [EMAIL PROTECTED] Yes, we have to divide up our time like that, between our politics and our equations. But to me our equations are far more important, for politics are only a matter of present concern. A mathematical equation stands forever. - Albert Einstein This email and its attachments are confidential under applicable law and are intended for use of the sender's addressee only, unless the sender expressly agrees otherwise, or unless a separate written agreement exists between Iron Mountain and a recipient company governing communications between the parties and any data that may be so transmitted. Transmission of email over the Internet is not a secure communications medium. If you are requesting or have requested the transmittal of personal data, as defined in applicable privacy laws, by means of email or in an attachment to email, you may wish to select a more secure alternate means of transmittal
RE: !!Please Read - Oracle-L is moving!!
Title: Message I don't recommend using Word as the editor for Outlook... Subscribe to the Windows and Office lists from www.woodyswatch.com Patrice. -Original Message-From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] Sent: January 28, 2004 3:49 AMTo: Multiple recipients of list ORACLE-LSubject: 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. -Original Message-From: Lord David [mailto:[EMAIL PROTECTED]Sent: Monday, January 26, 2004 11:09 AMTo: Multiple recipients of list ORACLE-LSubject: RE: !!Please Read - Oracle-L is moving!! One word - exchange. --David Lord -Original Message-From: Hemant K Chitale [mailto:[EMAIL PROTECTED] Sent: 23 January 2004 16:54To: Multiple recipients of list ORACLE-LSubject: RE: !!Please Read - Oracle-L is moving!!Why not stop using Outlook. I've been happy with Eudora for 1.5 years now.HemantHemantAt 07:54 AM 23-01-04 -0800, you wrote: Thanks guys! From: Kevin Toepke [mailto:[EMAIL PROTECTED]] Sent: Friday, January 23, 2004 9:30 AMTo: Multiple recipients of list ORACLE-LSubject: RE: !!Please Read - Oracle-L is moving!!Its easy to disable this "feature": Navigate to the Tools-Options menu Click the "Email Options" Button Uncheck the "Remove extra line breaks in plain text messages" checkbox Click Okay about 30 times and your're done!Kevin -Original Message- From: Lord David [mailto:[EMAIL PROTECTED]] Sent: Friday, January 23, 2004 9:14 AM To: Multiple recipients of list ORACLE-L Subject: RE: !!Please Read - Oracle-L is moving!! Bill The line breaks get removed from *incoming* mail, so I don't think it matters what your default new mail format is. I think its a new 'feature' in Outlook 2003 - I found this quote in the 'Whats new in Microsoft Office' in online help: - Extra line breaks automatically removed in messages Sometimes plain text messages that travel over the Internet acquire extra line breaks that make the message difficult to read. Outlook automatically removes the extra line breaks so it's easier to read the message. Ouch David Lord Senior DBA Iron Mountain (UK) Ltd Telephone: 029 2054 4000 Direct: 029 2054 4013 Fax: 029 2069 2464 Email: [EMAIL PROTECTED] -Original Message- From: Thater, William [mailto:[EMAIL PROTECTED]] Sent: 23 January 2004 13:24 To: Multiple recipients of list ORACLE-L Subject: RE: !!Please Read - Oracle-L is moving!! -Original Message- From: Lord David [mailto:[EMAIL PROTECTED]] Sent: Friday, January 23, 2004 3:14 AM To: Multiple recipients of list ORACLE-L Subject: RE: !!Please Read - Oracle-L is moving!! Tim Its something to do with outlook removing line breaks and thereby mangling the formatting of the command. In my Outlook, there is a message in the header of the mail saying something like 'Extra line breaks in this message were removed. To restore click here.' When I did click there and replied the subscription went through okay. What on earth lookout is doing removing line breaks I'm not sure. How does it decide which line breaks to remove? I couldn't find any way of stopping it doing this. well, it looks to me as if you're using HTML and/or Word for your email, and Outlook in it's infinite wisdom replaces line brakes with BR or whatever the hell Word uses. as to stopping it, i have no idea. -- Bill "Shrek" Thater ORACLE DBA "I'm going to work my ticket if I can..." -- Gilwell song [EMAIL PROTECTED] Yes, we have to divide up our time like that, between our politics and our equations. But to me our equations are far more important, for politics are only a matter of present concern. A mathematical equation stands forever. - Albert Einstein This email and its attachments are confidential under applicable law and are intended for use of the sender's addressee only,
RE: possible to load a string with paragraphs?
one word ... CLOB field ... 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- David Boyd Sent: Tuesday, January 27, 2004 2:30 PM To: Multiple recipients of list ORACLE-L Hi List, I have a web application that allows users to type notes with paragraphs. Is it possiable to load the string with paragraphs into Oracle (not save the note as a file)? Later on the application has to display the same format for the note when the user queries that record on the web. Thanks for any inputs. _ Check out the coupons and bargains on MSN Offers! http://shopping.msn.com/softcontent/softcontent.aspx?scmId=1418 -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: David Boyd 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).
RMOUG Training Days Upgrade
We have recently completed a successful upgrade P10 with the inclusion of the list's own Mogens Norgaard and the semi-retired Dave Ensor (the past Jonathan Lewis). For a full list of speakers, exhibitors and events, please visit www.rmoug.org. -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Daniel Fink 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).
RMOUG Training Days Oracle-L gathering
Is there any interest in an Oracle-L gathering after the first day at RMOUG TD? -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Daniel Fink 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: RMOUG Training Days Oracle-L gathering
yes! --- Daniel Fink [EMAIL PROTECTED] wrote: Is there any interest in an Oracle-L gathering after the first day at RMOUG TD? -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Daniel Fink 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).
RE: What to look for in STATSPACK report
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: http://www.orafaq.net -- Author: Anjo Kolk 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!
Question re. Oracle clustering on Red Hat Advanced Server
When running on a clustered environment, do all the servers have to be identical? Oracle says that the beauty of using blade servers is you buy what you need now, then add later. What if later is two years later? You might not be able to buy the same machines, only more powerful ones. Does that mess up Oracle RAC? Can RHAS cluster different hardware together successfully? Patrice. -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Boivin, Patrice J INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
measuring TPM
I've been asked to provide value for the Transactions Per Minute going through our primary OLTP production database. I believe I can use deltas in SCN values to measure transactions which do INSERT/UPDATE/DELETE and then COMMIT; Is there any way to measure/count the number of SELECTs which occur? If so, how? How would you derive a value for TPM for your DB? -- 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 list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: Question re. Oracle clustering on Red Hat Advanced Server
Hi All I know is that we used to completely different machines to set up a windows test RAC. The requirement seems to be that the OS must be the same. Jack -Original Message- Sent: Wednesday, January 28, 2004 4:24 PM To: Multiple recipients of list ORACLE-L When running on a clustered environment, do all the servers have to be identical? Oracle says that the beauty of using blade servers is you buy what you need now, then add later. What if later is two years later? You might not be able to buy the same machines, only more powerful ones. Does that mess up Oracle RAC? Can RHAS cluster different hardware together successfully? Patrice. -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Boivin, Patrice J INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Jack van Zanen 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
Charlie, I understand a transaction as a succession of SQL statements between two successive COMMITs or ROLLBACKs - you will find inside V$SYSSTAT how many COMMITs and ROLLBACKs were issued. If you are interested, besides transactions proper, in the number of statements executed, then have a look at 'execute count'. You also have stats to tell you how many of them were recursive statements I believe. Talking about metrics (and forgetting about what you have been asked to provide :-)), methinks you can have a reasonably fair (and balanced) view of what is going on by collecting six values : o Number of sessions and number of executions to see what users are asking of your database o Redo blocks written to see the 'update' activity and the number of bytes sent which roughly tell you what users want to be done o Physical and logical I/Os to see how efficiently it is done Discrepancies should trigger investigation. HTH, Stephane Faroult - --- Original Message --- - From: [EMAIL PROTECTED] To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Wed, 28 Jan 2004 07:29:25 I've been asked to provide value for the Transactions Per Minute going through our primary OLTP production database. I believe I can use deltas in SCN values to measure transactions which do INSERT/UPDATE/DELETE and then COMMIT; Is there any way to measure/count the number of SELECTs which occur? If so, how? How would you derive a value for TPM for your DB? -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Stephane Faroult INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Re: measuring TPM
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. The query would go like this: SQL select name, value from v$sysstat 2 where name = 'user commits'; NAME VALUE -- user commits 1 On 01/28/2004 10:29:25 AM, [EMAIL PROTECTED] wrote: I've been asked to provide value for the Transactions Per Minute going through our primary OLTP production database. I believe I can use deltas in SCN values to measure transactions which do INSERT/UPDATE/DELETE and then COMMIT; Is there any way to measure/count the number of SELECTs which occur? If so, how? How would you derive a value for TPM for your DB? -- 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 list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Mladen Gogala Oracle DBA -- 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
Check out 'user commits','user rollbacks' and (maybe) 'user calls' in v$sysstat. These get collected by statspack so you can plot a chart over time. Niall -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of [EMAIL PROTECTED] Sent: 28 January 2004 15:29 To: Multiple recipients of list ORACLE-L Subject: measuring TPM I've been asked to provide value for the Transactions Per Minute going through our primary OLTP production database. I believe I can use deltas in SCN values to measure transactions which do INSERT/UPDATE/DELETE and then COMMIT; Is there any way to measure/count the number of SELECTs which occur? If so, how? How would you derive a value for TPM for your DB? -- 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 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: 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: measuring TPM
Charlie, I use the following to determine this: EXEC SQL SELECT ROUND(VALUE/((SYSDATE-STARTUP_TIME)*1440),1) INTO :tp FROM V$SYSSTAT, V$INSTANCE WHERE NAME='user commits'; Dick Goulet Senior Oracle DBA Oracle Certified 8i DBA -Original Message- [mailto:[EMAIL PROTECTED] Sent: Wednesday, January 28, 2004 10:29 AM To: Multiple recipients of list ORACLE-L I've been asked to provide value for the Transactions Per Minute going through our primary OLTP production database. I believe I can use deltas in SCN values to measure transactions which do INSERT/UPDATE/DELETE and then COMMIT; Is there any way to measure/count the number of SELECTs which occur? If so, how? How would you derive a value for TPM for your DB? -- 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 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).
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, 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).
