Has an index
--Execute Plsql Script
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;
/
No index
--Retrieve Records
Setserveroutput on
Setconcat off
DECLARE
nrows number;
RID rowID;
Dobj number;
Rowsperblock number;
BEGIN
rowsperblock:=1000; --Estimating the maximum number of records in a single block
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 are only used if a datafile needs to being skipped due to ORA-376 ( A
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;
/
Two Good Blogs
Http://www.xifenfei.com/4160.html (core idea using ROWID to read block content)
Http://www.hellodba.com/reader.php?ID=216&lang=CN (core idea, data block substitution and copy + query)
