ORACLE 大表使用 rowid 切片备份到历史表

概述与要求

项目需求将几个5000万行大表先备份到历史表，再清理1个月前的数据；不停业务；

思路

先将大表通用存储过程rowid切片插入到历史表，再使用存储过程rowid切片清理1个月前的数据。

备份与清理表的信息

表名	数据量	清理策略建议
test	5000W	保留1个月数据，历史数据历史数据迁移至历史表。

方案

操作前准备

1.表空间容量检查。
2.通知数据中心注意数据库归档空间。
3.提前准备备份与清理SQL到生产环境。

切分字段

根据保留策略清理表记录按表中时间字段进行切分，需业务方确认。

备份步骤

备份大表分为2个步骤：
第1步创建表结构（不用创建索引），第2步使用存储过程将旧表的数据按区为单位插入并提交到新表。

清理步骤

清理大表分为2个步骤：
第1步确认数据已完成备份，第2步使用存储过程将各旧表保留策略之前的数据按区为单位删除并提交，注意保留策略使用的时间字段需要创建索引，不然全表扫删除会很慢。

脚本

备份存储过程如下：

declare 
CURSOR CUR_ROWID IS SELECT DBMS_ROWID.ROWID_CREATE(1,
                                   B.DATA_OBJECT_ID,
                                   A.RELATIVE_FNO,
                                   A.BLOCK_ID,
                                   0) ROWID1,
           DBMS_ROWID.ROWID_CREATE(1,
                                   B.DATA_OBJECT_ID,
                                   A.RELATIVE_FNO,
                                   A.BLOCK_ID + BLOCKS - 1,
                                   999) ROWID2
      FROM DBA_EXTENTS A, DBA_OBJECTS B
     WHERE A.SEGMENT_NAME = B.OBJECT_NAME
       AND A.OWNER = B.OWNER
       AND B.OBJECT_NAME = 'TEST'
       AND B.OWNER = 'SCOTT';
BEGIN
  FOR CUR IN CUR_ROWID LOOP
     insert into test_bak select * from test where rowid  between CUR.ROWID1 and CUR.ROWID2;
     COMMIT;
  END LOOP;
END;
/   

清理存储过程如下：

declare 
CURSOR CUR_ROWID IS SELECT DBMS_ROWID.ROWID_CREATE(1,
                                   B.DATA_OBJECT_ID,
                                   A.RELATIVE_FNO,
                                   A.BLOCK_ID,
                                   0) ROWID1,
           DBMS_ROWID.ROWID_CREATE(1,
                                   B.DATA_OBJECT_ID,
                                   A.RELATIVE_FNO,
                                   A.BLOCK_ID + BLOCKS - 1,
                                   999) ROWID2
      FROM DBA_EXTENTS A, DBA_OBJECTS B
     WHERE A.SEGMENT_NAME = B.OBJECT_NAME
       AND A.OWNER = B.OWNER
       AND B.OBJECT_NAME = 'TEST'
       AND B.OWNER = 'SCOTT';
BEGIN
  FOR CUR IN CUR_ROWID LOOP
     Delete from test where 日期列<策略日期 and  rowid  between CUR.ROWID1 and CUR.ROWID2;
     COMMIT;
  END LOOP;
END;
/   

创建历史表如下：

Create table test_bak20210903 as select * from test where rownum=0;