oracle - 用于维护 Oracle 表子分区的自动化脚本
问题描述
我是 PLSQL 的新手。
有人可以为我提供自动化脚本来维护(删除和创建)Oracle 表中的子分区。我知道维护表分区的脚本,但无法为子分区构建框架。
一些细节:
Range-Range partition
Subpartition: On date column (Monthly)
Retention : 180days data
下面是 Oracle 表的定义方式:
CREATE TABLE PART_TABLE
(
"REQUEST_ITEM_ID" NUMBER NOT NULL ENABLE,
"X_CLOB" CLOB,
"ENQUEUED_COUNT" NUMBER,
"UPDATE_LAST_META" NUMBER(1,0),
"CFI_TYPE_ID" NUMBER,
) SEGMENT CREATION IMMEDIATE
LOB ("X_CLOB") STORE AS BASICFILE (TABLESPACE "DATA_TS" ENABLE
PARTITION BY RANGE (PRIORITY)
SUBPARTITION BY RANGE (CREATED)
(PARTITION PART_01 VALUES less THAN(2)
(
SUBPARTITION PART_01_FEB_2020 VALUES LESS THAN (TO_DATE('2020-03-01 00:00:00', 'YYYY-MM-DD HH24:MI:SS')),
SUBPARTITION PART_01_MAR_2020 VALUES LESS THAN (TO_DATE('2020-04-01 00:00:00', 'YYYY-MM-DD HH24:MI:SS')),
SUBPARTITION PART_01_MAX VALUES LESS THAN (MAXVALUE)
),
PARTITION PART_02 VALUES less THAN(3)
(
SUBPARTITION PART_02_FEB_2020 VALUES LESS THAN (TO_DATE('2020-03-01 00:00:00', 'YYYY-MM-DD HH24:MI:SS')),
SUBPARTITION PART_02_MAR_2020 VALUES LESS THAN (TO_DATE('2020-04-01 00:00:00', 'YYYY-MM-DD HH24:MI:SS')),
SUBPARTITION PART_02_MAX VALUES LESS THAN (MAXVALUE)
)
PARTITION PART_03 VALUES less THAN(4)
(
SUBPARTITION PART_03_FEB_2020 VALUES LESS THAN (TO_DATE('2020-03-01 00:00:00', 'YYYY-MM-DD HH24:MI:SS')),
SUBPARTITION PART_03_MAR_2020 VALUES LESS THAN (TO_DATE('2020-04-01 00:00:00', 'YYYY-MM-DD HH24:MI:SS')),
SUBPARTITION PART_03_MAX VALUES LESS THAN (MAXVALUE)
));
解决方案
我会说,你的表有错误的分区模式。最好是这样:
CREATE TABLE ... (
your columns
)
PARTITION BY RANGE (CREATED) INTERVAL (INTERVAL '1' MONTH)
SUBPARTITION BY RANGE (PRIORITY)
SUBPARTITION TEMPLATE
(
SUBPARTITION PART_01 VALUES LESS THAN(2),
SUBPARTITION PART_02 VALUES LESS THAN(3),
SUBPARTITION PART_03 VALUES LESS THAN(4),
SUBPARTITION PART_MAX VALUES LESS THAN (MAXVALUE)
)
(
PARTITION P_BEFORE_2019 VALUES LESS THAN (TIMESTAMP '2019-01-01 00:00:00')
);
然后Oracle会自动创建新的分区,删除也会更容易。
无论如何,维护程序可能是这样的(未经测试):
DECLARE
ts TIMESTAMP;
sqlstr VARCHAR2(1000);
CURSOR TabSubPartitions IS
SELECT TABLE_NAME, PARTITION_NAME, SUBPARTITION_NAME, HIGH_VALUE
FROM USER_TAB_SUBPARTITIONS
WHERE TABLE_NAME = 'PART_TABLE'
ORDER BY PARTITION_NAME, SUBPARTITION_NAME;
BEGIN
FOR aSubPart IN TabSubPartitions LOOP
IF aSubPart.HIGH_VALUE <> 'MAXVALUE' THEN
EXECUTE IMMEDIATE 'BEGIN :ret := '||aSubPart.HIGH_VALUE||'; END;' USING OUT ts;
IF ts < SYSTIMESTAMP - INTERVAL '180' DAY THEN
-- As far as I remember you cannot drop a subpartition, thus you can only truncate it
sqlstr := 'ALTER TABLE '||aSubPart.TABLE_NAME||' TRUNCATE SUBPARTITION '||aSubPart.SUBPARTITION_NAME||' UPDATE GLOBAL INDEXES';
EXECUTE IMMEDIATE sqlstr;
END IF;
ELSE
IF TRUNC(LAST_DAY(SYSDATE)) = TRUNC(SYSDATE) THEN
-- If last day of current months then create new monthly partition
-- Perhaps further checks/exception handler are needed to check whether subpartition already exist.
sqlstr := 'ALTER TABLE '||aSubPart.TABLE_NAME||' SPLIT SUBPARTITION '||aSubPart.SUBPARTITION_NAME
||' AT (TIMESTAMP '''||TO_CHAR(SYSDATE+1, 'YYYY-MM-DD HH24:MI:SS')||''') INTO ('
||' SUBPARTITION '||aSubPart.PARTITION_NAME||'_'||TO_CHAR(SYSDATE+1, 'MON_YYYY')||'),'
||' SUBPARTITION '||aSubPart.PARTITION_NAME||'_MAX) UPDATE GLOBAL INDEXES';
EXECUTE IMMEDIATE sqlstr;
END IF;
END IF;
END LOOP;
END;
使用顶部给出的“正确”表设计,维护会更简单:
DECLARE
CANNOT_DROP_LAST_PARTITION EXCEPTION;
PRAGMA EXCEPTION_INIT(CANNOT_DROP_LAST_PARTITION, -14758);
ts TIMESTAMP;
sqlstr VARCHAR2(1000);
CURSOR TabPartitions IS
SELECT TABLE_NAME, PARTITION_NAME, HIGH_VALUE
FROM USER_TAB_PARTITIONS
WHERE TABLE_NAME = 'PART_TABLE'
ORDER BY PARTITION_NAME;
BEGIN
FOR aPart IN TabPartitions LOOP
BEGIN
EXECUTE IMMEDIATE 'BEGIN :ret := '||aPart.HIGH_VALUE||'; END;' USING OUT ts;
IF ts < SYSTIMESTAMP - INTERVAL '180' DAY THEN
sqlstr := 'ALTER TABLE '||aPart.TABLE_NAME||' DROP PARTITION '||aSubPart.PARTITION_NAME||' UPDATE GLOBAL INDEXES';
EXECUTE IMMEDIATE sqlstr;
END IF;
EXCEPTION
WHEN CANNOT_DROP_LAST_PARTITION THEN
EXECUTE IMMEDIATE 'ALTER TABLE '||aPart.TABLE_NAME||' SET INTERVAL ()';
EXECUTE IMMEDIATE 'ALTER TABLE '||aPart.TABLE_NAME||' DROP PARTITION ('||aPart.PARTITION_NAME||') UPDATE INDEXES';
EXECUTE IMMEDIATE 'ALTER TABLE '||aPart.TABLE_NAME||' SET INTERVAL (INTERVAL ''1'' MONTH)';
END;
END LOOP;
END;
你可以跳过异常处理程序WHEN CANNOT_DROP_LAST_PARTITION THEN ...
,这个异常只出现一次,当你得到然后你可以手动运行这三个命令。之后,此异常将不再引发。
推荐阅读
- grpc - gRPC 是否重新发送消息
- go - golang http中动态添加路由
- javascript - 如何在 JavaScript 中从 1.0 和 0.1 中删除 0?
- java - Spring:实际请求参数不满足参数条件“loanTitle”
- python - Django 信号重复,具有唯一的调度 ID
- cakephp-3.0 - 升级 php 版本后,所有错误现在都是 cake php 中的“发生内部服务器错误”
- html - 无法从我的 Centos 7 apache 实例访问 CSS 或 JS 文件
- c++ - How to use forward declaration with boost::msm to avoid circular dependency?
- primefaces - CKEditor 外部图片路径
- c# - 在第二个选项卡中没有干预 Unloaded 的两个 Loaded 事件,这是一个错误吗?