Oracle存储过程实现大批量数据清理

一、背景

假设存在一张数据量达到10亿的表SCHEMA_A.TABLE_TEST，需要清理历史数据，只保留最近一个月的数据（6000万）。

二、通过存储过程实现数据清理

1、创建临时表

create table SCHEMA_A.TABLE_TEST_BAK 
tablespace SCHEMA_A 
as select * from SCHEMA_A.TABLE_TEST 
where 1 = 2;

2、临时表为用户赋权

grant select,update,delete,insert
on SCHEMA_A.TABLE_TEST_BAK to test_user;

3、获取对应ROWID

DELCLARE
    CURSOR CUR_ROWID IS
        SELECT ROWID
        FROM SCHEMA_A.TABLE_TEST
        WHERE LAST_MODIFIED_TIME >= (CURRENT_TIMESTAMP - 30)

4、自定义类型和变量

-- 定义一种类型TYP_ROWID，由ROWID组成，被行数索引
TYPE TYP_ROWID IS TABLE OF ROWID INDEX BY PLS_INTEGER;
-- 定义ROWID变量
V_ROWID   TYP_ROWID;
-- 定义整型变量，每次迁移10000条
MOVE_ROWS_LIMIT PLS_INTEGER := 10000;

5、开始迁移，插入临时表

BEGIN
    OPEN CUR_ROWID --打开游标
    LOOP