Shrink UNDO tablespace - Oracle 10.2 RAC
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 10 of 10

Thread: Shrink UNDO tablespace - Oracle 10.2 RAC

Hybrid View

  1. #1
    Join Date
    Jun 2007
    Posts
    2

    Shrink UNDO tablespace - Oracle 10.2 RAC

    I am totally new to oracle (just joined as a junior trainee DBA). I am slightly intimidated will the documentations I read everyday. Any kind of explanation will be highly appreciated.

    I have a couple of instances each on 2 servers. Two 2 node RAC clusters on 2 servers. 10.2.0.2.0 on RHEL 3.

    In database1:
    TABLESPACE_NAME GB
    ---------- --------------------
    UNDOTBS1 4.90625
    UNDOTBS2 15.9136736

    Instance1:
    NAME TYPE VALUE
    --------------------------- ----------- ---------
    undo_management string AUTO
    undo_retention integer 900
    undo_tablespace string UNDOTBS1

    Instance2:
    NAME TYPE VALUE
    ------------------------- ----------- ---------
    undo_management string AUTO
    undo_retention integer 900
    undo_tablespace string UNDOTBS2



    In database2:
    TABLESPACE_NAME GB
    ---------- ---------------------
    UNDOTBS1 15.2107041
    UNDOTBS2 11.0957684

    Instance1:
    NAME TYPE VALUE
    ----------------------------- --------------- ---------------
    undo_management string AUTO
    undo_retention integer 900
    undo_tablespace string UNDOTBS1

    Instance2:
    NAME TYPE VALUE
    --------------------------- ------------- -----------------
    undo_management string AUTO
    undo_retention integer 900
    undo_tablespace string UNDOTBS2

    From what I read last few weeks, in oracle 10g, if we use automatic undo management and undo retention, oracle will automatically allocate and deallocate segments when required and we do not need to bother about it growing once we set a proper size. But later read that if we do not set the MAX_FILE_SIZE (i think this is what it is), it will keep growing and allocate segments all over the file system and occupy space.

    these are my questions:
    1. When undo TS is 15GB, does it mean that these 15GB is taken from the disk space and sitting here "useless".
    2. Do I have to shrink it (to re-claim space)?
    3. What are the steps to do it? I read in one form thread that I can create a new tablespace and change that to be the undo tablespace and drop the old one.
    4. what other information about my DB or server should I provide to complete my question?

    Can someone kindly explain the procedure? Reference to a documentation will also help. But if someone can list the steps, that will be really kind.

  2. #2
    Join Date
    Apr 2001
    Location
    Bangalore, India
    Posts
    727
    Thomas Saviour(royxavier@yahoo.com)
    Technical Lead (Databases)
    Thomson Reuters (Markets)

    http://ora600tom.wordpress.com/

  3. #3
    Join Date
    Jun 2007
    Posts
    2

    Thanks much

    I thought I did a lot of online research but sure did miss this post. But I think, I have to do some more reading to get a real grasp of undo_retention and undo generation etc and how such things go together.

  4. #4
    Join Date
    Oct 2007
    Posts
    2
    Does anyone know if something similar to the tip on the Oracle forum might work on the Temp tablespace? Both our Temp and Undo are growing too large and I'd like to reduce it as well.

    Thanks,
    Rob

  5. #5
    Join Date
    Jun 2000
    Location
    Madrid, Spain
    Posts
    7,447
    you can drop and add tempfiles

  6. #6
    Join Date
    Dec 2002
    Location
    Bangalore ( India )
    Posts
    2,434
    and ofcourse while adding file do set a max file size.
    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"

  7. #7
    Join Date
    Sep 2002
    Location
    England
    Posts
    7,333
    wouldnt it be better to work out why they are getting bigger?

  8. #8
    Join Date
    Oct 2007
    Posts
    2
    I agree, and I think I know why it got big, and I know how to prevent it from getting big in the future. But it is already big and I need to know how to fix that.

  9. #9
    Join Date
    Apr 2007
    Location
    USA
    Posts
    110
    Undo tablespace would grow and grow if you want it. What restricts its growth is
    - Undo_retention
    - max_size of datafile
    Now Oracle will shrink it when it comes under space pressure and when you retention time has been exceeded.
    if it has space it would continue to grow ignoring retention time.
    Looking for the greatest evil in the world? Look in the mirror.

  10. #10
    Join Date
    Sep 2002
    Location
    England
    Posts
    7,333
    oracle will never shrink the undo tablespace on its own

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