The purpose of this plsql script is to create a new table based on a table that is producing errors such as ORA-8103 or ORA-1410 or ORA-1578. The script skips the blocks/rows producing those errors.
This is done when there is not option to restore the table from a backup like applying media recovery or recovering the table from an export or other source.
The first option to skip an ORA-1578 error is to use the DBMS_REPAIR script and decide to create a new table using create table as select (CTAS); however if for any reason that does not work use the plsql in this document instead.
Run sqlplus with SYS or TABLE owner user
sqlplus '/ as sysdba'
or
sqlplus <table owner> / password
SKIP ORA-1578 ORA-8103 ORA-1410=================================
Create a new table based on the table that is producing errors with no rows:
SQL> create table <new table name> as select * from <original table name> where 1=2;
Create the table to keep track of ROWIDs pointing to affected rows:
SQL> create table bad_rows (row_id rowid ,oracle_error_code number);
set serveroutput on
DECLARE
TYPE RowIDTab IS TABLE OF ROWID INDEX BY BINARY_INTEGER;
CURSOR c1 IS select /*+ index(tab1) */ rowid
from <original table name> tab1
where <indexed column> is NOT NULL;
r RowIDTab;
rows NATURAL := 20000;
bad_rows number := 0 ;
errors number;
error_code number;
myrowid rowid;
BEGIN
OPEN c1;
LOOP
FETCH c1 BULK COLLECT INTO r LIMIT rows;
EXIT WHEN r.count=0;
BEGIN
FORALL i IN r.FIRST..r.LAST SAVE EXCEPTIONS
insert into <new table name>
select /*+ ROWID(A) */ <list of columns from table (ie col1, col2,..)> OR * for all columns
from <original table name> A where rowid = r(i);
EXCEPTION
when OTHERS then
BEGIN
errors := SQL%BULK_EXCEPTIONS.COUNT;
FOR err1 IN 1..errors LOOP
error_code := SQL%BULK_EXCEPTIONS(err1).ERROR_CODE;
if error_code in (1410, 8103, 1578) then
myrowid := r(SQL%BULK_EXCEPTIONS(err1).ERROR_INDEX);
bad_rows := bad_rows + 1;
insert into bad_rows values(myrowid, error_code);
else
raise;
end if;
END LOOP;
END;
END;
commit;
END LOOP;
commit;
CLOSE c1;
dbms_output.put_line('Total Bad Rows: '||bad_rows);
END;
/
Notes:
- Replace the next values in the plsql script by the values of the affected table: <original table name>, <indexed column>, <list of columns from table (ie col1, col2,..)>
- The idea is to get the rowid's from an existent index, then get all the columns from the table for each rowid and insert these rows into the new table. Using the "index" hint, allows the optimizer to choose the most appropriated index to scan the table based on the indexed column.
- Make sure that the select in the plsql is using an index. One way to verify if the index is used is to get an execution plan from sqlplus:
set autotrace trace explain
select /*+ index(tab1) */ rowid from <original table name> tab1 where <indexed column> is NOT NULL;
- Note that the plsql executes an INSERT for 20000 rows and COMMIT. If it is required to change this, adjust the value of rows. e.g.:
rows NATURAL := 50000; -> to insert 50000 rows in one execution of INSERT and commit every 50000 records.
- If 'Total Bad Rows:' displays 0 and it is known for certain that there is a block incorrect on disk that is causing the ORA-8103, , then it means that the block is empty (no rows) and there is not data loss.
SKIP ORA-600 in a Table=========================
This is useful when the ORA-600 is produced by a non-existent chained row (invalid nrid) like ORA-600 [kdsgrp1] and when event 10231 does not work.
SQL> drop table bad_rows;
SQL> create table bad_rows (row_id ROWID ,oracle_error_code number);
Create the new empty table:
SQL> create table &&new_table as select * from &&affected_table where 1=2;
set serveroutput on
declare
n number:=0;
bad_rows number := 0;
error_code number;
ora600 EXCEPTION;
PRAGMA EXCEPTION_INIT(ora600, -600);
begin
for i in (select rowid rid from &&affected_table) loop
begin
insert into &&new_table
select *
from &&affected_table
where rowid=i.rid;
n:=n+1;
exception
when ora600 then
bad_rows := bad_rows + 1;
insert into bad_rows values(i.rid,600);
commit;
when others then
error_code:=SQLCODE;
bad_rows := bad_rows + 1;
insert into bad_rows values(i.rid,error_code);
commit;
end;
end loop;
dbms_output.put_line('Total Bad Rows: '||bad_rows);
dbms_output.put_line('Total Good rows: '||n);
end;
/
SKIP ORA-600 in IOT====================
This is useful when the ORA-600 is produced by a non-existent chained row (invalid nrid) like ORA-600 [kdsgrp1] and when event 10231 does not work for an Index Organized Table (IOT).
SQL> drop table bad_rows;
SQL> create table bad_rows (row_id UROWID,oracle_error_code number);
Create the new empty table:
SQL> create table &&new_table as select * from &&affected_table where 1=2;
set serveroutput on
declare
n number:=0;
bad_rows number := 0;
error_code number;
ora600 EXCEPTION;
PRAGMA EXCEPTION_INIT(ora600, -600);
begin
for i in (select /*+ INDEX_FFS(IOT_1) */ rowid rid from &&affected_table IOT_1) loop
begin
insert into &&new_table
select *
from &&affected_table
where rowid=i.rid;
n:=n+1;
exception
when ora600 then
bad_rows := bad_rows + 1;
insert into bad_rows values(i.rid,600);
commit;
when others then
error_code:=SQLCODE;
bad_rows := bad_rows + 1;
insert into bad_rows values(i.rid,error_code);
commit;
end;
end loop;
dbms_output.put_line('Total Bad Rows: '||bad_rows);
dbms_output.put_line('Total Good rows: '||n);
end;
/
