Undo tablespace is not reclaimed
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 7 of 7

Thread: Undo tablespace is not reclaimed

  1. #1
    Join Date
    Sep 2005
    Posts
    21

    Exclamation Undo tablespace is not reclaimed

    I have executed one procedure which is updating partition tables's field with sequence. But i am commiting after each and every update of table. Table contains records at the most 5 lacs but still Undo tablespace is drastically down. My database has 15 GB undo tablespace and its undo_retention period is 3 hrs. Undo_tablespace is showing me that it has 30MB free. Undo tablespace is down 500 mb per minute. Please guide me for how to get back undo space.
    One troubleshooting steps is that we had restarted our server. but after restart it is showing only 2 GB out of 15 GB. How do i find remaining 13 GB undo tablespace status?

    Can anyone suggest troubleshooting steps. Is there some parameter should I have to take care? It will be greatfull if some one is provide suggestions on urgent basis.

    Thanks in advance.

  2. #2
    Join Date
    Sep 2002
    Location
    England
    Posts
    7,331
    I think you need to demonstarte your problem with some examples. Your points are mixed up and dont make sense

    Also hardly anyone here will know what a lac is.

    And committing after every update, yuck. Bet that is nice and slow for you

  3. #3
    Join Date
    Sep 2001
    Posts
    200
    one suggestion- reduce undo_retention for that particular session only...if you still wish to keep it system wide at this 3 hrs..
    eg
    alter session set undo_retention =1800 (for 30 minutes)
    Life is what is happening today while you were planning tomorrow.

  4. #4
    Join Date
    Dec 2000
    Location
    Ljubljana, Slovenia
    Posts
    4,439
    Quote Originally Posted by ndisang
    one suggestion- reduce undo_retention for that particular session only...if you still wish to keep it system wide at this 3 hrs..
    eg
    alter session set undo_retention =1800 (for 30 minutes)
    You can't set UNDO_RETENTION on the per-session basis! So, "alter session..." is a no-go in this case.
    Jurij Modic
    ASCII a stupid question, get a stupid ANSI
    24 hours in a day .... 24 beer in a case .... coincidence?

  5. #5
    Join Date
    Sep 2005
    Posts
    21
    Dear Freinds ,

    Problem got solved by oracle people. Actual Problem is one of my undo tablespace's segment is got corrupted so it is not freeing space. Now Database is working fine.

    Thanx for your help

    Dhaval

  6. #6
    Join Date
    Sep 2005
    Location
    Delhi
    Posts
    78
    why don't u create one more undo tablespace, make new tablespace default and delete old one.
    ~KD.DBA~

  7. #7
    Join Date
    Sep 2002
    Location
    England
    Posts
    7,331
    because that wasnt his problem, he wanted to know why the space wasnt reclaimed. Creating a new one just hides the problem until it happens again. Much better to find out what the problem is - which is what he did

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