Archive for the ‘10gR2’ Category


I’ve been troubleshooting this one for a while..A basic tablespace free space monitor script alerts that the undo tablespace is almost full. If I let it go, it then ups itself to 100% full.

Though it’s undo – not a regular tablespace – and thus is in continual flux and has the ability to reuse extents, these databases are also production and I don’t want prod to hit an ORA-1555 or ORA-30036. So, my initial response was to add space thinking that we now had more resource intensive queries…give them a bit extra, not a big deal.

However, it kept reporting the tablespace as full – and when I did further research, I found that yes,

select sum(bytes /(1024*1024)) from dba_undo_extents where status=’EXPIRED’;
select sum(bytes /(1024*1024)) from dba_undo_extents where status=’ACTIVE’;
select sum(bytes /(1024*1024)) from dba_undo_extents where status=’UNEXPIRED’;

showed almost every single extent as unexpired.

I then searched and queried trying to find what was using the undo. This has now happened multiple times and each time, even when there is next to nothing running on the db, it still has most all extents as unexpired.

I ran the “AUM Common Analysis/Diagnostic Scripts (877613.1)” and came up with nothing…The undo retention on the main db I used for research (this happens on multiple dbs) is set to 15 minutes, so I figured okay, after 15minutes, a lot of these extents will be released…but no.. They weren’t released – and it’s not just that they were taken by something else – again, next to nothing running on this box.

I searched Google and then tried MOS time and again…Finally, I came across a note that says that starting in 10gR2, if the tablespace is set with autoextend off, instead of following the undo_retention, it follows its own internal algorithm based on the tablespace size – which means that though you have it set for 15minutes, it might hold the extents for days.

It then goes on to say hey, no big deal if you see the tablespace as full, don’t worry about it. This isĀ  normal and expected, not a bug…Just ignore the ‘tablespace full’ warnings..Unless you get an ORA-1555

Um..okay. Except that I can’t sit back and assume that everything’s fine – until prod throws errors..If I see something that I could be proactively addressing, then I should be!

So, I ran a test – I changed all of the undo datafiles to autoextend with a maxsize of what their current file size is. Queried again and viola almost every single extent switched from unexpired to expired.

Given that it has been this way since 10gR2, this might be old news to most people. I’m not sure why I hadn’t encountered it regularly before, but whatever the reason, I’m glad this one is finally over as it had me banging my head against the keyboard.

And now I’m off to change the autoextend settings on our other databases..


Read Full Post »

« Newer Posts