How to fix block corruption of tables in the oracle database

[@SwatiMishra-saurabh]

really helpful

[@RahulMishra-r8c]

its good solution

[@TechDBA_Future]

Thanks

[@Technoserv_people]

thanks, it worked for me

[@TechDBA_Future]

Thanks

[@sidharthshukla1146]

good solution

[@TechDBA_Future]

Thanks

[@faiqaizaz1998]

Share the query

[@TechDBA_Future]

Sure

[@TechDBA_Future]

Posting the sql query here for block corruption check

[@TechDBA_Future]

SELECT e.owner, e.segment_type, e.segment_name SG_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#
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#
ORDER BY file#, corr_start_block#;