oracle批量update和delete

下面一段示例说明了oracle的基本操作insert、update、delete产生的undo的大小和记录条数。

--insert操作无论是1条或者多条，一次产生一条undo数据
SQL> create table a (b number, c varchar2(30));

Table created

SQL> insert into a values (1,'zhangsan');

1 row inserted

SQL>
SQL> select used_ublk,used_urec from v$session s, v$transaction t
2 where s.audsid=sys_context('userenv', 'sessionid') and s.taddr = t.addr;

USED_UBLK USED_UREC
---------- ----------
1 1

SQL> insert into a select rownum,rownum from dual connect by rownum<=50;

50 rows inserted

SQL>
SQL> select used_ublk,used_urec from v$session s, v$transaction t
2 where s.audsid=sys_context('userenv', 'sessionid') and s.taddr = t.addr;

USED_UBLK USED_UREC
---------- ----------
1 2

--delete操作产生的undo条数取决于delete操作本身影响的记录数SQL> delete from a;

51 rows deleted

SQL>
SQL> select used_ublk,used_urec from v$session s, v$transaction t
2 where s.audsid=sys_context('userenv', 'sessionid') and s.taddr = t.addr;

USED_UBLK USED_UREC
---------- ----------
1 53

SQL> insert into a select rownum,rownum from dual connect by rownum<=50;

50 rows inserted

SQL>
SQL> select used_ublk,used_urec from v$session s, v$transaction t
2 where s.audsid=sys_context('userenv', 'sessionid') and s.taddr = t.addr;

USED_UBLK USED_UREC
---------- ----------
1 54

--update操作产生的undo条数取决于被update的数据的原值和新值存在差异的记录数量
SQL> update a set c=rownum;

50 rows updated

SQL>
SQL> select used_ublk,used_urec from v$session s, v$transaction t
2 where s.audsid=sys_context('userenv', 'sessionid') and s.taddr = t.addr;

USED_UBLK USED_UREC
---------- ----------
2 57

SQL> update a set c=rownum+1000;

50 rows updated

SQL>
SQL> select used_ublk,used_urec from v$session s, v$transaction t
2 where s.audsid=sys_context('userenv', 'sessionid') and s.taddr = t.addr;

USED_UBLK USED_UREC
---------- ----------
2 107

SQL> update a set c=rownum;

50 rows updated

SQL>
SQL> select used_ublk,used_urec from v$session s, v$transaction t
2 where s.audsid=sys_context('userenv', 'sessionid') and s.taddr = t.addr;

USED_UBLK USED_UREC
---------- ----------
3 157

SQL> update a set c=rownum;

50 rows updated

SQL>
SQL> select used_ublk,used_urec from v$session s, v$transaction t
2 where s.audsid=sys_context('userenv', 'sessionid') and s.taddr = t.addr;

USED_UBLK USED_UREC
---------- ----------
3 160

测试中表没有主键,如果有主键,所有操作都会增加一条undo数据.在删除或者update大量数据时候会产生大量undo,
所以建议这两种操作进行分批提交.

--批量更新

create or replace procedure updateattr(in_number number)

is
cursor attr is
select *
from src_attr
where status = 'Y'
and mod(row_id, 10) = in_number;

begin

for rec in attr loop

update src_attr
set status = 'N'
where status = 'Y'
and rownum <= 1000000;
commit;
end loop;
commit;

end updateattr;

--批量删除

create or replace procedure delBigTab--分批提交删除
(
p_TableName in varchar2,--表名
p_Condition in varchar2,--条件
p_Count in varchar2--每批提交的条数
)
as
pragma autonomous_transaction;
n_delete number:=0;
begin
while 1=1 loop
EXECUTE IMMEDIATE
'delete from '||p_TableName||' where '||p_Condition||' and rownum <= :rn'
USING p_Count;
if SQL%NOTFOUND then
exit;
else
n_delete:=n_delete + SQL%ROWCOUNT;
end if;
commit;
end loop;
commit;
DBMS_OUTPUT.PUT_LINE('Finished!');
DBMS_OUTPUT.PUT_LINE('Totally '||to_char(n_delete)||' records deleted!');
end;

--

呵呵，我也做了一版，同时删除重复记录。：）

C:\Documents and Settings\Administrator>sqlplus "/ as sysdba"

SQL*Plus: Release 9.2.0.4.0 - Production on 星期三 7月 11 15:14:03 2007

Copyright (c) 1982, 2002, Oracle Corporation.  All rights reserved.


连接到:
Oracle9i Enterprise Edition Release 9.2.0.4.0 - Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.4.0 - Production

SQL> conn my_user/my_user
已连接。
SQL> drop table my_table1;

表已丢弃。

SQL> create table my_table1 as select * from my_table2;

表已创建。

SQL> select * from my_table1 order by id;

        ID NAME
---------- ----------
         1 name1
         2 name2
         2 name2
         2 name2
         2 name2
         3 name3
         3 name3

已选择7行。

SQL> set serveroutput on
SQL> create or replace procedure DelBigRows
  2  as
  3     rows_delete number := 0;
  4  begin
  5     loop
  6             execute immediate
  7                     'delete from my_table1 where rowid not in (select min(rowid) from my_table1 group by id) and rownum <2';
  8
  9             if SQL%NOTFOUND then
10                     exit;
11             else
12                     rows_delete := rows_delete + SQL%ROWCOUNT;
13                     dbms_output.put_line('deleted rows: '||to_char(rows_delete));
14             end if;
15             commit;
16     end loop;
17     commit;
18     dbms_output.put_line('finished this action.');
19  end;
20  /

过程已创建。

SQL> execute delbigrows;
deleted rows: 1
deleted rows: 2
deleted rows: 3
deleted rows: 4
finished this action.

PL/SQL 过程已成功完成。

SQL> select * from my_table1;

        ID NAME
---------- ----------
         1 name1
         2 name2
         3 name3

SQL>
SQL> exit
从Oracle9i Enterprise Edition Release 9.2.0.4.0 - Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.4.0 - Production中断开

C:\Documents and Settings\Administrator>sqlplus "/ as sysdba"

SQL*Plus: Release 9.2.0.4.0 - Production on 星期三 7月 11 15:39:13 2007

Copyright (c) 1982, 2002, Oracle Corporation.  All rights reserved.


连接到:
Oracle9i Enterprise Edition Release 9.2.0.4.0 - Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.4.0 - Production

SQL> conn my_user/my_user
已连接。
SQL> drop table my_table1;

表已丢弃。

SQL> create table my_table1 as select * from my_table2;

表已创建。

SQL> alter table my_table1 add my_birthday date;

表已更改。

SQL> update my_table1 set my_birthday=sysdate-1 where id=2;

已更新4行。

SQL> update my_table1 set my_birthday=sysdate where id <> 2;

已更新3行。

SQL> commit;

提交完成。

SQL> select id,name,to_char(my_birthday,'yyyy-mm-dd hh24:mi:ss') from my_table1 order by id;

        ID NAME       TO_CHAR(MY_BIRTHDAY
---------- ---------- -------------------
         1 name1      2007-07-11 15:39:56
         2 name2      2007-07-10 15:39:50
         2 name2      2007-07-10 15:39:50
         2 name2      2007-07-10 15:39:50
         2 name2      2007-07-10 15:39:50
         3 name3      2007-07-11 15:39:56
         3 name3      2007-07-11 15:39:56

已选择7行。

SQL> set serveroutput on
SQL> create or replace procedure DelBigRows
  2  as
  3     rows_delete number := 0;
  4  begin
  5     loop
  6             execute immediate
  7                     'delete from my_table1 where rowid not in (select min(rowid) from my_table1 group by id) and to_date(to_char(my_birthday,''yyyy-mm-dd''),''yyyy-mm-dd'') < (sysdate -1) and rownum <2';
  8
  9             if SQL%NOTFOUND then
10                     exit;
11             else
12                     rows_delete := rows_delete + SQL%ROWCOUNT;
13                     dbms_output.put_line('deleted rows: '||to_char(rows_delete));
14             end if;
15             commit;
16     end loop;
17     commit;
18     dbms_output.put_line('finished this action.');
19  end;
20  /

过程已创建。

SQL> exec delbigrows;
deleted rows: 1
deleted rows: 2
deleted rows: 3
finished this action.

PL/SQL 过程已成功完成。

SQL>  select id,name,to_char(my_birthday,'yyyy-mm-dd hh24:mi:ss') from my_table1 order by id;

        ID NAME       TO_CHAR(MY_BIRTHDAY
---------- ---------- -------------------
         1 name1      2007-07-11 15:39:56
         2 name2      2007-07-10 15:39:50
         3 name3      2007-07-11 15:39:56
         3 name3      2007-07-11 15:39:56

SQL>