Undo tablespace size
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 8 of 8

Thread: Undo tablespace size

  1. #1
    Join Date
    Apr 2001
    Posts
    126

    Unhappy Undo tablespace size

    hello:

    The undo tablespace size is more than 5GB and my disk space is filling up fast.

    I tried resizing the datafile and it fired with the ORA-03297.
    and i guess it affects the performance.

    I tried recreating new undo tablespace and assigning that and tried resizing old one. i got the same ORA-03297 error message.

    how do we do it??.

  2. #2
    Join Date
    Dec 2002
    Location
    Bangalore ( India )
    Posts
    2,434
    did u create undo TS with datafile size UNLIMITED?
    funky...

    "I Dont Want To Follow A Path, I would Rather Go Where There Is No Path And Leave A Trail."

    "Ego is the worst thing many have, try to overcome it & you will be the best, if not good, person on this earth"

  3. #3
    Join Date
    Apr 2001
    Posts
    126
    yes .. it is given as UNLIMITED and it has been removed now.
    how do we shrink it?.

  4. #4
    Join Date
    Jan 2001
    Location
    Vancouver, Canada
    Posts
    87
    You cannot shirk segments in undo tablespace. Fastest and safest way to accomplish this task is to create new undo tablespace with datafile size limit set as required.

    Switch database to use new undo tablespace. Drop old undo tablespace if ok to do.

    See Oracle9i DBA guide for details on managing undo tablespace in 9i.

    Hope this helps.
    Mohammad Zahid
    Software Developer
    Database Management Applications.
    Vancouver, Canada
    E-mail: mzahid@shaw.ca

  5. #5
    Join Date
    Nov 2002
    Location
    New Delhi, INDIA
    Posts
    1,796
    Amar
    "There is a difference between knowing the path and walking the path."

    Amar's Blog  Get Firefox!

  6. #6
    Join Date
    Apr 2001
    Posts
    126

    Unhappy

    i have tried creating new UNDO tablespace (with NO autoextend) and deleted the old one. I tried to do import , during import it fired an error message saying "unable to extend....", then i enabled "EXTEND" of undo datafile and again it started growing..

    how do we stop this undo tablespace growing ???.

  7. #7
    Join Date
    Jan 2001
    Location
    Vancouver, Canada
    Posts
    87
    Try import using commit=y option. This way all transaction get commited while import is running.
    Mohammad Zahid
    Software Developer
    Database Management Applications.
    Vancouver, Canada
    E-mail: mzahid@shaw.ca

  8. #8
    Join Date
    Apr 2001
    Posts
    126
    thanks.. there it gives error "enable to extend" for UNDO and TEMP tablespace even where there is ay insert/update happens .. how do we solve this problem of UNDO and TEMP tablespace growing???.

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