How do I shrink the size of my Rollback Tableapce?
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 4 of 4

Thread: How do I shrink the size of my Rollback Tableapce?

  1. #1
    Join Date
    Feb 2001
    Posts
    34

    Question

    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.

  2. #2
    Join Date
    Nov 2000
    Location
    Pittsburgh, PA
    Posts
    4,015
    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.

  3. #3
    Join Date
    Apr 2001
    Location
    Brisbane, Queensland, Australia
    Posts
    1,203
    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
    OCP 8i, 9i DBA
    Brisbane Australia

  4. #4
    Join Date
    Feb 2001
    Location
    Bangalore, India
    Posts
    109
    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;


Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Click Here to Expand Forum to Full Width