背景：
数据库的表数据太多会影响系统整体的性能，而数据在清理之前，需要备份到其他地方（CSV, 其他表）, 以免不时之需，比如审计。
花了2到3个小时写的，太久没写了，测试花了蛮多时间，希望能够给有类似需求的人带来帮助。
这个存储过程核心是根据数据库表结构定义，构建出merge into语句：
1 /** 2 * Common procedure to merge TABLE_X to ARCH_TABLE_X. 3 * The main logic is to build a common merge into statement for tables with different column 4 * You many need change the target table prefix from 'ARCH' to the one you want in this procedure. 5 * Author 烂人 6 * @tbl_name is the source data table 7 * @unique_key is the key to uniquely identify a data row 8 * @days_to_keep days to keep in original table, which means the rest data which plans to delete need merge into archiving table 9 */ 10 create or replace PROCEDURE "PRC_ARCH_TABLE" 11 (tbl_name VARCHAR2, unique_key VARCHAR2, days_to_keep Integer) 12 IS 13 v_columns_1 VARCHAR2 (4000); 14 v_columns_2 VARCHAR2 (4000); 15 v_columns_3 VARCHAR2 (4000); 16 v_columns_4 VARCHAR2 (4000); 17 v_stmt VARCHAR2 (8500); 18 BEGIN 19 --SET days_to_keep := days_to_keep - 1; 20 -- bulk fetch the column of selected table 21 select 22 rtrim(xmlagg(xmlparse(content column_name || ',' wellformed) ORDER BY t.column_name).getclobval(), ','), 23 rtrim(xmlagg(xmlparse(content 'x.'|| column_name || ',' wellformed) ORDER BY t.column_name).getclobval(), ','), 24 rtrim(xmlagg(xmlparse(content 'y.'|| column_name || ',' wellformed) ORDER BY t.column_name).getclobval(), ',') 25 into v_columns_1, v_columns_2, v_columns_3 26 from all_tab_cols t where table_name = tbl_name and hidden_column = 'NO' GROUP BY table_name; 27 28 select 29 rtrim(xmlagg(xmlparse(content 'x.'|| column_name || '=' || 'y.' || column_name || ',' wellformed) ORDER BY t.column_name).getclobval(), ',') 30 into v_columns_4 31 from all_tab_cols t where table_name = tbl_name and hidden_column = 'NO' and column_name <> upper(unique_key) GROUP BY table_name; 32 33 v_stmt := 'MERGE INTO ARCH_' 34 || tbl_name 35 ||' x USING (SELECT ' 36 || v_columns_1 37 || ' FROM ' 38 || tbl_name 39 || ' WHERE CREATE_DATE <= current_date - NUMTODSINTERVAL(' || (days_to_keep-1) || ', ''day'')) y ON (x.' 40 || unique_key 41 || '=y.' 42 || unique_key 43 || ')WHEN MATCHED THEN UPDATE SET ' 44 || v_columns_4 45 || ' WHEN NOT MATCHED THEN INSERT (' 46 || v_columns_2 47 || ') values (' 48 || v_columns_3 49 || ')'; 50 --DBMS_OUTPUT.PUT_LINE(v_stmt); 51 EXECUTE IMMEDIATE v_stmt; 52 DBMS_OUTPUT.PUT_LINE('Successfully execute ' || v_stmt); 53 EXCEPTION 54 WHEN OTHERS THEN 55 DBMS_OUTPUT.PUT_LINE('Failed to execute: ' || v_stmt); 56 DBMS_OUTPUT.PUT_LINE('Error msg: ' || sqlerrm); 57 END;
当然，用于备份的表数据不能无限增长，根据业务需要，可以把过时的数据清理掉，比如审计一般要保留3到5年，平常报文日志表只要保留1个月。
这个是清理旧数据的代码，清除的配置在CONFIGURATION表中，可以自己建一个，关键是要有data_key和value两个字段和下面的Procedure匹配：
1 create or replace PROCEDURE "PRC_ARCH_DATA_CLEAN" 2 (tbl_name VARCHAR2) 3 IS 4 v_stmt VARCHAR2 (4000); 5 keep_month INTEGER; 6 BEGIN 7 SELECT NVL(SUM(TO_NUMBER(value)), 0) INTO keep_month FROM CONFIGURATION WHERE data_key=upper(tbl_name); 8 IF keep_month = '0' THEN 9 DBMS_OUTPUT.PUT_LINE('The archiving table did not configured correctly!'); 10 ELSE 11 v_stmt := 'DELETE FROM ' 12 || tbl_name 13 || ' WHERE CREATE_DATE < current_date - numtoyminterval('||keep_month||',''month'')'; 14 EXECUTE IMMEDIATE v_stmt; 15 DBMS_OUTPUT.PUT_LINE('Successfully execute ' || v_stmt); 16 END IF; 17 EXCEPTION 18 WHEN OTHERS THEN 19 DBMS_OUTPUT.PUT_LINE('Failed to execute: ' || v_stmt); 20 DBMS_OUTPUT.PUT_LINE('Error msg: ' || sqlerrm); 21 END;
