テーブルの各カラムの最大値、最小値を確認するPL/SQL。
データ量増大で桁あふれが近々発生しないか監視するためなどに使用。
--★結果の出力先を指定
SPOOL P:\HOGE.TXT;
SET linesize 2000
SET SERVEROUTPUT ON;
DECLARE
--カーソル定義
CURSOR CURCOLUMNS IS
SELECT
USER_TAB_COLUMNS.TABLE_NAME
,USER_TAB_COLUMNS.COLUMN_NAME
,USER_TAB_COLUMNS.DATA_TYPE
,USER_TAB_COLUMNS.DATA_LENGTH
,USER_TAB_COLUMNS.DATA_PRECISION
,USER_TAB_COL_STATISTICS.LOW_VALUE
,USER_TAB_COL_STATISTICS.HIGH_VALUE
,USER_TAB_COL_STATISTICS.SAMPLE_SIZE
FROM USER_TAB_COLUMNS
LEFT JOIN USER_TAB_COL_STATISTICS
ON USER_TAB_COLUMNS.TABLE_NAME = USER_TAB_COL_STATISTICS.TABLE_NAME
AND USER_TAB_COLUMNS.COLUMN_NAME = USER_TAB_COL_STATISTICS.COLUMN_NAME
WHERE USER_TAB_COLUMNS.TABLE_NAME LIKE '%%' --★テーブル名でのフィルタ
ORDER BY TABLE_NAME,COLUMN_ID;
--変数の定義
VMIN_VAL VARCHAR2(32767) := NULL;
VMAX_VAL VARCHAR2(32767) := NULL;
--ローカルファンクション定義
FUNCTION FUNC_CONVERT_RAW_VALUE(pVALUE IN RAW, pDATATYPE IN VARCHAR2)
RETURN VARCHAR2
IS
VDATATYPE VARCHAR2(2000) := UPPER(pDATATYPE);
VDATE DATE;
VBINARYFLOAT BINARY_FLOAT;
VBINARYDOUBLE BINARY_DOUBLE;
VNUMBER NUMBER;
VROWID ROWID;
VRET VARCHAR2(32767) := NULL;
BEGIN
IF (pVALUE IS NOT NULL) THEN
CASE VDATATYPE
WHEN 'DATE' THEN
DBMS_STATS.CONVERT_RAW_VALUE(pVALUE, VDATE);
VRET := VDATE;
WHEN 'BINARY_FLOAT' THEN
DBMS_STATS.CONVERT_RAW_VALUE(pVALUE, VBINARYFLOAT);
VRET := TO_CHAR(VBINARYFLOAT);
WHEN 'BINARY_DOUBLE' THEN
DBMS_STATS.CONVERT_RAW_VALUE(pVALUE, VBINARYDOUBLE);
VRET := TO_CHAR(VBINARYDOUBLE);
WHEN 'NUMBER' THEN
DBMS_STATS.CONVERT_RAW_VALUE(pVALUE, VNUMBER);
VRET := TO_CHAR(VNUMBER);
WHEN 'CHAR' THEN
DBMS_STATS.CONVERT_RAW_VALUE(pVALUE, VRET);
WHEN 'VARCHAR2' THEN
DBMS_STATS.CONVERT_RAW_VALUE(pVALUE, VRET);
WHEN 'NCHAR' THEN
VRET := TO_CHAR(UTL_RAW.CAST_TO_NVARCHAR2(pVALUE));
WHEN 'NVARCHAR2' THEN
VRET := TO_CHAR(UTL_RAW.CAST_TO_NVARCHAR2(pVALUE));
WHEN 'ROWID' THEN
DBMS_STATS.CONVERT_RAW_VALUE_ROWID(pVALUE, VROWID);
VRET := TO_CHAR(VROWID);
ELSE
VRET := '['|| TO_CHAR(pVALUE) ||']';
END CASE;
END IF;
RETURN VRET;
EXCEPTION
WHEN OTHERS THEN
VRET := 'ERR!['|| TO_CHAR(pVALUE) ||']';
RETURN VRET;
END;
--**********メイン処理***********************************************************************
BEGIN
--タイトル
DBMS_OUTPUT.PUT_LINE('-----------------------------------------------------------------');
DBMS_OUTPUT.PUT_LINE( 'TABLE_NAME' || CHR(9)
|| 'COLUMN_NAME' || CHR(9)
|| 'DATA_TYPE' || CHR(9)
|| 'DATA_LENGTH' || CHR(9)
|| 'DATA_PRECISION' || CHR(9)
|| 'LOW_VALUE' || CHR(9)
|| 'HIGH_VALUE' || CHR(9)
|| 'SAMPLE_SIZE' || CHR(9)
|| 'MIN_VALUE' || CHR(9)
|| 'MAX_VALUE' || CHR(9)
);
--USER_TAB_COLUMNSのループ
FOR C_DEPT_REC IN CURCOLUMNS LOOP
--動的SQLでMIN/MAXを取得☆
EXECUTE IMMEDIATE
'SELECT MIN(' || C_DEPT_REC.COLUMN_NAME || ') FROM ' || C_DEPT_REC.TABLE_NAME INTO VMIN_VAL;
EXECUTE IMMEDIATE
'SELECT MAX(' || C_DEPT_REC.COLUMN_NAME || ') FROM ' || C_DEPT_REC.TABLE_NAME INTO VMAX_VAL;
---結果を出力
DBMS_OUTPUT.PUT_LINE(C_DEPT_REC.TABLE_NAME || CHR(9)
|| C_DEPT_REC.COLUMN_NAME || CHR(9)
|| C_DEPT_REC.DATA_TYPE || CHR(9)
|| C_DEPT_REC.DATA_LENGTH || CHR(9)
|| C_DEPT_REC.DATA_PRECISION || CHR(9)
|| FUNC_CONVERT_RAW_VALUE(C_DEPT_REC.LOW_VALUE, C_DEPT_REC.DATA_TYPE) || CHR(9)
|| FUNC_CONVERT_RAW_VALUE(C_DEPT_REC.HIGH_VALUE, C_DEPT_REC.DATA_TYPE) || CHR(9)
|| C_DEPT_REC.SAMPLE_SIZE || CHR(9)
|| VMIN_VAL || CHR(9)
|| VMAX_VAL || CHR(9)
);
END LOOP;
END;
/
SPOOL OFF;統計情報を格納しているUSER_TAB_COL_STATISTICSにも値を保持しているが
サンプルサイズが小さい場合は精度が低くなってしまうので動的SQLでSELECTを発行する機能を追加した。
⇒ただし実行時間がかかるのでそこまでの精度が不要ならコメントしてもよい
(レスポンスは無視して実装したので改善の余地あり)
動作確認環境：Oracle 10g
