find block corruption object/segment

Query to display corrupted block object/segment name
--------------------------------------------------------------------

set pages 1000 lines 180

COLUMN segment_name FORMAT A30

 

SELECT DISTINCT owner, segment_name
FROM   v$database_block_corruption dbc
JOIN dba_extents e ON dbc.file# = e.file_id AND dbc.block#
BETWEEN e.block_id and e.block_id+e.blocks-1
ORDER BY 1,2;

 

O/P

OWNER                          SEGMENT_NAME

------------------------------ ------------------------------

SYS                            SMON_SCN_TIME_SCN_IDX

 

ORA-00972: identifier is too long

Scenario :-

SQL> SELECT dbms_metadata.get_ddl('INDEX',’SMON_SCN_TIME_SCN_IDX’,'SYS') from dual;
SELECT dbms_metadata.get_ddl('INDEX',’SMON_SCN_TIME_SCN_IDX’,'SYS') from dual
                                     *
ERROR at line 1:
ORA-00972: identifier is too long

Cause: An object identifier was specified with more than 30 characters.
   
Action: Specify at most 30 characters.
                Make sure that Oracle object names are shorter than thirty characters.

we may experience this issue many times when we trying to access database column, alias, or table names which are too long which having large length. it can be cause ORA-00972.



------------------------           Thank you                -----------------------------------------