Menu

18 Ocak 2017 Çarşamba

ORA-01555: snapshot too old: rollback segment number 0 with name "SYSTEM" too small

ORA-01555: snapshot too old: rollback segment number 0 with name "SYSTEM" too  small

Query to execute to find issued tablespace:

select * from(
SELECT e.owner, e.segment_type, e.segment_name, e.partition_name, c.file#
, greatest(e.block_id, c.block#) corr_start_block#
 , least(e.block_id+e.blocks-1, c.block#+c.blocks-1) corr_end_block#
 , least(e.block_id+e.blocks-1, c.block#+c.blocks-1)
 - greatest(e.block_id, c.block#) + 1 blocks_corrupted
 , null description
 FROM dba_extents e, v$database_block_corruption c
 WHERE e.file_id = c.file#
 AND e.block_id <= c.block# + c.blocks - 1
 AND e.block_id + e.blocks - 1 >= c.block#
 UNION
 SELECT s.owner, s.segment_type, s.segment_name, s.partition_name, c.file#
 , header_block corr_start_block#
 , header_block corr_end_block#
 , 1 blocks_corrupted
 , 'Segment Header' description
 FROM dba_segments s, v$database_block_corruption c
 WHERE s.header_file = c.file#
 AND s.segment_name is NOT NULL
 AND s.header_block between c.block# and c.block# + c.blocks - 1
 UNION
 SELECT null owner, null segment_type, null segment_name, null partition_name, c.file#
 , greatest(f.block_id, c.block#) corr_start_block#
 , least(f.block_id+f.blocks-1, c.block#+c.blocks-1) corr_end_block#
 , least(f.block_id+f.blocks-1, c.block#+c.blocks-1)
 - greatest(f.block_id, c.block#) + 1 blocks_corrupted
 , 'Free Block' description
 FROM dba_free_space f, v$database_block_corruption c
 WHERE f.file_id = c.file#
 AND f.block_id <= c.block# + c.blocks - 1
 AND f.block_id + f.blocks - 1 >= c.block#
 order by file#, corr_start_block#) t where segment_name IS NOT NULL; 


Solution with workaround:
Force an extension of the SYSTEM rollback segment to occur using the following :

connect / as sysdba
alter system set undo_management = MANUAL scope=spfile ;
shutdown immediate ;
startup ;

create table sample (c1 number, c2 varchar2(10));
 

begin
for i in 1.. 400000 loop
insert into sample values (i,'ABCDEFGH');
end loop;
end;
/

delete sample;
commit;

alter system set undo_management = AUTO scope=spfile;

shutdown immediate
startup 

1 yorum: