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;
责任编辑：小草
您现在的位置： 