Re: System tablespace 99% full a problem?

Regarding the undo tablespace: If you've got autoextend on and a large undo_retention, Oracle is much more profligate with it's rollback usage. So if you have a maximum amount of extension (which you should if you have autoextend!) you will reach it much sooner than expected, and Oracle will barf with "Failure to extend rollback segment because of 30036 condition."

So don't use autoextend with a large undo_retention. Normally, Oracle will attempt to free up undo that is merely kept for retention, but won't if you have these settings.

Besides that, your undo_retention will determine how long until your undo ts will be less full. So if you are trying to shrink your undo, sometimes you just have to wait the retention time. Other times there's no way, and you have to simply recreate your undo ts (create a new one, alter system to use it, drop old one).

See metalink notes 268870.1 and 247184.1.

It is normal for system to be autoextend and just about full, so be real careful not to put user objects there, especially watch user default tablespaces and exp/imp.

jg

--
@home.com is bogus.
Dude!
http://www.signonsandiego.com/uniontrib/20050531/news_1n31velzy.html