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
        FETCH CUR_ROWID BULK COLLECT INTO V_ROWID LIMIT MOVE_ROWS_LIMIT; -- 一次加载10000条
        EXIT WHEN V_ROWID.COUNT = 0; -- 游标为0时退出
        FORALL I IN V_ROWID.FIRST .. V_ROWID.LAST
            INSERT INTO SCHEMA_A.TABLE_TEST_BAK -- 插入临时表
            SELECT * FROM SCHEMA_A.TABLE_TEST
            WHERE ROWID = V_ROWID(I)
        COMMIT;
    END LOOP;
    CLOSE CUR_ROWID; --关闭游标
END;

6、建立对应索引

CREATE UNIQUE INDEX SCHEMA_A.INDEX_TEST1
ON SCHEMA_A.TABLE_TEST_BAK(COL1)
TABLESPACE SCHEMA_INDEX;

7、两表重命名

 ALTER TABLE RENAME SCHEMA_A.TABLE_TEST TO  SCHEMA_A.TABLE_TEST_BACK;
 ALTER TABLE RENAME SCHEMA_A.TABLE_TEST_BAK TO  SCHEMA_A.TABLE_TEST;

8、验证索引有效性，状态需要为VALID

SELECT OWNER,INDEX_NAME,TABLE_OWNER,TABLE_NAME,STATUS
FROM DBA_INDEXES
WHERE INDEX_NAME = 'INDEX_TEST1';

DBA_INDEXES和ALL_INDEXES均可以查询的对应的索引信息。

9、验证用户权限

SELECT GRANTEE,OWNER,TABLE_NAME,GRANTOR,PRIVILEGE
FROM DBA_TAB_PRIVS
WHERE GRANTEE = 'test_user';

三、执行结果

实际验证结果，从10亿表中迁移出来6000万数据，在半个小时左右完成，效率还可以。