RE: ALTER ROLLBACK SEGMENT rbs SHRINK
Hi all, I did a test and my conclusions are: 1. You can shrink your rollback segment down to (next + initial + a bit of header (not so sure the size)) not down to initial * minextents this will cause ora 1555 for sure What I did for test ALTER ROLLBACK SEGMENT rbs SHRINK TO 1M; to get minimal shrink. Oracle will shrink as smaller size as possible. 2. You must set if next initial then Optimal size next * minextents elseif initial next then Optimal size initial * minextents else Oracle recommend initial = next endif Note: Sorry I'm not log my sql command during test This is trial and error type of test Supporting Document from Oracle: Set an Optimal Number of Extents for Each Rollback Segment You should carefully assess the kind of transactions the system runs when setting the OPTIMAL parameter for each rollback segment. For a system that executes long-running transactions frequently, OPTIMAL should be large so that Oracle does not have to shrink and allocate extents frequently. Also, for a system that executes long queries on active data, OPTIMAL should be large to avoid snapshot too old errors. OPTIMAL should be smaller for a system that mainly executes short transactions and queries so that the rollback segments remain small enough to be cached in memory, thus improving system performance. Sinardy -Original Message- Carmichael Sent: 31 January 2002 21:05 To: Multiple recipients of list ORACLE-L very strange got the same results you did. But when I did my own testing I didn't shrink below initial*min_extents Now, I had done one thing you didn't, which was to run a delete that would force the rollback segment to extend before I tried the shrink. Strangeness abounds Anyone know someone in Oracle who can go in and read the code? G --- Rachel Carmichael [EMAIL PROTECTED] wrote: doesn't sound like you are doing anything stupid, and I ran on 8.1.6.0 and it said it would shrink it but didn't. I'll use your test case against my databases in the morning --- Johnston, Tim [EMAIL PROTECTED] wrote: Ok... I've ran this test case on both a 8.1.6.0 and a 8.1.7.3 database... I've ran it with both public and private rollback segments (just in case)... I've looked at rssize in v$rollstat and sum(bytes) from dba_segments... And, in all cases, it allows me to shrink the segment to less then both optimal and min_extents*extent_size even though optimal=min_extents*extent_size... Is there something wrong in the test case? Has anyone ran this on one of their test databases? I'm confused since the test case seems straight forward... The way I read, it allows me to perform the shrink and the resulting segment is actually the size I specify... Am I doing something stupid in the test case? Thanks Tim PS - Here is the test case again... column segment_name format a30 create public rollback segment tim tablespace rbs storage ( initial 1M next 1M minextents 20 maxextents 100 optimal 20M ); alter rollback segment tim online; select segment_name, min_extents, initial_extent, next_extent from dba_segments where segment_name = 'TIM'; select rs.extents, rs.rssize/1048576 , rs.optsize/1048576 from dba_rollback_segs drs, v$rollstat rs where drs.segment_name = 'TIM' and drs.segment_id = rs.usn; alter rollback segment TIM shrink to 10M; select segment_name, min_extents, initial_extent, next_extent from dba_segments where segment_name = 'TIM'; select rs.extents, rs.rssize, rs.optsize from dba_rollback_segs drs, v$rollstat rs where drs.segment_name = 'TIM' and drs.segment_id = rs.usn; alter rollback segment tim offline; drop rollback segment tim; -Original Message- Sent: Wednesday, January 30, 2002 2:31 PM To: Multiple recipients of list ORACLE-L Um... Must be version differences... Here is a test case I ran on my 8.1.7.3 machine... SQL create public rollback segment tim tablespace rbs storage ( initial 1M next 1M minextents 20 maxextents 100 optimal 20M ); Rollback segment created. SQL alter rollback segment tim online; Rollback segment altered. SQL select segment_name, min_extents, initial_extent, next_extent 2from dba_segments 3 where segment_name = 'TIM'; SEGMENT_NAME MIN_EXTENTS INITIAL_EXTENT NEXT_EXTENT -- --- -- --- TIM 201048576 1048576 SQL select rs.extents, rs.rssize/1048576 , rs.optsize/1048576 2from dba_rollback_segs drs, 3 v$rollstat rs 4 where drs.segment_name = 'TIM' 5 and drs.segment_id = rs.usn; EXTENTS RS.RSSIZE/1048576 RS.OPTSIZE/1048576 -- - -- 20 20.296875
RE: ALTER ROLLBACK SEGMENT rbs SHRINK
but the rollback segment could be shrunk to optimal in the course of Oracle going about its business in the database (say another user is assigned to that rollback segment and needs to extend, Oracle will first shrink it before extending). and if no one is accessing the data in the rollback segment that is being shrunk then it won't cause an ora-1555 at all. so you can't really say that a shrink will cause an ora-1555. There's more to the cause of the ora-1555 than just shrinking the rollback segment --- Johnston, Tim [EMAIL PROTECTED] wrote: Can what cause a ORA-1555? A shrink? If that is the question... The answer is yes... Tim -Original Message- Sent: Thursday, January 31, 2002 11:00 PM To: Multiple recipients of list ORACLE-L Will this cause ora 1555 ? -Original Message- Tim Sent: 31 January 2002 03:31 To: Multiple recipients of list ORACLE-L Um... Must be version differences... Here is a test case I ran on my 8.1.7.3 machine... SQL create public rollback segment tim tablespace rbs storage ( initial 1M next 1M minextents 20 maxextents 100 optimal 20M ); Rollback segment created. SQL alter rollback segment tim online; Rollback segment altered. SQL select segment_name, min_extents, initial_extent, next_extent 2from dba_segments 3 where segment_name = 'TIM'; SEGMENT_NAME MIN_EXTENTS INITIAL_EXTENT NEXT_EXTENT -- --- -- --- TIM 201048576 1048576 SQL select rs.extents, rs.rssize/1048576 , rs.optsize/1048576 2from dba_rollback_segs drs, 3 v$rollstat rs 4 where drs.segment_name = 'TIM' 5 and drs.segment_id = rs.usn; EXTENTS RS.RSSIZE/1048576 RS.OPTSIZE/1048576 -- - -- 20 20.296875 20 SQL alter rollback segment TIM shrink to 10M; Rollback segment altered. SQL select segment_name, min_extents, initial_extent, next_extent 2from dba_segments 3 where segment_name = 'TIM'; SEGMENT_NAME MIN_EXTENTS INITIAL_EXTENT NEXT_EXTENT -- --- -- --- TIM 201048576 1048576 SQL select rs.extents, rs.rssize, rs.optsize 2from dba_rollback_segs drs, 3 v$rollstat rs 4 where drs.segment_name = 'TIM' 5 and drs.segment_id = rs.usn; EXTENTS RSSIZEOPTSIZE -- -- -- 10 10633216 20971520 SQL alter rollback segment tim offline; Rollback segment altered. SQL drop rollback segment tim; Rollback segment dropped. SQL What version is your test running on? Tim PS - Here is my test case if you want to run it... column segment_name format a30 create public rollback segment tim tablespace rbs storage ( initial 1M next 1M minextents 20 maxextents 100 optimal 20M ); alter rollback segment tim online; select segment_name, min_extents, initial_extent, next_extent from dba_segments where segment_name = 'TIM'; select rs.extents, rs.rssize/1048576 , rs.optsize/1048576 from dba_rollback_segs drs, v$rollstat rs where drs.segment_name = 'TIM' and drs.segment_id = rs.usn; alter rollback segment TIM shrink to 10M; select segment_name, min_extents, initial_extent, next_extent from dba_segments where segment_name = 'TIM'; select rs.extents, rs.rssize, rs.optsize from dba_rollback_segs drs, v$rollstat rs where drs.segment_name = 'TIM' and drs.segment_id = rs.usn; alter rollback segment tim offline; drop rollback segment tim; -Original Message- Sent: Wednesday, January 30, 2002 2:17 PM To: Multiple recipients of list ORACLE-L testing if the initial extent*min_extents is less than the optimal, yes you can shrink below optimal. if initial*minextents = optimal, you can execute an alter rollback segment statement that lists a shrink size that is less than optimal AND less than initial*minextents and it will not fail. But if you LOOK at the size it shrinks to, it's initial*minextents. so you can kinda shrink below optimal, the statement won't fail, but Oracle will ignore the number you give it. --- Johnston, Tim [EMAIL PROTECTED] wrote: You know what? I thought the same thing... But, the curious side of me decided to give it a try to be sure... And guess what... It works? Learn something new everyday... SQL select segment_name,sum(bytes) from dba_segments where tablespace_name = 'RBS' and segment_name = 'RBS17' group by segment_name; SEGMENT_NAME SUM(BYTES) -- RBS17 22364160 SQL select rs.optsize, rs.extents 2from dba_rollback_segs drs, 3 v$rollstat rs 4 where
RE: ALTER ROLLBACK SEGMENT rbs SHRINK
if you don't have optimal set, you need to manually monitor the size of your rollback segments or you can hit maxextents on them or run out of room in your rollback tablespace. It's best to size the rollback segments so that most of the time they don't need to extend. Then when you add optimal, it shrinks them back for the unusual cases (like one shot major loads) that extends them past the size you've set. --- Sinard Xing [EMAIL PROTECTED] wrote: Oracle said, optimal value will automatically shrink your rbs (with continuous extents off course). Since this is the case do you think is good to add the value of optimal. Sinardy -Original Message- Tim Sent: 01 February 2002 12:30 To: Multiple recipients of list ORACLE-L Can what cause a ORA-1555? A shrink? If that is the question... The answer is yes... Tim -Original Message- Sent: Thursday, January 31, 2002 11:00 PM To: Multiple recipients of list ORACLE-L Will this cause ora 1555 ? -Original Message- Tim Sent: 31 January 2002 03:31 To: Multiple recipients of list ORACLE-L Um... Must be version differences... Here is a test case I ran on my 8.1.7.3 machine... SQL create public rollback segment tim tablespace rbs storage ( initial 1M next 1M minextents 20 maxextents 100 optimal 20M ); Rollback segment created. SQL alter rollback segment tim online; Rollback segment altered. SQL select segment_name, min_extents, initial_extent, next_extent 2from dba_segments 3 where segment_name = 'TIM'; SEGMENT_NAME MIN_EXTENTS INITIAL_EXTENT NEXT_EXTENT -- --- -- --- TIM 201048576 1048576 SQL select rs.extents, rs.rssize/1048576 , rs.optsize/1048576 2from dba_rollback_segs drs, 3 v$rollstat rs 4 where drs.segment_name = 'TIM' 5 and drs.segment_id = rs.usn; EXTENTS RS.RSSIZE/1048576 RS.OPTSIZE/1048576 -- - -- 20 20.296875 20 SQL alter rollback segment TIM shrink to 10M; Rollback segment altered. SQL select segment_name, min_extents, initial_extent, next_extent 2from dba_segments 3 where segment_name = 'TIM'; SEGMENT_NAME MIN_EXTENTS INITIAL_EXTENT NEXT_EXTENT -- --- -- --- TIM 201048576 1048576 SQL select rs.extents, rs.rssize, rs.optsize 2from dba_rollback_segs drs, 3 v$rollstat rs 4 where drs.segment_name = 'TIM' 5 and drs.segment_id = rs.usn; EXTENTS RSSIZEOPTSIZE -- -- -- 10 10633216 20971520 SQL alter rollback segment tim offline; Rollback segment altered. SQL drop rollback segment tim; Rollback segment dropped. SQL What version is your test running on? Tim PS - Here is my test case if you want to run it... column segment_name format a30 create public rollback segment tim tablespace rbs storage ( initial 1M next 1M minextents 20 maxextents 100 optimal 20M ); alter rollback segment tim online; select segment_name, min_extents, initial_extent, next_extent from dba_segments where segment_name = 'TIM'; select rs.extents, rs.rssize/1048576 , rs.optsize/1048576 from dba_rollback_segs drs, v$rollstat rs where drs.segment_name = 'TIM' and drs.segment_id = rs.usn; alter rollback segment TIM shrink to 10M; select segment_name, min_extents, initial_extent, next_extent from dba_segments where segment_name = 'TIM'; select rs.extents, rs.rssize, rs.optsize from dba_rollback_segs drs, v$rollstat rs where drs.segment_name = 'TIM' and drs.segment_id = rs.usn; alter rollback segment tim offline; drop rollback segment tim; -Original Message- Sent: Wednesday, January 30, 2002 2:17 PM To: Multiple recipients of list ORACLE-L testing if the initial extent*min_extents is less than the optimal, yes you can shrink below optimal. if initial*minextents = optimal, you can execute an alter rollback segment statement that lists a shrink size that is less than optimal AND less than initial*minextents and it will not fail. But if you LOOK at the size it shrinks to, it's initial*minextents. so you can kinda shrink below optimal, the statement won't fail, but Oracle will ignore the number you give it. --- Johnston, Tim [EMAIL PROTECTED] wrote: You know what? I thought the same thing... But, the curious side of me decided to give it a try to be sure... And guess what... It works? Learn something new everyday... SQL select segment_name,sum(bytes) from dba_segments where tablespace_name = 'RBS' and segment_name = 'RBS17' group by segment_name; SEGMENT_NAME SUM(BYTES)
RE: ALTER ROLLBACK SEGMENT rbs SHRINK
You are completely correct... I read his original questions as Can a shrink cause a ORA-1555... Not Will a shrink cause a ORA-1555... A shrink CAN cause a ORA-1555 in the correct set of circumstances... A shrink WILL NOT always cause a ORA-1555... Sorry Tim -Original Message- Sent: Friday, February 01, 2002 8:00 AM To: Multiple recipients of list ORACLE-L but the rollback segment could be shrunk to optimal in the course of Oracle going about its business in the database (say another user is assigned to that rollback segment and needs to extend, Oracle will first shrink it before extending). and if no one is accessing the data in the rollback segment that is being shrunk then it won't cause an ora-1555 at all. so you can't really say that a shrink will cause an ora-1555. There's more to the cause of the ora-1555 than just shrinking the rollback segment --- Johnston, Tim [EMAIL PROTECTED] wrote: Can what cause a ORA-1555? A shrink? If that is the question... The answer is yes... Tim -Original Message- Sent: Thursday, January 31, 2002 11:00 PM To: Multiple recipients of list ORACLE-L Will this cause ora 1555 ? -Original Message- Tim Sent: 31 January 2002 03:31 To: Multiple recipients of list ORACLE-L Um... Must be version differences... Here is a test case I ran on my 8.1.7.3 machine... SQL create public rollback segment tim tablespace rbs storage ( initial 1M next 1M minextents 20 maxextents 100 optimal 20M ); Rollback segment created. SQL alter rollback segment tim online; Rollback segment altered. SQL select segment_name, min_extents, initial_extent, next_extent 2from dba_segments 3 where segment_name = 'TIM'; SEGMENT_NAME MIN_EXTENTS INITIAL_EXTENT NEXT_EXTENT -- --- -- --- TIM 201048576 1048576 SQL select rs.extents, rs.rssize/1048576 , rs.optsize/1048576 2from dba_rollback_segs drs, 3 v$rollstat rs 4 where drs.segment_name = 'TIM' 5 and drs.segment_id = rs.usn; EXTENTS RS.RSSIZE/1048576 RS.OPTSIZE/1048576 -- - -- 20 20.296875 20 SQL alter rollback segment TIM shrink to 10M; Rollback segment altered. SQL select segment_name, min_extents, initial_extent, next_extent 2from dba_segments 3 where segment_name = 'TIM'; SEGMENT_NAME MIN_EXTENTS INITIAL_EXTENT NEXT_EXTENT -- --- -- --- TIM 201048576 1048576 SQL select rs.extents, rs.rssize, rs.optsize 2from dba_rollback_segs drs, 3 v$rollstat rs 4 where drs.segment_name = 'TIM' 5 and drs.segment_id = rs.usn; EXTENTS RSSIZEOPTSIZE -- -- -- 10 10633216 20971520 SQL alter rollback segment tim offline; Rollback segment altered. SQL drop rollback segment tim; Rollback segment dropped. SQL What version is your test running on? Tim PS - Here is my test case if you want to run it... column segment_name format a30 create public rollback segment tim tablespace rbs storage ( initial 1M next 1M minextents 20 maxextents 100 optimal 20M ); alter rollback segment tim online; select segment_name, min_extents, initial_extent, next_extent from dba_segments where segment_name = 'TIM'; select rs.extents, rs.rssize/1048576 , rs.optsize/1048576 from dba_rollback_segs drs, v$rollstat rs where drs.segment_name = 'TIM' and drs.segment_id = rs.usn; alter rollback segment TIM shrink to 10M; select segment_name, min_extents, initial_extent, next_extent from dba_segments where segment_name = 'TIM'; select rs.extents, rs.rssize, rs.optsize from dba_rollback_segs drs, v$rollstat rs where drs.segment_name = 'TIM' and drs.segment_id = rs.usn; alter rollback segment tim offline; drop rollback segment tim; -Original Message- Sent: Wednesday, January 30, 2002 2:17 PM To: Multiple recipients of list ORACLE-L testing if the initial extent*min_extents is less than the optimal, yes you can shrink below optimal. if initial*minextents = optimal, you can execute an alter rollback segment statement that lists a shrink size that is less than optimal AND less than initial*minextents and it will not fail. But if you LOOK at the size it shrinks to, it's initial*minextents. so you can kinda shrink below optimal, the statement won't fail, but Oracle will ignore the number you give it. --- Johnston, Tim [EMAIL PROTECTED] wrote: You know what? I thought the same thing... But, the curious side of me decided to give it a try to be sure... And guess what... It works? Learn something new everyday... SQL
RE: ALTER ROLLBACK SEGMENT rbs SHRINK
very strange got the same results you did. But when I did my own testing I didn't shrink below initial*min_extents Now, I had done one thing you didn't, which was to run a delete that would force the rollback segment to extend before I tried the shrink. Strangeness abounds Anyone know someone in Oracle who can go in and read the code? G --- Rachel Carmichael [EMAIL PROTECTED] wrote: doesn't sound like you are doing anything stupid, and I ran on 8.1.6.0 and it said it would shrink it but didn't. I'll use your test case against my databases in the morning --- Johnston, Tim [EMAIL PROTECTED] wrote: Ok... I've ran this test case on both a 8.1.6.0 and a 8.1.7.3 database... I've ran it with both public and private rollback segments (just in case)... I've looked at rssize in v$rollstat and sum(bytes) from dba_segments... And, in all cases, it allows me to shrink the segment to less then both optimal and min_extents*extent_size even though optimal=min_extents*extent_size... Is there something wrong in the test case? Has anyone ran this on one of their test databases? I'm confused since the test case seems straight forward... The way I read, it allows me to perform the shrink and the resulting segment is actually the size I specify... Am I doing something stupid in the test case? Thanks Tim PS - Here is the test case again... column segment_name format a30 create public rollback segment tim tablespace rbs storage ( initial 1M next 1M minextents 20 maxextents 100 optimal 20M ); alter rollback segment tim online; select segment_name, min_extents, initial_extent, next_extent from dba_segments where segment_name = 'TIM'; select rs.extents, rs.rssize/1048576 , rs.optsize/1048576 from dba_rollback_segs drs, v$rollstat rs where drs.segment_name = 'TIM' and drs.segment_id = rs.usn; alter rollback segment TIM shrink to 10M; select segment_name, min_extents, initial_extent, next_extent from dba_segments where segment_name = 'TIM'; select rs.extents, rs.rssize, rs.optsize from dba_rollback_segs drs, v$rollstat rs where drs.segment_name = 'TIM' and drs.segment_id = rs.usn; alter rollback segment tim offline; drop rollback segment tim; -Original Message- Sent: Wednesday, January 30, 2002 2:31 PM To: Multiple recipients of list ORACLE-L Um... Must be version differences... Here is a test case I ran on my 8.1.7.3 machine... SQL create public rollback segment tim tablespace rbs storage ( initial 1M next 1M minextents 20 maxextents 100 optimal 20M ); Rollback segment created. SQL alter rollback segment tim online; Rollback segment altered. SQL select segment_name, min_extents, initial_extent, next_extent 2from dba_segments 3 where segment_name = 'TIM'; SEGMENT_NAME MIN_EXTENTS INITIAL_EXTENT NEXT_EXTENT -- --- -- --- TIM 201048576 1048576 SQL select rs.extents, rs.rssize/1048576 , rs.optsize/1048576 2from dba_rollback_segs drs, 3 v$rollstat rs 4 where drs.segment_name = 'TIM' 5 and drs.segment_id = rs.usn; EXTENTS RS.RSSIZE/1048576 RS.OPTSIZE/1048576 -- - -- 20 20.296875 20 SQL alter rollback segment TIM shrink to 10M; Rollback segment altered. SQL select segment_name, min_extents, initial_extent, next_extent 2from dba_segments 3 where segment_name = 'TIM'; SEGMENT_NAME MIN_EXTENTS INITIAL_EXTENT NEXT_EXTENT -- --- -- --- TIM 201048576 1048576 SQL select rs.extents, rs.rssize, rs.optsize 2from dba_rollback_segs drs, 3 v$rollstat rs 4 where drs.segment_name = 'TIM' 5 and drs.segment_id = rs.usn; EXTENTS RSSIZEOPTSIZE -- -- -- 10 10633216 20971520 SQL alter rollback segment tim offline; Rollback segment altered. SQL drop rollback segment tim; Rollback segment dropped. SQL What version is your test running on? Tim PS - Here is my test case if you want to run it... column segment_name format a30 create public rollback segment tim tablespace rbs storage ( initial 1M next 1M minextents 20 maxextents 100 optimal 20M ); alter rollback segment tim online; select segment_name, min_extents, initial_extent, next_extent from dba_segments where segment_name = 'TIM'; select rs.extents, rs.rssize/1048576 , rs.optsize/1048576 from dba_rollback_segs drs, v$rollstat rs where drs.segment_name = 'TIM' and drs.segment_id = rs.usn;
RE: ALTER ROLLBACK SEGMENT rbs SHRINK
Will this cause ora 1555 ? -Original Message- Tim Sent: 31 January 2002 03:31 To: Multiple recipients of list ORACLE-L Um... Must be version differences... Here is a test case I ran on my 8.1.7.3 machine... SQL create public rollback segment tim tablespace rbs storage ( initial 1M next 1M minextents 20 maxextents 100 optimal 20M ); Rollback segment created. SQL alter rollback segment tim online; Rollback segment altered. SQL select segment_name, min_extents, initial_extent, next_extent 2from dba_segments 3 where segment_name = 'TIM'; SEGMENT_NAME MIN_EXTENTS INITIAL_EXTENT NEXT_EXTENT -- --- -- --- TIM 201048576 1048576 SQL select rs.extents, rs.rssize/1048576 , rs.optsize/1048576 2from dba_rollback_segs drs, 3 v$rollstat rs 4 where drs.segment_name = 'TIM' 5 and drs.segment_id = rs.usn; EXTENTS RS.RSSIZE/1048576 RS.OPTSIZE/1048576 -- - -- 20 20.296875 20 SQL alter rollback segment TIM shrink to 10M; Rollback segment altered. SQL select segment_name, min_extents, initial_extent, next_extent 2from dba_segments 3 where segment_name = 'TIM'; SEGMENT_NAME MIN_EXTENTS INITIAL_EXTENT NEXT_EXTENT -- --- -- --- TIM 201048576 1048576 SQL select rs.extents, rs.rssize, rs.optsize 2from dba_rollback_segs drs, 3 v$rollstat rs 4 where drs.segment_name = 'TIM' 5 and drs.segment_id = rs.usn; EXTENTS RSSIZEOPTSIZE -- -- -- 10 10633216 20971520 SQL alter rollback segment tim offline; Rollback segment altered. SQL drop rollback segment tim; Rollback segment dropped. SQL What version is your test running on? Tim PS - Here is my test case if you want to run it... column segment_name format a30 create public rollback segment tim tablespace rbs storage ( initial 1M next 1M minextents 20 maxextents 100 optimal 20M ); alter rollback segment tim online; select segment_name, min_extents, initial_extent, next_extent from dba_segments where segment_name = 'TIM'; select rs.extents, rs.rssize/1048576 , rs.optsize/1048576 from dba_rollback_segs drs, v$rollstat rs where drs.segment_name = 'TIM' and drs.segment_id = rs.usn; alter rollback segment TIM shrink to 10M; select segment_name, min_extents, initial_extent, next_extent from dba_segments where segment_name = 'TIM'; select rs.extents, rs.rssize, rs.optsize from dba_rollback_segs drs, v$rollstat rs where drs.segment_name = 'TIM' and drs.segment_id = rs.usn; alter rollback segment tim offline; drop rollback segment tim; -Original Message- Sent: Wednesday, January 30, 2002 2:17 PM To: Multiple recipients of list ORACLE-L testing if the initial extent*min_extents is less than the optimal, yes you can shrink below optimal. if initial*minextents = optimal, you can execute an alter rollback segment statement that lists a shrink size that is less than optimal AND less than initial*minextents and it will not fail. But if you LOOK at the size it shrinks to, it's initial*minextents. so you can kinda shrink below optimal, the statement won't fail, but Oracle will ignore the number you give it. --- Johnston, Tim [EMAIL PROTECTED] wrote: You know what? I thought the same thing... But, the curious side of me decided to give it a try to be sure... And guess what... It works? Learn something new everyday... SQL select segment_name,sum(bytes) from dba_segments where tablespace_name = 'RBS' and segment_name = 'RBS17' group by segment_name; SEGMENT_NAME SUM(BYTES) -- RBS17 22364160 SQL select rs.optsize, rs.extents 2from dba_rollback_segs drs, 3 v$rollstat rs 4 where drs.segment_name = 'RBS17' 5 and drs.segment_id = rs.usn; OPTSIZEEXTENTS -- -- 22020096 21 SQL alter rollback segment RBS17 shrink to 10M; Rollback segment altered. SQL select segment_name,sum(bytes) from dba_segments where tablespace_name = 'RBS' and segment_name = 'RBS17' group by segment_name; SEGMENT_NAME SUM(BYTES) - -- RBS17 10649600 SQL select rs.optsize, rs.extents 2from dba_rollback_segs drs, 3 v$rollstat rs 4 where drs.segment_name = 'RBS17' 5 and drs.segment_id = rs.usn; OPTSIZEEXTENTS -- -- 22020096 10 SQL 8.1.7.3 on Solaris 2.8 Tim -Original Message- Sent: Wednesday, January 30, 2002 10:26 AM To: Multiple recipients of list ORACLE-L no, you can't shrink a rollback
RE: ALTER ROLLBACK SEGMENT rbs SHRINK
Can what cause a ORA-1555? A shrink? If that is the question... The answer is yes... Tim -Original Message- Sent: Thursday, January 31, 2002 11:00 PM To: Multiple recipients of list ORACLE-L Will this cause ora 1555 ? -Original Message- Tim Sent: 31 January 2002 03:31 To: Multiple recipients of list ORACLE-L Um... Must be version differences... Here is a test case I ran on my 8.1.7.3 machine... SQL create public rollback segment tim tablespace rbs storage ( initial 1M next 1M minextents 20 maxextents 100 optimal 20M ); Rollback segment created. SQL alter rollback segment tim online; Rollback segment altered. SQL select segment_name, min_extents, initial_extent, next_extent 2from dba_segments 3 where segment_name = 'TIM'; SEGMENT_NAME MIN_EXTENTS INITIAL_EXTENT NEXT_EXTENT -- --- -- --- TIM 201048576 1048576 SQL select rs.extents, rs.rssize/1048576 , rs.optsize/1048576 2from dba_rollback_segs drs, 3 v$rollstat rs 4 where drs.segment_name = 'TIM' 5 and drs.segment_id = rs.usn; EXTENTS RS.RSSIZE/1048576 RS.OPTSIZE/1048576 -- - -- 20 20.296875 20 SQL alter rollback segment TIM shrink to 10M; Rollback segment altered. SQL select segment_name, min_extents, initial_extent, next_extent 2from dba_segments 3 where segment_name = 'TIM'; SEGMENT_NAME MIN_EXTENTS INITIAL_EXTENT NEXT_EXTENT -- --- -- --- TIM 201048576 1048576 SQL select rs.extents, rs.rssize, rs.optsize 2from dba_rollback_segs drs, 3 v$rollstat rs 4 where drs.segment_name = 'TIM' 5 and drs.segment_id = rs.usn; EXTENTS RSSIZEOPTSIZE -- -- -- 10 10633216 20971520 SQL alter rollback segment tim offline; Rollback segment altered. SQL drop rollback segment tim; Rollback segment dropped. SQL What version is your test running on? Tim PS - Here is my test case if you want to run it... column segment_name format a30 create public rollback segment tim tablespace rbs storage ( initial 1M next 1M minextents 20 maxextents 100 optimal 20M ); alter rollback segment tim online; select segment_name, min_extents, initial_extent, next_extent from dba_segments where segment_name = 'TIM'; select rs.extents, rs.rssize/1048576 , rs.optsize/1048576 from dba_rollback_segs drs, v$rollstat rs where drs.segment_name = 'TIM' and drs.segment_id = rs.usn; alter rollback segment TIM shrink to 10M; select segment_name, min_extents, initial_extent, next_extent from dba_segments where segment_name = 'TIM'; select rs.extents, rs.rssize, rs.optsize from dba_rollback_segs drs, v$rollstat rs where drs.segment_name = 'TIM' and drs.segment_id = rs.usn; alter rollback segment tim offline; drop rollback segment tim; -Original Message- Sent: Wednesday, January 30, 2002 2:17 PM To: Multiple recipients of list ORACLE-L testing if the initial extent*min_extents is less than the optimal, yes you can shrink below optimal. if initial*minextents = optimal, you can execute an alter rollback segment statement that lists a shrink size that is less than optimal AND less than initial*minextents and it will not fail. But if you LOOK at the size it shrinks to, it's initial*minextents. so you can kinda shrink below optimal, the statement won't fail, but Oracle will ignore the number you give it. --- Johnston, Tim [EMAIL PROTECTED] wrote: You know what? I thought the same thing... But, the curious side of me decided to give it a try to be sure... And guess what... It works? Learn something new everyday... SQL select segment_name,sum(bytes) from dba_segments where tablespace_name = 'RBS' and segment_name = 'RBS17' group by segment_name; SEGMENT_NAME SUM(BYTES) -- RBS17 22364160 SQL select rs.optsize, rs.extents 2from dba_rollback_segs drs, 3 v$rollstat rs 4 where drs.segment_name = 'RBS17' 5 and drs.segment_id = rs.usn; OPTSIZEEXTENTS -- -- 22020096 21 SQL alter rollback segment RBS17 shrink to 10M; Rollback segment altered. SQL select segment_name,sum(bytes) from dba_segments where tablespace_name = 'RBS' and segment_name = 'RBS17' group by segment_name; SEGMENT_NAME SUM(BYTES) - -- RBS17 10649600 SQL select rs.optsize, rs.extents 2from dba_rollback_segs drs, 3 v$rollstat rs 4 where drs.segment_name = 'RBS17' 5 and drs.segment_id = rs.usn; OPTSIZEEXTENTS -- -- 22020096
RE: ALTER ROLLBACK SEGMENT rbs SHRINK
Oracle said, optimal value will automatically shrink your rbs (with continuous extents off course). Since this is the case do you think is good to add the value of optimal. Sinardy -Original Message- Tim Sent: 01 February 2002 12:30 To: Multiple recipients of list ORACLE-L Can what cause a ORA-1555? A shrink? If that is the question... The answer is yes... Tim -Original Message- Sent: Thursday, January 31, 2002 11:00 PM To: Multiple recipients of list ORACLE-L Will this cause ora 1555 ? -Original Message- Tim Sent: 31 January 2002 03:31 To: Multiple recipients of list ORACLE-L Um... Must be version differences... Here is a test case I ran on my 8.1.7.3 machine... SQL create public rollback segment tim tablespace rbs storage ( initial 1M next 1M minextents 20 maxextents 100 optimal 20M ); Rollback segment created. SQL alter rollback segment tim online; Rollback segment altered. SQL select segment_name, min_extents, initial_extent, next_extent 2from dba_segments 3 where segment_name = 'TIM'; SEGMENT_NAME MIN_EXTENTS INITIAL_EXTENT NEXT_EXTENT -- --- -- --- TIM 201048576 1048576 SQL select rs.extents, rs.rssize/1048576 , rs.optsize/1048576 2from dba_rollback_segs drs, 3 v$rollstat rs 4 where drs.segment_name = 'TIM' 5 and drs.segment_id = rs.usn; EXTENTS RS.RSSIZE/1048576 RS.OPTSIZE/1048576 -- - -- 20 20.296875 20 SQL alter rollback segment TIM shrink to 10M; Rollback segment altered. SQL select segment_name, min_extents, initial_extent, next_extent 2from dba_segments 3 where segment_name = 'TIM'; SEGMENT_NAME MIN_EXTENTS INITIAL_EXTENT NEXT_EXTENT -- --- -- --- TIM 201048576 1048576 SQL select rs.extents, rs.rssize, rs.optsize 2from dba_rollback_segs drs, 3 v$rollstat rs 4 where drs.segment_name = 'TIM' 5 and drs.segment_id = rs.usn; EXTENTS RSSIZEOPTSIZE -- -- -- 10 10633216 20971520 SQL alter rollback segment tim offline; Rollback segment altered. SQL drop rollback segment tim; Rollback segment dropped. SQL What version is your test running on? Tim PS - Here is my test case if you want to run it... column segment_name format a30 create public rollback segment tim tablespace rbs storage ( initial 1M next 1M minextents 20 maxextents 100 optimal 20M ); alter rollback segment tim online; select segment_name, min_extents, initial_extent, next_extent from dba_segments where segment_name = 'TIM'; select rs.extents, rs.rssize/1048576 , rs.optsize/1048576 from dba_rollback_segs drs, v$rollstat rs where drs.segment_name = 'TIM' and drs.segment_id = rs.usn; alter rollback segment TIM shrink to 10M; select segment_name, min_extents, initial_extent, next_extent from dba_segments where segment_name = 'TIM'; select rs.extents, rs.rssize, rs.optsize from dba_rollback_segs drs, v$rollstat rs where drs.segment_name = 'TIM' and drs.segment_id = rs.usn; alter rollback segment tim offline; drop rollback segment tim; -Original Message- Sent: Wednesday, January 30, 2002 2:17 PM To: Multiple recipients of list ORACLE-L testing if the initial extent*min_extents is less than the optimal, yes you can shrink below optimal. if initial*minextents = optimal, you can execute an alter rollback segment statement that lists a shrink size that is less than optimal AND less than initial*minextents and it will not fail. But if you LOOK at the size it shrinks to, it's initial*minextents. so you can kinda shrink below optimal, the statement won't fail, but Oracle will ignore the number you give it. --- Johnston, Tim [EMAIL PROTECTED] wrote: You know what? I thought the same thing... But, the curious side of me decided to give it a try to be sure... And guess what... It works? Learn something new everyday... SQL select segment_name,sum(bytes) from dba_segments where tablespace_name = 'RBS' and segment_name = 'RBS17' group by segment_name; SEGMENT_NAME SUM(BYTES) -- RBS17 22364160 SQL select rs.optsize, rs.extents 2from dba_rollback_segs drs, 3 v$rollstat rs 4 where drs.segment_name = 'RBS17' 5 and drs.segment_id = rs.usn; OPTSIZEEXTENTS -- -- 22020096 21 SQL alter rollback segment RBS17 shrink to 10M; Rollback segment altered. SQL select segment_name,sum(bytes) from dba_segments where tablespace_name = 'RBS' and segment_name = 'RBS17' group by segment_name; SEGMENT_NAME SUM(BYTES) - -- RBS17
Re: ALTER ROLLBACK SEGMENT rbs SHRINK
no, you can't shrink a rollback segment below the optimal value --- Sinard Xing [EMAIL PROTECTED] wrote: Hi, Assume INITIAL * MINEXTENT = 2M and also = OPTIMAL Do you think ALTER ROLLBACK SEGMENT rbs SHRINK TO 1M; will work ? Thanks, Sinardy -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Sinard Xing 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!? Great stuff seeking new owners in Yahoo! Auctions! http://auctions.yahoo.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Rachel Carmichael 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: ALTER ROLLBACK SEGMENT rbs SHRINK
You know what? I thought the same thing... But, the curious side of me decided to give it a try to be sure... And guess what... It works? Learn something new everyday... SQL select segment_name,sum(bytes) from dba_segments where tablespace_name = 'RBS' and segment_name = 'RBS17' group by segment_name; SEGMENT_NAME SUM(BYTES) -- RBS17 22364160 SQL select rs.optsize, rs.extents 2from dba_rollback_segs drs, 3 v$rollstat rs 4 where drs.segment_name = 'RBS17' 5 and drs.segment_id = rs.usn; OPTSIZEEXTENTS -- -- 22020096 21 SQL alter rollback segment RBS17 shrink to 10M; Rollback segment altered. SQL select segment_name,sum(bytes) from dba_segments where tablespace_name = 'RBS' and segment_name = 'RBS17' group by segment_name; SEGMENT_NAME SUM(BYTES) - -- RBS17 10649600 SQL select rs.optsize, rs.extents 2from dba_rollback_segs drs, 3 v$rollstat rs 4 where drs.segment_name = 'RBS17' 5 and drs.segment_id = rs.usn; OPTSIZEEXTENTS -- -- 22020096 10 SQL 8.1.7.3 on Solaris 2.8 Tim -Original Message- Sent: Wednesday, January 30, 2002 10:26 AM To: Multiple recipients of list ORACLE-L no, you can't shrink a rollback segment below the optimal value --- Sinard Xing [EMAIL PROTECTED] wrote: Hi, Assume INITIAL * MINEXTENT = 2M and also = OPTIMAL Do you think ALTER ROLLBACK SEGMENT rbs SHRINK TO 1M; will work ? Thanks, Sinardy -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Sinard Xing 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!? Great stuff seeking new owners in Yahoo! Auctions! http://auctions.yahoo.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Rachel Carmichael 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: Johnston, Tim 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: ALTER ROLLBACK SEGMENT rbs SHRINK
Yes, it can be shrinked below optimal as per following details specially R03 rollback segment Regards Rafiq before: NAME EXT MB OPTSIZE ACTIVE WATER SHRINKSWRAPS WAITS STATUS --- --- --- --- --- --- -- --- SYSTEM 2 0.2 0.0 0.1 00 0 ONLINE R04 57 115.8 200 0.0 115.8 00 0 ONLINE R01 50 101.6 200 0.0 101.6 00 0 ONLINE R02 50 101.6 200 0.0 101.6 00 0 ONLINE R03 99 201.1 200 0.0 201.1 00 0 ONLINE run script: SQL alter rollback segment r03 shrink to 100M; After: NAME EXT MB OPTSIZE ACTIVE WATER SHRINKSWRAPS WAITS STATUS --- --- --- --- --- --- -- --- SYSTEM 2 0.2 0.0 0.1 00 0 ONLINE R04 57 115.8 200 0.0 115.8 00 0 ONLINE R01 50 101.6 200 0.0 101.6 00 0 ONLINE R02 50 101.6 200 0.0 101.6 00 0 ONLINE R03 50 101.6 200 0.0 201.1 50 0 ONLINE Reply-To: [EMAIL PROTECTED] To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Date: Wed, 30 Jan 2002 07:25:30 -0800 no, you can't shrink a rollback segment below the optimal value --- Sinard Xing [EMAIL PROTECTED] wrote: Hi, Assume INITIAL * MINEXTENT = 2M and also = OPTIMAL Do you think ALTER ROLLBACK SEGMENT rbs SHRINK TO 1M; will work ? Thanks, Sinardy -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Sinard Xing 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!? Great stuff seeking new owners in Yahoo! Auctions! http://auctions.yahoo.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Rachel Carmichael 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: ALTER ROLLBACK SEGMENT rbs SHRINK
Here is the confirmation of what Sandeep posted: SQL select a.usn, b.name, a.optsize 2 from v$rollstat a, v$rollname b 3 where a.usn = b.usn; USN NAME OPTSIZE -- -- -- 0 SYSTEM 1 RBS01 419430400 2 RBS02 419430400 SQL alter rollback segment rbs01 shrink to 10M; Rollback segment altered. No error. Think it did it? Look below :( SQL select a.usn, b.name, a.optsize 2 from v$rollstat a, v$rollname b 3 where a.usn = b.usn; USN NAME OPTSIZE -- -- -- 0 SYSTEM 1 RBS01 419430400 2 RBS02 419430400 SQL - Kirti -Original Message- Sent: Wednesday, January 30, 2002 1:50 AM To: Multiple recipients of list ORACLE-L I don't think - it will work. You won't get any error as well as it won't shrink. Any other inputs from others??? Rgds, -Original Message- Sent: Wednesday, January 30, 2002 9:55 AM To: Multiple recipients of list ORACLE-L Hi, Assume INITIAL * MINEXTENT = 2M and also = OPTIMAL Do you think ALTER ROLLBACK SEGMENT rbs SHRINK TO 1M; will work ? Thanks, Sinardy -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Deshpande, Kirti 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: ALTER ROLLBACK SEGMENT rbs SHRINK
Yes, Mohammad is right !! I messed up by not seeing the correct column... Oracle will not honor the size you asked for (to shrink), if there are other transactions using up space more than what you asked for, but it can go below the OPTSIZE... Thanks, Mohammad. - Kirti -Original Message- Sent: Wednesday, January 30, 2002 10:40 AM To: Multiple recipients of list ORACLE-L Yes, it can be shrinked below optimal as per following details specially R03 rollback segment Regards Rafiq before: NAME EXT MB OPTSIZE ACTIVE WATER SHRINKSWRAPS WAITS STATUS --- --- --- --- --- --- -- --- SYSTEM 2 0.2 0.0 0.1 00 0 ONLINE R04 57 115.8 200 0.0 115.8 00 0 ONLINE R01 50 101.6 200 0.0 101.6 00 0 ONLINE R02 50 101.6 200 0.0 101.6 00 0 ONLINE R03 99 201.1 200 0.0 201.1 00 0 ONLINE run script: SQL alter rollback segment r03 shrink to 100M; After: NAME EXT MB OPTSIZE ACTIVE WATER SHRINKSWRAPS WAITS STATUS --- --- --- --- --- --- -- --- SYSTEM 2 0.2 0.0 0.1 00 0 ONLINE R04 57 115.8 200 0.0 115.8 00 0 ONLINE R01 50 101.6 200 0.0 101.6 00 0 ONLINE R02 50 101.6 200 0.0 101.6 00 0 ONLINE R03 50 101.6 200 0.0 201.1 50 0 ONLINE Reply-To: [EMAIL PROTECTED] To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Date: Wed, 30 Jan 2002 07:25:30 -0800 no, you can't shrink a rollback segment below the optimal value --- Sinard Xing [EMAIL PROTECTED] wrote: Hi, Assume INITIAL * MINEXTENT = 2M and also = OPTIMAL Do you think ALTER ROLLBACK SEGMENT rbs SHRINK TO 1M; will work ? Thanks, Sinardy -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Sinard Xing 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!? Great stuff seeking new owners in Yahoo! Auctions! http://auctions.yahoo.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Rachel Carmichael 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 world's 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). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Deshpande, Kirti 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
Re: ALTER ROLLBACK SEGMENT rbs SHRINK
what are the minimum extents on the rollback segments (specifically r03) as well as initial and next sizes please? If optimal is higher than the minimum extents, you're right, it can shrink down to minimum extents*initial but it doesn't make a lot of sense to set things up that way --- Mohammad Rafiq [EMAIL PROTECTED] wrote: Yes, it can be shrinked below optimal as per following details specially R03 rollback segment Regards Rafiq before: NAME EXT MB OPTSIZE ACTIVE WATER SHRINKSWRAPS WAITS STATUS --- --- --- --- --- --- -- --- SYSTEM 2 0.2 0.0 0.1 00 0 ONLINE R04 57 115.8 200 0.0 115.8 00 0 ONLINE R01 50 101.6 200 0.0 101.6 00 0 ONLINE R02 50 101.6 200 0.0 101.6 00 0 ONLINE R03 99 201.1 200 0.0 201.1 00 0 ONLINE run script: SQL alter rollback segment r03 shrink to 100M; After: NAME EXT MB OPTSIZE ACTIVE WATER SHRINKSWRAPS WAITS STATUS --- --- --- --- --- --- -- --- SYSTEM 2 0.2 0.0 0.1 00 0 ONLINE R04 57 115.8 200 0.0 115.8 00 0 ONLINE R01 50 101.6 200 0.0 101.6 00 0 ONLINE R02 50 101.6 200 0.0 101.6 00 0 ONLINE R03 50 101.6 200 0.0 201.1 50 0 ONLINE Reply-To: [EMAIL PROTECTED] To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Date: Wed, 30 Jan 2002 07:25:30 -0800 no, you can't shrink a rollback segment below the optimal value --- Sinard Xing [EMAIL PROTECTED] wrote: Hi, Assume INITIAL * MINEXTENT = 2M and also = OPTIMAL Do you think ALTER ROLLBACK SEGMENT rbs SHRINK TO 1M; will work ? Thanks, Sinardy -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Sinard Xing 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!? Great stuff seeking new owners in Yahoo! Auctions! http://auctions.yahoo.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Rachel Carmichael 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). __ Do You Yahoo!? Great stuff seeking new owners in Yahoo! Auctions! http://auctions.yahoo.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Rachel Carmichael INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists
Re: ALTER ROLLBACK SEGMENT rbs SHRINK
H, if you are shrinking below optimal doesn't that mean optimal isn't :-). John [EMAIL PROTECTED] wrote: Yes, Mohammad is right !! I messed up by not seeing the correct column... Oracle will not honor the size you asked for (to shrink), if there are other transactions using up space more than what you asked for, but it can go below the OPTSIZE... Thanks, Mohammad. - Kirti -Original Message- Sent: Wednesday, January 30, 2002 10:40 AM To: Multiple recipients of list ORACLE-L Yes, it can be shrinked below optimal as per following details specially R03 rollback segment Regards Rafiq before: NAME EXT MB OPTSIZE ACTIVE WATER SHRINKSWRAPS WAITS STATUS --- --- --- --- --- --- -- --- SYSTEM 2 0.2 0.0 0.1 00 0 ONLINE R04 57 115.8 200 0.0 115.8 00 0 ONLINE R01 50 101.6 200 0.0 101.6 00 0 ONLINE R02 50 101.6 200 0.0 101.6 00 0 ONLINE R03 99 201.1 200 0.0 201.1 00 0 ONLINE run script: SQL alter rollback segment r03 shrink to 100M; After: NAME EXT MB OPTSIZE ACTIVE WATER SHRINKSWRAPS WAITS STATUS --- --- --- --- --- --- -- --- SYSTEM 2 0.2 0.0 0.1 00 0 ONLINE R04 57 115.8 200 0.0 115.8 00 0 ONLINE R01 50 101.6 200 0.0 101.6 00 0 ONLINE R02 50 101.6 200 0.0 101.6 00 0 ONLINE R03 50 101.6 200 0.0 201.1 50 0 ONLINE Reply-To: [EMAIL PROTECTED] To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Date: Wed, 30 Jan 2002 07:25:30 -0800 no, you can't shrink a rollback segment below the optimal value --- Sinard Xing [EMAIL PROTECTED] wrote: Hi, Assume INITIAL * MINEXTENT = 2M and also = OPTIMAL Do you think ALTER ROLLBACK SEGMENT rbs SHRINK TO 1M; will work ? Thanks, Sinardy -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Sinard Xing 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!? Great stuff seeking new owners in Yahoo! Auctions! http://auctions.yahoo.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Rachel Carmichael 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 world's largest e-mail service with MSN Hotmail. http://www.hotmail.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: orantdba 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: ALTER ROLLBACK SEGMENT rbs SHRINK
You are welcome, In our shop I have been using this command occasionally to get release some space back to tablespace... Regards Rafiq Reply-To: [EMAIL PROTECTED] To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Date: Wed, 30 Jan 2002 09:55:35 -0800 Yes, Mohammad is right !! I messed up by not seeing the correct column... Oracle will not honor the size you asked for (to shrink), if there are other transactions using up space more than what you asked for, but it can go below the OPTSIZE... Thanks, Mohammad. - Kirti -Original Message- Sent: Wednesday, January 30, 2002 10:40 AM To: Multiple recipients of list ORACLE-L Yes, it can be shrinked below optimal as per following details specially R03 rollback segment Regards Rafiq before: NAME EXT MB OPTSIZE ACTIVE WATER SHRINKSWRAPS WAITS STATUS --- --- --- --- --- --- -- --- SYSTEM 2 0.2 0.0 0.1 00 0 ONLINE R04 57 115.8 200 0.0 115.8 00 0 ONLINE R01 50 101.6 200 0.0 101.6 00 0 ONLINE R02 50 101.6 200 0.0 101.6 00 0 ONLINE R03 99 201.1 200 0.0 201.1 00 0 ONLINE run script: SQL alter rollback segment r03 shrink to 100M; After: NAME EXT MB OPTSIZE ACTIVE WATER SHRINKSWRAPS WAITS STATUS --- --- --- --- --- --- -- --- SYSTEM 2 0.2 0.0 0.1 00 0 ONLINE R04 57 115.8 200 0.0 115.8 00 0 ONLINE R01 50 101.6 200 0.0 101.6 00 0 ONLINE R02 50 101.6 200 0.0 101.6 00 0 ONLINE R03 50 101.6 200 0.0 201.1 50 0 ONLINE Reply-To: [EMAIL PROTECTED] To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Date: Wed, 30 Jan 2002 07:25:30 -0800 no, you can't shrink a rollback segment below the optimal value --- Sinard Xing [EMAIL PROTECTED] wrote: Hi, Assume INITIAL * MINEXTENT = 2M and also = OPTIMAL Do you think ALTER ROLLBACK SEGMENT rbs SHRINK TO 1M; will work ? Thanks, Sinardy -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Sinard Xing 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!? Great stuff seeking new owners in Yahoo! Auctions! http://auctions.yahoo.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Rachel Carmichael 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 world's 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). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Deshpande, Kirti INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego,
RE: ALTER ROLLBACK SEGMENT rbs SHRINK
Sure sure... Make me look again... :-) SQL select segment_name, min_extents, initial_extent, next_extent 2from dba_segments 3 where segment_name = 'RBS06'; SEGMENT_NAME MIN_EXTENTS INITIAL_EXTENT NEXT_EXTENT -- --- -- --- RBS06 201048576 1048576 SQL select rs.extents, rs.rssize/1048576 , rs.optsize/1048576 2from dba_rollback_segs drs, 3 v$rollstat rs 4 where drs.segment_name = 'RBS06' 5 and drs.segment_id = rs.usn; EXTENTS RS.RSSIZE/1048576 RS.OPTSIZE/1048576 -- - -- 21 21.3125 21 SQL alter rollback segment RBS06 shrink to 10M; Rollback segment altered. SQL select segment_name, min_extents, initial_extent, next_extent 2from dba_segments 3 where segment_name = 'RBS06'; SEGMENT_NAME MIN_EXTENTS INITIAL_EXTENT NEXT_EXTENT -- --- -- --- RBS06 201048576 1048576 SQL select rs.extents, rs.rssize, rs.optsize 2from dba_rollback_segs drs, 3 v$rollstat rs 4 where drs.segment_name = 'RBS06' 5 and drs.segment_id = rs.usn; EXTENTS RSSIZEOPTSIZE -- -- -- 10 10633216 22020096 Interesting eh? Doesn't seem like this should work this way but it does... Again... YMMV... 8.1.7.3 on Solaris... Tim -Original Message- Sent: Wednesday, January 30, 2002 1:35 PM To: Multiple recipients of list ORACLE-L what are the minimum extents on the rollback segments (specifically r03) as well as initial and next sizes please? If optimal is higher than the minimum extents, you're right, it can shrink down to minimum extents*initial but it doesn't make a lot of sense to set things up that way --- Mohammad Rafiq [EMAIL PROTECTED] wrote: Yes, it can be shrinked below optimal as per following details specially R03 rollback segment Regards Rafiq before: NAME EXT MB OPTSIZE ACTIVE WATER SHRINKSWRAPS WAITS STATUS --- --- --- --- --- --- -- --- SYSTEM 2 0.2 0.0 0.1 00 0 ONLINE R04 57 115.8 200 0.0 115.8 00 0 ONLINE R01 50 101.6 200 0.0 101.6 00 0 ONLINE R02 50 101.6 200 0.0 101.6 00 0 ONLINE R03 99 201.1 200 0.0 201.1 00 0 ONLINE run script: SQL alter rollback segment r03 shrink to 100M; After: NAME EXT MB OPTSIZE ACTIVE WATER SHRINKSWRAPS WAITS STATUS --- --- --- --- --- --- -- --- SYSTEM 2 0.2 0.0 0.1 00 0 ONLINE R04 57 115.8 200 0.0 115.8 00 0 ONLINE R01 50 101.6 200 0.0 101.6 00 0 ONLINE R02 50 101.6 200 0.0 101.6 00 0 ONLINE R03 50 101.6 200 0.0 201.1 50 0 ONLINE Reply-To: [EMAIL PROTECTED] To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Date: Wed, 30 Jan 2002 07:25:30 -0800 no, you can't shrink a rollback segment below the optimal value --- Sinard Xing [EMAIL PROTECTED] wrote: Hi, Assume INITIAL * MINEXTENT = 2M and also = OPTIMAL Do you think ALTER ROLLBACK SEGMENT rbs SHRINK TO 1M; will work ? Thanks, Sinardy -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Sinard Xing 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!? Great stuff seeking new owners in Yahoo! Auctions! http://auctions.yahoo.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Rachel Carmichael INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists
RE: ALTER ROLLBACK SEGMENT rbs SHRINK
testing if the initial extent*min_extents is less than the optimal, yes you can shrink below optimal. if initial*minextents = optimal, you can execute an alter rollback segment statement that lists a shrink size that is less than optimal AND less than initial*minextents and it will not fail. But if you LOOK at the size it shrinks to, it's initial*minextents. so you can kinda shrink below optimal, the statement won't fail, but Oracle will ignore the number you give it. --- Johnston, Tim [EMAIL PROTECTED] wrote: You know what? I thought the same thing... But, the curious side of me decided to give it a try to be sure... And guess what... It works? Learn something new everyday... SQL select segment_name,sum(bytes) from dba_segments where tablespace_name = 'RBS' and segment_name = 'RBS17' group by segment_name; SEGMENT_NAME SUM(BYTES) -- RBS17 22364160 SQL select rs.optsize, rs.extents 2from dba_rollback_segs drs, 3 v$rollstat rs 4 where drs.segment_name = 'RBS17' 5 and drs.segment_id = rs.usn; OPTSIZEEXTENTS -- -- 22020096 21 SQL alter rollback segment RBS17 shrink to 10M; Rollback segment altered. SQL select segment_name,sum(bytes) from dba_segments where tablespace_name = 'RBS' and segment_name = 'RBS17' group by segment_name; SEGMENT_NAME SUM(BYTES) - -- RBS17 10649600 SQL select rs.optsize, rs.extents 2from dba_rollback_segs drs, 3 v$rollstat rs 4 where drs.segment_name = 'RBS17' 5 and drs.segment_id = rs.usn; OPTSIZEEXTENTS -- -- 22020096 10 SQL 8.1.7.3 on Solaris 2.8 Tim -Original Message- Sent: Wednesday, January 30, 2002 10:26 AM To: Multiple recipients of list ORACLE-L no, you can't shrink a rollback segment below the optimal value --- Sinard Xing [EMAIL PROTECTED] wrote: Hi, Assume INITIAL * MINEXTENT = 2M and also = OPTIMAL Do you think ALTER ROLLBACK SEGMENT rbs SHRINK TO 1M; will work ? Thanks, Sinardy -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Sinard Xing 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!? Great stuff seeking new owners in Yahoo! Auctions! http://auctions.yahoo.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Rachel Carmichael 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: Johnston, Tim 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!? Great stuff seeking new owners in Yahoo! Auctions! http://auctions.yahoo.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Rachel Carmichael 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]
RE: ALTER ROLLBACK SEGMENT rbs SHRINK
but only if the initial*minextents is less than optimal. otherwise, it accepts the command, tells you it did it and shrinks it to optimal. --- Deshpande, Kirti [EMAIL PROTECTED] wrote: Yes, Mohammad is right !! I messed up by not seeing the correct column... Oracle will not honor the size you asked for (to shrink), if there are other transactions using up space more than what you asked for, but it can go below the OPTSIZE... Thanks, Mohammad. - Kirti -Original Message- Sent: Wednesday, January 30, 2002 10:40 AM To: Multiple recipients of list ORACLE-L Yes, it can be shrinked below optimal as per following details specially R03 rollback segment Regards Rafiq before: NAME EXT MB OPTSIZE ACTIVE WATER SHRINKSWRAPS WAITS STATUS --- --- --- --- --- --- -- --- SYSTEM 2 0.2 0.0 0.1 00 0 ONLINE R04 57 115.8 200 0.0 115.8 00 0 ONLINE R01 50 101.6 200 0.0 101.6 00 0 ONLINE R02 50 101.6 200 0.0 101.6 00 0 ONLINE R03 99 201.1 200 0.0 201.1 00 0 ONLINE run script: SQL alter rollback segment r03 shrink to 100M; After: NAME EXT MB OPTSIZE ACTIVE WATER SHRINKSWRAPS WAITS STATUS --- --- --- --- --- --- -- --- SYSTEM 2 0.2 0.0 0.1 00 0 ONLINE R04 57 115.8 200 0.0 115.8 00 0 ONLINE R01 50 101.6 200 0.0 101.6 00 0 ONLINE R02 50 101.6 200 0.0 101.6 00 0 ONLINE R03 50 101.6 200 0.0 201.1 50 0 ONLINE Reply-To: [EMAIL PROTECTED] To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Date: Wed, 30 Jan 2002 07:25:30 -0800 no, you can't shrink a rollback segment below the optimal value --- Sinard Xing [EMAIL PROTECTED] wrote: Hi, Assume INITIAL * MINEXTENT = 2M and also = OPTIMAL Do you think ALTER ROLLBACK SEGMENT rbs SHRINK TO 1M; will work ? Thanks, Sinardy -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Sinard Xing 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!? Great stuff seeking new owners in Yahoo! Auctions! http://auctions.yahoo.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Rachel Carmichael 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 world's 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). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Deshpande, Kirti INET: [EMAIL PROTECTED] Fat
RE: ALTER ROLLBACK SEGMENT rbs SHRINK
Um... Must be version differences... Here is a test case I ran on my 8.1.7.3 machine... SQL create public rollback segment tim tablespace rbs storage ( initial 1M next 1M minextents 20 maxextents 100 optimal 20M ); Rollback segment created. SQL alter rollback segment tim online; Rollback segment altered. SQL select segment_name, min_extents, initial_extent, next_extent 2from dba_segments 3 where segment_name = 'TIM'; SEGMENT_NAME MIN_EXTENTS INITIAL_EXTENT NEXT_EXTENT -- --- -- --- TIM 201048576 1048576 SQL select rs.extents, rs.rssize/1048576 , rs.optsize/1048576 2from dba_rollback_segs drs, 3 v$rollstat rs 4 where drs.segment_name = 'TIM' 5 and drs.segment_id = rs.usn; EXTENTS RS.RSSIZE/1048576 RS.OPTSIZE/1048576 -- - -- 20 20.296875 20 SQL alter rollback segment TIM shrink to 10M; Rollback segment altered. SQL select segment_name, min_extents, initial_extent, next_extent 2from dba_segments 3 where segment_name = 'TIM'; SEGMENT_NAME MIN_EXTENTS INITIAL_EXTENT NEXT_EXTENT -- --- -- --- TIM 201048576 1048576 SQL select rs.extents, rs.rssize, rs.optsize 2from dba_rollback_segs drs, 3 v$rollstat rs 4 where drs.segment_name = 'TIM' 5 and drs.segment_id = rs.usn; EXTENTS RSSIZEOPTSIZE -- -- -- 10 10633216 20971520 SQL alter rollback segment tim offline; Rollback segment altered. SQL drop rollback segment tim; Rollback segment dropped. SQL What version is your test running on? Tim PS - Here is my test case if you want to run it... column segment_name format a30 create public rollback segment tim tablespace rbs storage ( initial 1M next 1M minextents 20 maxextents 100 optimal 20M ); alter rollback segment tim online; select segment_name, min_extents, initial_extent, next_extent from dba_segments where segment_name = 'TIM'; select rs.extents, rs.rssize/1048576 , rs.optsize/1048576 from dba_rollback_segs drs, v$rollstat rs where drs.segment_name = 'TIM' and drs.segment_id = rs.usn; alter rollback segment TIM shrink to 10M; select segment_name, min_extents, initial_extent, next_extent from dba_segments where segment_name = 'TIM'; select rs.extents, rs.rssize, rs.optsize from dba_rollback_segs drs, v$rollstat rs where drs.segment_name = 'TIM' and drs.segment_id = rs.usn; alter rollback segment tim offline; drop rollback segment tim; -Original Message- Sent: Wednesday, January 30, 2002 2:17 PM To: Multiple recipients of list ORACLE-L testing if the initial extent*min_extents is less than the optimal, yes you can shrink below optimal. if initial*minextents = optimal, you can execute an alter rollback segment statement that lists a shrink size that is less than optimal AND less than initial*minextents and it will not fail. But if you LOOK at the size it shrinks to, it's initial*minextents. so you can kinda shrink below optimal, the statement won't fail, but Oracle will ignore the number you give it. --- Johnston, Tim [EMAIL PROTECTED] wrote: You know what? I thought the same thing... But, the curious side of me decided to give it a try to be sure... And guess what... It works? Learn something new everyday... SQL select segment_name,sum(bytes) from dba_segments where tablespace_name = 'RBS' and segment_name = 'RBS17' group by segment_name; SEGMENT_NAME SUM(BYTES) -- RBS17 22364160 SQL select rs.optsize, rs.extents 2from dba_rollback_segs drs, 3 v$rollstat rs 4 where drs.segment_name = 'RBS17' 5 and drs.segment_id = rs.usn; OPTSIZEEXTENTS -- -- 22020096 21 SQL alter rollback segment RBS17 shrink to 10M; Rollback segment altered. SQL select segment_name,sum(bytes) from dba_segments where tablespace_name = 'RBS' and segment_name = 'RBS17' group by segment_name; SEGMENT_NAME SUM(BYTES) - -- RBS17 10649600 SQL select rs.optsize, rs.extents 2from dba_rollback_segs drs, 3 v$rollstat rs 4 where drs.segment_name = 'RBS17' 5 and drs.segment_id = rs.usn; OPTSIZEEXTENTS -- -- 22020096 10 SQL 8.1.7.3 on Solaris 2.8 Tim -Original Message- Sent: Wednesday, January 30, 2002 10:26 AM To: Multiple recipients of list ORACLE-L no, you can't shrink a rollback segment below the optimal value --- Sinard Xing [EMAIL PROTECTED] wrote: Hi, Assume INITIAL * MINEXTENT = 2M and
Re: ALTER ROLLBACK SEGMENT rbs SHRINK
Rachel, Desired info as follows: Min extents 20 initial 2M next 2M question was whether it works or not. Yes,it works... As regard your comment itsn't make a lot of sense to set things up that way you are right but it depends what type of rollback segments and its usage you are dealing withyou might require to use shrink command to bring down usage to a certain level,occassionally. However, it is my humble reply to our Oracle GODESS Hope you don't mind... Regards Rafiq Reply-To: [EMAIL PROTECTED] To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Date: Wed, 30 Jan 2002 10:35:28 -0800 what are the minimum extents on the rollback segments (specifically r03) as well as initial and next sizes please? If optimal is higher than the minimum extents, you're right, it can shrink down to minimum extents*initial doebut --- Mohammad Rafiq [EMAIL PROTECTED] wrote: Yes, it can be shrinked below optimal as per following details specially R03 rollback segment Regards Rafiq before: NAME EXT MB OPTSIZE ACTIVE WATER SHRINKSWRAPS WAITS STATUS --- --- --- --- --- --- -- --- SYSTEM 2 0.2 0.0 0.1 00 0 ONLINE R04 57 115.8 200 0.0 115.8 00 0 ONLINE R01 50 101.6 200 0.0 101.6 00 0 ONLINE R02 50 101.6 200 0.0 101.6 00 0 ONLINE R03 99 201.1 200 0.0 201.1 00 0 ONLINE run script: SQL alter rollback segment r03 shrink to 100M; After: NAME EXT MB OPTSIZE ACTIVE WATER SHRINKSWRAPS WAITS STATUS --- --- --- --- --- --- -- --- SYSTEM 2 0.2 0.0 0.1 00 0 ONLINE R04 57 115.8 200 0.0 115.8 00 0 ONLINE R01 50 101.6 200 0.0 101.6 00 0 ONLINE R02 50 101.6 200 0.0 101.6 00 0 ONLINE R03 50 101.6 200 0.0 201.1 50 0 ONLINE Reply-To: [EMAIL PROTECTED] To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Date: Wed, 30 Jan 2002 07:25:30 -0800 no, you can't shrink a rollback segment below the optimal value --- Sinard Xing [EMAIL PROTECTED] wrote: Hi, Assume INITIAL * MINEXTENT = 2M and also = OPTIMAL Do you think ALTER ROLLBACK SEGMENT rbs SHRINK TO 1M; will work ? Thanks, Sinardy -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Sinard Xing 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!? Great stuff seeking new owners in Yahoo! Auctions! http://auctions.yahoo.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Rachel Carmichael 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
RE: ALTER ROLLBACK SEGMENT rbs SHRINK
Ok... I've ran this test case on both a 8.1.6.0 and a 8.1.7.3 database... I've ran it with both public and private rollback segments (just in case)... I've looked at rssize in v$rollstat and sum(bytes) from dba_segments... And, in all cases, it allows me to shrink the segment to less then both optimal and min_extents*extent_size even though optimal=min_extents*extent_size... Is there something wrong in the test case? Has anyone ran this on one of their test databases? I'm confused since the test case seems straight forward... The way I read, it allows me to perform the shrink and the resulting segment is actually the size I specify... Am I doing something stupid in the test case? Thanks Tim PS - Here is the test case again... column segment_name format a30 create public rollback segment tim tablespace rbs storage ( initial 1M next 1M minextents 20 maxextents 100 optimal 20M ); alter rollback segment tim online; select segment_name, min_extents, initial_extent, next_extent from dba_segments where segment_name = 'TIM'; select rs.extents, rs.rssize/1048576 , rs.optsize/1048576 from dba_rollback_segs drs, v$rollstat rs where drs.segment_name = 'TIM' and drs.segment_id = rs.usn; alter rollback segment TIM shrink to 10M; select segment_name, min_extents, initial_extent, next_extent from dba_segments where segment_name = 'TIM'; select rs.extents, rs.rssize, rs.optsize from dba_rollback_segs drs, v$rollstat rs where drs.segment_name = 'TIM' and drs.segment_id = rs.usn; alter rollback segment tim offline; drop rollback segment tim; -Original Message- Sent: Wednesday, January 30, 2002 2:31 PM To: Multiple recipients of list ORACLE-L Um... Must be version differences... Here is a test case I ran on my 8.1.7.3 machine... SQL create public rollback segment tim tablespace rbs storage ( initial 1M next 1M minextents 20 maxextents 100 optimal 20M ); Rollback segment created. SQL alter rollback segment tim online; Rollback segment altered. SQL select segment_name, min_extents, initial_extent, next_extent 2from dba_segments 3 where segment_name = 'TIM'; SEGMENT_NAME MIN_EXTENTS INITIAL_EXTENT NEXT_EXTENT -- --- -- --- TIM 201048576 1048576 SQL select rs.extents, rs.rssize/1048576 , rs.optsize/1048576 2from dba_rollback_segs drs, 3 v$rollstat rs 4 where drs.segment_name = 'TIM' 5 and drs.segment_id = rs.usn; EXTENTS RS.RSSIZE/1048576 RS.OPTSIZE/1048576 -- - -- 20 20.296875 20 SQL alter rollback segment TIM shrink to 10M; Rollback segment altered. SQL select segment_name, min_extents, initial_extent, next_extent 2from dba_segments 3 where segment_name = 'TIM'; SEGMENT_NAME MIN_EXTENTS INITIAL_EXTENT NEXT_EXTENT -- --- -- --- TIM 201048576 1048576 SQL select rs.extents, rs.rssize, rs.optsize 2from dba_rollback_segs drs, 3 v$rollstat rs 4 where drs.segment_name = 'TIM' 5 and drs.segment_id = rs.usn; EXTENTS RSSIZEOPTSIZE -- -- -- 10 10633216 20971520 SQL alter rollback segment tim offline; Rollback segment altered. SQL drop rollback segment tim; Rollback segment dropped. SQL What version is your test running on? Tim PS - Here is my test case if you want to run it... column segment_name format a30 create public rollback segment tim tablespace rbs storage ( initial 1M next 1M minextents 20 maxextents 100 optimal 20M ); alter rollback segment tim online; select segment_name, min_extents, initial_extent, next_extent from dba_segments where segment_name = 'TIM'; select rs.extents, rs.rssize/1048576 , rs.optsize/1048576 from dba_rollback_segs drs, v$rollstat rs where drs.segment_name = 'TIM' and drs.segment_id = rs.usn; alter rollback segment TIM shrink to 10M; select segment_name, min_extents, initial_extent, next_extent from dba_segments where segment_name = 'TIM'; select rs.extents, rs.rssize, rs.optsize from dba_rollback_segs drs, v$rollstat rs where drs.segment_name = 'TIM' and drs.segment_id = rs.usn; alter rollback segment tim offline; drop rollback segment tim; -Original Message- Sent: Wednesday, January 30, 2002 2:17 PM To: Multiple recipients of list ORACLE-L testing if the initial extent*min_extents is less than the optimal, yes you can shrink below optimal. if initial*minextents = optimal, you can execute an alter rollback segment statement that lists a shrink size that is less than optimal AND less than initial*minextents and it will not fail. But if you LOOK at the size it shrinks to, it's initial*minextents. so you can kinda shrink below optimal, the statement
Re: ALTER ROLLBACK SEGMENT rbs SHRINK
nah.. just means you are experimenting :) --- orantdba [EMAIL PROTECTED] wrote: H, if you are shrinking below optimal doesn't that mean optimal isn't :-). John [EMAIL PROTECTED] wrote: Yes, Mohammad is right !! I messed up by not seeing the correct column... Oracle will not honor the size you asked for (to shrink), if there are other transactions using up space more than what you asked for, but it can go below the OPTSIZE... Thanks, Mohammad. - Kirti -Original Message- Sent: Wednesday, January 30, 2002 10:40 AM To: Multiple recipients of list ORACLE-L Yes, it can be shrinked below optimal as per following details specially R03 rollback segment Regards Rafiq before: NAME EXT MB OPTSIZE ACTIVE WATER SHRINKS WRAPS WAITS STATUS --- --- --- --- --- --- -- --- SYSTEM 2 0.2 0.0 0.1 0 0 0 ONLINE R04 57 115.8 200 0.0 115.8 0 0 0 ONLINE R01 50 101.6 200 0.0 101.6 0 0 0 ONLINE R02 50 101.6 200 0.0 101.6 0 0 0 ONLINE R03 99 201.1 200 0.0 201.1 0 0 0 ONLINE run script: SQL alter rollback segment r03 shrink to 100M; After: NAME EXT MB OPTSIZE ACTIVE WATER SHRINKS WRAPS WAITS STATUS --- --- --- --- --- --- -- --- SYSTEM 2 0.2 0.0 0.1 0 0 0 ONLINE R04 57 115.8 200 0.0 115.8 0 0 0 ONLINE R01 50 101.6 200 0.0 101.6 0 0 0 ONLINE R02 50 101.6 200 0.0 101.6 0 0 0 ONLINE R03 50 101.6 200 0.0 201.1 5 0 0 ONLINE Reply-To: [EMAIL PROTECTED] To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Date: Wed, 30 Jan 2002 07:25:30 -0800 no, you can't shrink a rollback segment below the optimal value --- Sinard Xing [EMAIL PROTECTED] wrote: Hi, Assume INITIAL * MINEXTENT = 2M and also = OPTIMAL Do you think ALTER ROLLBACK SEGMENT rbs SHRINK TO 1M; will work ? Thanks, Sinardy -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Sinard Xing 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!? Great stuff seeking new owners in Yahoo! Auctions! http://auctions.yahoo.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Rachel Carmichael 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 world's largest e-mail service with MSN Hotmail. http://www.hotmail.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: orantdba 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!? Great stuff seeking new owners in Yahoo! Auctions!
RE: ALTER ROLLBACK SEGMENT rbs SHRINK
doesn't sound like you are doing anything stupid, and I ran on 8.1.6.0 and it said it would shrink it but didn't. I'll use your test case against my databases in the morning --- Johnston, Tim [EMAIL PROTECTED] wrote: Ok... I've ran this test case on both a 8.1.6.0 and a 8.1.7.3 database... I've ran it with both public and private rollback segments (just in case)... I've looked at rssize in v$rollstat and sum(bytes) from dba_segments... And, in all cases, it allows me to shrink the segment to less then both optimal and min_extents*extent_size even though optimal=min_extents*extent_size... Is there something wrong in the test case? Has anyone ran this on one of their test databases? I'm confused since the test case seems straight forward... The way I read, it allows me to perform the shrink and the resulting segment is actually the size I specify... Am I doing something stupid in the test case? Thanks Tim PS - Here is the test case again... column segment_name format a30 create public rollback segment tim tablespace rbs storage ( initial 1M next 1M minextents 20 maxextents 100 optimal 20M ); alter rollback segment tim online; select segment_name, min_extents, initial_extent, next_extent from dba_segments where segment_name = 'TIM'; select rs.extents, rs.rssize/1048576 , rs.optsize/1048576 from dba_rollback_segs drs, v$rollstat rs where drs.segment_name = 'TIM' and drs.segment_id = rs.usn; alter rollback segment TIM shrink to 10M; select segment_name, min_extents, initial_extent, next_extent from dba_segments where segment_name = 'TIM'; select rs.extents, rs.rssize, rs.optsize from dba_rollback_segs drs, v$rollstat rs where drs.segment_name = 'TIM' and drs.segment_id = rs.usn; alter rollback segment tim offline; drop rollback segment tim; -Original Message- Sent: Wednesday, January 30, 2002 2:31 PM To: Multiple recipients of list ORACLE-L Um... Must be version differences... Here is a test case I ran on my 8.1.7.3 machine... SQL create public rollback segment tim tablespace rbs storage ( initial 1M next 1M minextents 20 maxextents 100 optimal 20M ); Rollback segment created. SQL alter rollback segment tim online; Rollback segment altered. SQL select segment_name, min_extents, initial_extent, next_extent 2from dba_segments 3 where segment_name = 'TIM'; SEGMENT_NAME MIN_EXTENTS INITIAL_EXTENT NEXT_EXTENT -- --- -- --- TIM 201048576 1048576 SQL select rs.extents, rs.rssize/1048576 , rs.optsize/1048576 2from dba_rollback_segs drs, 3 v$rollstat rs 4 where drs.segment_name = 'TIM' 5 and drs.segment_id = rs.usn; EXTENTS RS.RSSIZE/1048576 RS.OPTSIZE/1048576 -- - -- 20 20.296875 20 SQL alter rollback segment TIM shrink to 10M; Rollback segment altered. SQL select segment_name, min_extents, initial_extent, next_extent 2from dba_segments 3 where segment_name = 'TIM'; SEGMENT_NAME MIN_EXTENTS INITIAL_EXTENT NEXT_EXTENT -- --- -- --- TIM 201048576 1048576 SQL select rs.extents, rs.rssize, rs.optsize 2from dba_rollback_segs drs, 3 v$rollstat rs 4 where drs.segment_name = 'TIM' 5 and drs.segment_id = rs.usn; EXTENTS RSSIZEOPTSIZE -- -- -- 10 10633216 20971520 SQL alter rollback segment tim offline; Rollback segment altered. SQL drop rollback segment tim; Rollback segment dropped. SQL What version is your test running on? Tim PS - Here is my test case if you want to run it... column segment_name format a30 create public rollback segment tim tablespace rbs storage ( initial 1M next 1M minextents 20 maxextents 100 optimal 20M ); alter rollback segment tim online; select segment_name, min_extents, initial_extent, next_extent from dba_segments where segment_name = 'TIM'; select rs.extents, rs.rssize/1048576 , rs.optsize/1048576 from dba_rollback_segs drs, v$rollstat rs where drs.segment_name = 'TIM' and drs.segment_id = rs.usn; alter rollback segment TIM shrink to 10M; select segment_name, min_extents, initial_extent, next_extent from dba_segments where segment_name = 'TIM'; select rs.extents, rs.rssize, rs.optsize from dba_rollback_segs drs, v$rollstat rs where drs.segment_name = 'TIM' and drs.segment_id = rs.usn; alter rollback segment tim offline; drop rollback segment tim; -Original Message- Sent: Wednesday, January 30, 2002 2:17 PM To: Multiple recipients of list ORACLE-L testing if the initial
RE: ALTER ROLLBACK SEGMENT rbs SHRINK
8.1.6 hm. --- Johnston, Tim [EMAIL PROTECTED] wrote: Um... Must be version differences... Here is a test case I ran on my 8.1.7.3 machine... SQL create public rollback segment tim tablespace rbs storage ( initial 1M next 1M minextents 20 maxextents 100 optimal 20M ); Rollback segment created. SQL alter rollback segment tim online; Rollback segment altered. SQL select segment_name, min_extents, initial_extent, next_extent 2from dba_segments 3 where segment_name = 'TIM'; SEGMENT_NAME MIN_EXTENTS INITIAL_EXTENT NEXT_EXTENT -- --- -- --- TIM 201048576 1048576 SQL select rs.extents, rs.rssize/1048576 , rs.optsize/1048576 2from dba_rollback_segs drs, 3 v$rollstat rs 4 where drs.segment_name = 'TIM' 5 and drs.segment_id = rs.usn; EXTENTS RS.RSSIZE/1048576 RS.OPTSIZE/1048576 -- - -- 20 20.296875 20 SQL alter rollback segment TIM shrink to 10M; Rollback segment altered. SQL select segment_name, min_extents, initial_extent, next_extent 2from dba_segments 3 where segment_name = 'TIM'; SEGMENT_NAME MIN_EXTENTS INITIAL_EXTENT NEXT_EXTENT -- --- -- --- TIM 201048576 1048576 SQL select rs.extents, rs.rssize, rs.optsize 2from dba_rollback_segs drs, 3 v$rollstat rs 4 where drs.segment_name = 'TIM' 5 and drs.segment_id = rs.usn; EXTENTS RSSIZEOPTSIZE -- -- -- 10 10633216 20971520 SQL alter rollback segment tim offline; Rollback segment altered. SQL drop rollback segment tim; Rollback segment dropped. SQL What version is your test running on? Tim PS - Here is my test case if you want to run it... column segment_name format a30 create public rollback segment tim tablespace rbs storage ( initial 1M next 1M minextents 20 maxextents 100 optimal 20M ); alter rollback segment tim online; select segment_name, min_extents, initial_extent, next_extent from dba_segments where segment_name = 'TIM'; select rs.extents, rs.rssize/1048576 , rs.optsize/1048576 from dba_rollback_segs drs, v$rollstat rs where drs.segment_name = 'TIM' and drs.segment_id = rs.usn; alter rollback segment TIM shrink to 10M; select segment_name, min_extents, initial_extent, next_extent from dba_segments where segment_name = 'TIM'; select rs.extents, rs.rssize, rs.optsize from dba_rollback_segs drs, v$rollstat rs where drs.segment_name = 'TIM' and drs.segment_id = rs.usn; alter rollback segment tim offline; drop rollback segment tim; -Original Message- Sent: Wednesday, January 30, 2002 2:17 PM To: Multiple recipients of list ORACLE-L testing if the initial extent*min_extents is less than the optimal, yes you can shrink below optimal. if initial*minextents = optimal, you can execute an alter rollback segment statement that lists a shrink size that is less than optimal AND less than initial*minextents and it will not fail. But if you LOOK at the size it shrinks to, it's initial*minextents. so you can kinda shrink below optimal, the statement won't fail, but Oracle will ignore the number you give it. --- Johnston, Tim [EMAIL PROTECTED] wrote: You know what? I thought the same thing... But, the curious side of me decided to give it a try to be sure... And guess what... It works? Learn something new everyday... SQL select segment_name,sum(bytes) from dba_segments where tablespace_name = 'RBS' and segment_name = 'RBS17' group by segment_name; SEGMENT_NAME SUM(BYTES) -- RBS17 22364160 SQL select rs.optsize, rs.extents 2from dba_rollback_segs drs, 3 v$rollstat rs 4 where drs.segment_name = 'RBS17' 5 and drs.segment_id = rs.usn; OPTSIZEEXTENTS -- -- 22020096 21 SQL alter rollback segment RBS17 shrink to 10M; Rollback segment altered. SQL select segment_name,sum(bytes) from dba_segments where tablespace_name = 'RBS' and segment_name = 'RBS17' group by segment_name; SEGMENT_NAME SUM(BYTES) - -- RBS17 10649600 SQL select rs.optsize, rs.extents 2from dba_rollback_segs drs, 3 v$rollstat rs 4 where drs.segment_name = 'RBS17' 5 and drs.segment_id = rs.usn; OPTSIZEEXTENTS -- -- 22020096 10 SQL 8.1.7.3 on Solaris 2.8 Tim -Original Message- Sent: Wednesday,
RE: ALTER ROLLBACK SEGMENT rbs SHRINK
I don't think - it will work. You won't get any error as well as it won't shrink. Any other inputs from others??? Rgds, -Original Message- Sent: Wednesday, January 30, 2002 9:55 AM To: Multiple recipients of list ORACLE-L Hi, Assume INITIAL * MINEXTENT = 2M and also = OPTIMAL Do you think ALTER ROLLBACK SEGMENT rbs SHRINK TO 1M; will work ? Thanks, Sinardy -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Sinard Xing 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: Sandeep Kurliye 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).