sqlplus抢救数据（测）

有索引
--执行plsql脚本
DECLARE
TYPE RowIDTab ISTABLE OF ROWID INDEXBY BINARY_INTEGER;

CURSORc1 IS select /*+ index(xifenfei ind_xifenfei) */ rowid
fromchf.xifenfei
whereobject_id isNOT NULL;

r RowIDTab;
rows NATURAL := 20000;
bad_rows number := 0 ;
errors number;
error_code number;
myrowid rowid;
BEGIN
OPENc1;
LOOP
FETCH c1 BULK COLLECT INTOr LIMIT rows;
EXITWHENr.count=0;
BEGIN
FORALL i INr.FIRST..r.LASTSAVE EXCEPTIONS
insertinto chf.xifenfei_new
select/*+ ROWID(A) */ *
fromchf.xifenfei A whererowid = r(i);
EXCEPTION
whenOTHERS then
BEGIN
errors := SQL%BULK_EXCEPTIONS.COUNT;
FORerr1 IN1..errors LOOP
error_code := SQL%BULK_EXCEPTIONS(err1).ERROR_CODE;
if error_code in(1410, 8103) then
myrowid := r(SQL%BULK_EXCEPTIONS(err1).ERROR_INDEX);
bad_rows := bad_rows + 1;
insertinto chf.bad_rows values(myrowid, error_code);
else
raise;
endif;
ENDLOOP;
END;
END;
commit;
ENDLOOP;
commit;
CLOSEc1;
dbms_output.put_line('Total Bad Rows: '||bad_rows);
END;
/
没有索引
--找回记录