在資料倉儲環境中，分割槽表會非常多，尤其是範圍分割槽的表。為了確保業務不受影響，需要確保分割槽表的分割槽的邊界足夠大，以免匯入資料時會出錯。

為了避免出錯，很多人設定了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;

提交一個定時任務，讓它定期執行就可以實現分割槽邊界溢位的自動化監控了。

如何監控分割槽表邊界溢位

相關文章