执行存储过程del_tab时出现下面的错误
BEGIN del_tab; END;
*
ERROR 位于第 1 行:
ORA-01555: 快照过旧: 回退段号 7 在名称为 "_SYSSMU7$" 过小
ORA-06512: 在"LICHENG.DEL_TAB", line 5
ORA-06512: 在line 1存储过程内容为:
create or replace procedure del_tab is
cursor mycur is
select clbh from xxfl where jgsj<=add_months(sysdate,-1);
begin
for var_clbh in mycur loop
delete from tpxx where tpid=var_clbh.clbh;
delete from xxfl where clbh=var_clbh.clbh;
delete from bjxx where clbh=var_clbh.clbh;
end loop;
commit;
end;
执行一次存储过程可能要删除10万-20万张图片
BEGIN del_tab; END;
*
ERROR 位于第 1 行:
ORA-01555: 快照过旧: 回退段号 7 在名称为 "_SYSSMU7$" 过小
ORA-06512: 在"LICHENG.DEL_TAB", line 5
ORA-06512: 在line 1存储过程内容为:
create or replace procedure del_tab is
cursor mycur is
select clbh from xxfl where jgsj<=add_months(sysdate,-1);
begin
for var_clbh in mycur loop
delete from tpxx where tpid=var_clbh.clbh;
delete from xxfl where clbh=var_clbh.clbh;
delete from bjxx where clbh=var_clbh.clbh;
end loop;
commit;
end;
执行一次存储过程可能要删除10万-20万张图片
-------------
你删除的数据量太大了,需要的回滚段空间不够,所以产生这个错误
解决办法:
1.在循环里,当每删除1000或10000笔数据的时候,你做一次提交,它就不会占用那么多的空间
2.增大回滚段空间,当然数据超过几十万笔时,需要分配的空间也是非常大的建议你用第一个办法
cursor mycur is
select clbh from xxfl where jgsj <=add_months(sysdate,-1);
begin
for var_clbh in mycur loop
delete from tpxx where tpid=var_clbh.clbh;
commit;
delete from xxfl where clbh=var_clbh.clbh;
commit;
delete from bjxx where clbh=var_clbh.clbh;
commit;
end loop;
create or replace procedure del_tab is
cursor mycur is
select clbh from xxfl where jgsj <=add_months(sysdate,-1);
begin
for var_clbh in mycur loop
delete from tpxx where tpid=var_clbh.clbh;
delete from xxfl where clbh=var_clbh.clbh;
delete from bjxx where clbh=var_clbh.clbh;
commit;
end loop;
commit;
end;