How to shrink my undo tablespace
DBAsupport.com Forums - Powered by vBulletin
Page 1 of 2 12 LastLast
Results 1 to 10 of 11

Thread: How to shrink my undo tablespace

  1. #1
    Join Date
    Apr 2001
    Posts
    45
    Could someone please tell me how to shrink my undo tablespace back to its original size or to some manageable value. It currently sits at 1.3 Gb.
    It grew to that size when I intentionally inserted 9,000,000 rows into a table with a commit at each insert.
    Since then I have truncated the table but the undo still remains at the same size.

    Oracle Version 9.0.1 on NT 4

    Any help will be greatly appreciated.

    Thanks
    May$e

  2. #2
    Join Date
    Aug 2001
    Location
    Waterloo, On
    Posts
    547
    Do you have an 'optimal' clause in your undo tablespace?
    I have never used 9i but in 8i, you simply shrink the tablespace by

    ALTER ROLLBACK SEGMENT rbs1 SHRINK TO 100K;


    This statement attempts to reduce the size of the rollback segment to the specified size, but will stop short if an extent cannot be deallocated because it is active.



    Raminder Singh

    Oracle Certified DBA: Oracle 8i, 9i


    Mail me at raminderahluwalia@rediffmail.com.

  3. #3
    Join Date
    Apr 2001
    Posts
    45
    Thanks Raminder.

    I don't know if I can srink the undo tablespace in 9i.

    By the time I got your response, I had created another undo tablespace took the original undo t/s offline and made the new one on line and bounced the d/b.
    It worked well and I was able to delete the undo tablespace and its file.

    But it would be interesting to findout if this is a bug or if I had other options than the one I decided to take.

    Thanks
    May$e

  4. #4
    Join Date
    Feb 2001
    Posts
    389
    1) You can shrink the roll back segments as

    ALTER ROLLBACK SEGMENT rbs1 SHRINK TO 100K;

    Then u can shrink the tablespace by shrinking the datafiles as before, only thing
    u should care about is the undo_retention period, the tablespace should be
    sized keeping in mind the undo_retention period set.

  5. #5
    Join Date
    Apr 2001
    Posts
    45
    Hi GPSingh,

    I am aware of that command for the 8i env, but I don't think it applies in this situation. I am dealing with the undo tablespace in the 9i env.

    May$e

  6. #6
    Join Date
    Jun 2001
    Location
    Helsinki. Finland
    Posts
    3,938
    Originally posted by mayse
    Hi GPSingh,

    I am aware of that command for the 8i env, but I don't think it applies in this situation. I am dealing with the undo tablespace in the 9i env.

    May$e
    Is it Oracle managed? Do not try to shrink it if it is.


  7. #7
    Join Date
    Apr 2001
    Posts
    45
    Hi Julian,

    Yes it is Oracle managed. So what were my choices ?
    How can I shrink it beside the brute force method that I used.

    Thanks

  8. #8
    Join Date
    Jun 2001
    Location
    Helsinki. Finland
    Posts
    3,938
    If you choose to use Oracle Managed Undo Tablespaces, then you are stuck with them. Hands off :-! Let Oracle do the shrinking for you if such is neceessary.




  9. #9
    Join Date
    Apr 2001
    Posts
    45
    Hi Julian,

    That's good to know. However, what would have to transpire for "oracle" to decide when to shrink. As I found out I could not make it shrink, even after truncating the table, bounced the d/b, dropped the user, created another undo tablespace. I basically tried everything to force oracle's undo manager to shrink it, but failed.

    I'll study the other option: namely user managed and see what it offers from the management perspective.


    Thanks

  10. #10
    Join Date
    Jun 2001
    Location
    Helsinki. Finland
    Posts
    3,938
    You are going "deep". Do you know what a "steal extent" is?

    In 9i, SMON is responsible for managing space within the undo tablespace, periodically or to use the Oracle's own terminology, proactively performing shrinks on idled undo segments.

    However, SMON performs space management when foreground processes need to steal extents. Only then!

    If you can and know hoe to manage your RBSs, you better make them by hand.


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