Oracle分区表的一些简单技巧
1.查找表分区的情况
SELECT * FROM USER_TAB_PARTITIONS
WHERE TABLE_NAME = XXXX
可查对应表的具体分区情况
2.分区表清空指定分区
不删分区仅清空分区数据推荐使用
ALTER TABLE TABLE_NAME TRUNCATE PARTITION_NAME
不建议使用
DELETE FROM TABLE_NAME WHERE PARTITION_NAME = XXXX
DELETE FROM
的形式对于数据量大的表来说有可能导致数据库服务卡死,且对水位线无影响
清空数据且删除数据
ALTER TABLE TABLE_NAME DROP PARTITION PARTITION_NAME
3.结合以上两点的一个小的实际应用
目标表为list分区,且存储周期为36个月,每个存储周期为1个分区,分区名为SUM_MONTHYYYYMM,逻辑实现表同步之前的分区清空操作
具体代码如下
DECLARE
V_SQL VARCHAR2(500);
V_SUM_MONTH VARCHAR2(10) := '201910';
V_PART_RES NUMBER :=0;
V_PART_NUM NUMBER :=0;
V_MIN_MONTH VARCHAR2(30);
BEGIN
V_SQL := 'SELECT COUNT(1) FROM USER_TAB_PARTITIONS WHERE TABLE_NAME = ''TABLE_NAME'' AND PARTITION_NAME= ''SUM_MONTH' || V_SUM_MONTH || '''';
EXECUTE IMMEDIATE V_SQL
INTO V_PART_RES;
--DBMS_OUTPUT.PUT_LINE(V_PART_RES);
IF V_PART_RES = 0 THEN --判断是否有对应分区
BEGIN --没有分区则添加对应分区
V_SQL := 'SELECT COUNT(1) FROM USER_TAB_PARTITIONS WHERE TABLE_NAME = ''TABLE_NAME''';
EXECUTE IMMEDIATE V_SQL INTO V_PART_NUM; --查询当前存储周期数
IF V_PART_NUM >= 36 THEN --超过36个周期清除最小周期
BEGIN
V_SQL := 'SELECT MIN(PARTITION_NAME) FROM USER_TAB_PARTITIONS WHERE TABLE_NAME = ''TABLE_NAME''';
EXECUTE IMMEDIATE V_SQL INTO V_MIN_MONTH; --查询当前最小统计周期
EXECUTE IMMEDIATE 'ALTER TABLE TABLE_NAME DROP PARTITION ' ||V_MIN_MONTH; --删除最早的分区
END;
END IF;
V_SQL := 'ALTER TABLE TABLE_NAME ADD PARTITION SUM_MONTH' ||
V_SUM_MONTH || ' VALUES(''' || V_SUM_MONTH || ''')';
EXECUTE IMMEDIATE V_SQL;
END;
ELSE
BEGIN --有分区则清空对应分区
EXECUTE IMMEDIATE 'ALTER TABLE TABLE_NAME TRUNCATE PARTITION SUM_MONTH'||V_SUM_MONTH;
END;
END IF;
END;
需要注意,分区名称格式得固定,且需要同步的分区字段格式不变
如果有其他更好的实现方法欢迎讨论
本文来自博客园,作者:Splus,转载请注明原文链接:https://www.cnblogs.com/s-plus/p/15889930.html