Results 1 to 4 of 4
  1. #1
    Join Date
    Jan 2004
    Posts
    16

    Unhappy Unanswered: shrinking undo tablespace!!!

    How would I go about shrinking my undo tablespace if I have undo_management set to auto?

    Is there anyway to shrink undo tablespace under Oracle 9i?
    Jason B. Simms

  2. #2
    Join Date
    Apr 2003
    Location
    Minneapolis, MN
    Posts
    273
    No, You cannot manually shrink the UNDO tablespacec if your database is running under AUM mode. Oracle maintains the undo segments by itself.
    Bhavin

    MS Computer Science
    OCP DBA 9i/8i

  3. #3
    Join Date
    Jan 2004
    Posts
    16
    Originally posted by Hings
    No, You cannot manually shrink the UNDO tablespacec if your database is running under AUM mode. Oracle maintains the undo segments by itself.

    Ok. But my tablespace has grown to a capacity where my disk space has 99% usage. Even though tablespace is set to auto, is there anyway to reoccupy this space. For instance, can I just drop the undo tablespace and just recreate it?
    Jason B. Simms

  4. #4
    Join Date
    Apr 2003
    Location
    Minneapolis, MN
    Posts
    273
    No, You cannot DROP the UNDO tablespace if it is presently used by your instance. I guess you must have set UNDO_RETENTION very high or May be You have very large transactions going on the instance.

    One possible way is

    ALTER SYSTEM SET UNDO_RETENTION=<small number>

    I guess it should enable Oracle to shrink the Segment(s).
    Bhavin

    MS Computer Science
    OCP DBA 9i/8i

Posting Permissions

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