快速插入[@more@]
SQL> insert /**//*+append*/ into t select * from dba_objects nologging;
9157 rows created.
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
---------- ---------- ---------- ---------- ------------------------------
2 21 1 1 CUST
SQL> commit;
Commit complete.
SQL> insert into t select * from dba_objects;
9157 rows created.
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
---------- ---------- ---------- ---------- ------------------------------
1 1 13 423 CUST
create or replace procedure p_delete
as
n number(10);
cursor my_cur is select * from t1;
begin
n:=0;
for i in my_cur loop
delete from t1 where <条件>;
n:=n+1;
if (mod(n,2000))=0 then
commit;
end if;
end loop;
end;
/
SQL> insert /**//*+append*/ into t select * from dba_objects nologging;9157 rows created.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---------- ---------- ---------- ---------- ------------------------------ 2 21 1 1 CUSTSQL> commit;Commit complete.SQL> insert into t select * from dba_objects;9157 rows created.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---------- ---------- ---------- ---------- ------------------------------ 1 1 13 423 CUST快速删除
建个存储过程,达到2000条或者更多条,提交一次.
create or replace procedure p_deleteasn number(10);cursor my_cur is select * from t1;beginn:=0;for i in my_cur loop delete from t1 where <条件>;n:=n+1;if (mod(n,2000))=0 thencommit;end if;end loop;end;/ 来自 “ ITPUB博客 ” ，链接：https://blog.itpub.net/9099175/viewspace-980794/ ，如需转载，请注明出处，否则将追究法律责任。
上一篇：
取本页URL地址的方法总结
下一篇：
Ruby介绍
请登录后发表评论
登录
全部评论
