How do I shrink the size of my Rollback Tableapce?

[joeyb]

Hello All!

I created a tablespace for my rollback segments at the initial install of my instance like so:

create tablespace RBS_1 datafile 'yada/rbs01.dbf' size 100M
reuse autoextend on next 5120K minimum extent 512K
default storage ( initial 512K next 512K minextents 2 maxextents 4096);

I have 12 rollback segments using this tablespace. Well, my tablespace size has expanded to almost 2Gb.

What did I do wrong and how do I fix it? Also, how do I shrink my tablespace size back down?! I 've been doing some research, but I'm skeptical about trying anything because I'm afraid of messing up the instance.

Thanks in advance!!

Joe B.

[gandolf989]

If you have the disk space you can create a new tablespace for rollback. Create new rollback segments and set an optimal size. If you are doing a lot of large DML transactions whichever rollback segment that is getting used will grow, without the use of the optimal parameter they will not shrink. You want to allocate enough rollback so that Oracle is not always dynamically creating rollback. You may want to make the rollback extent size larger. There are some tuning scripts that will tell you about the efficiency of your rollback sizing. If you average transactio take 5megs of rollback you may want to have 5 meg extent sizes.

This is an example of one of our rollback segments

create public rollback segment rb1
storage(initial 512K next 512K minextents 32
optimal 16M maxextents unlimited)
tablespace RBS;
alter rollback segment rb1 online;

You may want to make yours bigger if you are doing more dml. The real test when doing optimal sizing on rollback is how many times do they shrink. You want to keep that number low.

[grjohnson]

Nope, nope, nope. Can't shrink the tablespace past the datafile's High Water Mark. i.e, the file's largest point (in your case almost 2 GB).

You may have to find alternatives, such as recreating some rollback segments to a new RBS_TEMP tablespace dropping your original and then recreating the RBS tablespace again. Whilst I haven't performed this myself, it does sound feasible.

Cheers

[sln81]

Try these two commands.

First find out the the active number of extents in each rollback segment and shrink the rollback segments

1. Alter Rollback segment r1 shrink to nn m;

After shrinking the rollback segments like this,

2. Alter database datafile 'rbs.dbf' resize 1024M;