Rollback segment problem
474257Dec 6 2005 — edited Dec 7 2005Hi,
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