Simpler Navigation for Servers and Operating Systems
Completed: a much simpler Servers and Operating Systems section of the Community. We combined many of the older boards, so you won't have to click through so many levels to get at the information you need. Check the consolidated boards here as many sub-forums are now single boards.
General
cancel
Showing results for 
Search instead for 
Did you mean: 

Error since rollback segment number 2 with name "R01"

Enrico Venturi
Super Advisor

Error since rollback segment number 2 with name "R01"

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
7 REPLIES
Jean-Luc Oudart
Honored Contributor

Re: Error since rollback segment number 2 with name "R01"

Hi,

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

Regards
Jean-Luc
fiat lux
Jean-Luc Oudart
Honored Contributor

Re: Error since rollback segment number 2 with name "R01"

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
fiat lux
Yogeeraj_1
Honored Contributor

Re: Error since rollback segment number 2 with name "R01"

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
No person was ever honoured for what he received. Honour has been the reward for what he gave (clavin coolidge)
Jean-Luc Oudart
Honored Contributor

Re: Error since rollback segment number 2 with name "R01"

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
fiat lux
Yogeeraj_1
Honored Contributor

Re: Error since rollback segment number 2 with name "R01"

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
No person was ever honoured for what he received. Honour has been the reward for what he gave (clavin coolidge)
Enrico Venturi
Super Advisor

Re: Error since rollback segment number 2 with name "R01"

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
Honored Contributor

Re: Error since rollback segment number 2 with name "R01"

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
No person was ever honoured for what he received. Honour has been the reward for what he gave (clavin coolidge)