Oracle 大表数据删除或清理方法总结

一、 哪些表是大表

--查詢普通表或分區表數據所在時間範圍

SELECT MIN(TESTTIME),MAX(TESTTIME)  FROM NHFDEV.COLORD37VALUE --查詢普通表

SELECT MIN(TESTTIME),MAX(TESTTIME)  FROM NHFDEV.COLORD37VALUE  PARTITION(P1)  --查詢分區表

分區 表

--查詢分區表狀況及佔用空間大小

SELECT A.OWNER,B.TABLE_NAME,A.PARTITION_NAME,'','','','',A.TABLESPACE_NAME,ROUND(A.BYTES/1024/1024/1024,0),'',B.COMPRESSION

FROM DBA_SEGMENTS A,DBA_TAB_PARTITIONS B

WHERE A.OWNER=B.TABLE_OWNER

AND A.SEGMENT_NAME=B.TABLE_NAME

AND A.PARTITION_NAME=B.PARTITION_NAME

AND A.OWNER like '%DEV'

AND A.BYTES/1024/1024/1024>10

--查詢分區表LOB字段分區狀況及占用空間大小(x>10G以上)

SELECT  A.OWNER,B.TABLE_NAME,B.PARTITION_NAME,B.COLUMN_NAME,'','','',A.TABLESPACE_NAME,'',ROUND(A.BYTES/1024/1024/1024,0) LOB_SIZE,B.COMPRESSION

FROM DBA_SEGMENTS A,DBA_LOB_PARTITIONS B

WHERE A.OWNER LIKE '%DEV'  AND A.OWNER  NOT LIKE '%APDEV'

AND A.BYTES/1024/1024/1024>10

AND A.OWNER=B.TABLE_OWNER AND A.SEGMENT_NAME=B.LOB_NAME

AND A.PARTITION_NAME=B.LOB_PARTITION_NAME

ORDER BY OWNER ,LOB_SIZE DESC;

非分區表

--查詢普通表(即非分區表）佔用空間大小(x>10G以上)

SELECT A.OWNER,A.SEGMENT_NAME,'','','','','',A.TABLESPACE_NAME,ROUND(A.BYTES/1024/1024/1024,0),'',B.COMPRESSION

FROM DBA_SEGMENTS A,DBA_TABLES B

WHERE  A.OWNER=B.OWNER

AND A.SEGMENT_NAME=B.TABLE_NAME

AND A.BYTES/1024/1024/1024>10

AND A.OWNER LIKE '%DEV'

AND A.SEGMENT_NAME NOT LIKE 'SYS%'

AND A.SEGMENT_TYPE='TABLE'

AND B.COMPRESSION!='OLTP'

ORDER BY OWNER,SEGMENT_NAME,ROUND(BYTES/1024/1024/1024,1) DESC

--查詢普通表(即非分區表）含LOB字段的表占用空間大小(x10G)

SELECT A.OWNER,b.table_name,a.SEGMENT_NAME,'','','','',A.TABLESPACE_NAME,'',ROUND(A.BYTES/1024/1024/1024,0),B.COMPRESSION

FROM DBA_SEGMENTS A,DBA_LOBS B

WHERE  A.OWNER=B.OWNER

AND A.SEGMENT_NAME=B.SEGMENT_NAME

AND A.BYTES/1024/1024/1024>10

AND A.OWNER LIKE '%DEV'

AND A.SEGMENT_TYPE='LOBSEGMENT'

ORDER BY a.OWNER,b.table_name,ROUND(BYTES/1024/1024/1024,1) DESC

统计好后，确认各大表是否可清理，需保存多久数据。
二、 清理分类

目前遇到以下几种场景：
1. 可以drop

    备份表、临时表、已无用的表
    时间范围分区表：索引改为local索引后，按分区drop

2. 可以truncate

    部分日志表

   注意对于大表（例如上百G的表）应该分次执行drop或者truncate，

3. 可以rename然后重建空表

    可以暂停写入，不通过程序读取的表
    按业务要求看是否将最近几个月数据插回新表，插回后删除备份表
    或者不插回数据，几个月后删除备份表

4. 只能delete

    参考第三点处理原则

5. 业务接口删除

    业务关联性很强的表，不能简单按时间删除，需要由业务方编写删除程序或者使用标准接口，典型的案例就是ERP里的标准表。

三、 千万级以上大表如何delete
1. 直接删除的问题

    耗时长，可能最终遇到ORA-1555报错
    产生大事务，从库可能出现高延迟，且中断回滚耗时极长
    可能阻塞业务其他DML操作
    undo表空间过度使用，可能影响到其他用户正常操作

分批删除并提交，将大事务化为小事务。另外，删除时注意归档及闪回日志产生量。
2. 按天删除数据

       首先需要在对应时间字段加索引，一天的数据量大概在一两百万的话问题不大，再多可能需要拆得更细些。

    时间字段为时间类型（date,timestamp）

DECLARE
  begin_date date := to_date('2020-03-01','yyyy-mm-dd');
BEGIN
   WHILE begin_date < to_date('2020-05-01','yyyy-mm-dd') loop
      DELETE FROM MYTAB WHERE CREATE_DT >= begin_date and CREATE_DT < begin_date+1;
      commit;
      begin_date := begin_date+1;
   end loop;
END;
/

    时间字段为字符串类型

DECLARE
  begin_date date := to_date('20180901','yyyymmdd');
BEGIN   
   WHILE begin_date < to_date('20181001','yyyymmdd') loop
      DELETE FROM MYTAB WHERE CREATE_DT = to_char(begin_date,'yyyymmdd');
      commit;
      begin_date := begin_date+1;
   end loop;
END;
/

3. 游标对比rowid批量删除

下面两个方法来自：Oracle库Delete删除千万以上普通堆表数据的方法 - AlfredZhao - 博客园

有部分调整，避免全表取数及循环判断时间，原代码请参考原文

删除2020年3-4月的数据，每1万行提交一次

-- del_cur 游标名
-- tmp 要删除数据的表名
-- create_dt 时间字段名

declare
     cursor del_cur is select a.rowid row_id from tmp where create_dt >= to_date('2020-03-01','yyyy-mm-dd') and create_dt < to_date('2020-05-01','yyyy-mm-dd') order by a.rowid;
begin
     for v_cusor in del_cur loop
          delete from tmp where rowid = v_cusor.row_id;
          if mod(del_cur%rowcount,10000)=0 then
               commit;
          end if;
     end loop;
     commit;
end;
/

3. 直接按rowid删除

-- del_cur 游标名
-- tmp 要删除数据的表名

declare  
maxrows number default 10000;
delete_cnt number default 0;
begin
select count(1)/maxrows into delete_cnt from tmp where create_dt >= to_date('2020-03-01','yyyy-mm-dd') and create_dt < to_date('2020-05-01','yyyy-mm-dd');
for i in 1..TRUNC(delete_cnt)+1
loop
delete tmp where create_dt >= to_date('2020-03-01','yyyy-mm-dd') and create_dt < to_date('2020-05-01','yyyy-mm-dd') and rownum <= maxrows;
commit;
end loop;
end;
/

   4. 如何看delete释放了多少空间

-- 使用方法
-- set serveroutput on
-- exec show_space('TABLE_NAME','OWNER');
-- 各字段含义参考：https://docs.oracle.com/database/121/ARPLS/d_space.htm#ARPLS68113

create or replace procedure show_space
( p_segname_1 in varchar2,
p_owner_1 in varchar2 default user,
p_type_1 in varchar2 default 'TABLE',
p_space in varchar2 default 'AUTO',
p_analyzed in varchar2 default 'Y'
)
as
p_segname varchar2(100);
p_type varchar2(10);
p_owner varchar2(30);
l_unformatted_blocks number;
l_unformatted_bytes number;
l_fs1_blocks number;
l_fs1_bytes number;
l_fs2_blocks number;
l_fs2_bytes number;
l_fs3_blocks number;
l_fs3_bytes number;
l_fs4_blocks number;
l_fs4_bytes number;
l_full_blocks number;
l_full_bytes number;
l_free_blks number;
l_total_blocks number;
l_total_bytes number;
l_unused_blocks number;
l_unused_bytes number;
l_LastUsedExtFileId number;
l_LastUsedExtBlockId number;
l_LAST_USED_BLOCK number;

procedure p( p_label in varchar2, p_num in number )
is
begin
dbms_output.put_line( rpad(p_label,40,'.') || p_num );
end;

begin
p_segname := upper(p_segname_1); -- rainy changed
p_owner := upper(p_owner_1);
p_type := p_type_1;

if (p_type_1 = 'i' or p_type_1 = 'I') then --rainy changed
p_type := 'INDEX';
end if;

if (p_type_1 = 't' or p_type_1 = 'T') then --rainy changed
p_type := 'TABLE';
end if;
 
if (p_type_1 = 'c' or p_type_1 = 'C') then --rainy changed
p_type := 'CLUSTER';
end if;

dbms_space.unused_space
( segment_owner => p_owner,
segment_name => p_segname,
segment_type => p_type,
total_blocks => l_total_blocks,
total_bytes => l_total_bytes,
unused_blocks => l_unused_blocks,
unused_bytes => l_unused_bytes,
LAST_USED_EXTENT_FILE_ID => l_LastUsedExtFileId,
LAST_USED_EXTENT_BLOCK_ID => l_LastUsedExtBlockId,
LAST_USED_BLOCK => l_LAST_USED_BLOCK
);

if p_space = 'MANUAL' or (p_space <> 'auto' and p_space <> 'AUTO') then
dbms_space.free_blocks
( segment_owner => p_owner,
segment_name => p_segname,
segment_type => p_type,
freelist_group_id => 0,
free_blks => l_free_blks );
p( 'Free Blocks', l_free_blks );
end if;

/*IF the segment is analyzed */
if p_analyzed = 'Y' then
dbms_space.space_usage(segment_owner => p_owner ,
segment_name => p_segname ,
segment_type => p_type ,
unformatted_blocks => l_unformatted_blocks ,
unformatted_bytes => l_unformatted_bytes,
fs1_blocks => l_fs1_blocks,
fs1_bytes => l_fs1_bytes ,
fs2_blocks => l_fs2_blocks,
fs2_bytes => l_fs2_bytes,
fs3_blocks => l_fs3_blocks ,
fs3_bytes => l_fs3_bytes,
fs4_blocks => l_fs4_blocks,
fs4_bytes => l_fs4_bytes,
full_blocks => l_full_blocks,
full_bytes => l_full_bytes);
-- dbms_output.put_line(rpad(' ',50,'*'));
-- dbms_output.put_line('The segment is analyzed');
-- p( 'Unformatted Blocks', l_unformatted_blocks );
-- p( 'Unformatted Bytes', l_unformatted_bytes );
p( 'Unused Blocks', l_unused_blocks );
p( 'Unused Bytes', l_unused_bytes );
p( '0% -- 25% free space blocks', l_fs1_blocks);
p( '0% -- 25% free space bytes', l_fs1_bytes);
p( '25% -- 50% free space blocks', l_fs2_blocks);
p( '25% -- 50% free space bytes', l_fs2_bytes);
p( '50% -- 75% free space blocks', l_fs3_blocks);
p( '50% -- 75% free space bytes', l_fs3_bytes);
p( '75% -- 100% free space blocks', l_fs4_blocks);
p( '75% -- 100% free space bytes', l_fs4_bytes);
p( 'Full Blocks', l_full_blocks);
p( 'Full bytes', l_full_bytes);
p( 'Total Blocks', l_total_blocks );
p( 'Total Bytes', l_total_bytes );
-- p( 'Last Used Ext FileId', l_LastUsedExtFileId );
-- p( 'Last Used Ext BlockId', l_LastUsedExtBlockId );
-- p( 'Last Used Block', l_LAST_USED_BLOCK );
end if;
end show_space;
/

四、 分区化改造 （对大表强烈推荐）

       对于需要定期清理的表，建议在线重定义为分区表，提高删除效率。