Identify the table rowid's of corrupted lob segment blocks

Identify the table rowid's referencing the corrupted lob segment blocks by running the following plsq script
drop table bad_rows;
create table bad_rows (row_id ROWID,oracle_error_code number);
set concat off
set serveroutput on
DECLARE
    n            NUMBER;
    ERROR_CODE   NUMBER;
    bad_rows     NUMBER := 0;
    ora1578      EXCEPTION;
    ora600       EXCEPTION;
    PRAGMA EXCEPTION_INIT (ora1578, -1578);
    PRAGMA EXCEPTION_INIT (ora600, -600);
BEGIN
    FOR cursor_lob
        IN (SELECT ROWID rid, &&lob_column
              FROM &&table_owner.&&table_with_lob)
    LOOP
        BEGIN
            n :=
                DBMS_LOB.INSTR (cursor_lob.&&lob_column, HEXTORAW ('889911'));
        EXCEPTION
            WHEN ora1578
            THEN
                bad_rows := bad_rows + 1;
                INSERT INTO bad_rows
                     VALUES (cursor_lob.rid, 1578);
                COMMIT;
            WHEN ora600
            THEN
                bad_rows := bad_rows + 1;
                INSERT INTO bad_rows
                     VALUES (cursor_lob.rid, 600);
                COMMIT;
            WHEN OTHERS
            THEN
                ERROR_CODE := SQLCODE;
                bad_rows := bad_rows + 1;
                INSERT INTO bad_rows
                     VALUES (cursor_lob.rid, ERROR_CODE);

                COMMIT;
        END;
    END LOOP;
    DBMS_OUTPUT.put_line (
        'Total Rows identified with errors in LOB column: ' || bad_rows);
END;
/
undefine lob_column
select * from bad_rows;

Yorum Gönder

Daha yeni Daha eski