如何监控分区表边界溢出

在数据仓库环境中，分区表会非常多，尤其是范围分区的表。为了确保业务不受影响，需要确保分区表的分区的边界足够大，以免导入数据时会出错。

为了避免出错，很多人设置了maxvalue或者提早分配很多个分区，这些都是不好的习惯。

设置MAXVALUE可能会导致两个问题：
1) 产生垃圾数据
2) 因为疏忽导致MAXVALUE的分区变得巨大，给后续的数据管理带来麻烦

提早分配很多个分区也会导致一些问题：
1) 也可能会产生垃圾数据
2) 不是按需分配可能会导致后续的一系列问题，如因为数据量变化而修改分区方式等

更好的做法是只提前分配少量的分区，并密切监控分区的扩展情况。那么，如何监控分区是否会溢出边界呢？我的做法是写一段代码实时监控分区的边界及当前时间的差异，如果边界接近溢出，则告警。
这里比较麻烦的是处理high_value，它是一个long类型的字段，下面是我的做法。
[@more@] --适用场景：按时间作分区的表。分区字段数据类型可以使DATE，也可以使VARCHAR2。如果分区字段是VARCHAR2，则键值的格式为YYYY-MM-DD。
在我的数据仓库环境中，有大量的这种分区表存在，且分区表都是严格按照标准的设置的，所以可以用以下代码监控。

-- CREATE TEMPORATY TABLE
CREATE GLOBAL TEMPORARY TABLE TEMP_PARTITION_MONITOR
(OWNER VARCHAR2(32),TABLE_NAME VARCHAR2(32),PARTITION_NAME VARCHAR2(32),DATA_TYPE VARCHAR2(20),HIGH_VALUE VARCHAR2(128))
ON COMMIT PRESERVE ROWS;


CREATE OR REPLACE PROCEDURE P_MONITOR_PARTITION(P_PRE_DAYS NUMBER DEFAULT 7) IS

L_CNT INT;
L_BEHIND_DAYS INT := 90;
BEGIN

/*
P_PRE_DAYS: 设定提前多少天预警
*/

FOR C IN (SELECT A.*, K.OWNER, K.NAME, K.COLUMN_NAME, TC.DATA_TYPE
FROM (SELECT TABLE_NAME, PARTITION_NAME, PARTITION_POSITION, HIGH_VALUE
FROM (SELECT /*+ rule */
T1.TABLE_NAME, T1.PARTITION_NAME, PARTITION_POSITION, HIGH_VALUE, ROW_NUMBER() OVER(PARTITION BY T1.TABLE_OWNER, T1.TABLE_NAME ORDER BY PARTITION_POSITION DESC) RN
FROM DBA_TAB_PARTITIONS T1, DBA_PART_TABLES T2
WHERE T1.TABLE_OWNER = 'OWNER' AND
T1.TABLE_OWNER = T2.OWNER AND
T1.TABLE_NAME = T2.TABLE_NAME AND
T2.PARTITIONING_TYPE = 'RANGE')
WHERE RN = 1) A, DBA_PART_KEY_COLUMNS K, DBA_TAB_COLUMNS TC
WHERE K.OWNER = 'OWNER' AND K.NAME = A.TABLE_NAME AND
K.COLUMN_POSITION = 1 AND K.OBJECT_TYPE = 'TABLE' AND
K.OWNER = TC.OWNER AND K.NAME = TC.TABLE_NAME AND
K.COLUMN_NAME = TC.COLUMN_NAME AND
TC.DATA_TYPE NOT IN ('NUMBER')) LOOP
--我关心的信息比较多，所以SQL会比较长

INSERT INTO TEMP_PARTITION_MONITOR
VALUES
(C.OWNER, C.TABLE_NAME, C.PARTITION_NAME, C.DATA_TYPE, REPLACE(REPLACE(C.HIGH_VALUE, '''', ''), ' ', ''));
END LOOP;

SELECT COUNT(1)
INTO L_CNT
FROM TEMP_PARTITION_MONITOR T
WHERE (DATA_TYPE IN ('DATE') OR DATA_TYPE LIKE '%CHAR%') AND
HIGH_VALUE <> 'MAXVALUE' AND CASE WHEN
DATA_TYPE LIKE '%CHAR%' THEN HIGH_VALUE ELSE SUBSTR(HIGH_VALUE, INSTR(HIGH_VALUE, '(') + 1, INSTR(HIGH_VALUE, ',') - INSTR(HIGH_VALUE, '(') - 9) END > TO_CHAR(SYSDATE - L_BEHIND_DAYS, 'YYYY-MM-DD') AND CASE WHEN DATA_TYPE LIKE '%CHAR%' THEN HIGH_VALUE ELSE SUBSTR(HIGH_VALUE, INSTR(HIGH_VALUE, '(') + 1, INSTR(HIGH_VALUE, ',') - INSTR(HIGH_VALUE, '(') - 9) END < TO_CHAR(SYSDATE + P_PRE_DAYS, 'YYYY-MM-DD');

IF L_CNT > 0 THEN
DBMS_OUTPUT.PUT_LINE(L_CNT || ' tables will run out of range in ' ||
P_PRE_DAYS || ' days');
--可以在这里添加发送邮件或者短信的代码，自动告警
END IF;

END P_MONITOR_PARTITION;


提交一个定时任务，让它定期运行就可以实现分区边界溢出的自动化监控了。

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/231499/viewspace-1040594/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/231499/viewspace-1040594/