Post date: 18-Oct-2012 17:32:45
set lines 200 pages 0
set serveroutput on size 1000000
begin
for x in (
select table_name,index_name, column_name from dba_ind_columns where index_name in (
select segment_name 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.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#) )
) loop
dbms_output.put_line(' declare w1 FACDW.'||x.table_name||'.'||x.column_name||'%TYPE;');
dbms_output.put_line('begin ');
dbms_output.put_line('select /*+ parallel(a 8) index(a '||x.index_name||')*/ max(to_char('||x.column_name||')) into w1 from FACDW.'||x.table_name||' a;');
dbms_output.put_line('dbms_output.put_line(''Index '||x.index_name||' - OK'');' );
dbms_output.put_line('exception when others then dbms_output.put_line(''Index '||x.index_name||' is corrupt'');');
dbms_output.put_line('end;');
dbms_output.put_line('/');
end loop;
end;
/
V$DATABASE_BLOCK_CORRUPTION is populated after an RMAN-backup.
It might be that corrupted blocks are NOT reused (yet) after the object recreation, so will remain as free blocks in the datafile, but will STILL be corrupt.
The corruption is cleared once the object is reused.
So you can check the following :
Check with RMAN if there are datablock corruptions :
% rman target /
RMAN> run {
allocate channel d1 type disk;
allocate channel d2 type disk;
backup check logical validate filesperset 10 database;
}
RMAN is populating the view V$DATABASE_BLOCK_CORRUPTION with the above commands, if corrupted blocks are found
% sqlplus "/ as sysdba"
SQL> select * from v$database_block_corruption ;
SQL> 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.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#;
If the blocks are marked as 'Free Block', than you can use :
Note 336133.1 : How to Format Corrupted Block Not Part of Any Segment
to FORCE the format the block and clear the corruption.