有索引
--运行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;
/
没有索引
--找回记录
setserveroutput on
setconcat off
DECLARE
nrows number;
rid rowid;
dobj number;
ROWSPERBLOCK number;
BEGIN
ROWSPERBLOCK:=1000; --估算最大的一个块中记录条数
nrows:=0;
selectdata_object_id intodobj
fromdba_objects
whereowner = 'CHF'
andobject_name = 'T_XIFENFEI'
-- and subobject_name = '<table partition>' Add this condition if table is partitioned
;
fori in(selectrelative_fno, block_id, block_id+blocks-1 totblocks
fromdba_extents
whereowner = 'CHF'
andsegment_name = 'T_XIFENFEI'
-- and partition_name = '<table partition>' Add this condition if table is partitioned
-- and file_id != <OFFLINED DATAFILE> This condition is only used if a datafile needs to be skipped due to ORA-376 (A)
orderby extent_id)
loop
forbr ini.block_id..i.totblocks loop
forj in1..ROWSPERBLOCK loop
begin
rid := dbms_rowid.ROWID_CREATE(1,dobj,i.relative_fno, br , j-1);
insertinto CHF.T_XIFENFEI_NEW
select/*+ ROWID(A) */ *
fromCHF.T_XIFENFEI A
whererowid = rid;
if sql%rowcount = 1 thennrows:=nrows+1; endif;
if (mod(nrows,10000)=0) thencommit;endif;
exceptionwhenothers thennull;
end;
endloop;
endloop;
endloop;
COMMIT;
dbms_output.put_line('Total rows: '||to_char(nrows));
END;
/
两个不错的博客
http://www.xifenfei.com/4160.html（核心思想利用rowid读取块内容）
http://www.hellodba.com/reader.php?ID=216&lang=CN（核心思想。数据块替换与拷贝+查询）