RE: Rollback Segs
Which database version are you using? as long as I know, at least on Oracle 8.0.6 you can not specify which rollback segment a particular transaction should use. We disable all rollback segments and only enable those which we know won't make big updates / deletes crash. -Mensaje original-De: venkat Rama [mailto:[EMAIL PROTECTED]Enviado el: miércoles, 16 de julio de 2003 6:34Para: Multiple recipients of list ORACLE-LAsunto: Rollback Segs Hi Friends, I have 150Gb database with 30 rollback segs with optimal settings!! for normal operations no problems!! But we are doing massive updates for some of big tables!! So we are using vb programs and connecting to the database thru odbc dsn. My first question is how to set transaction use rollback segment rbs_big? I tried to set as first stmt, but its not using rbs_big segment, its using some other rollback segment. Is that good idea to make all my rollback segs maxextents unlimited to avoid maxextents reached error?? I have lot of rbs space nearly 10Gb. I saw one time, one of my rbs segment extented 2gb and failed transaction with maxextents reached with 5m initial and next, minextents 20 and optimal 100M. Any help will be appreciated Thanks venkat. Do you Yahoo!?SBC Yahoo! DSL - Now only $29.95 per month!
RE: Rollback Segs
Yes you are right, sorry but I made a mistake, since we are using Baan IVc4 which works against Oracle we have no means to interfere with its transactions when they are commited to Oracle and we can not modify them so that it uses a specific rollback segment, so the only way for us to do it is to disable all except the one we want it to use. -Mensaje original-De: Lee Cullip [mailto:[EMAIL PROTECTED]Enviado el: miércoles, 16 de julio de 2003 10:54Para: Multiple recipients of list ORACLE-LAsunto: RE: Rollback Segs set transaction use rollback segment only works if it's the first statement in your transaction, an easy way to make sure this is the case is to issue a commit directly before issuing this statement. I believe this has been a feature of Oracle since way back in version 7 (Correct me if I'm wrong). Hope this helps. Cheers Lee -Original Message-From: Fermin Bernaus [mailto:[EMAIL PROTECTED]Sent: 16 July 2003 09:09To: Multiple recipients of list ORACLE-LSubject: RE: Rollback Segs Which database version are you using? as long as I know, at least on Oracle 8.0.6 you can not specify which rollback segment a particular transaction should use. We disable all rollback segments and only enable those which we know won't make big updates / deletes crash. -Mensaje original-De: venkat Rama [mailto:[EMAIL PROTECTED]Enviado el: miércoles, 16 de julio de 2003 6:34Para: Multiple recipients of list ORACLE-LAsunto: Rollback Segs Hi Friends, I have 150Gb database with 30 rollback segs with optimal settings!! for normal operations no problems!! But we are doing massive updates for some of big tables!! So we are using vb programs and connecting to the database thru odbc dsn. My first question is how to set transaction use rollback segment rbs_big? I tried to set as first stmt, but its not using rbs_big segment, its using some other rollback segment. Is that good idea to make all my rollback segs maxextents unlimited to avoid maxextents reached error?? I have lot of rbs space nearly 10Gb. I saw one time, one of my rbs segment extented 2gb and failed transaction with maxextents reached with 5m initial and next, minextents 20 and optimal 100M. Any help will be appreciated Thanks venkat. Do you Yahoo!?SBC Yahoo! DSL - Now only $29.95 per month!This e-mail message (including any attachment) is intended only for the personal use of the recipient(s) named above. This message is confidential and may be legally privileged. If you are not an intended recipient, you may not review, copy or distribute this message. If you have received this communication in error, please notify us immediately by e-mail and delete the original message.Any views or opinions expressed in this message are those of the author only. Furthermore, this message (including any attachment) does not create any legally binding rights or obligations whatsoever, which may only be created by the exchange of hard copy documents signed by a duly authorised representative of Hutchison 3G UK Limited.
TOAD vs BMC's XRAY
Hi there, I am considering buying an administration tool and programmer's tool. The two I know are Toad and BMC's XRAY but since I do not know them very well I have difficulties deciding which of them is the best. What I need more is to tune the database, trace into time and CPU consuming SQLs, a procedure / trigger editor and debugger. Or maybe you know better tools than these two... any help will be greatly appreciated. Thanks in advance, . Fermín Bernaus Berraondo Dpto. de Informática SAMMIC, S.A. [EMAIL PROTECTED] http://www.sammic.com Telf. +34 - 943 157 331 Fax +34 - 943 151 276 . -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Fermin Bernaus INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (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: Multiple Datafiles and performance?
(Other than time to recover from datafile loss) It is probably safe to assume that the datafiles exist on a RAID 5. (for now) 3) Why the recommendation to take a Win2k datafile to just over 2G? For future apps I am pushing for optimal recommendations that go for more raid 1 sets or raid 10 over the Raid 5. This should allow for more flexibility for spreading out the i/o. Thanks for your patience and all the help. David Phillips Support DBA BAARF Member #30 -Original Message- Sent: Thursday, August 07, 2003 9:59 AM To: Multiple recipients of list ORACLE-L Win2K. If you decide to increase the filesize, do it to more than 2G (doesn't have to be by much). Of course, you didn't mention autoextend so this may not be an issue. Also, just how many physical disks do you have? Logical disks are not the issue. If you're going to get any increased performance you should be putting the second datafile on a second physical disk. Any how come you're not using some sort of RAID device (or don't you have your logical drives striped across your physical drives)? There is no easy answer to your question without an understanding of the reality of your disk layout. "Fermin Bernaus" @sammic.com> cc: Sent by: Subject: RE: Multiple Datafiles and performance? ml-errors 08/07/2003 08:05 AM Please respond to ORACLE-L In my experience, spreading datafiles across volumes (specially if you are careful not to locate the a table's datafiles and its indexes datafiles in the same drive) greatly increases performance. As for the file size, I can not say because I have not tested it, but I think it should have no real impact compared to splitting it. Reorganizing the database regularly is a better way to optimize performance. . Fermín Bernaus Berraondo Dpto. de Informática SAMMIC, S.A. [EMAIL PROTECTED] http://www.sammic.com Telf. +34 - 943 157 331 Fax +34 - 943 151 276 . -Mensaje original- De: Dave Phillips [mailto:[EMAIL PROTECTED] Enviado el: miércoles, 06 de agosto de 2003 22:14 Para: Multiple recipients of list ORACLE-L Asunto: Multiple Datafiles and performance? Oracle 8.1.7.4 Win2k What is the consensus on datafile sizing and the impact/overhead multiple datafiles have on performance? For example, if I have one 2.5g datafile, and three 1g datafiles, and I need more space, would it be better to increase the size of the 1g to 2g or add another 1g datafile?. Is it better to keep them all uniform in size? I would think having multiple datafiles that could be spread across drive volumes would be beneficial, am I wrong? (Wouldn't be the first time :) TIA David Phillips Support DBA Gasper Corp. BAARF member #30 -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Dave Phillips INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (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: Fermin Bernaus INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (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
RE: Change based recovery
see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: DENNIS WILLIAMS INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Fermin Bernaus INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (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: Multiple Datafiles and performance?
In my experience, spreading datafiles across volumes (specially if you are careful not to locate the a table's datafiles and its indexes datafiles in the same drive) greatly increases performance. As for the file size, I can not say because I have not tested it, but I think it should have no real impact compared to splitting it. Reorganizing the database regularly is a better way to optimize performance. .. Fermín Bernaus Berraondo Dpto. de Informática SAMMIC, S.A. [EMAIL PROTECTED] http://www.sammic.com Telf. +34 - 943 157 331 Fax +34 - 943 151 276 .. -Mensaje original- De: Dave Phillips [mailto:[EMAIL PROTECTED] Enviado el: miércoles, 06 de agosto de 2003 22:14 Para: Multiple recipients of list ORACLE-L Asunto: Multiple Datafiles and performance? Oracle 8.1.7.4 Win2k What is the consensus on datafile sizing and the impact/overhead multiple datafiles have on performance? For example, if I have one 2.5g datafile, and three 1g datafiles, and I need more space, would it be better to increase the size of the 1g to 2g or add another 1g datafile?. Is it better to keep them all uniform in size? I would think having multiple datafiles that could be spread across drive volumes would be beneficial, am I wrong? (Wouldn't be the first time :) TIA David Phillips Support DBA Gasper Corp. BAARF member #30 -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Dave Phillips INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (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: Fermin Bernaus INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (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: Multiple Datafiles and performance?
, and I > need more space, would it be better to increase the size of the 1g to > 2g or add another 1g datafile?. > Is it better to keep them all uniform in size? > > I would think having multiple datafiles that could be spread across > drive volumes would be beneficial, am I wrong? (Wouldn't be the first > time :) > > TIA > > David Phillips > Support DBA > Gasper Corp. > BAARF member #30 -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Tim Gorman INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Fermin Bernaus INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (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: Change based recovery
Well I am quite de-motivated actually!! but at least it is good to know I was (partially) wrong and that I will feel safer after reading your comments, thanks! I am really in doubt now, but I remember when we were testing we did recover all datafiles (the ones that are stated in the v$datafile table) from a cold backup except control files and redolog files; we were able to restore the whole database with the commands I wrote down in my first message. If I am still wrong, will you please be kind enough to tell me which are the exact commands needed to recover the whole database from a cold backup if I have no online backups and I lose everything except for the archived logs? can it really be done? Thank you so much! Fermin. -Mensaje original-De: Venu Gopal [mailto:[EMAIL PROTECTED]Enviado el: lunes, 11 de agosto de 2003 13:44Para: Multiple recipients of list ORACLE-LAsunto: RE: Change based recovery Fermin, I don’t want to de-motivate you, but I really doubt whether your backup strategy really works. The command that you have mentioned below will NOT do a complete recovery as it’s a cold backup. As for your questions: 1) You can recover your entire database in either case (Cold or Hot), If you have your archive logs. Difference being, You have recreate your control file if it’s a cold DB backup and recover the DB using ‘BACKUP CONTROL FILE’ option. 2) Lets look at it the other way; you do NOT need any downtime for Hot backups while you need downtime for Cold backups. Downtime could be very expensive depending on the type of database. Secondly, you can take a hot backup very frequently as it does not involve any downtime. Recent backup means less recovery is required and less time to bring up the database. Let me know if you need anymore info. Cheers! Venu -Original Message-From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of Fermin BernausSent: Monday, August 11, 2003 4:54 PMTo: Multiple recipients of list ORACLE-LSubject: RE: Change based recovery I've been reading your messages with much interest. I have some experience with database administration and I have done many tests, but I've not tried what I am going to expose in this message, maybe you can help. We do cold backups on a regular basis (every weekend) then just backup the archive log every day, then delete them every time a new cold backup is done. We have tested it and if all database files (parameters file, datafiles, control files) except for one control file and the archived logs were lost we could recover the entire database issuing the following commands after restoring all missing files and mounting the database: SET AUTORECOVERY ON RECOVER DATABASE ALTER DATABASE OPEN My questions are: 1 - Could a complete restore be done even if we lost ALL control files? can we recover the entire database from a cold backup provided we have all archived logs until the failure time? 2 - If the answer is yes, what is the advantage of doing on-line backups of datafiles and control files? Thanks for your answers, I always learn so much from this list!! Fermin. -Mensaje original-De: Hand, Michael T [mailto:[EMAIL PROTECTED]Enviado el: viernes, 08 de agosto de 2003 18:10Para: Multiple recipients of list ORACLE-LAsunto: RE: Change based recovery Lisa, The 3rd option (besides shuting down source database and using a controlfile trace) is to "alter database backup controlfile to 'filename'; ", use this file, then proceed with the recovery as Venu suggests. I've used this method on a hot backup to roll the database forward. Also, don't bother restoring the redo logs as you will be overwriting / recreating them with the "alter database open resetlogs". One more thing I noticed. Your until change number looks to me like an archive sequence number rather than the SCN it needs to be. Hope this helps. Mike Hand -Original Message-From: Dobson, Lisa [mailto:[EMAIL PROTECTED]Sent: Thursday, August 07, 2003 8:21 AMTo: Multiple recipients of list ORACLE-LSubject: Change based recovery Hi Guys and Gals, We are currently doing some testing to enable us to move our production database from one unix box to another. We are running a 7.3.4 db in archivelog mode. The approach that management want to use is to restore the database on the new server from a backup and then roll it forward using the archived redo logs.
RE: Change based recovery
I've been reading your messages with much interest. I have some experience with database administration and I have done many tests, but I've not tried what I am going to expose in this message, maybe you can help. We do cold backups on a regular basis (every weekend) then just backup the archive log every day, then delete them every time a new cold backup is done. We have tested it and if all database files (parameters file, datafiles, control files) except for one control file and the archived logs were lost we could recover the entire database issuing the following commands after restoring all missing files and mounting the database: SET AUTORECOVERY ON RECOVER DATABASE ALTER DATABASE OPEN My questions are: 1 - Could a complete restore be done even if we lost ALL control files? can we recover the entire database from a cold backup provided we have all archived logs until the failure time? 2 - If the answer is yes, what is the advantage of doing on-line backups of datafiles and control files? Thanks for your answers, I always learn so much from this list!! Fermin. -Mensaje original-De: Hand, Michael T [mailto:[EMAIL PROTECTED]Enviado el: viernes, 08 de agosto de 2003 18:10Para: Multiple recipients of list ORACLE-LAsunto: RE: Change based recovery Lisa, The 3rd option (besides shuting down source database and using a controlfile trace) is to "alter database backup controlfile to 'filename'; ", use this file, then proceed with the recovery as Venu suggests. I've used this method on a hot backup to roll the database forward. Also, don't bother restoring the redo logs as you will be overwriting / recreating them with the "alter database open resetlogs". One more thing I noticed. Your until change number looks to me like an archive sequence number rather than the SCN it needs to be. Hope this helps. Mike Hand -Original Message-From: Dobson, Lisa [mailto:[EMAIL PROTECTED]Sent: Thursday, August 07, 2003 8:21 AMTo: Multiple recipients of list ORACLE-LSubject: Change based recovery Hi Guys and Gals, We are currently doing some testing to enable us to move our production database from one unix box to another. We are running a 7.3.4 db in archivelog mode. The approach that management want to use is to restore the database on the new server from a backup and then roll it forward using the archived redo logs. I have a full cold back up from last Friday. I have restored the datafiles, controlfiles and redo logs onto our test server from the backup tape, and then ftp'd the archived logs over. I then do - SVRMGR> startup mountORACLE instance started.Total System Global Area 258304260 bytesFixed Size 45092 bytesVariable Size 126925024 bytesDatabase Buffers 131072000 bytesRedo Buffers 262144 bytesDatabase mounted.SVRMGR> recover database until change 10349;Media recovery complete.
RE: Semaphore problem
Did you have a look to the alert file? what does it say? error 13 means some kind of permission has been violated at the OS level (at least under UNIX) probably trying to access a file (for cration, reading or whatever) Fermin. -Mensaje original- De: Smith, Ron L. [mailto:[EMAIL PROTECTED] Enviado el: miércoles, 13 de agosto de 2003 19:10 Para: Multiple recipients of list ORACLE-L Asunto: Semaphore problem Has anyone ever seen an error like: ERROR: rbusy(SEMOP) (13; Permission denied) There is no Oracle error associated with it. Thanks! Ron -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Smith, Ron L. INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (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: Fermin Bernaus INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (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: Change based recovery
EN My questions are: 1 - Could a complete restore be done even if we lost ALL control files? can we recover the entire database from a cold backup provided we have all archived logs until the failure time? 2 - If the answer is yes, what is the advantage of doing on-line backups of datafiles and control files? Thanks for your answers, I always learn so much from this list!! Fermin. -Mensaje original- De: Hand, Michael T [mailto:[EMAIL PROTECTED] Enviado el: viernes, 08 de agosto de 2003 18:10 Para: Multiple recipients of list ORACLE-L Asunto: RE: Change based recovery Lisa, The 3rd option (besides shuting down source database and using a controlfile trace) is to "alter database backup controlfile to 'filename'; ", use this file, then proceed with the recovery as Venu suggests. I've used this method on a hot backup to roll the database forward. Also, don't bother restoring the redo logs as you will be overwriting / recreating them with the "alter database open resetlogs". One more thing I noticed. Your until change number looks to me like an archive sequence number rather than the SCN it needs to be. Hope this helps. Mike Hand -Original Message- Sent: Thursday, August 07, 2003 8:21 AM To: Multiple recipients of list ORACLE-L Hi Guys and Gals, We are currently doing some testing to enable us to move our production database from one unix box to another. We are running a 7.3.4 db in archivelog mode. The approach that management want to use is to restore the database on the new server from a backup and then roll it forward using the archived redo logs. I have a full cold back up from last Friday. I have restored the datafiles, controlfiles and redo logs onto our test server from the backup tape, and then ftp'd the archived logs over. I then do - SVRMGR> startup mount ORACLE instance started. Total System Global Area 258304260 bytes Fixed Size 45092 bytes Variable Size126925024 bytes Database Buffers 131072000 bytes Redo Buffers262144 bytes Database mounted. SVRMGR> recover database until change 10349; Media recovery complete. -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: DENNIS WILLIAMS INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Fermin Bernaus INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (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: DENNIS WILLIAMS INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Fermin Bernaus INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (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-01403 error, help!!!
If logged in SQL Plus the following SQL returns just one row: SELECT t$orno FROM ttdsls805501 WHERE t$user = 'exped9' GROUP BY t$orno; If I do use the same SELECT statement inside a stored procedure and have the returning value stored in a local variable: SELECT t$orno INTO eskaria FROM ttdsls805501 WHERE t$user = 'exped9' GROUP BY t$orno; where eskaria has been declared as: eskaria ttdsls805501.t$orno%TYPE; I get ORA-01403. I have no clue why I am getting this error, can you help please? Many thanks! .. Fermín Bernaus Berraondo Dpto. de Informática SAMMIC, S.A. [EMAIL PROTECTED] http://www.sammic.com Telf. +34 - 943 157 331 Fax +34 - 943 151 276 .. -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Fermin Bernaus INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Weird behavior with VARCHAR fields (was: ORA-01403 error, help!!!)
Thank you all of you who answered to my first question. No, the problem has nothing to do with permissions. The record existed in the table, but here it is what I discovered. Can anyone explain please? It may be the normal Oracle behavior, but I believe it was not working like this under 7.3.4 (now we have 8.0.3). I can not confirm though. TTDSLS805501 table definition is as follows: NameNull? Type T$ORNO NOT NULLNUMBER T$USER NOT NULLCHAR(10) If I do an INSERT like the following: INSERT INTO TTDSLS805501 VALUES (151124 , 'exped9') The value 'exped9' for the field T$USER is 6 characters long, while the definition has got 10. After the insertion, I find that the field for the new record is 10 chars long, the last 4 characters being filled with blanks. But here comes the weird behavior. If I construct the 2 following SELECT on this table from SQLPlus, both work OK and return the expected row: SELECT t$orno FROM TTDSLS805501 WHERE t$user = 'exped9'; -> 1 row returned SELECT t$orno FROM TTDSLS805501 WHERE t$user = 'exped9'; -> 1 row returned But within a stored procedure and using a local variable the following will work OK: SELECT t$orno INTO eskaria FROM TTDSLS805501 WHERE t$user = my_var; where my_var is of type VARCHAR2 and has got the value 'exped9' Whilst the following will come up with the ORA-01403 error: SELECT t$orno INTO eskaria FROM TTDSLS805501 WHERE t$user = my_var; where my_var is of type VARCHAR2 and has got the value 'exped9' Is this normal behavior? why does not the "SELECT t$orno FROM TTDSLS805501 WHERE t$user = 'exped9';" statement return 0 rows in the first place? Fermin. -Mensaje original- De: Tim Gorman [mailto:[EMAIL PROTECTED] Enviado el: miércoles, 10 de septiembre de 2003 10:44 Para: Multiple recipients of list ORACLE-L Asunto: Re: ORA-01403 error, help!!! Unless the stored procedure was created with invoker's rights, then it is probably executing using the permissions and schema of the account that owns it. Who is the owner of the stored procedure? Is it different from who you are logged into SQL*Plus as? The explanation might be that, through some reason like synonyms pointing in different directions from the different accounts, the name TTDSLS805501 might be resolving to different tables altogether... on 9/9/03 10:09 AM, Fermin Bernaus at [EMAIL PROTECTED] wrote: > > If logged in SQL Plus the following SQL returns just one row: > > SELECT t$orno FROM ttdsls805501 WHERE t$user = 'exped9' GROUP BY t$orno; > > If I do use the same SELECT statement inside a stored procedure and have the > returning value stored in a local variable: > > SELECT t$orno INTO eskaria FROM ttdsls805501 WHERE t$user = 'exped9' GROUP BY > t$orno; > > where eskaria has been declared as: > > eskaria ttdsls805501.t$orno%TYPE; > > I get ORA-01403. I have no clue why I am getting this error, can you help > please? > > Many thanks! > > . > Fermín Bernaus Berraondo > Dpto. de Informática > SAMMIC, S.A. > [EMAIL PROTECTED] > http://www.sammic.com > Telf. +34 - 943 157 331 > Fax +34 - 943 151 276 > . -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Tim Gorman INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Fermin Bernaus INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (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: Replication in Oracle RDBMS
For replication docs, go to: http://download-west.oracle.com/docs/cd/A91202_01/901_doc/server.901/a87499/toc.htm Fermin. -Mensaje original- De: Veeraraju_Mareddi [mailto:[EMAIL PROTECTED] Enviado el: miércoles, 10 de septiembre de 2003 13:09 Para: Multiple recipients of list ORACLE-L Asunto: Replication in Oracle RDBMS Dear Friends, Can somebody send me / direct me to startup documents for ORacle Replication. TIA, Rajuveera ** This email (including any attachments) is intended for the sole use of the intended recipient/s and may contain material that is CONFIDENTIAL AND PRIVATE COMPANY INFORMATION. Any review or reliance by others or copying or distribution or forwarding of any or all of the contents in this message is STRICTLY PROHIBITED. If you are not the intended recipient, please contact the sender by email and delete all copies; your cooperation in this regard is appreciated. ** -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Veeraraju_Mareddi INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (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: Fermin Bernaus INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (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: Weird behavior with VARCHAR fields (was: ORA-01403 error,
Yet I do not understand why it returns a row in SQLPlus and it does not in a stored procedure... The problem is now fixed, but I'd like to know the reason it won't work leaving it as it was. It also fails if I put a string instead of a variable in the stored procedure. Thanks! Fermin. -Mensaje original- De: Tim Gorman [mailto:[EMAIL PROTECTED] Enviado el: miércoles, 10 de septiembre de 2003 16:50 Para: Multiple recipients of list ORACLE-L Asunto: Re: Weird behavior with VARCHAR fields (was: ORA-01403 error, Fermin, You are running into the well documented behavior of the CHAR datatype. Use VARCHAR2 instead if you wish to avoid those pitfalls. Hope this helps... -Tim on 9/10/03 3:29 AM, Fermin Bernaus at [EMAIL PROTECTED] wrote: > > Thank you all of you who answered to my first question. > > No, the problem has nothing to do with permissions. The record existed in the > table, but here it is what I discovered. Can anyone explain please? It may be > the normal Oracle behavior, but I believe it was not working like this under > 7.3.4 (now we have 8.0.3). I can not confirm though. > > TTDSLS805501 table definition is as follows: > > NameNull?Type > T$ORNONOT NULLNUMBER > T$USERNOT NULLCHAR(10) > > If I do an INSERT like the following: > > INSERT INTO TTDSLS805501 VALUES (151124 , 'exped9') > > The value 'exped9' for the field T$USER is 6 characters long, while the > definition has got 10. After the insertion, I find that the field for the new > record is 10 chars long, the last 4 characters being filled with blanks. > > But here comes the weird behavior. If I construct the 2 following SELECT on > this table from SQLPlus, both work OK and return the expected row: > > SELECT t$orno FROM TTDSLS805501 WHERE t$user = 'exped9';-> 1 row returned > SELECT t$orno FROM TTDSLS805501 WHERE t$user = 'exped9';-> 1 row > returned > > But within a stored procedure and using a local variable the following will > work OK: > > SELECT t$orno INTO eskaria FROM TTDSLS805501 WHERE t$user = my_var; > > where my_var is of type VARCHAR2 and has got the value 'exped9' > > Whilst the following will come up with the ORA-01403 error: > > SELECT t$orno INTO eskaria FROM TTDSLS805501 WHERE t$user = my_var; > > where my_var is of type VARCHAR2 and has got the value 'exped9' > > Is this normal behavior? why does not the "SELECT t$orno FROM TTDSLS805501 > WHERE t$user = 'exped9';" statement return 0 rows in the first place? > > Fermin. > > > -Mensaje original- > De: Tim Gorman [mailto:[EMAIL PROTECTED] > Enviado el: miércoles, 10 de septiembre de 2003 10:44 > Para: Multiple recipients of list ORACLE-L > Asunto: Re: ORA-01403 error, help!!! > > > Unless the stored procedure was created with invoker's rights, then it is > probably executing using the permissions and schema of the account that owns > it. Who is the owner of the stored procedure? Is it different from who you > are logged into SQL*Plus as? > > The explanation might be that, through some reason like synonyms pointing in > different directions from the different accounts, the name TTDSLS805501 > might be resolving to different tables altogether... > > > > on 9/9/03 10:09 AM, Fermin Bernaus at [EMAIL PROTECTED] wrote: > >> >> If logged in SQL Plus the following SQL returns just one row: >> >> SELECT t$orno FROM ttdsls805501 WHERE t$user = 'exped9' GROUP BY t$orno; >> >> If I do use the same SELECT statement inside a stored procedure and have the >> returning value stored in a local variable: >> >> SELECT t$orno INTO eskaria FROM ttdsls805501 WHERE t$user = 'exped9' GROUP BY >> t$orno; >> >> where eskaria has been declared as: >> >> eskaria ttdsls805501.t$orno%TYPE; >> >> I get ORA-01403. I have no clue why I am getting this error, can you help >> please? >> >> Many thanks! >> >> . >> Fermín Bernaus Berraondo >> Dpto. de Informática >> SAMMIC, S.A. >> [EMAIL PROTECTED] >> http://www.sammic.com >> Telf. +34 - 943 157 331 >> Fax +34 - 943 151 276 >> . -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Tim Gorman INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services --
Which process is taking up so much CPU???
I usually track our HP-UX 11.0 system with the 'top' command so I can notice when the system is under slow performance. If that happens, I use Toad to look for any active Oracle SQL query which may be heavy enough for degrading the performance. I think my question is simple, but since I am a newbie on this... how can I see who is executing an Oracle SQL that is taking all our CPU provided that I only see his PID with the TOP command? I only see the oracle process, but I don't know how to get the username and the SQL beside him. Thank you for your answers! Fermin. -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Fermin Bernaus Berraondo INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (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: Which process is taking up so much CPU???
Thank you all for your help. I wonder where I should set TIMED STATISTICS = TRUE, if any of you has the time to answer I'd be grateful, but I will look for it in the docs. Fermin. -Mensaje original- De: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] nombre de DENNIS WILLIAMS Enviado el: jueves, 27 de marzo de 2003 18:49 Para: Multiple recipients of list ORACLE-L Asunto: RE: Which process is taking up so much CPU??? Fermin The spid column in the v$process column matches the Unix process i.d. You may need to track it back through the Oracle shadow process. Dennis Williams DBA, 40%OCP, 100% DBA Lifetouch, Inc. [EMAIL PROTECTED] -Original Message- Sent: Thursday, March 27, 2003 9:14 AM To: Multiple recipients of list ORACLE-L I usually track our HP-UX 11.0 system with the 'top' command so I can notice when the system is under slow performance. If that happens, I use Toad to look for any active Oracle SQL query which may be heavy enough for degrading the performance. I think my question is simple, but since I am a newbie on this... how can I see who is executing an Oracle SQL that is taking all our CPU provided that I only see his PID with the TOP command? I only see the oracle process, but I don't know how to get the username and the SQL beside him. Thank you for your answers! Fermin. -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Fermin Bernaus Berraondo INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (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: DENNIS WILLIAMS INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Fermin Bernaus Berraondo INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (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: Which process is taking up so much CPU???
It works under Oracle 8.0.6 as well, that's our platform version and I successfully run your queries. Thanks! -Mensaje original- De: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] nombre de Jeremiah Wilton Enviado el: viernes, 28 de marzo de 2003 16:19 Para: Multiple recipients of list ORACLE-L Asunto: RE: Which process is taking up so much CPU??? On Fri, 28 Mar 2003, DENNIS WILLIAMS wrote: > Fermin >Add this line to your init.ora file. > timed_statistics = true >Then shutdown, startup your Oracle instance. I would hasten to point out that this parameter can be set dynamically using alter system from at least 8.1.x forward. Thus, restarting the instance is unnecessary and only reduces availability. -- Jeremiah Wilton http://www.speakeasy.net/~jwilton > -Original Message- > Sent: Friday, March 28, 2003 6:24 AM > To: Multiple recipients of list ORACLE-L > > I wonder where I should set TIMED STATISTICS = TRUE, if any of you > has the time to answer I'd be grateful, but I will look for it in the docs. -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Jeremiah Wilton INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (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: Fermin Bernaus Berraondo INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (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: Which process is taking up so much CPU???
Dennis, you seem to be have a master in Oracle! please can you help, I can see the SPID column under table v$process, but how do I link it to table v$session so that I actually know which UNIX process it corresponds to. I think there must be another table that links both of them; you talk about the shadow process, where is it or where can I get more info on it. Thank you for your time. Fermin. -Mensaje original- De: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] nombre de DENNIS WILLIAMS Enviado el: jueves, 27 de marzo de 2003 18:49 Para: Multiple recipients of list ORACLE-L Asunto: RE: Which process is taking up so much CPU??? Fermin The spid column in the v$process column matches the Unix process i.d. You may need to track it back through the Oracle shadow process. Dennis Williams DBA, 40%OCP, 100% DBA Lifetouch, Inc. [EMAIL PROTECTED] -Original Message- Sent: Thursday, March 27, 2003 9:14 AM To: Multiple recipients of list ORACLE-L I usually track our HP-UX 11.0 system with the 'top' command so I can notice when the system is under slow performance. If that happens, I use Toad to look for any active Oracle SQL query which may be heavy enough for degrading the performance. I think my question is simple, but since I am a newbie on this... how can I see who is executing an Oracle SQL that is taking all our CPU provided that I only see his PID with the TOP command? I only see the oracle process, but I don't know how to get the username and the SQL beside him. Thank you for your answers! Fermin. -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Fermin Bernaus Berraondo INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (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: Which process is taking up so much CPU???
I just found that v$session.paddr = v$process.addr is the join that has to be done. Right? -Mensaje original- De: Fermin Bernaus Berraondo [mailto:[EMAIL PROTECTED] Enviado el: lunes, 31 de marzo de 2003 18:01 Para: '[EMAIL PROTECTED]' Asunto: RE: Which process is taking up so much CPU??? Dennis, you seem to be have a master in Oracle! please can you help, I can see the SPID column under table v$process, but how do I link it to table v$session so that I actually know which UNIX process it corresponds to. I think there must be another table that links both of them; you talk about the shadow process, where is it or where can I get more info on it. Thank you for your time. Fermin. -Mensaje original- De: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] nombre de DENNIS WILLIAMS Enviado el: jueves, 27 de marzo de 2003 18:49 Para: Multiple recipients of list ORACLE-L Asunto: RE: Which process is taking up so much CPU??? Fermin The spid column in the v$process column matches the Unix process i.d. You may need to track it back through the Oracle shadow process. Dennis Williams DBA, 40%OCP, 100% DBA Lifetouch, Inc. [EMAIL PROTECTED] -Original Message- Sent: Thursday, March 27, 2003 9:14 AM To: Multiple recipients of list ORACLE-L I usually track our HP-UX 11.0 system with the 'top' command so I can notice when the system is under slow performance. If that happens, I use Toad to look for any active Oracle SQL query which may be heavy enough for degrading the performance. I think my question is simple, but since I am a newbie on this... how can I see who is executing an Oracle SQL that is taking all our CPU provided that I only see his PID with the TOP command? I only see the oracle process, but I don't know how to get the username and the SQL beside him. Thank you for your answers! Fermin. -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Fermin Bernaus Berraondo INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: how to calculate table size
Hi Ravindra, Use the following, supposing your db_block_size is 2048 (change as appropiate). SELECT segment_type, segment_name,BLOCKS*2048/1024 "Kb" FROM DBA_SEGMENTS WHERE OWNER=UPPER('') AND SEGMENT_NAME = UPPER(''); You should substract emptied blocks from this table, using: ANALYZE TABLE . ESTIMATE STATISTICS; SELECT TABLE_NAME, EMPTY_BLOCKS*2048/1024 "Kb" FROM DBA_TABLES WHERE OWNER=UPPER('') AND TABLE_NAME = UPPER(''); This will give you how many kb are occupied by empty blocks, so substract this amount from the prior result. Hope this helps. -Mensaje original- De: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] nombre de Basavaraja, Ravindra Enviado el: martes, 01 de abril de 2003 1:24 Para: Multiple recipients of list ORACLE-L Asunto: how to calculate table size Hi, Anyone having any formula to calculate table size?Basically to estimate the growth of table over a peroid of time. I have the row_size,db_block_size.How do i get the table size. Thanks -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Basavaraja, Ravindra INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (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: Fermin Bernaus Berraondo INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: how to calculate table size
I've been using this query for both normal and empty tables and works so far. For those tables with no data in them (or that have suffered no deletion) you can ommit the second query since the table should not have any emptied block. Emptied blocks are those who have been occupied by data from the table but that have been deleted; for these, Oracle marks them as deleted but are still asigned to a table. I do not know if you want to consider this free space as part of the table or not. There is a way to deallocate unused space to a table that has been previously used. You must use 'alter table ... deallocate' for that. You have explanations on this in the manual, check: http://download-west.oracle.com/docs/cd/A91202_01/901_doc/server.901/a90125/statements_32.htm#2080417 I am sending this mail to the list as well, I am a learner and do not consider myself an expert, maybe someone else can join and comment something. -Mensaje original- De: Basavaraja, Ravindra [mailto:[EMAIL PROTECTED] Enviado el: martes, 01 de abril de 2003 20:41 CC: '[EMAIL PROTECTED]' Asunto: RE: how to calculate table size Hi Fermin, Thanks for your reply. I am estimating the growth of database tables for a new database and many tables don't have any data. Can I still use the same queries to estimate the size of the tables or do you have anything different? Thanks -Original Message- Sent: Tuesday, April 01, 2003 4:24 AM To: Multiple recipients of list ORACLE-L Hi Ravindra, Use the following, supposing your db_block_size is 2048 (change as appropiate). SELECT segment_type, segment_name,BLOCKS*2048/1024 "Kb" FROM DBA_SEGMENTS WHERE OWNER=UPPER('') AND SEGMENT_NAME = UPPER(''); You should substract emptied blocks from this table, using: ANALYZE TABLE . ESTIMATE STATISTICS; SELECT TABLE_NAME, EMPTY_BLOCKS*2048/1024 "Kb" FROM DBA_TABLES WHERE OWNER=UPPER('') AND TABLE_NAME = UPPER(''); This will give you how many kb are occupied by empty blocks, so substract this amount from the prior result. Hope this helps. -Mensaje original- De: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] nombre de Basavaraja, Ravindra Enviado el: martes, 01 de abril de 2003 1:24 Para: Multiple recipients of list ORACLE-L Asunto: how to calculate table size Hi, Anyone having any formula to calculate table size?Basically to estimate the growth of table over a peroid of time. I have the row_size,db_block_size.How do i get the table size. Thanks -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Basavaraja, Ravindra INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (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: Fermin Bernaus Berraondo INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (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: Fermin Bernaus Berraondo INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (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: Virus.
Our antivirus program did not catch it as well, maybe a new variant ??? -Mensaje original- De: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] nombre de April Wells Enviado el: miercoles, 02 de abril de 2003 15:04 Para: Multiple recipients of list ORACLE-L Asunto: RE: Virus. I got the one that Craig sent out the notice on. from Chris as well but there WAS an attachment... AvrilLavinge.exe I sent it to our help desk, who scanned the attachment and it came back clean, so "we" are dismissing it... but it did give me great pause because there was an attachment. ajw -Original Message- Sent: Wednesday, April 02, 2003 6:44 AM To: Multiple recipients of list ORACLE-L Just had a virus come in to the list via [EMAIL PROTECTED] (who I've copied directly, so "heads up Chris" ;)). The virus is WORM_LIRVA.A, I didn't get infected (thankfully attachements are stripped).. More details about the worm can be found here: http://www.trendmicro.com/vinfo/virusencyclo/default5.asp?VName=WORM_LIRVA.A &VSect=T Mark === Mark Leith | T: +44 (0)1905 330 281 Sales & Marketing | F: +44 (0)870 127 5283 Cool Tools UK Ltd | E: [EMAIL PROTECTED] === http://www.cool-tools.co.uk Maximising throughput & performance -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Mark Leith INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). The information contained in this communication, including attachments, is strictly confidential and for the intended use of the addressee only; it may also contain proprietary, price sensitive, or legally privileged information. Notice is hereby given that any disclosure, distribution, dissemination, use, or copying of the information by anyone other than the intended recipient is strictly prohibited and may be illegal. If you have received this communication in error, please notify the sender immediately by reply e-mail, delete this communication, and destroy all copies. Corporate Systems, Inc. has taken reasonable precautions to ensure that any attachment to this e-mail has been swept for viruses. We specifically disclaim all liability and will accept no responsibility for any damage sustained as a result of software viruses and advise you to carry out your own virus checks before opening any attachment. -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: April Wells INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (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: Fermin Bernaus Berraondo INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Cannot allocate new log - checkpoint not complete
I think I am having problems with my redologs. Under normal circumstances no errors arise, but if I do a massive import of data as I was doing last night, this is what alertSID.log shows from time to time: Wed Apr 2 23:29:52 2003 Thread 1 advanced to log sequence 557295 Current log# 3 seq# 557295 mem# 0: /baandata/oradata/baan/redobaan03.log Wed Apr 2 23:31:11 2003 Thread 1 cannot allocate new log, sequence 557296 Checkpoint not complete Current log# 3 seq# 557295 mem# 0: /baandata/oradata/baan/redobaan03.log Wed Apr 2 23:31:50 2003 In that exact time, everything freezes and the database is dead until a new redolog can be used. I have 3 redologs 50 Mb each. I've read that the error is because too much data is trying to get into the redologs and all of them are full, Oracle does not have the time to reuse a redolog and has to wait until the redolog is ready to be reused. So the solution seems to make these redolog files bigger or to create new ones. What are the side effects of one or the other? will performance under normal work be penalised? .. Fermín Bernaus Berraondo Dpto. de Informática SAMMIC, S.A. [EMAIL PROTECTED] http://www.sammic.com Telf. +34 - 943 157 331 Fax +34 - 943 151 276 .. -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Fermin Bernaus Berraondo INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (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: Cannot allocate new log - checkpoint not complete
I dissagree, they will be bigger but there will be less of them. If the amount of processed data does not change, I do not think changing the size of the redolog files should affect the total amount fo bytes to be backeup up Thanks for the recommendation anyway ;) Cheers, Fermin. -Mensaje original- De: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] nombre de Stefan Jahnke Enviado el: jueves, 03 de abril de 2003 15:44 Para: Multiple recipients of list ORACLE-L Asunto: AW: Cannot allocate new log - checkpoint not complete Hi I would suggest to increase the redo log size. Doesn't effect you during daily operation, but prevents the database from hanging during nightly batches. No side effects I can think of (except for the fact that, of course, it will take you longer to backup the archived logs since the files are bigger, duh ;). Good luck Stefan Stefan Jahnke Consultant BOV Aktiengesellschaft Voice: +49 201 - 4513-298 Fax: +49 201 - 4513-149 mailto: [EMAIL PROTECTED] Please remove nospam to contact me via email. visit our website: http://www.bov.de subscribe to our newsletter: http://www.bov.de/presse/newsletter.asp Sicherheitsluecken mit IT-Security-Konzepten von BOV effizient schliessen! Weitere Informationen unter +49 201/45 13-240 oder E-Mail an mailto:[EMAIL PROTECTED] Wie Sie wissen, koennen ueber das Internet versandte E-Mails leicht unter fremden Namen erstellt oder manipuliert werden. Aus diesem Grunde bitten wir um Verstaendnis dafuer, dass wir zu Ihrem und unserem Schutz die rechtliche Verbindlichkeit der vorstehenden Erklaerungen und Aeusserungen ausschliessen. As you are probably aware, e-mails sent via the Internet can easily be copied or manipulated by third parties. For this reason we would ask for your understanding that, for your own protection and ours, we must decline all legal responsibility for the validity of the statements and comments given above. -Ursprüngliche Nachricht- Von: Fermin Bernaus Berraondo [mailto:[EMAIL PROTECTED] Gesendet: Donnerstag, 3. April 2003 10:04 An: Multiple recipients of list ORACLE-L Betreff: Cannot allocate new log - checkpoint not complete I think I am having problems with my redologs. Under normal circumstances no errors arise, but if I do a massive import of data as I was doing last night, this is what alertSID.log shows from time to time: Wed Apr 2 23:29:52 2003 Thread 1 advanced to log sequence 557295 Current log# 3 seq# 557295 mem# 0: /baandata/oradata/baan/redobaan03.log Wed Apr 2 23:31:11 2003 Thread 1 cannot allocate new log, sequence 557296 Checkpoint not complete Current log# 3 seq# 557295 mem# 0: /baandata/oradata/baan/redobaan03.log Wed Apr 2 23:31:50 2003 In that exact time, everything freezes and the database is dead until a new redolog can be used. I have 3 redologs 50 Mb each. I've read that the error is because too much data is trying to get into the redologs and all of them are full, Oracle does not have the time to reuse a redolog and has to wait until the redolog is ready to be reused. So the solution seems to make these redolog files bigger or to create new ones. What are the side effects of one or the other? will performance under normal work be penalised? .. Fermín Bernaus Berraondo Dpto. de Informática SAMMIC, S.A. [EMAIL PROTECTED] http://www.sammic.com Telf. +34 - 943 157 331 Fax +34 - 943 151 276 .. -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Fermin Bernaus Berraondo INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (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: Stefan Jahnke INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (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: Fermin Bernaus Berraondo INET: [EMAI
RE: Cannot allocate new log - checkpoint not complete
Dennis, This is our actual distribution: Datafiles belonging to data in a separate disk, name it /baandata Datafiles belonging to index in a separate disk, name it /baanindex And 3 redolog files, two of them in another two separate disks, and the third one located in the same device as the data files (/baandata). All of them are mirrored disks. Your comment makes sense, but if keeping datafiles and one of the redolog files in the same device should affect performance, then I wonder why the "cannot allocate new log, checkpoint not complete" message is affecting to the 3 redolog files and not only to the one located in that datafile device. I did not think on this. Anyway I have no more disks in which I can split the redologs... I can not wait for your comments! Regards, Fermin. -Mensaje original- De: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] nombre de DENNIS WILLIAMS Enviado el: jueves, 03 de abril de 2003 17:04 Para: Multiple recipients of list ORACLE-L Asunto: RE: Cannot allocate new log - checkpoint not complete Fermin - Connor's reply sparked an idea. By any chance do you have your redo logs on the same device as your data files? Dennis Williams DBA, 40%OCP, 100% DBA Lifetouch, Inc. [EMAIL PROTECTED] -Original Message- Sent: Thursday, April 03, 2003 5:04 AM To: Multiple recipients of list ORACLE-L Basically as the message suggests the redo cannot be recycled until the checkpoint has completed flushing out the cache. A *workaround* is to add redo log (size or number) but its really a heads-up about your I/O subsystem not being up to keep up under stress. hth connor --- Fermin Bernaus Berraondo <[EMAIL PROTECTED]> wrote: > > I think I am having problems with my redologs. > Under normal circumstances no errors arise, but if I > do a massive import of data as I was doing last > night, this is what alertSID.log shows from time to > time: > > Wed Apr 2 23:29:52 2003 > Thread 1 advanced to log sequence 557295 > Current log# 3 seq# 557295 mem# 0: > /baandata/oradata/baan/redobaan03.log > Wed Apr 2 23:31:11 2003 > Thread 1 cannot allocate new log, sequence 557296 > Checkpoint not complete > Current log# 3 seq# 557295 mem# 0: > /baandata/oradata/baan/redobaan03.log > Wed Apr 2 23:31:50 2003 > > In that exact time, everything freezes and the > database is dead until a new redolog can be used. > > I have 3 redologs 50 Mb each. I've read that the > error is because too much data is trying to get into > the redologs and all of them are full, Oracle does > not have the time to reuse a redolog and has to wait > until the redolog is ready to be reused. > > So the solution seems to make these redolog files > bigger or to create new ones. What are the side > effects of one or the other? will performance under > normal work be penalised? > > .. > Fermín Bernaus Berraondo > Dpto. de Informática > SAMMIC, S.A. > [EMAIL PROTECTED] > http://www.sammic.com > Telf. +34 - 943 157 331 > Fax +34 - 943 151 276 > .. > > -- > Please see the official ORACLE-L FAQ: > http://www.orafaq.net > -- > Author: Fermin Bernaus Berraondo > INET: [EMAIL PROTECTED] > > Fat City Network Services-- 858-538-5051 > http://www.fatcity.com > San Diego, California-- Mailing list and web > hosting services > - > To REMOVE yourself from this mailing list, send an > E-Mail message > to: [EMAIL PROTECTED] (note EXACT spelling of > 'ListGuru') and in > the message BODY, include a line containing: UNSUB > ORACLE-L > (or the name of mailing list you want to be removed > from). You may > also send the HELP command for other information > (like subscribing). > = Connor McDonald web: http://www.oracledba.co.uk web: http://www.oaktable.net email: [EMAIL PROTECTED] "GIVE a man a fish and he will eat for a day. But TEACH him how to fish, and...he will sit in a boat and drink beer all day" __ Yahoo! Plus For a better Internet experience http://www.yahoo.co.uk/btoffer -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: =?iso-8859-1?q?Connor=20McDonald?= INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru')
RE: Cannot allocate new log - checkpoint not complete
No problem with the archive log management scripts, I back up archived log files daily, no matter how many of them we have. -Mensaje original- De: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] nombre de Reardon, Bruce (CALBBAY) Enviado el: viernes, 04 de abril de 2003 3:28 Para: Multiple recipients of list ORACLE-L Asunto: RE: Cannot allocate new log - checkpoint not complete There are some other effects I can think of. Up to you if these are important / significant to your users. If you make them bigger and you have a standby database then the standby might end up being further behind production (unless you have a script to workaround this) and also in this case you may then not get the files transferring successfully (depends on your network etc). Also, if you make them bigger and you have a loss of all redo logs (hopefully unlikely if they are mirrored on mirrored disks) then you will lose more data as it will be a longer period of time since the last archive log was created. And what about your archive log management scripts - do they keep x days worth of files (in which case the volume of archive log on disk will not change) or do they keep y files - in this case the volume of disks would increase unless the script(s) are altered. Regards, Bruce Reardon -Original Message- Sent: Friday, 4 April 2003 3:44 AM To: Multiple recipients of list ORACLE-L Correct. The only potential disadvantage is that recovery will take longer when bringing up the database after a crash. Jay Miller -Original Message- Sent: Thursday, April 03, 2003 9:39 AM To: Multiple recipients of list ORACLE-L I dissagree, they will be bigger but there will be less of them. If the amount of processed data does not change, I do not think changing the size of the redolog files should affect the total amount fo bytes to be backeup up Thanks for the recommendation anyway ;) Cheers, Fermin. -Mensaje original- De: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] nombre de Stefan Jahnke Enviado el: jueves, 03 de abril de 2003 15:44 Para: Multiple recipients of list ORACLE-L Asunto: AW: Cannot allocate new log - checkpoint not complete Hi I would suggest to increase the redo log size. Doesn't effect you during daily operation, but prevents the database from hanging during nightly batches. No side effects I can think of (except for the fact that, of course, it will take you longer to backup the archived logs since the files are bigger, duh ;). Good luck Stefan Stefan Jahnke Consultant BOV Aktiengesellschaft Voice: +49 201 - 4513-298 Fax: +49 201 - 4513-149 mailto: [EMAIL PROTECTED] Please remove nospam to contact me via email. visit our website: http://www.bov.de subscribe to our newsletter: http://www.bov.de/presse/newsletter.asp Sicherheitsluecken mit IT-Security-Konzepten von BOV effizient schliessen! Weitere Informationen unter +49 201/45 13-240 oder E-Mail an mailto:[EMAIL PROTECTED] Wie Sie wissen, koennen ueber das Internet versandte E-Mails leicht unter fremden Namen erstellt oder manipuliert werden. Aus diesem Grunde bitten wir um Verstaendnis dafuer, dass wir zu Ihrem und unserem Schutz die rechtliche Verbindlichkeit der vorstehenden Erklaerungen und Aeusserungen ausschliessen. As you are probably aware, e-mails sent via the Internet can easily be copied or manipulated by third parties. For this reason we would ask for your understanding that, for your own protection and ours, we must decline all legal responsibility for the validity of the statements and comments given above. -Ursprüngliche Nachricht- Von: Fermin Bernaus Berraondo [mailto:[EMAIL PROTECTED] Gesendet: Donnerstag, 3. April 2003 10:04 An: Multiple recipients of list ORACLE-L Betreff: Cannot allocate new log - checkpoint not complete I think I am having problems with my redologs. Under normal circumstances no errors arise, but if I do a massive import of data as I was doing last night, this is what alertSID.log shows from time to time: Wed Apr 2 23:29:52 2003 Thread 1 advanced to log sequence 557295 Current log# 3 seq# 557295 mem# 0: /baandata/oradata/baan/redobaan03.log Wed Apr 2 23:31:11 2003 Thread 1 cannot allocate new log, sequence 557296 Checkpoint not complete Current log# 3 seq# 557295 mem# 0: /baandata/oradata/baan/redobaan03.log Wed Apr 2 23:31:50 2003 In that exact time, everything freezes and the database is dead until a new redolog can be used. I have 3 redologs 50 Mb each. I've read that the error is because too much data is trying to get into the redologs and all of them are full, Oracle does not have the time to reuse a redolog and has to wait until the redolog is ready to be reused. So the solution seems to make these redolog files bigger or to create new ones. What are the side effects of one or the other? will performance under normal work be
RE: Cannot allocate new log - checkpoint not complete
So do you think the following distribution will contribute to a better performance: data datafiles - device a index datafiles - device b redolog1 - device c redolog2 - device d redolog3 - device c instead of: data datafiles - device a index datafiles - device b redolog1 - device c redolog2 - device d redolog3 - device a Because I only have 5 devices available. -Mensaje original- De: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] nombre de Connor McDonald Enviado el: viernes, 04 de abril de 2003 11:49 Para: Multiple recipients of list ORACLE-L Asunto: RE: Cannot allocate new log - checkpoint not complete Its not really a particular redo log that is the issue. You've used up redo's (say) 1, 2, 3 and you want to cycle around to 1 but the checkpoint that would free up redo 1 is not yet finished. Thus its not a single redo log that is the problem - the IO rate of the checkpoint is not sufficient quick to avoid the redo cycling around...If one of your redo's is on common datafile disk, this could contribute to this hth connor --- Fermin Bernaus Berraondo <[EMAIL PROTECTED]> wrote: > > Dennis, > > This is our actual distribution: > > Datafiles belonging to data in a separate disk, > name it /baandata > Datafiles belonging to index in a separate disk, > name it /baanindex > > And 3 redolog files, two of them in another two > separate disks, and the third one located in the > same device as the data files (/baandata). > > All of them are mirrored disks. > > Your comment makes sense, but if keeping datafiles > and one of the redolog files in the same device > should affect performance, then I wonder why the > "cannot allocate new log, checkpoint not complete" > message is affecting to the 3 redolog files and not > only to the one located in that datafile device. > > I did not think on this. Anyway I have no more > disks in which I can split the redologs... > > I can not wait for your comments! > > Regards, > > Fermin. > > -Mensaje original- > De: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] > nombre de DENNIS > WILLIAMS > Enviado el: jueves, 03 de abril de 2003 17:04 > Para: Multiple recipients of list ORACLE-L > Asunto: RE: Cannot allocate new log - checkpoint not > complete > > > Fermin - Connor's reply sparked an idea. By any > chance do you have your redo > logs on the same device as your data files? > > Dennis Williams > DBA, 40%OCP, 100% DBA > Lifetouch, Inc. > [EMAIL PROTECTED] > > > -Original Message- > Sent: Thursday, April 03, 2003 5:04 AM > To: Multiple recipients of list ORACLE-L > > > Basically as the message suggests the redo cannot be > recycled until the checkpoint has completed flushing > out the cache. > > A *workaround* is to add redo log (size or number) > but > its really a heads-up about your I/O subsystem not > being up to keep up under stress. > > hth > connor > > --- Fermin Bernaus Berraondo <[EMAIL PROTECTED]> > wrote: > > > I think I am having problems with my redologs. > > Under normal circumstances no errors arise, but if > I > > do a massive import of data as I was doing last > > night, this is what alertSID.log shows from time > to > > time: > > > > Wed Apr 2 23:29:52 2003 > > Thread 1 advanced to log sequence 557295 > > Current log# 3 seq# 557295 mem# 0: > > /baandata/oradata/baan/redobaan03.log > > Wed Apr 2 23:31:11 2003 > > Thread 1 cannot allocate new log, sequence 557296 > > Checkpoint not complete > > Current log# 3 seq# 557295 mem# 0: > > /baandata/oradata/baan/redobaan03.log > > Wed Apr 2 23:31:50 2003 > > > > In that exact time, everything freezes and the > > database is dead until a new redolog can be used. > > > > I have 3 redologs 50 Mb each. I've read that the > > error is because too much data is trying to get > into > > the redologs and all of them are full, Oracle does > > not have the time to reuse a redolog and has to > wait > > until the redolog is ready to be reused. > > > > So the solution seems to make these redolog files > > bigger or to create new ones. What are the side > > effects of one or the other? will performance > under > > normal work be penalised? > > > > .. > > Fermín Bernaus Berraondo > > Dpto. de Informática >
RE: Date difference function
Just substract one to the other and that's it. Regards, Fermin. -Mensaje original-De: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]En nombre de Santosh VarmaEnviado el: lunes, 10 de marzo de 2003 11:39Para: Multiple recipients of list ORACLE-LAsunto: Date difference function Hello list, I want to find the difference between 2 dates.. Any Oracle function for that ??? Thanks and Regards, Santosh
RE: Oracle Enterprise vs Standard
We have BAAN IVc4 and Oracle Enterprise edition at our site. Enterprise Edition has got some advantages over the Standard Edition which would be too lenghty to explain here, but we are not taking advantage of them and, besides, baan does not exploit Oracle's facilities too much and Standard should be enough. So why do we have Enterprise instead of Standard? because Oracle does not provide the Standard edition for our 64-bit HP-UX machine. What a shame :(( I recommend you get the Standard edition, and you can upgrade later on if you need. Regards, .. Fermín Bernaus Berraondo Dpto. de Informática SAMMIC, S.A. [EMAIL PROTECTED] http://www.sammic.com Telf. +34 - 943 157 331 Fax +34 - 943 151 276 .. -Mensaje original- De: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] nombre de Zhou, Tapiwanashe Enviado el: jueves, 13 de marzo de 2003 9:44 Para: Multiple recipients of list ORACLE-L Asunto: FW: Oracle Enterprise vs Standard I need to buy a new Oracle license for 50 users. I have got two different quotes for Oracle Enterprise Server and Oracle Standard; it seems the former is quite expensive. What problems will I get if I buy the cheaper Standard version. This Oracle will be used for BAAN ERP implementation. The environment will be IBM RS6000, AIX 5L and a mixture of LAN and WAN clients Thanks in advance for you advice Regards Tapiwa -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Zhou, Tapiwanashe INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (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: Fermin Bernaus Berraondo INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).