Undo tablespace is not reclaimed

[dhaval trivedi]

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.

[davey23uk]

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

[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)

[jmodic]

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.

[dhaval trivedi]

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

[dhar_kiran]

why don't u create one more undo tablespace, make new tablespace default and delete old one.

[davey23uk]

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