To find segment name from Corrupt Block

Find segment name from Corrupt Block:

Message from Alert Log :


Fri Apr 01 13:56:27 2016
Errors in file /u01/app/oracle/diag/rdbms/prod/PROD/trace/PROD_pr04_4915260.trc:
ORA-00339: archived log does not contain any redo
ORA-00334: archived log: '/u02/flash_recovery_area/PROD/PROD/onlinelog/o1_mf_1_chsdf09j_.log'
Errors in file /u01/app/oracle/diag/rdbms/prod/PROD/trace/PROD_pr04_4915260.trc (incident=4243):
ORA-00600: internal error code, arguments: [kdBlkCheckError], [80], [1288987], [6264], [], [], [], [], [], [], [], []
Incident details in: /u01/app/oracle/diag/rdbms/PROD/PROD/incident/incdir_4243/PROD_pr04_4915260_i4243.trc
Use ADRCI or Support Workbench to package the incident.
See Note 411.1 at My Oracle Support for error and packaging details.
Slave exiting with ORA-10562 exception
Errors in file /u01/app/oracle/diag/rdbms/prod/PROD/trace/PROD_pr04_4915260.trc:
ORA-10562: Error occurred while applying redo to data block (file# 80, block# 1288987)
ORA-10564: tablespace REGISTRATION
ORA-01110: data file 80: '/u02/flash_recovery_area/PROD/ORADATA/hr01.dbf'
ORA-10561: block type 'TRANSACTION MANAGED DATA BLOCK', data object# 89532
ORA-00600: internal error code, arguments: [kdBlkCheckError], [80], [1288987], [6264], [], [], [], [], [], [], [], []
............................


In the alert log we found that datafile 80 has corrupt block and the dartafile is hr01.dbf. The corrupted block is "1288987".

Then run below query to find segment name :

1) Query-1: Find segment name with owner

SQL> select * from dba_extents where file_id = 80 and 1288987 between block_id and block_id + blocks -1 ; 


2) Query-2: Find object details

SQL> select * from dba_objects where object_name='MLOG$_PATIENT' and object_type='TABLE'; 


It will help you.