RE: RE: Rollback Segment Problem
, The transaction id (which is recorded in the ITL) consists of 3 components. Undo Segment Number, Slot Number and Wrap Number. THe max value of the wrap number is limited by UB4MAXVAL. (Approx 4G in numbers) and once it reaches the MAXVAL the rollback segment is considered as DEAD. i.e. it can not hold undo information for any transactions., THis condition is called as DEATH of a Rollback segment. THis is true for SMU also. Best Regards, K Gopalakrishnan Bangalore, INDIA -Original Message- Sent: Thursday, February 14, 2002 2:35 AM Hi Ganesh, Could u please elaborate, i did not get what u said. What do u mean by Rollback segment dying. I did not pick up the earlier mails so maybe thats why. thanks and reg Guru On Thu, 14 Feb 2002 K Gopalakrishnan wrote : You never need to recreate the rollback segments (DROP and CREATE) unless the rollback segment dies. THe rollback segment will die after approx 4M (the number is not very accurate.. okay,, some number) transactions. THis is the only case I need to drop and recreate them. Best Regards, K Gopalakrishnan Bangalore, INDIA -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]]On Behalf Of Mohammad Rafiq Sent: Wednesday, February 13, 2002 9:54 AM To: Multiple recipients of list ORACLE-L Subject: RE: Rollback Segment Problem In many cases you will have to take RB segments offline and drop them, then re-create _ Do You Yahoo!? Get your free @yahoo.com address at http://mail.yahoo.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: K Gopalakrishnan INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Gogala, Mladen INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). _ Do You Yahoo!? Get your free @yahoo.com address at http://mail.yahoo.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: K Gopalakrishnan INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Gogala, Mladen INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). _ Do You Yahoo!? Get your free @yahoo.com address at http://mail.yahoo.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: K Gopalakrishnan INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list
RE: RE: Rollback Segment Problem
to be executed by the ways of the weight watchers! -Original Message- Sent: Thursday, February 14, 2002 2:36 PM To: Multiple recipients of list ORACLE-L Hi G_DBA , The transaction id (which is recorded in the ITL) consists of 3 components. Undo Segment Number, Slot Number and Wrap Number. THe max value of the wrap number is limited by UB4MAXVAL. (Approx 4G in numbers) and once it reaches the MAXVAL the rollback segment is considered as DEAD. i.e. it can not hold undo information for any transactions., THis condition is called as DEATH of a Rollback segment. THis is true for SMU also. Best Regards, K Gopalakrishnan Bangalore, INDIA -Original Message- Sent: Thursday, February 14, 2002 2:35 AM Hi Ganesh, Could u please elaborate, i did not get what u said. What do u mean by Rollback segment dying. I did not pick up the earlier mails so maybe thats why. thanks and reg Guru On Thu, 14 Feb 2002 K Gopalakrishnan wrote : You never need to recreate the rollback segments (DROP and CREATE) unless the rollback segment dies. THe rollback segment will die after approx 4M (the number is not very accurate.. okay,, some number) transactions. THis is the only case I need to drop and recreate them. Best Regards, K Gopalakrishnan Bangalore, INDIA -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]]On Behalf Of Mohammad Rafiq Sent: Wednesday, February 13, 2002 9:54 AM To: Multiple recipients of list ORACLE-L Subject: RE: Rollback Segment Problem In many cases you will have to take RB segments offline and drop them, then re-create _ Do You Yahoo!? Get your free @yahoo.com address at http://mail.yahoo.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: K Gopalakrishnan INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Gogala, Mladen INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). _ Do You Yahoo!? Get your free @yahoo.com address at http://mail.yahoo.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: K Gopalakrishnan INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Gogala, Mladen INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). _ Do You Yahoo!? Get your free @yahoo.com address at http://mail.yahoo.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: K Gopalakrishnan INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send
RE: RE: Rollback Segment Problem
Hi G_DBA , The transaction id (which is recorded in the ITL) consists of 3 components. Undo Segment Number, Slot Number and Wrap Number. THe max value of the wrap number is limited by UB4MAXVAL. (Approx 4G in numbers) and once it reaches the MAXVAL the rollback segment is considered as DEAD. i.e. it can not hold undo information for any transactions., THis condition is called as DEATH of a Rollback segment. THis is true for SMU also. Best Regards, K Gopalakrishnan Bangalore, INDIA -Original Message- Sent: Thursday, February 14, 2002 2:35 AM Hi Ganesh, Could u please elaborate, i did not get what u said. What do u mean by Rollback segment dying. I did not pick up the earlier mails so maybe thats why. thanks and reg Guru On Thu, 14 Feb 2002 K Gopalakrishnan wrote : You never need to recreate the rollback segments (DROP and CREATE) unless the rollback segment dies. THe rollback segment will die after approx 4M (the number is not very accurate.. okay,, some number) transactions. THis is the only case I need to drop and recreate them. Best Regards, K Gopalakrishnan Bangalore, INDIA -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]]On Behalf Of Mohammad Rafiq Sent: Wednesday, February 13, 2002 9:54 AM To: Multiple recipients of list ORACLE-L Subject: RE: Rollback Segment Problem In many cases you will have to take RB segments offline and drop them, then re-create _ Do You Yahoo!? Get your free @yahoo.com address at http://mail.yahoo.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: K Gopalakrishnan INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: RE: Rollback Segment Problem
How long does a database have to remain running in order for a rollback segment to go to the happy hunting grounds? Have you ever seen such an event? A simple instance restart will cure that disease. Anyway, the only thing that I can envision to cause this is to have an OLTP database with something like 100 online users doing more then 4000 transactions a day and the instance is configured with a single rollback segment. In that case the DBA deserves to be executed by the ways of the weight watchers! -Original Message- Sent: Thursday, February 14, 2002 2:36 PM To: Multiple recipients of list ORACLE-L Hi G_DBA , The transaction id (which is recorded in the ITL) consists of 3 components. Undo Segment Number, Slot Number and Wrap Number. THe max value of the wrap number is limited by UB4MAXVAL. (Approx 4G in numbers) and once it reaches the MAXVAL the rollback segment is considered as DEAD. i.e. it can not hold undo information for any transactions., THis condition is called as DEATH of a Rollback segment. THis is true for SMU also. Best Regards, K Gopalakrishnan Bangalore, INDIA -Original Message- Sent: Thursday, February 14, 2002 2:35 AM Hi Ganesh, Could u please elaborate, i did not get what u said. What do u mean by Rollback segment dying. I did not pick up the earlier mails so maybe thats why. thanks and reg Guru On Thu, 14 Feb 2002 K Gopalakrishnan wrote : You never need to recreate the rollback segments (DROP and CREATE) unless the rollback segment dies. THe rollback segment will die after approx 4M (the number is not very accurate.. okay,, some number) transactions. THis is the only case I need to drop and recreate them. Best Regards, K Gopalakrishnan Bangalore, INDIA -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]]On Behalf Of Mohammad Rafiq Sent: Wednesday, February 13, 2002 9:54 AM To: Multiple recipients of list ORACLE-L Subject: RE: Rollback Segment Problem In many cases you will have to take RB segments offline and drop them, then re-create _ Do You Yahoo!? Get your free @yahoo.com address at http://mail.yahoo.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: K Gopalakrishnan INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Gogala, Mladen INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: RE: Rollback Segment Problem
Gogala, Not quite true.A simple restart will NOT cure that disease. The WRAP# is visible as KTUXESQN in the X$KTUXE. You can check that value by restating the instance. You can simply drop the rollback segment and force the instance to use the other rollback segements, but not by restarting..:) Best Regards, K Gopalakrishnan Bangalore, INDIA -Original Message- Mladen Sent: Thursday, February 14, 2002 12:26 PM To: Multiple recipients of list ORACLE-L How long does a database have to remain running in order for a rollback segment to go to the happy hunting grounds? Have you ever seen such an event? A simple instance restart will cure that disease. Anyway, the only thing that I can envision to cause this is to have an OLTP database with something like 100 online users doing more then 4000 transactions a day and the instance is configured with a single rollback segment. In that case the DBA deserves to be executed by the ways of the weight watchers! -Original Message- Sent: Thursday, February 14, 2002 2:36 PM To: Multiple recipients of list ORACLE-L Hi G_DBA , The transaction id (which is recorded in the ITL) consists of 3 components. Undo Segment Number, Slot Number and Wrap Number. THe max value of the wrap number is limited by UB4MAXVAL. (Approx 4G in numbers) and once it reaches the MAXVAL the rollback segment is considered as DEAD. i.e. it can not hold undo information for any transactions., THis condition is called as DEATH of a Rollback segment. THis is true for SMU also. Best Regards, K Gopalakrishnan Bangalore, INDIA -Original Message- Sent: Thursday, February 14, 2002 2:35 AM Hi Ganesh, Could u please elaborate, i did not get what u said. What do u mean by Rollback segment dying. I did not pick up the earlier mails so maybe thats why. thanks and reg Guru On Thu, 14 Feb 2002 K Gopalakrishnan wrote : You never need to recreate the rollback segments (DROP and CREATE) unless the rollback segment dies. THe rollback segment will die after approx 4M (the number is not very accurate.. okay,, some number) transactions. THis is the only case I need to drop and recreate them. Best Regards, K Gopalakrishnan Bangalore, INDIA -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]]On Behalf Of Mohammad Rafiq Sent: Wednesday, February 13, 2002 9:54 AM To: Multiple recipients of list ORACLE-L Subject: RE: Rollback Segment Problem In many cases you will have to take RB segments offline and drop them, then re-create _ Do You Yahoo!? Get your free @yahoo.com address at http://mail.yahoo.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: K Gopalakrishnan INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Gogala, Mladen INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). _ Do You Yahoo!? Get your free @yahoo.com address at http://mail.yahoo.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: K Gopalakrishnan INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: RE: Rollback Segment Problem
That was my question also. I am agree with Gogala, after bouncing database no of wraps goes to 0(zero). This is normal behaviour with 7.3.4 to 8.1.7 databases. Unfortunately our databases are bounced everyday for snapshot backup and I saw this behaviour everyday... Regards Rafiq Reply-To: [EMAIL PROTECTED] To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Date: Thu, 14 Feb 2002 13:03:40 -0800 Gogala, Not quite true.A simple restart will NOT cure that disease. The WRAP# is visible as KTUXESQN in the X$KTUXE. You can check that value by restating the instance. You can simply drop the rollback segment and force the instance to use the other rollback segements, but not by restarting..:) Best Regards, K Gopalakrishnan Bangalore, INDIA -Original Message- Mladen Sent: Thursday, February 14, 2002 12:26 PM To: Multiple recipients of list ORACLE-L How long does a database have to remain running in order for a rollback segment to go to the happy hunting grounds? Have you ever seen such an event? A simple instance restart will cure that disease. Anyway, the only thing that I can envision to cause this is to have an OLTP database with something like 100 online users doing more then 4000 transactions a day and the instance is configured with a single rollback segment. In that case the DBA deserves to be executed by the ways of the weight watchers! -Original Message- Sent: Thursday, February 14, 2002 2:36 PM To: Multiple recipients of list ORACLE-L Hi G_DBA , The transaction id (which is recorded in the ITL) consists of 3 components. Undo Segment Number, Slot Number and Wrap Number. THe max value of the wrap number is limited by UB4MAXVAL. (Approx 4G in numbers) and once it reaches the MAXVAL the rollback segment is considered as DEAD. i.e. it can not hold undo information for any transactions., THis condition is called as DEATH of a Rollback segment. THis is true for SMU also. Best Regards, K Gopalakrishnan Bangalore, INDIA -Original Message- Sent: Thursday, February 14, 2002 2:35 AM Hi Ganesh, Could u please elaborate, i did not get what u said. What do u mean by Rollback segment dying. I did not pick up the earlier mails so maybe thats why. thanks and reg Guru On Thu, 14 Feb 2002 K Gopalakrishnan wrote : You never need to recreate the rollback segments (DROP and CREATE) unless the rollback segment dies. THe rollback segment will die after approx 4M (the number is not very accurate.. okay,, some number) transactions. THis is the only case I need to drop and recreate them. Best Regards, K Gopalakrishnan Bangalore, INDIA -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]]On Behalf Of Mohammad Rafiq Sent: Wednesday, February 13, 2002 9:54 AM To: Multiple recipients of list ORACLE-L Subject: RE: Rollback Segment Problem In many cases you will have to take RB segments offline and drop them, then re-create _ Do You Yahoo!? Get your free @yahoo.com address at http://mail.yahoo.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: K Gopalakrishnan INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Gogala, Mladen INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). _ Do You Yahoo!? Get your free @yahoo.com address at http://mail.yahoo.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: K Gopalakrishnan INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling
RE: RE: Rollback Segment Problem
Thanks for the correction. I was looking some other column. I am sorry for the mistake. Best Regards, K Gopalakrishnan Bangalore, INDIA -Original Message- Mladen Sent: Thursday, February 14, 2002 2:42 PM To: Multiple recipients of list ORACLE-L Number of wraps gets reset with the instance. Below is the proof. May be after the death of a rollback segment, when can have it's resurrection? What would we name it? A vampire rollback segment? May be we should use documented V$ tables instead of the undocumented X$ ones. pdev3-a:dev:/oracle sqlplus internal SQL*Plus: Release 8.1.7.0.0 - Production on Thu Feb 14 17:26:02 2002 (c) Copyright 2000 Oracle Corporation. All rights reserved. Connected to: Oracle8i Enterprise Edition Release 8.1.7.2.0 - 64bit Production With the Partitioning option JServer Release 8.1.7.2.0 - 64bit Production SQL SQL SQL select usn,wraps from v$rollstat; USN WRAPS -- -- 0 0 2 2 3 3 4 3 5 2 6 2 6 rows selected. SQL sshutdown immediate SP2-0734: unknown command beginning sshutdown ... - rest of line ignored. SQL shutdown immediate Database closed. Database dismounted. ORACLE instance shut down. SQL startup ORACLE instance started. Total System Global Area 114231784 bytes Fixed Size 104936 bytes Variable Size 80039936 bytes Database Buffers 33554432 bytes Redo Buffers 532480 bytes Database mounted. Database opened. SQL select usn,wraps from v$rollstat; USN WRAPS -- -- 0 0 2 0 3 0 4 0 5 0 6 0 6 rows selected. SQL -Original Message- Sent: Thursday, February 14, 2002 4:04 PM To: Multiple recipients of list ORACLE-L Gogala, Not quite true.A simple restart will NOT cure that disease. The WRAP# is visible as KTUXESQN in the X$KTUXE. You can check that value by restating the instance. You can simply drop the rollback segment and force the instance to use the other rollback segements, but not by restarting..:) Best Regards, K Gopalakrishnan Bangalore, INDIA -Original Message- Mladen Sent: Thursday, February 14, 2002 12:26 PM To: Multiple recipients of list ORACLE-L How long does a database have to remain running in order for a rollback segment to go to the happy hunting grounds? Have you ever seen such an event? A simple instance restart will cure that disease. Anyway, the only thing that I can envision to cause this is to have an OLTP database with something like 100 online users doing more then 4000 transactions a day and the instance is configured with a single rollback segment. In that case the DBA deserves to be executed by the ways of the weight watchers! -Original Message- Sent: Thursday, February 14, 2002 2:36 PM To: Multiple recipients of list ORACLE-L Hi G_DBA , The transaction id (which is recorded in the ITL) consists of 3 components. Undo Segment Number, Slot Number and Wrap Number. THe max value of the wrap number is limited by UB4MAXVAL. (Approx 4G in numbers) and once it reaches the MAXVAL the rollback segment is considered as DEAD. i.e. it can not hold undo information for any transactions., THis condition is called as DEATH of a Rollback segment. THis is true for SMU also. Best Regards, K Gopalakrishnan Bangalore, INDIA -Original Message- Sent: Thursday, February 14, 2002 2:35 AM Hi Ganesh, Could u please elaborate, i did not get what u said. What do u mean by Rollback segment dying. I did not pick up the earlier mails so maybe thats why. thanks and reg Guru On Thu, 14 Feb 2002 K Gopalakrishnan wrote : You never need to recreate the rollback segments (DROP and CREATE) unless the rollback segment dies. THe rollback segment will die after approx 4M (the number is not very accurate.. okay,, some number) transactions. THis is the only case I need to drop and recreate them. Best Regards, K Gopalakrishnan Bangalore, INDIA -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]]On Behalf Of Mohammad Rafiq Sent: Wednesday, February 13, 2002 9:54 AM To: Multiple recipients of list ORACLE-L Subject: RE: Rollback Segment Problem In many cases you will have to take RB segments offline and drop them, then re-create _ Do You Yahoo!? Get your free @yahoo.com address at http://mail.yahoo.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: K Gopalakrishnan INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message
RE: RE: Rollback Segment Problem
Gogala, THe column you are looking is not the actual one. I have overlooked your post and thought you are right. Now I have decided to test myself and here is the result. SQL select max(ktuxesqn) from X$KTuxe; MAX(KTUXESQN) - 956 SQL startup force pfile=c:\orawin\admin\test\pfile\init.ora ORACLE instance started. Total System Global Area 97283948 bytes Fixed Size 282476 bytes Variable Size 62914560 bytes Database Buffers 33554432 bytes Redo Buffers 532480 bytes Database mounted. Database opened. SQL select max(ktuxesqn) from X$KTuxe; MAX(KTUXESQN) - 956 It is not getting changed by startup force. Best Regards, K Gopalakrishnan Bangalore, INDIA -Original Message- Mladen Sent: Thursday, February 14, 2002 2:42 PM To: Multiple recipients of list ORACLE-L Number of wraps gets reset with the instance. Below is the proof. May be after the death of a rollback segment, when can have it's resurrection? What would we name it? A vampire rollback segment? May be we should use documented V$ tables instead of the undocumented X$ ones. pdev3-a:dev:/oracle sqlplus internal SQL*Plus: Release 8.1.7.0.0 - Production on Thu Feb 14 17:26:02 2002 (c) Copyright 2000 Oracle Corporation. All rights reserved. Connected to: Oracle8i Enterprise Edition Release 8.1.7.2.0 - 64bit Production With the Partitioning option JServer Release 8.1.7.2.0 - 64bit Production SQL SQL SQL select usn,wraps from v$rollstat; USN WRAPS -- -- 0 0 2 2 3 3 4 3 5 2 6 2 6 rows selected. SQL sshutdown immediate SP2-0734: unknown command beginning sshutdown ... - rest of line ignored. SQL shutdown immediate Database closed. Database dismounted. ORACLE instance shut down. SQL startup ORACLE instance started. Total System Global Area 114231784 bytes Fixed Size 104936 bytes Variable Size 80039936 bytes Database Buffers 33554432 bytes Redo Buffers 532480 bytes Database mounted. Database opened. SQL select usn,wraps from v$rollstat; USN WRAPS -- -- 0 0 2 0 3 0 4 0 5 0 6 0 6 rows selected. SQL -Original Message- Sent: Thursday, February 14, 2002 4:04 PM To: Multiple recipients of list ORACLE-L Gogala, Not quite true.A simple restart will NOT cure that disease. The WRAP# is visible as KTUXESQN in the X$KTUXE. You can check that value by restating the instance. You can simply drop the rollback segment and force the instance to use the other rollback segements, but not by restarting..:) Best Regards, K Gopalakrishnan Bangalore, INDIA -Original Message- Mladen Sent: Thursday, February 14, 2002 12:26 PM To: Multiple recipients of list ORACLE-L How long does a database have to remain running in order for a rollback segment to go to the happy hunting grounds? Have you ever seen such an event? A simple instance restart will cure that disease. Anyway, the only thing that I can envision to cause this is to have an OLTP database with something like 100 online users doing more then 4000 transactions a day and the instance is configured with a single rollback segment. In that case the DBA deserves to be executed by the ways of the weight watchers! -Original Message- Sent: Thursday, February 14, 2002 2:36 PM To: Multiple recipients of list ORACLE-L Hi G_DBA , The transaction id (which is recorded in the ITL) consists of 3 components. Undo Segment Number, Slot Number and Wrap Number. THe max value of the wrap number is limited by UB4MAXVAL. (Approx 4G in numbers) and once it reaches the MAXVAL the rollback segment is considered as DEAD. i.e. it can not hold undo information for any transactions., THis condition is called as DEATH of a Rollback segment. THis is true for SMU also. Best Regards, K Gopalakrishnan Bangalore, INDIA -Original Message- Sent: Thursday, February 14, 2002 2:35 AM Hi Ganesh, Could u please elaborate, i did not get what u said. What do u mean by Rollback segment dying. I did not pick up the earlier mails so maybe thats why. thanks and reg Guru On Thu, 14 Feb 2002 K Gopalakrishnan wrote : You never need to recreate the rollback segments (DROP and CREATE) unless the rollback segment dies. THe rollback segment will die after approx 4M (the number is not very accurate.. okay,, some number) transactions. THis is the only case I need to drop and recreate them. Best Regards, K Gopalakrishnan Bangalore, INDIA -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]]On Behalf Of Mohammad Rafiq Sent: Wednesday, February 13, 2002 9:54 AM To: Multiple recipients of list ORACLE-L Subject: RE: Rollback Segment
RE: Rollback Segment Problem
Thanks a lot for ur help, Rafiq. I have acted as per ur recommendation and asked the customer to try the delete job again.have not received a feedback from them yet but sometimes they only revert back to me if they have faced a problem again.if I do not hear anything by this evening, I will assume that it has worked. Regards, Samir Samir Sarkar Oracle DBA - Lennon Team SchlumbergerSema Email : [EMAIL PROTECTED] [EMAIL PROTECTED] Phone : +44 (0) 115 - 957 6217 EPABX : +44 (0) 115 - 957 6418 Ext. 76217 Fax : +44 (0) 115 - 957 6018 -Original Message- Sent: 12 February 2002 17:49 To: Multiple recipients of list ORACLE-L Is your tablespace fragmented as it is not finding contingous extent .. TRy to coalesce your subject tablespace and try... you can use following script to check whether coalesing is required or not.. If percent is 100 then coalesce it.. select substr(tablespace_name,1,10)TS_NAME,total_extents Total_Extnts,extents_coalesced,round(percent_extents_coalesced,0) from dba_free_space_coalesced order by tablespace_name / HTH Regards Rafiq Reply-To: [EMAIL PROTECTED] To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Date: Tue, 12 Feb 2002 02:33:19 -0800 All, The following is the pitiable scenario I am in and would really appreciate a bit of help : We were running a long running delete job which gave the following error : Oracle8 Enterprise Edition Release 8.0.5.2.1 - Production PL/SQL Release 8.0.5.2.0 - Production SQLWKS EXECUTE PKG_PAS_AnnualDataMaintenance.Delete_OldData (2000, '/home/sqribe/work/live', '2000AnnualDataDelete.log'); ORA-01562: failed to extend rollback segment number 8 ORA-01650: unable to extend rollback segment RSUNDB08 by 50 in tablespace RSUN425 ORA-06512: at PAS.PKG_PAS_ANNUALDATAMAINTENANCE, line 66 ORA-06512: at line 2 I don't understand why it ran out of rollback space. The rollback segment has a virtually unlimited number of extents, so the only explanation I can see is that this particular rollback segment expanded until it exceeded the space allocation for the whole tablespace RSUN425, which is 3,000M. However, Oracle Storage Manager shows only 200M of the tablespace used, and it also shows high water marks against each rollback segment, none of which exceeds 400M. This would suggest to me that none of the previous month's deletions exceeded 400M per month, so why should it fail on this particular one when it had nearly 3,000M available? Is there something I am not understanding about rollback segments ?? Thanks and Regards, Samir Samir Sarkar Oracle DBA - Lennon Team SchlumbergerSema Email : [EMAIL PROTECTED] [EMAIL PROTECTED] Phone : +44 (0) 115 - 957 6217 EPABX : +44 (0) 115 - 957 6418 Ext. 76217 Fax : +44 (0) 115 - 957 6018 ___ This email is confidential and intended solely for the use of the individual to whom it is addressed. Any views or opinions presented are solely those of the author and do not necessarily represent those of SchlumbergerSema. If you are not the intended recipient, be advised that you have received this email in error and that any use, dissemination, forwarding, printing, or copying of this email is strictly prohibited. If you have received this email in error please notify the SchlumbergerSema Helpdesk by telephone on +44 (0) 121 627 5600. ___ -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: SARKAR, Samir INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). MOHAMMAD RAFIQ _ Chat with friends online, try MSN Messenger: http://messenger.msn.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Mohammad Rafiq INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and
RE: Rollback Segment Problem
Rafiq, Just a small questionr the rollback segment extents in the rollback segment tablespace de-allocated when the database is shut down ?? In that case, does the fragmentation remain when the database is restarted or r the blocks coalesced automatically ?? Regards, Samir Samir Sarkar Oracle DBA - Lennon Team SchlumbergerSema Email : [EMAIL PROTECTED] [EMAIL PROTECTED] Phone : +44 (0) 115 - 957 6217 EPABX : +44 (0) 115 - 957 6418 Ext. 76217 Fax : +44 (0) 115 - 957 6018 -Original Message- Sent: 12 February 2002 17:49 To: Multiple recipients of list ORACLE-L Is your tablespace fragmented as it is not finding contingous extent .. TRy to coalesce your subject tablespace and try... you can use following script to check whether coalesing is required or not.. If percent is 100 then coalesce it.. select substr(tablespace_name,1,10)TS_NAME,total_extents Total_Extnts,extents_coalesced,round(percent_extents_coalesced,0) from dba_free_space_coalesced order by tablespace_name / HTH Regards Rafiq Reply-To: [EMAIL PROTECTED] To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Date: Tue, 12 Feb 2002 02:33:19 -0800 All, The following is the pitiable scenario I am in and would really appreciate a bit of help : We were running a long running delete job which gave the following error : Oracle8 Enterprise Edition Release 8.0.5.2.1 - Production PL/SQL Release 8.0.5.2.0 - Production SQLWKS EXECUTE PKG_PAS_AnnualDataMaintenance.Delete_OldData (2000, '/home/sqribe/work/live', '2000AnnualDataDelete.log'); ORA-01562: failed to extend rollback segment number 8 ORA-01650: unable to extend rollback segment RSUNDB08 by 50 in tablespace RSUN425 ORA-06512: at PAS.PKG_PAS_ANNUALDATAMAINTENANCE, line 66 ORA-06512: at line 2 I don't understand why it ran out of rollback space. The rollback segment has a virtually unlimited number of extents, so the only explanation I can see is that this particular rollback segment expanded until it exceeded the space allocation for the whole tablespace RSUN425, which is 3,000M. However, Oracle Storage Manager shows only 200M of the tablespace used, and it also shows high water marks against each rollback segment, none of which exceeds 400M. This would suggest to me that none of the previous month's deletions exceeded 400M per month, so why should it fail on this particular one when it had nearly 3,000M available? Is there something I am not understanding about rollback segments ?? Thanks and Regards, Samir Samir Sarkar Oracle DBA - Lennon Team SchlumbergerSema Email : [EMAIL PROTECTED] [EMAIL PROTECTED] Phone : +44 (0) 115 - 957 6217 EPABX : +44 (0) 115 - 957 6418 Ext. 76217 Fax : +44 (0) 115 - 957 6018 ___ This email is confidential and intended solely for the use of the individual to whom it is addressed. Any views or opinions presented are solely those of the author and do not necessarily represent those of SchlumbergerSema. If you are not the intended recipient, be advised that you have received this email in error and that any use, dissemination, forwarding, printing, or copying of this email is strictly prohibited. If you have received this email in error please notify the SchlumbergerSema Helpdesk by telephone on +44 (0) 121 627 5600. ___ -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: SARKAR, Samir INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). MOHAMMAD RAFIQ _ Chat with friends online, try MSN Messenger: http://messenger.msn.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Mohammad Rafiq INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L
RE: Rollback Segment Problem
Samir, It is not coalesced automatically at shutdown and startup. It is to be coalesced manually. It applies to any tablespace with pctincrease set as 0(zero). I have no idea of 9i but this is normal behaviour of ver 7.3.4 to 8.1.7... Regards Rafiq Reply-To: [EMAIL PROTECTED] To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Date: Wed, 13 Feb 2002 05:48:28 -0800 Rafiq, Just a small questionr the rollback segment extents in the rollback segment tablespace de-allocated when the database is shut down ?? In that case, does the fragmentation remain when the database is restarted or r the blocks coalesced automatically ?? Regards, Samir Samir Sarkar Oracle DBA - Lennon Team SchlumbergerSema Email : [EMAIL PROTECTED] [EMAIL PROTECTED] Phone : +44 (0) 115 - 957 6217 EPABX : +44 (0) 115 - 957 6418 Ext. 76217 Fax : +44 (0) 115 - 957 6018 -Original Message- Sent: 12 February 2002 17:49 To: Multiple recipients of list ORACLE-L Is your tablespace fragmented as it is not finding contingous extent .. TRy to coalesce your subject tablespace and try... you can use following script to check whether coalesing is required or not.. If percent is 100 then coalesce it.. select substr(tablespace_name,1,10)TS_NAME,total_extents Total_Extnts,extents_coalesced,round(percent_extents_coalesced,0) from dba_free_space_coalesced order by tablespace_name / HTH Regards Rafiq Reply-To: [EMAIL PROTECTED] To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Date: Tue, 12 Feb 2002 02:33:19 -0800 All, The following is the pitiable scenario I am in and would really appreciate a bit of help : We were running a long running delete job which gave the following error : Oracle8 Enterprise Edition Release 8.0.5.2.1 - Production PL/SQL Release 8.0.5.2.0 - Production SQLWKS EXECUTE PKG_PAS_AnnualDataMaintenance.Delete_OldData (2000, '/home/sqribe/work/live', '2000AnnualDataDelete.log'); ORA-01562: failed to extend rollback segment number 8 ORA-01650: unable to extend rollback segment RSUNDB08 by 50 in tablespace RSUN425 ORA-06512: at PAS.PKG_PAS_ANNUALDATAMAINTENANCE, line 66 ORA-06512: at line 2 I don't understand why it ran out of rollback space. The rollback segment has a virtually unlimited number of extents, so the only explanation I can see is that this particular rollback segment expanded until it exceeded the space allocation for the whole tablespace RSUN425, which is 3,000M. However, Oracle Storage Manager shows only 200M of the tablespace used, and it also shows high water marks against each rollback segment, none of which exceeds 400M. This would suggest to me that none of the previous month's deletions exceeded 400M per month, so why should it fail on this particular one when it had nearly 3,000M available? Is there something I am not understanding about rollback segments ?? Thanks and Regards, Samir Samir Sarkar Oracle DBA - Lennon Team SchlumbergerSema Email : [EMAIL PROTECTED] [EMAIL PROTECTED] Phone : +44 (0) 115 - 957 6217 EPABX : +44 (0) 115 - 957 6418 Ext. 76217 Fax : +44 (0) 115 - 957 6018 ___ This email is confidential and intended solely for the use of the individual to whom it is addressed. Any views or opinions presented are solely those of the author and do not necessarily represent those of SchlumbergerSema. If you are not the intended recipient, be advised that you have received this email in error and that any use, dissemination, forwarding, printing, or copying of this email is strictly prohibited. If you have received this email in error please notify the SchlumbergerSema Helpdesk by telephone on +44 (0) 121 627 5600. ___ -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: SARKAR, Samir INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). MOHAMMAD RAFIQ _ Chat with friends online, try MSN Messenger: http://messenger.msn.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Mohammad Rafiq INET: [EMAIL PROTECTED] Fat City Network Services
RE: Rollback Segment Problem
Samir, Thanks..Due to lot of activities of rollback segment resulting in shrinkage make rbs tablesapce fragmented hence contigous space becomes a issue...Possible solution to set a good size of optsize of rbs to avoid shrinkages or frequent coalesing of rbs tablespace / or any other tablespace on the basis of following query ...if pct 100 select substr(tablespace_name,1,10)TS_NAME,total_extents Total_Extnts,extents_coalesced,round(percent_extents_coalesced,0) from dba_free_space_coalesced order by tablespace_name / Regards Rafiq Reply-To: [EMAIL PROTECTED] To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Date: Wed, 13 Feb 2002 02:03:28 -0800 Thanks a lot for ur help, Rafiq. I have acted as per ur recommendation and asked the customer to try the delete job again.have not received a feedback from them yet but sometimes they only revert back to me if they have faced a problem again.if I do not hear anything by this evening, I will assume that it has worked. Regards, Samir Samir Sarkar Oracle DBA - Lennon Team SchlumbergerSema Email : [EMAIL PROTECTED] [EMAIL PROTECTED] Phone : +44 (0) 115 - 957 6217 EPABX : +44 (0) 115 - 957 6418 Ext. 76217 Fax : +44 (0) 115 - 957 6018 -Original Message- Sent: 12 February 2002 17:49 To: Multiple recipients of list ORACLE-L Is your tablespace fragmented as it is not finding contingous extent .. TRy to coalesce your subject tablespace and try... you can use following script to check whether coalesing is required or not.. If percent is 100 then coalesce it.. select substr(tablespace_name,1,10)TS_NAME,total_extents Total_Extnts,extents_coalesced,round(percent_extents_coalesced,0) from dba_free_space_coalesced order by tablespace_name / HTH Regards Rafiq Reply-To: [EMAIL PROTECTED] To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Date: Tue, 12 Feb 2002 02:33:19 -0800 All, The following is the pitiable scenario I am in and would really appreciate a bit of help : We were running a long running delete job which gave the following error : Oracle8 Enterprise Edition Release 8.0.5.2.1 - Production PL/SQL Release 8.0.5.2.0 - Production SQLWKS EXECUTE PKG_PAS_AnnualDataMaintenance.Delete_OldData (2000, '/home/sqribe/work/live', '2000AnnualDataDelete.log'); ORA-01562: failed to extend rollback segment number 8 ORA-01650: unable to extend rollback segment RSUNDB08 by 50 in tablespace RSUN425 ORA-06512: at PAS.PKG_PAS_ANNUALDATAMAINTENANCE, line 66 ORA-06512: at line 2 I don't understand why it ran out of rollback space. The rollback segment has a virtually unlimited number of extents, so the only explanation I can see is that this particular rollback segment expanded until it exceeded the space allocation for the whole tablespace RSUN425, which is 3,000M. However, Oracle Storage Manager shows only 200M of the tablespace used, and it also shows high water marks against each rollback segment, none of which exceeds 400M. This would suggest to me that none of the previous month's deletions exceeded 400M per month, so why should it fail on this particular one when it had nearly 3,000M available? Is there something I am not understanding about rollback segments ?? Thanks and Regards, Samir Samir Sarkar Oracle DBA - Lennon Team SchlumbergerSema Email : [EMAIL PROTECTED] [EMAIL PROTECTED] Phone : +44 (0) 115 - 957 6217 EPABX : +44 (0) 115 - 957 6418 Ext. 76217 Fax : +44 (0) 115 - 957 6018 ___ This email is confidential and intended solely for the use of the individual to whom it is addressed. Any views or opinions presented are solely those of the author and do not necessarily represent those of SchlumbergerSema. If you are not the intended recipient, be advised that you have received this email in error and that any use, dissemination, forwarding, printing, or copying of this email is strictly prohibited. If you have received this email in error please notify the SchlumbergerSema Helpdesk by telephone on +44 (0) 121 627 5600. ___ -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: SARKAR, Samir INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP
RE: Rollback Segment Problem
Thanks a lot, Rafiqthe output from ur query after running it on the database showed the rollback segment to be highly fragmented.the total extents were 7490 and the extents coalesced were only 440. The initial and next extents have been set at 409K which seems to me as quite small. Regards, Samir Samir Sarkar Oracle DBA - Lennon Team SchlumbergerSema Email : [EMAIL PROTECTED] [EMAIL PROTECTED] Phone : +44 (0) 115 - 957 6217 EPABX : +44 (0) 115 - 957 6418 Ext. 76217 Fax : +44 (0) 115 - 957 6018 -Original Message- Sent: 13 February 2002 16:03 To: Multiple recipients of list ORACLE-L Samir, It is not coalesced automatically at shutdown and startup. It is to be coalesced manually. It applies to any tablespace with pctincrease set as 0(zero). I have no idea of 9i but this is normal behaviour of ver 7.3.4 to 8.1.7... Regards Rafiq Reply-To: [EMAIL PROTECTED] To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Date: Wed, 13 Feb 2002 05:48:28 -0800 Rafiq, Just a small questionr the rollback segment extents in the rollback segment tablespace de-allocated when the database is shut down ?? In that case, does the fragmentation remain when the database is restarted or r the blocks coalesced automatically ?? Regards, Samir Samir Sarkar Oracle DBA - Lennon Team SchlumbergerSema Email : [EMAIL PROTECTED] [EMAIL PROTECTED] Phone : +44 (0) 115 - 957 6217 EPABX : +44 (0) 115 - 957 6418 Ext. 76217 Fax : +44 (0) 115 - 957 6018 -Original Message- Sent: 12 February 2002 17:49 To: Multiple recipients of list ORACLE-L Is your tablespace fragmented as it is not finding contingous extent .. TRy to coalesce your subject tablespace and try... you can use following script to check whether coalesing is required or not.. If percent is 100 then coalesce it.. select substr(tablespace_name,1,10)TS_NAME,total_extents Total_Extnts,extents_coalesced,round(percent_extents_coalesced,0) from dba_free_space_coalesced order by tablespace_name / HTH Regards Rafiq Reply-To: [EMAIL PROTECTED] To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Date: Tue, 12 Feb 2002 02:33:19 -0800 All, The following is the pitiable scenario I am in and would really appreciate a bit of help : We were running a long running delete job which gave the following error : Oracle8 Enterprise Edition Release 8.0.5.2.1 - Production PL/SQL Release 8.0.5.2.0 - Production SQLWKS EXECUTE PKG_PAS_AnnualDataMaintenance.Delete_OldData (2000, '/home/sqribe/work/live', '2000AnnualDataDelete.log'); ORA-01562: failed to extend rollback segment number 8 ORA-01650: unable to extend rollback segment RSUNDB08 by 50 in tablespace RSUN425 ORA-06512: at PAS.PKG_PAS_ANNUALDATAMAINTENANCE, line 66 ORA-06512: at line 2 I don't understand why it ran out of rollback space. The rollback segment has a virtually unlimited number of extents, so the only explanation I can see is that this particular rollback segment expanded until it exceeded the space allocation for the whole tablespace RSUN425, which is 3,000M. However, Oracle Storage Manager shows only 200M of the tablespace used, and it also shows high water marks against each rollback segment, none of which exceeds 400M. This would suggest to me that none of the previous month's deletions exceeded 400M per month, so why should it fail on this particular one when it had nearly 3,000M available? Is there something I am not understanding about rollback segments ?? Thanks and Regards, Samir Samir Sarkar Oracle DBA - Lennon Team SchlumbergerSema Email : [EMAIL PROTECTED] [EMAIL PROTECTED] Phone : +44 (0) 115 - 957 6217 EPABX : +44 (0) 115 - 957 6418 Ext. 76217 Fax : +44 (0) 115 - 957 6018 ___ This email is confidential and intended solely for the use of the individual to whom it is addressed. Any views or opinions presented are solely those of the author and do not necessarily represent those of SchlumbergerSema. If you are not the intended recipient, be advised that you have received this email in error and that any use, dissemination, forwarding, printing, or copying of this email is strictly prohibited. If you have received this email in error please notify the SchlumbergerSema Helpdesk by telephone on +44 (0) 121 627 5600. ___ -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: SARKAR, Samir INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists
RE: Rollback Segment Problem
No..specially when pctincrease of tablespace set as 0(zero)... Regards Rafiq Reply-To: [EMAIL PROTECTED] To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Date: Tue, 12 Feb 2002 15:08:27 -0800 Why would you need coalesce at all?. My understanding that if Oracle cannot find contiguous free space it try coalesce itself. Alex Hillman -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]]On Behalf Of Mohammad Rafiq Sent: Tuesday, February 12, 2002 4:53 PM To: Multiple recipients of list ORACLE-L Subject: RE: Rollback Segment Problem Jerry, Thanks for sharing script. We might have better systems as timing was never be a issue. I just tested my script on 2 systems and it ran between 34.5 mseconds to 4.65 seconds...and on the basis of it is results we coalesce all such tablespaces. I prefer to keep pctincrease of tablespaces as 0(zero) and manually coalesing as and when it is required So have you ran your deletion job after coalesing or not? Regards Rafiq To: [EMAIL PROTECTED] CC: [EMAIL PROTECTED] Date: Tue, 12 Feb 2002 15:28:41 -0600 Rafiq, While your script provides a lot of good information, it sure is slow on my system. It took 1:44 minutes while my script below took 420 mseconds. I was really surprised as my script has a self-join. Then I checked out dba_free_space_coalesced. It sure is complicated as it calls on two other views and a table. select a.tablespace_name, count(a.tablespace_name) ContinguousFreeBlocks from dba_free_space a, dba_free_space b where a.tablespace_name = b.tablespace_name and a.file_id = b.file_id and a.block_id = b.block_id + b.Blocks group by a.tablespace_name; If ContinguousFreeBlocks is 10, I coalesce. Jerry Whittle ACIFICS DBA NCI Information Systems Inc. [EMAIL PROTECTED] 618-622-4145 -Original Message- From: Mohammad Rafiq [SMTP:[EMAIL PROTECTED]] Is your tablespace fragmented as it is not finding contingous extent .. TRy to coalesce your subject tablespace and try... you can use following script to check whether coalesing is required or not.. If percent is 100 then coalesce it.. select substr(tablespace_name,1,10)TS_NAME,total_extents Total_Extnts,extents_coalesced,round(percent_extents_coalesced,0) from dba_free_space_coalesced order by tablespace_name / HTH Regards Rafiq MOHAMMAD RAFIQ _ Get your FREE download of MSN Explorer at http://explorer.msn.com/intl.asp. -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Mohammad Rafiq INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Alex Hillman INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). MOHAMMAD RAFIQ _ Join the worlds largest e-mail service with MSN Hotmail. http://www.hotmail.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Mohammad Rafiq INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: Rollback Segment Problem
Hi, guys, Just my $0.02, coalescing free space will NEVER eliminate fragmentation. In many cases you will have to take RB segments offline and drop them, then re-create. In the interim period you might want to have a big rollback segment(or a few) in another tablespace. Best, Sergey -Original Message- Sent: Wednesday, February 13, 2002 11:03 AM To: Multiple recipients of list ORACLE-L Samir, It is not coalesced automatically at shutdown and startup. It is to be coalesced manually. It applies to any tablespace with pctincrease set as 0(zero). I have no idea of 9i but this is normal behaviour of ver 7.3.4 to 8.1.7... Regards Rafiq Reply-To: [EMAIL PROTECTED] To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Date: Wed, 13 Feb 2002 05:48:28 -0800 Rafiq, Just a small questionr the rollback segment extents in the rollback segment tablespace de-allocated when the database is shut down ?? In that case, does the fragmentation remain when the database is restarted or r the blocks coalesced automatically ?? Regards, Samir Samir Sarkar Oracle DBA - Lennon Team SchlumbergerSema Email : [EMAIL PROTECTED] [EMAIL PROTECTED] Phone : +44 (0) 115 - 957 6217 EPABX : +44 (0) 115 - 957 6418 Ext. 76217 Fax : +44 (0) 115 - 957 6018 -Original Message- Sent: 12 February 2002 17:49 To: Multiple recipients of list ORACLE-L Is your tablespace fragmented as it is not finding contingous extent .. TRy to coalesce your subject tablespace and try... you can use following script to check whether coalesing is required or not.. If percent is 100 then coalesce it.. select substr(tablespace_name,1,10)TS_NAME,total_extents Total_Extnts,extents_coalesced,round(percent_extents_coalesced,0) from dba_free_space_coalesced order by tablespace_name / HTH Regards Rafiq Reply-To: [EMAIL PROTECTED] To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Date: Tue, 12 Feb 2002 02:33:19 -0800 All, The following is the pitiable scenario I am in and would really appreciate a bit of help : We were running a long running delete job which gave the following error : Oracle8 Enterprise Edition Release 8.0.5.2.1 - Production PL/SQL Release 8.0.5.2.0 - Production SQLWKS EXECUTE PKG_PAS_AnnualDataMaintenance.Delete_OldData (2000, '/home/sqribe/work/live', '2000AnnualDataDelete.log'); ORA-01562: failed to extend rollback segment number 8 ORA-01650: unable to extend rollback segment RSUNDB08 by 50 in tablespace RSUN425 ORA-06512: at PAS.PKG_PAS_ANNUALDATAMAINTENANCE, line 66 ORA-06512: at line 2 I don't understand why it ran out of rollback space. The rollback segment has a virtually unlimited number of extents, so the only explanation I can see is that this particular rollback segment expanded until it exceeded the space allocation for the whole tablespace RSUN425, which is 3,000M. However, Oracle Storage Manager shows only 200M of the tablespace used, and it also shows high water marks against each rollback segment, none of which exceeds 400M. This would suggest to me that none of the previous month's deletions exceeded 400M per month, so why should it fail on this particular one when it had nearly 3,000M available? Is there something I am not understanding about rollback segments ?? Thanks and Regards, Samir Samir Sarkar Oracle DBA - Lennon Team SchlumbergerSema Email : [EMAIL PROTECTED] [EMAIL PROTECTED] Phone : +44 (0) 115 - 957 6217 EPABX : +44 (0) 115 - 957 6418 Ext. 76217 Fax : +44 (0) 115 - 957 6018 ___ This email is confidential and intended solely for the use of the individual to whom it is addressed. Any views or opinions presented are solely those of the author and do not necessarily represent those of SchlumbergerSema. If you are not the intended recipient, be advised that you have received this email in error and that any use, dissemination, forwarding, printing, or copying of this email is strictly prohibited. If you have received this email in error please notify the SchlumbergerSema Helpdesk by telephone on +44 (0) 121 627 5600. ___ -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: SARKAR, Samir INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to
RE: Rollback Segment Problem
In many cases you will have to take RB segments offline and drop them, then re-create --Even in this case you have to coalesce rbs tablespace before recreating rollback segments using that particular tablespace to make all released extents as contigous...Besides it is a good practice to reorg rollback segments.. Regards Rafiq Reply-To: [EMAIL PROTECTED] To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Date: Wed, 13 Feb 2002 08:58:47 -0800 Hi, guys, Just my $0.02, coalescing free space will NEVER eliminate fragmentation. In many cases you will have to take RB segments offline and drop them, then re-create. In the interim period you might want to have a big rollback segment(or a few) in another tablespace. Best, Sergey -Original Message- Sent: Wednesday, February 13, 2002 11:03 AM To: Multiple recipients of list ORACLE-L Samir, It is not coalesced automatically at shutdown and startup. It is to be coalesced manually. It applies to any tablespace with pctincrease set as 0(zero). I have no idea of 9i but this is normal behaviour of ver 7.3.4 to 8.1.7... Regards Rafiq Reply-To: [EMAIL PROTECTED] To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Date: Wed, 13 Feb 2002 05:48:28 -0800 Rafiq, Just a small questionr the rollback segment extents in the rollback segment tablespace de-allocated when the database is shut down ?? In that case, does the fragmentation remain when the database is restarted or r the blocks coalesced automatically ?? Regards, Samir Samir Sarkar Oracle DBA - Lennon Team SchlumbergerSema Email : [EMAIL PROTECTED] [EMAIL PROTECTED] Phone : +44 (0) 115 - 957 6217 EPABX : +44 (0) 115 - 957 6418 Ext. 76217 Fax : +44 (0) 115 - 957 6018 -Original Message- Sent: 12 February 2002 17:49 To: Multiple recipients of list ORACLE-L Is your tablespace fragmented as it is not finding contingous extent .. TRy to coalesce your subject tablespace and try... you can use following script to check whether coalesing is required or not.. If percent is 100 then coalesce it.. select substr(tablespace_name,1,10)TS_NAME,total_extents Total_Extnts,extents_coalesced,round(percent_extents_coalesced,0) from dba_free_space_coalesced order by tablespace_name / HTH Regards Rafiq Reply-To: [EMAIL PROTECTED] To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Date: Tue, 12 Feb 2002 02:33:19 -0800 All, The following is the pitiable scenario I am in and would really appreciate a bit of help : We were running a long running delete job which gave the following error : Oracle8 Enterprise Edition Release 8.0.5.2.1 - Production PL/SQL Release 8.0.5.2.0 - Production SQLWKS EXECUTE PKG_PAS_AnnualDataMaintenance.Delete_OldData (2000, '/home/sqribe/work/live', '2000AnnualDataDelete.log'); ORA-01562: failed to extend rollback segment number 8 ORA-01650: unable to extend rollback segment RSUNDB08 by 50 in tablespace RSUN425 ORA-06512: at PAS.PKG_PAS_ANNUALDATAMAINTENANCE, line 66 ORA-06512: at line 2 I don't understand why it ran out of rollback space. The rollback segment has a virtually unlimited number of extents, so the only explanation I can see is that this particular rollback segment expanded until it exceeded the space allocation for the whole tablespace RSUN425, which is 3,000M. However, Oracle Storage Manager shows only 200M of the tablespace used, and it also shows high water marks against each rollback segment, none of which exceeds 400M. This would suggest to me that none of the previous month's deletions exceeded 400M per month, so why should it fail on this particular one when it had nearly 3,000M available? Is there something I am not understanding about rollback segments ?? Thanks and Regards, Samir Samir Sarkar Oracle DBA - Lennon Team SchlumbergerSema Email : [EMAIL PROTECTED] [EMAIL PROTECTED] Phone : +44 (0) 115 - 957 6217 EPABX : +44 (0) 115 - 957 6418 Ext. 76217 Fax : +44 (0) 115 - 957 6018 ___ This email is confidential and intended solely for the use of the individual to whom it is addressed. Any views or opinions presented are solely those of the author and do not necessarily represent those of SchlumbergerSema. If you are not the intended recipient, be advised that you have received this email in error and that any use, dissemination, forwarding, printing, or copying of this email is strictly prohibited. If you have received this email in error please notify the SchlumbergerSema Helpdesk by telephone on +44 (0) 121 627 5600. ___ -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: SARKAR, Samir
RE: Rollback Segment Problem
Samir, For a long term solution, it is better to reorg rbs tablespace and rollback segments with correct sizing based on usage... You may use following script to get better info about rollback segments usage 1 set linesize 120 2 column name format A15 3 column status format A7 4 column ext format 999 5 column MB format 9990.0 6 column HWMSIZE heading HIGH|WATER justify center format 9990.0 7 column AVEACT heading AVERAGE|ACTIVE justify center format 9990.0 8 column optsize format 90 9 column shrinks format 9990 10 column waits format 9 11 set numwidth 8 12 select ud.name, 13 sg.extents ext, 14 round(sg.blocks * ts.blocksize / 1048576, 2) MB, 15 round(s.optsize / 1048576, 1) optsize, 16 round(s.aveactive / 1048576, 1) AVEACT, 17 round(s.hwmsize / 1048576, 1) HWMSIZE, 18 s.shrinks, 19 s.wraps, 20 s.waits, 21 nvl(s.status, 'OFFLINE') status 22 from v$rollstat s, 23 sys.undo$ ud, 24 sys.seg$ sg, 25 sys.ts$ ts 26 where ud.us# = s.usn (+) 27and ud.file# = sg.file# 28and ud.block# = sg.block# 29* and sg.ts# = ts.ts# Regards Rafiq To: '[EMAIL PROTECTED]' [EMAIL PROTECTED] CC: '[EMAIL PROTECTED]' [EMAIL PROTECTED] Date: Wed, 13 Feb 2002 16:50:37 - Thanks a lot, Rafiqthe output from ur query after running it on the database showed the rollback segment to be highly fragmented.the total extents were 7490 and the extents coalesced were only 440. The initial and next extents have been set at 409K which seems to me as quite small. Regards, Samir Samir Sarkar Oracle DBA - Lennon Team SchlumbergerSema Email : [EMAIL PROTECTED] [EMAIL PROTECTED] Phone : +44 (0) 115 - 957 6217 EPABX : +44 (0) 115 - 957 6418 Ext. 76217 Fax : +44 (0) 115 - 957 6018 -Original Message- Sent: 13 February 2002 16:03 To: Multiple recipients of list ORACLE-L Samir, It is not coalesced automatically at shutdown and startup. It is to be coalesced manually. It applies to any tablespace with pctincrease set as 0(zero). I have no idea of 9i but this is normal behaviour of ver 7.3.4 to 8.1.7... Regards Rafiq Reply-To: [EMAIL PROTECTED] To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Date: Wed, 13 Feb 2002 05:48:28 -0800 Rafiq, Just a small questionr the rollback segment extents in the rollback segment tablespace de-allocated when the database is shut down ?? In that case, does the fragmentation remain when the database is restarted or r the blocks coalesced automatically ?? Regards, Samir Samir Sarkar Oracle DBA - Lennon Team SchlumbergerSema Email : [EMAIL PROTECTED] [EMAIL PROTECTED] Phone : +44 (0) 115 - 957 6217 EPABX : +44 (0) 115 - 957 6418 Ext. 76217 Fax : +44 (0) 115 - 957 6018 -Original Message- Sent: 12 February 2002 17:49 To: Multiple recipients of list ORACLE-L Is your tablespace fragmented as it is not finding contingous extent .. TRy to coalesce your subject tablespace and try... you can use following script to check whether coalesing is required or not.. If percent is 100 then coalesce it.. select substr(tablespace_name,1,10)TS_NAME,total_extents Total_Extnts,extents_coalesced,round(percent_extents_coalesced,0) from dba_free_space_coalesced order by tablespace_name / HTH Regards Rafiq Reply-To: [EMAIL PROTECTED] To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Date: Tue, 12 Feb 2002 02:33:19 -0800 All, The following is the pitiable scenario I am in and would really appreciate a bit of help : We were running a long running delete job which gave the following error : Oracle8 Enterprise Edition Release 8.0.5.2.1 - Production PL/SQL Release 8.0.5.2.0 - Production SQLWKS EXECUTE PKG_PAS_AnnualDataMaintenance.Delete_OldData (2000, '/home/sqribe/work/live', '2000AnnualDataDelete.log'); ORA-01562: failed to extend rollback segment number 8 ORA-01650: unable to extend rollback segment RSUNDB08 by 50 in tablespace RSUN425 ORA-06512: at PAS.PKG_PAS_ANNUALDATAMAINTENANCE, line 66 ORA-06512: at line 2 I don't understand why it ran out of rollback space. The rollback segment has a virtually unlimited number of extents, so the only explanation I can see is that this particular rollback segment expanded until it exceeded the space allocation for the whole tablespace RSUN425, which is 3,000M. However, Oracle Storage Manager shows only 200M of the tablespace used, and it also shows high water marks against each rollback segment, none of which exceeds 400M. This would suggest to me that none of the previous month's deletions exceeded 400M per month, so why should it fail on this particular one when it had nearly 3,000M available? Is there something I am not
Re: Rollback Segment Problem
Here we go again... Alex is correct. Read up in the concepts manual. Oracle will *automatically* coalesce, if needed, at the time of extension. Jared On Tuesday 12 February 2002 15:08, Alex Hillman wrote: Why would you need coalesce at all?. My understanding that if Oracle cannot find contiguous free space it try coalesce itself. Alex Hillman -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]]On Behalf Of Mohammad Rafiq Sent: Tuesday, February 12, 2002 4:53 PM To: Multiple recipients of list ORACLE-L Subject: RE: Rollback Segment Problem Jerry, Thanks for sharing script. We might have better systems as timing was never be a issue. I just tested my script on 2 systems and it ran between 34.5 mseconds to 4.65 seconds...and on the basis of it is results we coalesce all such tablespaces. I prefer to keep pctincrease of tablespaces as 0(zero) and manually coalesing as and when it is required So have you ran your deletion job after coalesing or not? Regards Rafiq To: [EMAIL PROTECTED] CC: [EMAIL PROTECTED] Date: Tue, 12 Feb 2002 15:28:41 -0600 Rafiq, While your script provides a lot of good information, it sure is slow on my system. It took 1:44 minutes while my script below took 420 mseconds. I was really surprised as my script has a self-join. Then I checked out dba_free_space_coalesced. It sure is complicated as it calls on two other views and a table. select a.tablespace_name, count(a.tablespace_name) ContinguousFreeBlocks from dba_free_space a, dba_free_space b where a.tablespace_name = b.tablespace_name and a.file_id = b.file_id and a.block_id = b.block_id + b.Blocks group by a.tablespace_name; If ContinguousFreeBlocks is 10, I coalesce. Jerry Whittle ACIFICS DBA NCI Information Systems Inc. [EMAIL PROTECTED] 618-622-4145 -Original Message- From:Mohammad Rafiq [SMTP:[EMAIL PROTECTED]] Is your tablespace fragmented as it is not finding contingous extent .. TRy to coalesce your subject tablespace and try... you can use following script to check whether coalesing is required or not.. If percent is 100 then coalesce it.. select substr(tablespace_name,1,10)TS_NAME,total_extents Total_Extnts,extents_coalesced,round(percent_extents_coalesced,0) from dba_free_space_coalesced order by tablespace_name / HTH Regards Rafiq MOHAMMAD RAFIQ _ Get your FREE download of MSN Explorer at http://explorer.msn.com/intl.asp. -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Mohammad Rafiq INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Jared Still INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: Rollback Segment Problem
IN THIS CASE yes, but NOT BEFORE That was my point. Thanks, Best, Sergey -Original Message- Sent: Wednesday, February 13, 2002 12:54 PM To: Multiple recipients of list ORACLE-L In many cases you will have to take RB segments offline and drop them, then re-create --Even in this case you have to coalesce rbs tablespace before recreating rollback segments using that particular tablespace to make all released extents as contigous...Besides it is a good practice to reorg rollback segments.. Regards Rafiq Reply-To: [EMAIL PROTECTED] To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Date: Wed, 13 Feb 2002 08:58:47 -0800 Hi, guys, Just my $0.02, coalescing free space will NEVER eliminate fragmentation. In many cases you will have to take RB segments offline and drop them, then re-create. In the interim period you might want to have a big rollback segment(or a few) in another tablespace. Best, Sergey -Original Message- Sent: Wednesday, February 13, 2002 11:03 AM To: Multiple recipients of list ORACLE-L Samir, It is not coalesced automatically at shutdown and startup. It is to be coalesced manually. It applies to any tablespace with pctincrease set as 0(zero). I have no idea of 9i but this is normal behaviour of ver 7.3.4 to 8.1.7... Regards Rafiq Reply-To: [EMAIL PROTECTED] To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Date: Wed, 13 Feb 2002 05:48:28 -0800 Rafiq, Just a small questionr the rollback segment extents in the rollback segment tablespace de-allocated when the database is shut down ?? In that case, does the fragmentation remain when the database is restarted or r the blocks coalesced automatically ?? Regards, Samir Samir Sarkar Oracle DBA - Lennon Team SchlumbergerSema Email : [EMAIL PROTECTED] [EMAIL PROTECTED] Phone : +44 (0) 115 - 957 6217 EPABX : +44 (0) 115 - 957 6418 Ext. 76217 Fax : +44 (0) 115 - 957 6018 -Original Message- Sent: 12 February 2002 17:49 To: Multiple recipients of list ORACLE-L Is your tablespace fragmented as it is not finding contingous extent .. TRy to coalesce your subject tablespace and try... you can use following script to check whether coalesing is required or not.. If percent is 100 then coalesce it.. select substr(tablespace_name,1,10)TS_NAME,total_extents Total_Extnts,extents_coalesced,round(percent_extents_coalesced,0) from dba_free_space_coalesced order by tablespace_name / HTH Regards Rafiq Reply-To: [EMAIL PROTECTED] To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Date: Tue, 12 Feb 2002 02:33:19 -0800 All, The following is the pitiable scenario I am in and would really appreciate a bit of help : We were running a long running delete job which gave the following error : Oracle8 Enterprise Edition Release 8.0.5.2.1 - Production PL/SQL Release 8.0.5.2.0 - Production SQLWKS EXECUTE PKG_PAS_AnnualDataMaintenance.Delete_OldData (2000, '/home/sqribe/work/live', '2000AnnualDataDelete.log'); ORA-01562: failed to extend rollback segment number 8 ORA-01650: unable to extend rollback segment RSUNDB08 by 50 in tablespace RSUN425 ORA-06512: at PAS.PKG_PAS_ANNUALDATAMAINTENANCE, line 66 ORA-06512: at line 2 I don't understand why it ran out of rollback space. The rollback segment has a virtually unlimited number of extents, so the only explanation I can see is that this particular rollback segment expanded until it exceeded the space allocation for the whole tablespace RSUN425, which is 3,000M. However, Oracle Storage Manager shows only 200M of the tablespace used, and it also shows high water marks against each rollback segment, none of which exceeds 400M. This would suggest to me that none of the previous month's deletions exceeded 400M per month, so why should it fail on this particular one when it had nearly 3,000M available? Is there something I am not understanding about rollback segments ?? Thanks and Regards, Samir Samir Sarkar Oracle DBA - Lennon Team SchlumbergerSema Email : [EMAIL PROTECTED] [EMAIL PROTECTED] Phone : +44 (0) 115 - 957 6217 EPABX : +44 (0) 115 - 957 6418 Ext. 76217 Fax : +44 (0) 115 - 957 6018 ___ This email is confidential and intended solely for the use of the individual to whom it is addressed. Any views or opinions presented are solely those of the author and do not necessarily represent those of SchlumbergerSema. If you are not the intended recipient, be advised that you have received this email in error and that any use, dissemination, forwarding, printing, or copying of this email is strictly prohibited. If you have received this email in error please notify the SchlumbergerSema Helpdesk by telephone
RE: Rollback Segment Problem
Exactly. It depends pretty much on the application, how often it commits transactions, segment sizes, of course, OLTP amount etc. You can't always wait for SMON to wake up and do its job. The point is COALESCING alone won't always help and you have to defrag. Another point is ONE thing is reading the manuals, and ANOTHER one is dealing with rollback issues on every day basis. Best regards to everyone, Sergey -Original Message- Sent: Wednesday, February 13, 2002 2:14 PM To: Multiple recipients of list ORACLE-L Thanks...even is not true completely...if you have seen responses of Sameer Sarkar, originator of this thread...resolved his problem after coalesing his rbs table_space... I have to dealt with this situation on one of our databases because of some codes which results in lot of shrinkage of rollback segment and we have to coalesce that rbs tablespace quit often to get scattered/fragmented extents in contitgous extents Regards Rafiq Reply-To: [EMAIL PROTECTED] To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Date: Wed, 13 Feb 2002 10:28:58 -0800 IN THIS CASE yes, but NOT BEFORE That was my point. Thanks, Best, Sergey -Original Message- Sent: Wednesday, February 13, 2002 12:54 PM To: Multiple recipients of list ORACLE-L In many cases you will have to take RB segments offline and drop them, then re-create --Even in this case you have to coalesce rbs tablespace before recreating rollback segments using that particular tablespace to make all released extents as contigous...Besides it is a good practice to reorg rollback segments.. Regards Rafiq Reply-To: [EMAIL PROTECTED] To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Date: Wed, 13 Feb 2002 08:58:47 -0800 Hi, guys, Just my $0.02, coalescing free space will NEVER eliminate fragmentation. In many cases you will have to take RB segments offline and drop them, then re-create. In the interim period you might want to have a big rollback segment(or a few) in another tablespace. Best, Sergey -Original Message- Sent: Wednesday, February 13, 2002 11:03 AM To: Multiple recipients of list ORACLE-L Samir, It is not coalesced automatically at shutdown and startup. It is to be coalesced manually. It applies to any tablespace with pctincrease set as 0(zero). I have no idea of 9i but this is normal behaviour of ver 7.3.4 to 8.1.7... Regards Rafiq Reply-To: [EMAIL PROTECTED] To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Date: Wed, 13 Feb 2002 05:48:28 -0800 Rafiq, Just a small questionr the rollback segment extents in the rollback segment tablespace de-allocated when the database is shut down ?? In that case, does the fragmentation remain when the database is restarted or r the blocks coalesced automatically ?? Regards, Samir Samir Sarkar Oracle DBA - Lennon Team SchlumbergerSema Email : [EMAIL PROTECTED] [EMAIL PROTECTED] Phone : +44 (0) 115 - 957 6217 EPABX : +44 (0) 115 - 957 6418 Ext. 76217 Fax : +44 (0) 115 - 957 6018 -Original Message- Sent: 12 February 2002 17:49 To: Multiple recipients of list ORACLE-L Is your tablespace fragmented as it is not finding contingous extent .. TRy to coalesce your subject tablespace and try... you can use following script to check whether coalesing is required or not.. If percent is 100 then coalesce it.. select substr(tablespace_name,1,10)TS_NAME,total_extents Total_Extnts,extents_coalesced,round(percent_extents_coalesced,0) from dba_free_space_coalesced order by tablespace_name / HTH Regards Rafiq Reply-To: [EMAIL PROTECTED] To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Date: Tue, 12 Feb 2002 02:33:19 -0800 All, The following is the pitiable scenario I am in and would really appreciate a bit of help : We were running a long running delete job which gave the following error : Oracle8 Enterprise Edition Release 8.0.5.2.1 - Production PL/SQL Release 8.0.5.2.0 - Production SQLWKS EXECUTE PKG_PAS_AnnualDataMaintenance.Delete_OldData (2000, '/home/sqribe/work/live', '2000AnnualDataDelete.log'); ORA-01562: failed to extend rollback segment number 8 ORA-01650: unable to extend rollback segment RSUNDB08 by 50 in tablespace RSUN425 ORA-06512: at PAS.PKG_PAS_ANNUALDATAMAINTENANCE, line 66 ORA-06512: at line 2 I don't understand why it ran out of rollback space. The rollback segment has a virtually unlimited number of extents, so the only explanation I can see is that this particular rollback segment expanded until it exceeded the space allocation for the whole tablespace RSUN425, which is 3,000M. However, Oracle Storage Manager shows only 200M of the tablespace used, and it also shows high water marks against each rollback segment, none of which
RE: Rollback Segment Problem
One wouldn't have to deal with rollback issues on every day basis if s/he read manuals in advance and knew how to setup rollbacks right. If rollback tablespace is locally managed with uniform extent size - cannot be any fragmentation. If dictionary managed - tablespace pctincrease should be 0, minimumextent should be used (if version 8+), extent sizes for all rollbacks should be the same - equal tablespace default sizes etc. and one will never have any fragmentation problems Regards, Alex Hillman -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]]On Behalf Of Babich , Sergey Sent: Wednesday, February 13, 2002 3:00 PM To: Multiple recipients of list ORACLE-L Subject: RE: Rollback Segment Problem Exactly. It depends pretty much on the application, how often it commits transactions, segment sizes, of course, OLTP amount etc. You can't always wait for SMON to wake up and do its job. The point is COALESCING alone won't always help and you have to defrag. Another point is ONE thing is reading the manuals, and ANOTHER one is dealing with rollback issues on every day basis. Best regards to everyone, Sergey -Original Message- Sent: Wednesday, February 13, 2002 2:14 PM To: Multiple recipients of list ORACLE-L Thanks...even is not true completely...if you have seen responses of Sameer Sarkar, originator of this thread...resolved his problem after coalesing his rbs table_space... I have to dealt with this situation on one of our databases because of some codes which results in lot of shrinkage of rollback segment and we have to coalesce that rbs tablespace quit often to get scattered/fragmented extents in contitgous extents Regards Rafiq Reply-To: [EMAIL PROTECTED] To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Date: Wed, 13 Feb 2002 10:28:58 -0800 IN THIS CASE yes, but NOT BEFORE That was my point. Thanks, Best, Sergey -Original Message- Sent: Wednesday, February 13, 2002 12:54 PM To: Multiple recipients of list ORACLE-L In many cases you will have to take RB segments offline and drop them, then re-create --Even in this case you have to coalesce rbs tablespace before recreating rollback segments using that particular tablespace to make all released extents as contigous...Besides it is a good practice to reorg rollback segments.. Regards Rafiq Reply-To: [EMAIL PROTECTED] To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Date: Wed, 13 Feb 2002 08:58:47 -0800 Hi, guys, Just my $0.02, coalescing free space will NEVER eliminate fragmentation. In many cases you will have to take RB segments offline and drop them, then re-create. In the interim period you might want to have a big rollback segment(or a few) in another tablespace. Best, Sergey -Original Message- Sent: Wednesday, February 13, 2002 11:03 AM To: Multiple recipients of list ORACLE-L Samir, It is not coalesced automatically at shutdown and startup. It is to be coalesced manually. It applies to any tablespace with pctincrease set as 0(zero). I have no idea of 9i but this is normal behaviour of ver 7.3.4 to 8.1.7... Regards Rafiq Reply-To: [EMAIL PROTECTED] To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Date: Wed, 13 Feb 2002 05:48:28 -0800 Rafiq, Just a small questionr the rollback segment extents in the rollback segment tablespace de-allocated when the database is shut down ?? In that case, does the fragmentation remain when the database is restarted or r the blocks coalesced automatically ?? Regards, Samir Samir Sarkar Oracle DBA - Lennon Team SchlumbergerSema Email : [EMAIL PROTECTED] [EMAIL PROTECTED] Phone : +44 (0) 115 - 957 6217 EPABX : +44 (0) 115 - 957 6418 Ext. 76217 Fax : +44 (0) 115 - 957 6018 -Original Message- Sent: 12 February 2002 17:49 To: Multiple recipients of list ORACLE-L Is your tablespace fragmented as it is not finding contingous extent .. TRy to coalesce your subject tablespace and try... you can use following script to check whether coalesing is required or not.. If percent is 100 then coalesce it.. select substr(tablespace_name,1,10)TS_NAME,total_extents Total_Extnts,extents_coalesced,round(percent_extents_coalesced,0) from dba_free_space_coalesced order by tablespace_name / HTH Regards Rafiq Reply-To: [EMAIL PROTECTED] To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Date: Tue, 12 Feb 2002 02:33:19 -0800 All, The following is the pitiable scenario I am in and would really appreciate a bit of help : We were running a long running delete job which gave the following error : Oracle8 Enterprise Edition Release 8.0.5.2.1 - Production PL/SQL Release 8.0.5.2.0 - Production SQLWKS EXECUTE PKG_PAS_AnnualDataMaintenance.Delete_OldData (2000, '/home
RE: Rollback Segment Problem
Here you are absolutely right... Regards Rafiq Reply-To: [EMAIL PROTECTED] To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Date: Wed, 13 Feb 2002 11:59:53 -0800 Exactly. It depends pretty much on the application, how often it commits transactions, segment sizes, of course, OLTP amount etc. You can't always wait for SMON to wake up and do its job. The point is COALESCING alone won't always help and you have to defrag. Another point is ONE thing is reading the manuals, and ANOTHER one is dealing with rollback issues on every day basis. Best regards to everyone, Sergey -Original Message- Sent: Wednesday, February 13, 2002 2:14 PM To: Multiple recipients of list ORACLE-L Thanks...even is not true completely...if you have seen responses of Sameer Sarkar, originator of this thread...resolved his problem after coalesing his rbs table_space... I have to dealt with this situation on one of our databases because of some codes which results in lot of shrinkage of rollback segment and we have to coalesce that rbs tablespace quit often to get scattered/fragmented extents in contitgous extents Regards Rafiq Reply-To: [EMAIL PROTECTED] To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Date: Wed, 13 Feb 2002 10:28:58 -0800 IN THIS CASE yes, but NOT BEFORE That was my point. Thanks, Best, Sergey -Original Message- Sent: Wednesday, February 13, 2002 12:54 PM To: Multiple recipients of list ORACLE-L In many cases you will have to take RB segments offline and drop them, then re-create --Even in this case you have to coalesce rbs tablespace before recreating rollback segments using that particular tablespace to make all released extents as contigous...Besides it is a good practice to reorg rollback segments.. Regards Rafiq Reply-To: [EMAIL PROTECTED] To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Date: Wed, 13 Feb 2002 08:58:47 -0800 Hi, guys, Just my $0.02, coalescing free space will NEVER eliminate fragmentation. In many cases you will have to take RB segments offline and drop them, then re-create. In the interim period you might want to have a big rollback segment(or a few) in another tablespace. Best, Sergey -Original Message- Sent: Wednesday, February 13, 2002 11:03 AM To: Multiple recipients of list ORACLE-L Samir, It is not coalesced automatically at shutdown and startup. It is to be coalesced manually. It applies to any tablespace with pctincrease set as 0(zero). I have no idea of 9i but this is normal behaviour of ver 7.3.4 to 8.1.7... Regards Rafiq Reply-To: [EMAIL PROTECTED] To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Date: Wed, 13 Feb 2002 05:48:28 -0800 Rafiq, Just a small questionr the rollback segment extents in the rollback segment tablespace de-allocated when the database is shut down ?? In that case, does the fragmentation remain when the database is restarted or r the blocks coalesced automatically ?? Regards, Samir Samir Sarkar Oracle DBA - Lennon Team SchlumbergerSema Email : [EMAIL PROTECTED] [EMAIL PROTECTED] Phone : +44 (0) 115 - 957 6217 EPABX : +44 (0) 115 - 957 6418 Ext. 76217 Fax : +44 (0) 115 - 957 6018 -Original Message- Sent: 12 February 2002 17:49 To: Multiple recipients of list ORACLE-L Is your tablespace fragmented as it is not finding contingous extent .. TRy to coalesce your subject tablespace and try... you can use following script to check whether coalesing is required or not.. If percent is 100 then coalesce it.. select substr(tablespace_name,1,10)TS_NAME,total_extents Total_Extnts,extents_coalesced,round(percent_extents_coalesced,0) from dba_free_space_coalesced order by tablespace_name / HTH Regards Rafiq Reply-To: [EMAIL PROTECTED] To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Date: Tue, 12 Feb 2002 02:33:19 -0800 All, The following is the pitiable scenario I am in and would really appreciate a bit of help : We were running a long running delete job which gave the following error : Oracle8 Enterprise Edition Release 8.0.5.2.1 - Production PL/SQL Release 8.0.5.2.0 - Production SQLWKS EXECUTE PKG_PAS_AnnualDataMaintenance.Delete_OldData (2000, '/home/sqribe/work/live', '2000AnnualDataDelete.log'); ORA-01562: failed to extend rollback segment number 8 ORA-01650: unable to extend rollback segment RSUNDB08 by 50 in tablespace RSUN425 ORA-06512: at PAS.PKG_PAS_ANNUALDATAMAINTENANCE, line 66 ORA-06512: at line 2 I don't understand why it ran out of rollback space. The rollback segment has a virtually unlimited number of extents, so the only explanation I can see is that this particular rollback segment expanded until it exceeded the space allocation for the whole tablespace RSUN425,
RE: Rollback Segment Problem
You never need to recreate the rollback segments (DROP and CREATE) unless the rollback segment dies. THe rollback segment will die after approx 4M (the number is not very accurate.. okay,, some number)transactions. THis is the only case I need to drop and recreate them. Best Regards, K Gopalakrishnan Bangalore, INDIA -Original Message- Rafiq Sent: Wednesday, February 13, 2002 9:54 AM To: Multiple recipients of list ORACLE-L In many cases you will have to take RB segments offline and drop them, then re-create - _ Do You Yahoo!? Get your free @yahoo.com address at http://mail.yahoo.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: K Gopalakrishnan INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Re: Rollback Segment Problem
Is your tablespace fragmented as it is not finding contingous extent .. TRy to coalesce your subject tablespace and try... you can use following script to check whether coalesing is required or not.. If percent is 100 then coalesce it.. select substr(tablespace_name,1,10)TS_NAME,total_extents Total_Extnts,extents_coalesced,round(percent_extents_coalesced,0) from dba_free_space_coalesced order by tablespace_name / HTH Regards Rafiq Reply-To: [EMAIL PROTECTED] To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Date: Tue, 12 Feb 2002 02:33:19 -0800 All, The following is the pitiable scenario I am in and would really appreciate a bit of help : We were running a long running delete job which gave the following error : Oracle8 Enterprise Edition Release 8.0.5.2.1 - Production PL/SQL Release 8.0.5.2.0 - Production SQLWKS EXECUTE PKG_PAS_AnnualDataMaintenance.Delete_OldData (2000, '/home/sqribe/work/live', '2000AnnualDataDelete.log'); ORA-01562: failed to extend rollback segment number 8 ORA-01650: unable to extend rollback segment RSUNDB08 by 50 in tablespace RSUN425 ORA-06512: at PAS.PKG_PAS_ANNUALDATAMAINTENANCE, line 66 ORA-06512: at line 2 I don't understand why it ran out of rollback space. The rollback segment has a virtually unlimited number of extents, so the only explanation I can see is that this particular rollback segment expanded until it exceeded the space allocation for the whole tablespace RSUN425, which is 3,000M. However, Oracle Storage Manager shows only 200M of the tablespace used, and it also shows high water marks against each rollback segment, none of which exceeds 400M. This would suggest to me that none of the previous month's deletions exceeded 400M per month, so why should it fail on this particular one when it had nearly 3,000M available? Is there something I am not understanding about rollback segments ?? Thanks and Regards, Samir Samir Sarkar Oracle DBA - Lennon Team SchlumbergerSema Email : [EMAIL PROTECTED] [EMAIL PROTECTED] Phone : +44 (0) 115 - 957 6217 EPABX : +44 (0) 115 - 957 6418 Ext. 76217 Fax : +44 (0) 115 - 957 6018 ___ This email is confidential and intended solely for the use of the individual to whom it is addressed. Any views or opinions presented are solely those of the author and do not necessarily represent those of SchlumbergerSema. If you are not the intended recipient, be advised that you have received this email in error and that any use, dissemination, forwarding, printing, or copying of this email is strictly prohibited. If you have received this email in error please notify the SchlumbergerSema Helpdesk by telephone on +44 (0) 121 627 5600. ___ -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: SARKAR, Samir INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). MOHAMMAD RAFIQ _ Chat with friends online, try MSN Messenger: http://messenger.msn.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Mohammad Rafiq INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: Rollback Segment Problem
Rafiq, While your script provides a lot of good information, it sure is slow on my system. It took 1:44 minutes while my script below took 420 mseconds. I was really surprised as my script has a self-join. Then I checked out dba_free_space_coalesced. It sure is complicated as it calls on two other views and a table. select a.tablespace_name, count(a.tablespace_name) ContinguousFreeBlocks from dba_free_space a, dba_free_space b where a.tablespace_name = b.tablespace_name and a.file_id = b.file_id and a.block_id = b.block_id + b.Blocks group by a.tablespace_name; If ContinguousFreeBlocks is 10, I coalesce. Jerry Whittle ACIFICS DBA NCI Information Systems Inc. [EMAIL PROTECTED] 618-622-4145 -Original Message- From: Mohammad Rafiq [SMTP:[EMAIL PROTECTED]] Is your tablespace fragmented as it is not finding contingous extent .. TRy to coalesce your subject tablespace and try... you can use following script to check whether coalesing is required or not.. If percent is 100 then coalesce it.. select substr(tablespace_name,1,10)TS_NAME,total_extents Total_Extnts,extents_coalesced,round(percent_extents_coalesced,0) from dba_free_space_coalesced order by tablespace_name / HTH Regards Rafiq -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Whittle Jerome Contr NCI INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: Rollback Segment Problem
Jerry, Thanks for sharing script. We might have better systems as timing was never be a issue. I just tested my script on 2 systems and it ran between 34.5 mseconds to 4.65 seconds...and on the basis of it is results we coalesce all such tablespaces. I prefer to keep pctincrease of tablespaces as 0(zero) and manually coalesing as and when it is required So have you ran your deletion job after coalesing or not? Regards Rafiq To: [EMAIL PROTECTED] CC: [EMAIL PROTECTED] Date: Tue, 12 Feb 2002 15:28:41 -0600 Rafiq, While your script provides a lot of good information, it sure is slow on my system. It took 1:44 minutes while my script below took 420 mseconds. I was really surprised as my script has a self-join. Then I checked out dba_free_space_coalesced. It sure is complicated as it calls on two other views and a table. select a.tablespace_name, count(a.tablespace_name) ContinguousFreeBlocks from dba_free_space a, dba_free_space b where a.tablespace_name = b.tablespace_name and a.file_id = b.file_id and a.block_id = b.block_id + b.Blocks group by a.tablespace_name; If ContinguousFreeBlocks is 10, I coalesce. Jerry Whittle ACIFICS DBA NCI Information Systems Inc. [EMAIL PROTECTED] 618-622-4145 -Original Message- From:Mohammad Rafiq [SMTP:[EMAIL PROTECTED]] Is your tablespace fragmented as it is not finding contingous extent .. TRy to coalesce your subject tablespace and try... you can use following script to check whether coalesing is required or not.. If percent is 100 then coalesce it.. select substr(tablespace_name,1,10)TS_NAME,total_extents Total_Extnts,extents_coalesced,round(percent_extents_coalesced,0) from dba_free_space_coalesced order by tablespace_name / HTH Regards Rafiq MOHAMMAD RAFIQ _ Get your FREE download of MSN Explorer at http://explorer.msn.com/intl.asp. -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Mohammad Rafiq INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
