Error since rollback segment number 2 with name "R01"

[Enrico Venturi]

Hello colleagues,
see below an error I got when dropping DATA tablespace;
what have I to do? How can I do it?

regards
Enrico
SQL> drop tablespace DATA including contents cascade constraints;
drop tablespace DATA including contents cascade constraints
*
ERROR at line 1:
ORA-00604: error occurred at recursive SQL level 1
ORA-01555: snapshot too old: rollback segment number 2 with name "R01" too
small

[Jean-Luc Oudart]

Hi,

try to assign the transaction to a bigger rollback segment (larger extents) an re-run the statement.

Regards
Jean-Luc

[Jean-Luc Oudart]

For information :
00604, 00000, "error occurred at recursive SQL level %s"
// *Cause: An error occurred while processing a recursive SQL statement
// (a statement applying to internal dictionary tables).
// *Action: If the situation described in the next error on the stack
// can be corrected, do so; otherwise contact Oracle Support.


% oerr ora 1555
01555, 00000, "snapshot too old: rollback segment number %s with name \"%s\"
too small"
// *Cause: rollback records needed by a reader for consistent read are
overwritten by other writers
// *Action: Use larger rollback segments

Regards
Jean-Luc

[Yogeeraj_1]

hi,

Metalink say that there are two possible reasons for ORA-01555:
A. The rollback information has been overwritten so that Oracle is unable to rollback the (committed) transaction entries to attain a sufficiently old enough version of the block.

B. The transaction slot in the rollback segment's transaction table (stored in the rollback segment's header) is overwritten, and Oracle cannot rollback the transaction header sufficiently to derive the original rollback segment transaction slot.

Coming to your case, the tablespace DATA contains too many objects. I would consider dropping some of the objects in the tablespace first before attempting to drop the tablespace.

hope this helps too!

kind regards
yogeeraj

[Jean-Luc Oudart]

Hi again,

there were also some bugs related to this error.
Could you give you exact version number ?
"select * from v$version"

Regards
Jean-Luc

[Yogeeraj_1]

hi,

the following script can help you generate a script that would help you drop the objects (tables and indexes) that currently exists on the tablespace DATA:
select 'drop table '||table_name||';'
from dba_tables
where tablespace_name='DATA'
union all
select 'drop index '||index_name||';'
from dba_indexes
where tablespace_name='DATA';


hope this helps too!

kind regards
yogeeraj

[Enrico Venturi]

BANNER
----------------------------------------------------------------
Oracle9i Enterprise Edition Release 9.2.0.2.0 - 64bit Production
PL/SQL Release 9.2.0.2.0 - Production
CORE 9.2.0.2.0 Production
TNS for HPUX: Version 9.2.0.2.0 - Production
NLSRTL Version 9.2.0.2.0 - Production

[Yogeeraj_1]

hi again!

so strange! are you using Rollback segments instead of undo tablespace (default)?

You should be using AUM (automatic undo management) in this version.

post the output of:
show undo
and
show roll


kind regards
yogeeraj