随笔 - 31  文章 - 2 评论 - 26 阅读 - 46585
< 2025年2月 >
26 27 28 29 30 31 1
2 3 4 5 6 7 8
9 10 11 12 13 14 15
16 17 18 19 20 21 22
23 24 25 26 27 28 1
2 3 4 5 6 7 8

背景:

数据库的表数据太多会影响系统整体的性能,而数据在清理之前,需要备份到其他地方(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;

 

posted @ 2023-11-29 11:52 烂人 阅读(22) 评论(0) 推荐(1) 编辑
摘要: 背景:公司接到一个亿级的项目,软件大概占到1/4的比例,整个项目包含了硬件和软件团队。软件团队是要实是一个软件产品,让其控制各种硬件设备做自动化运作,并打通上下游系统的数据。软件同时统计分析(包括机器学习和AI) 整个项目设备的运作和任务执行情况,服务于后续运营优化。 项目成员结构:大项目经理,对这 阅读全文
posted @ 2023-09-26 14:49 烂人 阅读(6280) 评论(16) 推荐(23) 编辑
摘要: 好久没法技术博客了,最近接触开发多了许多,又有新总结发给遇到问题的老铁们。 大家可能都知道使用JDK中自带的wsimport命令把WSDL文件或URL直接转换成JAVA客户端代码,以实现快速开发。 如下: wsimport -keep "mywsdlfile.xml" -p com.zeng.tes 阅读全文
posted @ 2021-05-02 07:57 烂人 阅读(585) 评论(0) 推荐(0) 编辑
摘要: 1. 先安装pystan依赖 按照https://pystan.readthedocs.io/en/latest/windows.html说明,请使用如下命令 conda install libpython m2w64-toolchain -c msys2 conda install matplot 阅读全文
posted @ 2019-03-05 16:18 烂人 阅读(2086) 评论(0) 推荐(0) 编辑
摘要: Python 列表下标操作 引用网址: https://www.jianshu.com/p/a98e935e4d46 阅读全文
posted @ 2019-01-18 13:21 烂人 阅读(3131) 评论(0) 推荐(0) 编辑
摘要: Weka 阅读全文
posted @ 2018-08-10 14:38 烂人 阅读(351) 评论(0) 推荐(0) 编辑
摘要: 金山注入浏览器默认开启上网导航 www.uu114.cn 今天突然发现我的电脑所有浏览器打开后,都会默认打开一个www.uu114.cn网站,chrome、firefox和IE都中招了。经过排查,发现快捷方式没有被注入网址参数。 无奈,先把hosts文件改了,绝对不能让黑商得逞!! 不过用电脑心里还 阅读全文
posted @ 2018-04-07 22:41 烂人 阅读(4452) 评论(2) 推荐(1) 编辑
摘要: 1. 用浏览器(我用Chrome)登录web微信(wx.qq.com) 2. 这个时候如果有人发视频,可以点开播放。用F12打开chrome的调试平台,查看视频源的URL(绿色框的src内容) 3. 用这个URL重新打开新的网页,可以看到浏览器直接用原生的播放器播放,这时用浏览器原生播放器下载下来的 阅读全文
posted @ 2018-02-06 17:24 烂人 阅读(280) 评论(0) 推荐(0) 编辑
摘要: 磁盘上有旧系统留下的目录比如old.windows、program files、users(中文目录是用户,删除命令里还是要用user才有效),因为目录的特殊设置,导致无法直接删除,需要修改属性和权限,下面是完整的命令,必须在运行于管理员权限的命令行窗口实现。本人是在F盘,所以下面命令使用F盘开头, 阅读全文
posted @ 2017-11-18 11:31 烂人 阅读(1520) 评论(0) 推荐(0) 编辑
摘要: 如果你的硬盘使用BitLocker加密了,但是有时候需要高效率的硬盘做某些事情,可以暂时挂起加密,命令行如下方便做个bat。 挂起: manage-bde -protectors -disable C: 恢复: manage-bde -protectors -enable C: 阅读全文
posted @ 2016-12-28 13:48 烂人 阅读(3697) 评论(0) 推荐(0) 编辑
点击右上角即可分享
微信分享提示