经典问题求经典解决办法之delete

Question

这个问题是关于delete和truncate的：
我的一个存储过程在维护一张表的时候，删除一个月前的数据。
现在这个表积压数据1亿3000W条数据，delete时候，回滚段不足。
不能truncate表，大家一般遇到此类问题怎么解决。（排除扩UNDO表空间）

Answer 1

先在表上建某一字段的索引 ，然后新建一张表保存需要保留的表数据。
truncate原表，  再往空表插入。

Answer 2

没碰到过。
delete的时候加入条件。
一部分一部分的删除数据，
在进行每次删除之后立即做commit操作。
这样应该就不会存在回滚段不足的情况了吧。

Answer 3

二楼三楼的方法都可行。
这次我的处理方法，和三楼分批处理的思路类似。
我用了这么一个过程：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;

Answer 4

建议你不要采用DELETE的方法，你D几次以后，你表空间都被你折腾满了。把需要的数据插入另外一个表，然后TRUNCATE，然后再把数据搞回来~~

Answer 5

我觉得应该重新设置undo_retention 这个参数为0，
这样删除数据时，在undo表空间中就不会保存数据，
删除完后，再改回来

Answer 6

create table newtab nologging as  select * from oldtab where 需要的数据条件;truncate table oldtab;
drop table oldtab;alter table newtab rename to oldtab

Answer 7

偶也碰到这样的问题，不过我的数据没有楼主那么高级，只是百万级，没有楼主那么大。
大概准备删除一半的的数据。
新建一张表保存需要保留的表数据也要消耗不少空间和时间，跟直接写个SQL删除的效果是一样的。除了分区表的方法以外还有别的方法吗？

Answer 8

-- 给你一个我清理垃圾数据的例子：-- USER_SIGNONLINE_TIME_TJ-- *1) 查看建表语句：
-- (目的：使用 CREATE TABLE new_tb AS SEELCT * FROM old_tb，会使其 new_tb 的某些有默认值的字段其默认值丢失)，
-- 所以先查看其建表语句
select dbms_metadata.get_ddl('TABLE','USER_SIGNONLINE_TIME_TJ') from dual;------------------------------------------------------------------------------  CREATE TABLE "HLL"."USER_SIGNONLINE_TIME_TJ"
   (    "MOBILE" VARCHAR2(20),
        "SIGN_DATE" DATE DEFAULT SYSDATE,
        "LOGOUT_DATE" DATE DEFAULT SYSDATE,
        "SECONDS" NUMBER(18,0)
   ) PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING
  STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
  TABLESPACE "HLL"
-- 查看表的总记录行数：
select count(*) from USER_SIGNONLINE_TIME_TJ;  COUNT(*)
----------
  72320528-- 查看将要保留的总记录行数：
create table tmobile_tmp2
as select distinct mobile from tmobile_tmp t1
where exists (select 1 from imsi2mobile t2
where t2.imsi like '87%'
  and t2.mobile = t1.mobile );
create index tmobile_tmp2_inx on tmobile_tmp2(mobile) tablespace index01;select count(*) from USER_SIGNONLINE_TIME_TJ t1
where not exists ( select 1
  from tmobile_tmp2 t2
where t2.mobile = t1.mobile );  COUNT(*)
----------
  10276923-- 创建备份表：(排除将要删除的数据，保留有用的数据)
create table USER_SIGNONLINE_TIME_bak
as select * from USER_SIGNONLINE_TIME_TJ t1
where not exists ( select 1
  from tmobile_tmp2 t2
where t2.mobile = t1.mobile );-- 给字段加上默认值：
alter table USER_SIGNONLINE_TIME_bak modify sign_date default SYSDATE;
alter table USER_SIGNONLINE_TIME_bak modify logout_date default SYSDATE;-- 删除今天的数据：(没有今天的有用数据)
delete from USER_SIGNONLINE_TIME_bak
where logout_date >=trunc(sysdate);-- 查看表的相关性：
-- 查看 user_signlog 的表的相关性 (看是否有其相关的触发器)
column name for a30;
column type for a30;
column referenced_name for a30;
column referenced_type for a30;
select name, type,
       referenced_name, referenced_type
from   user_dependencies
where referenced_name='USER_SIGNONLINE_TIME_TJ';NAME                           TYPE                           REFERENCED_NAME                REFERENCED_TYPE
------------------------------ ------------------------------ ------------------------------ ----------------------
SXF_ONLINE_TJ                  PROCEDURE                      USER_SIGNONLINE_TIME_TJ        TABLE
USERPOINTS_TJ_PROC_ALL         PROCEDURE                      USER_SIGNONLINE_TIME_TJ        TABLE
USERPOINTS_TJ_PROC             PROCEDURE                      USER_SIGNONLINE_TIME_TJ        TABLE-- 都是一些存储过程，没有触发器，可以放心重命名表(无物化视图)alter table USER_SIGNONLINE_TIME_TJ rename to USER_SIGNONLINE_TIME_bak2;
alter table USER_SIGNONLINE_TIME_bak rename to USER_SIGNONLINE_TIME_TJ;
alter table USER_SIGNONLINE_TIME_bak2 rename to USER_SIGNONLINE_TIME_bak;-- 查看原表还有没有包含今天的有用数据：
select count(*) from USER_SIGNONLINE_TIME_bak t1
where logout_date >=trunc(sysdate)
and not exists (select 1
  from tmobile_tmp2 t2
where t2.mobile = t1.mobile );-- 创建索引：select count(*) from USER_SIGNONLINE_TIME_tj;-- 查看新新替换的表的创建代码(看是否日期字段包含默认值)
select dbms_metadata.get_ddl('TABLE','USER_SIGNONLINE_TIME_TJ') from dual;
 CREATE TABLE "HLL"."USER_SIGNONLINE_TIME_TJ"
  (    "MOBILE" VARCHAR2(20),
       "SIGN_DATE" DATE DEFAULT SYSDATE,
       "LOGOUT_DATE" DATE DEFAULT SYSDATE,
       "SECONDS" NUMBER(18,0)
  ) PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING
 STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
 PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
 TABLESPACE "HLL"-- OK，默认值存在，创建成功!-- 删除临时表：
drop table tmobile_tmp2 purge;-- drop table USER_SIGNONLINE_TIME_bak purge;