Skip to Main Content

Oracle Database Discussions

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Rollback segment problem

474257Dec 6 2005 — edited Dec 7 2005
Hi,

I got error like this :
ORA-01555: snapshot too old: rollback segment number 2 with name "RBS1" too small

Then I added a new rollback segment, as sysdba I did :

create tablespace tempx datafile '/rbs/tempx.dbf' size 2000M REUSE AUTOEXTEND ON NEXT 5M MAXSIZE 2100M;
CREATE ROLLBACK SEGMENT large STORAGE(INITIAL 300M NEXT 5M) tablespace tempx;
alter rollback segment large online;
set transaction use rollback segment large;
exit;

Then I connect as user :

set transaction use rollback segment large;

select count(id) from datafolder where folder_state(id) = 1;

ERROR at line 1:
ORA-01555: snapshot too old: rollback segment number 2 with name "RBS1" too
small

I still get that error! I checked the rollback segments :
SQL> SELECT segment_name, tablespace_name, bytes, blocks, extents
2 FROM sys.dba_segments
3 WHERE segment_type = 'ROLLBACK';

SEGMENT_NAME
--------------------------------------------------------------------------------
TABLESPACE_NAME BYTES BLOCKS EXTENTS
------------------------------ ---------- ---------- ----------
SYSTEM
SYSTEM 819200 100 10

RBS0
SYSTEM 114688 14 2

RBS1
RBS 2621440 320 16


SEGMENT_NAME
--------------------------------------------------------------------------------
TABLESPACE_NAME BYTES BLOCKS EXTENTS
------------------------------ ---------- ---------- ----------
RBS2
RBS 2785280 340 17

RBS3
RBS 13107200 1600 80

RBS4
RBS 13107200 1600 80


SEGMENT_NAME
--------------------------------------------------------------------------------
TABLESPACE_NAME BYTES BLOCKS EXTENTS
------------------------------ ---------- ---------- ----------
LARGE
TEMPX 319815680 39040 2


Questions:
1. Why I still got that error ?
2. Do I need to issue "set transaction use rollback segment large;" everytime before any query ?
3. Is there any workaround ?

Thanks
Comments
Locked Post
New comments cannot be posted to this locked post.
Post Details
Locked on Jan 4 2006
Added on Dec 6 2005
8 comments
3,225 views