Problem with Undo Tablespace

Hi Gurus,

I'm facing problems with my undo tablespace.

I've undo tablespace with 10G capacity. When I tried to analyze one of the schemas in the database, it failed with ORA-1650 unable to extend rollback segment error.

I checked the usage of rollback space in v$rollstat, it is just around 2G. But, the undo tablespace shows 100% full, no free space left in the tablespace.

My database is oracle 9206 on Solaris 9.

Please help.

Check your UNDO_RETENTION setting, this may have prevented reuse of undo space.

If you run this query you may see some rollback segments in OFFLINE state

SELECT segment_name, tablespace_name, status FROM dba_rollback_segs;

Hi

Your undo_retention to should be set to the following value:
select MAXQUERYLEN from v$undostat;
You will not face any errors after setting this value.