I have a problem with an undo tablespace that keeps growing. there are no transactions - I have stopped and started the instance to make sure. But for the last few months it just keeps getting bigger. it is a development instance only but it is staring to become critical. Undo_retention is set to the default of 10800. Does anyone have any ideas on what I could check. I am a sole charge DBA so I there is no one here to ask.
03-13-2007, 08:09 PM
Turn off auto extend on data files and reduce retention value.
03-13-2007, 08:14 PM
Auto extend is off already - It was never on. I am a bit confused how reducing the retention value will work as well as it has been growing since november last year and the retention value is set to 10800 which is 3 hours right?
03-13-2007, 08:59 PM
how is it getting bigger then if it never extended?
03-13-2007, 09:48 PM
By growing I mean in percent used. the extents don't seem to be expiring.
When I check dba_undo_extent It has several unexpired extents. Could that be causing the undo space to become 99 percent full?
The free extents has been steadily decreasing since last November. It looks to me like there is an old uncommitted transaction hung up somewhere but I have stopped and started the instance a couple of times over the last few days and it still remains 99% full.
Is this a reason to worry or will oracle reuse the unexpired extents eventually and I shouldn't worry about it too much.
03-13-2007, 10:37 PM
If tablespace is not extending, and it is showing 99% full, it is normal. No need to worry. When more space is needed, it will reuse the existing used extents if it is beyond retention time.