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;
Etiketler :
Oracle Troubleshooting