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') and in the message BODY, include a line containing: UNSUB ORACLE-L (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
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 penalised
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 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
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? .. Fermn Bernaus Berraondo Dpto. de Informtica 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: [EMAIL PROTECTED] Fat City Network
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).
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('owner') AND SEGMENT_NAME = UPPER('table_name');
You should substract emptied blocks from this table, using:
ANALYZE TABLE owner.table_name ESTIMATE STATISTICS;
SELECT TABLE_NAME, EMPTY_BLOCKS*2048/1024 Kb
FROM DBA_TABLES
WHERE OWNER=UPPER('owner') AND TABLE_NAME = UPPER('table_name');
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('owner') AND SEGMENT_NAME = UPPER('table_name');
You should substract emptied blocks from this table, using:
ANALYZE TABLE owner.table_name ESTIMATE STATISTICS;
SELECT TABLE_NAME, EMPTY_BLOCKS*2048/1024 Kb
FROM DBA_TABLES
WHERE OWNER=UPPER('owner') AND TABLE_NAME = UPPER('table_name');
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: 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: 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).
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: 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).
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
