rollback segment number 0 with name "SYSTEM" too small
Applies to:
Oracle Server - Enterprise Edition - Version: 10.2.0.4 and later [Release: 10.2 and later ]Information in this document applies to any platform.
Symptoms
You encounter the following errors when performing a database operation, such as dropping a tablespace:ORA-00604: error occurred at recursive SQL level 1
ORA-01555: snapshot too old: rollback segment number 0 with name "SYSTEM" too small
Changes
The patch for bug:7313166 has been applied.
Cause
This is not a code bug in Oracle, but rather is due to the system rollback segment being too small. Prior to the fix for bug:7313166 when space transactions bound to undo segment 0 they were not generating much undo. Now, with the fix for bug:7313166 it is possible for space transactions binding to undo segment 0 to generate quite a lot of undo. Therefore as the SYSTEM rollback segment has no undo retention policy, and is usually not configured to be very large, it is possible to encounter a situation where we could run out of space and hence raise an ORA-1555 error, particularly for larger dictionary queries.Solution
If you are using a dictionary managed system tablespace then you should be able to workaround this by manually changing the system undo segments storage attributes and forcing an extension to occur, e.g.:set echo on
set feedback on
alter system set "_smu_debug_mode"=4;
drop table helper_for_1555;
create table helper_for_1555 (col1 varchar2(4000));
insert into helper_for_1555 values (rpad('a', 3999));
declare
a number;
begin
for a in 1..18 loop
insert /*+ APPEND +*/ into helper_for_1555 select * from helper_for_1555;
commit;
end loop;
end;
/
rem alter SYSTEM rbseg's storage params
alter rollback segment system storage (next 256M);
alter rollback segment system storage (optimal 1024M);
select segment_name, blocks, bytes, extents from dba_segments
where segment_type='ROLLBACK';
rem bloat the segment
alter system set "_in_memory_undo"=false;
set transaction use rollback segment SYSTEM;
delete from helper_for_1555;
rollback;
select segment_name, blocks, bytes, extents from dba_segments
where segment_type='ROLLBACK';
alter system set "_smu_debug_mode"=0;
alter system set "_in_memory_undo"=true;
set feedback on
alter system set "_smu_debug_mode"=4;
drop table helper_for_1555;
create table helper_for_1555 (col1 varchar2(4000));
insert into helper_for_1555 values (rpad('a', 3999));
declare
a number;
begin
for a in 1..18 loop
insert /*+ APPEND +*/ into helper_for_1555 select * from helper_for_1555;
commit;
end loop;
end;
/
rem alter SYSTEM rbseg's storage params
alter rollback segment system storage (next 256M);
alter rollback segment system storage (optimal 1024M);
select segment_name, blocks, bytes, extents from dba_segments
where segment_type='ROLLBACK';
rem bloat the segment
alter system set "_in_memory_undo"=false;
set transaction use rollback segment SYSTEM;
delete from helper_for_1555;
rollback;
select segment_name, blocks, bytes, extents from dba_segments
where segment_type='ROLLBACK';
alter system set "_smu_debug_mode"=0;
alter system set "_in_memory_undo"=true;
For a locally managed system tablespace, again force an extension of the SYSTEM rollback segment to occur using the following workaround:
connect / as sysdba
alter system set undo_management = MANUAL scope=spfile
shutdown immediate
startup
create table test (t1 number, t2 varchar2(10));
begin
for i in 1.. 300000 loop
insert into test values (i,'AAAAAAAAAA');
end loop;
end;
/
delete test;
commit;
alter system set undo_management = AUTO scope=spfile;
shutdown immediate
startup
alter system set undo_management = MANUAL scope=spfile
shutdown immediate
startup
create table test (t1 number, t2 varchar2(10));
begin
for i in 1.. 300000 loop
insert into test values (i,'AAAAAAAAAA');
end loop;
end;
/
delete test;
commit;
alter system set undo_management = AUTO scope=spfile;
shutdown immediate
startup
NOTES
In the above examples the number of iterations of the INSERT loops performed should be calculated to ensure that when executed it exceeds the current size of the SYSTEM undo segment, so as to ensure the segment extends as intended. For example, in the above locally managed SYSTEM tablespace script, if the SYSTEM rollback segment is 5000000 bytes in size as per the BYTES column of the DBA_SEGMENTS view and the script. is run, because the script. generates more than 5000000 bytes of undo in a single transaction, then the SYSTEM rollback segment will extend causing it's BYTES column value of the DBA_SEGMENTS view to increase. However, if you run the script. a second time with the same number of insert iterations it is unlikely that the SYSTEM rollback segment will grow again.
In the above examples the number of iterations of the INSERT loops performed should be calculated to ensure that when executed it exceeds the current size of the SYSTEM undo segment, so as to ensure the segment extends as intended. For example, in the above locally managed SYSTEM tablespace script, if the SYSTEM rollback segment is 5000000 bytes in size as per the BYTES column of the DBA_SEGMENTS view and the script. is run, because the script. generates more than 5000000 bytes of undo in a single transaction, then the SYSTEM rollback segment will extend causing it's BYTES column value of the DBA_SEGMENTS view to increase. However, if you run the script. a second time with the same number of insert iterations it is unlikely that the SYSTEM rollback segment will grow again.
来自 “ ITPUB博客 ” ，链接：https://blog.itpub.net/7900083/viewspace-711941/ ，如需转载，请注明出处，否则将追究法律责任。
请登录后发表评论
登录
全部评论
