关于oracle快速删除和快速插入的方法代码

会计考友

-快速插入  

SQL> insert /**//*+append*/ into t select * from dba_objects nologging;  

9 M2 a% {8 h! {' i; J3 ^4 o& G9157 rows created.  
  O4 B7 H& s0 u: S7 E3 B  v8 E( A0 T$ y
% d: g  l3 }' N( c; L7 s) W
SQL> select a.xidusn,a.xidslot,a.used_ublk,a.used_urec,b.username  
& M& [: c) f. A$ t( H2 R. l 2 from v$transaction a,v$session b,v$mystat c  
$ S' p3 ^9 O' N% N$ m0 y8 r* u 3 where a.addr = b.taddr and c.statistic# = 1  
4 /  

1 l3 o# l& p; @- g XIDUSN XIDSLOT USED_UBLK USED_UREC USERNAME  
---------- ---------- ---------- ---------- ------------------------------  
2 21 1 1 CUST  

SQL> commit;  
0 q4 [5 |' ~" }: N
Commit complete.  

6 L1 |: T7 l2 [8 X0 }3 D1 B. U4 wSQL> insert into t select * from dba_objects;  
# b7 i! P) p3 i. x$ q' j
4 u4 r; c6 p9 e' J+ o3 |9157 rows created.  
' K- j) r6 R4 E! q# G
SQL> select a.xidusn,a.xidslot,a.used_ublk,a.used_urec,b.username  
2 from v$transaction a,v$session b,v$mystat c  
3 where a.addr = b.taddr and c.statistic# = 1  
4 /  

XIDUSN XIDSLOT USED_UBLK USED_UREC USERNAME  
---------- ---------- ---------- ---------- ------------------------------  
4 u8 z( A8 I# o4 Z7 M# A5 [ 1 1 13 423 CUST  

* X4 o# e+ `1 [9 G# |  H
. w. \% i- T" `8 v' j+ S快速删除  
4 J& Y8 Y+ g4 [, y- e2 j9 N建个存储过程,达到2000条或者更多条,提交一次.  
create or replace procedure p_delete  
2 `7 g& b- {4 G' e# b3 A& e  Uas  
, n: |" s$ F6 \' z2 A7 u0 Zn number(10);  
cursor my_cur is select * from t1;  
begin  
n:=0;  
( O  k1 V* Z  K( V. s; Sfor i in my_cur loop   
delete from t1 where ;  
n:=n+1;  
' K* s6 D3 t  ^* X% B: X' g: jif (mod(n,2000))=0 then  
) s8 B: F4 f- Z& i3 |$ Dcommit;  
! s# Q9 {5 |( F7 y1 A; `: C* y4 bend if;  
8 x$ R% d2 y7 Q5 o. w( eend loop;  
) S. W) ]7 c. Y9 R* [end;  
) T* \  ~: Z' B8 b/