PL/SQL によるテーブル、パーティション、サブパーティション単位の件数取得

　こんにちはミンです。
　本日も現場ネタです。
　oracleデータベースに全てのテーブル別、パーティション別、さらにサブパーティション別のデータ件数を知りたいという要件があります。
　以前PL/SQLを勉強したことがありますが、長い間使わず、忘れたところで、今回のタスクのおかげで、復習して、下記の便利ツールを作成しました。
　ローカル環境でOracle Database Enterprise Editionの準備ができていないため、テーブル単位まで集計させていただきます。

　早速、始めましょう。

　１、ディレクトリ・オブジェクト作成

CREATE OR REPLACE DIRECTORY WORKTMPDIR AS '/home/oracle/work_tmp';
※パスは任意、シングルクォーテーション
GRANT read on DIRECTORY WORKTMPDIR to sys;
GRANT write on DIRECTORY WORKTMPDIR to sys;

※DBユーザーの使用は規則に従ってください。テストのため、sysを使用しています。

　２、ファンクション STRTOKEN作成

文字列切り出し操作があります。
ソースは下記のサイトを参考にしています。

ファンクション STRTOKEN

　３、件数集計PL/SQL作成

下記の内容をコピペ、count_targets_row_nums.sqlとして作成します。

SET SERVEROUT ON

DECLARE
    in_handle           utl_file.file_type;
    out_handle          utl_file.file_type;
    in_buf              VARCHAR2(32767);
    out_buf             VARCHAR2(32767);
    out_table_name      VARCHAR2(100);
    table_name          VARCHAR2(100);
    parttition_name     VARCHAR2(100);
    subparttition_name  VARCHAR2(100);
    table_count         VARCHAR2(20);
    sql_count           VARCHAR2(1000);
BEGIN
    dbms_output.put_line('start');
    -- 読み込みモードでファイルオープン
    in_handle := utl_file.fopen('WORKTMPDIR', '&1', 'r');
    -- 書き込みモードでファイルオープン
    out_handle := utl_file.fopen('WORKTMPDIR', '&2', 'w');
    LOOP
        BEGIN
            -- ファイルを1レコード読み込み
            utl_file.get_line(in_handle, in_buf);
            table_name := strtoken(in_buf, ':', 1);
            parttition_name := strtoken(in_buf, ':', 2);
            subparttition_name := strtoken(in_buf, ':', 3);
            IF subparttition_name IS NOT NULL THEN
                sql_count := 'SELECT COUNT(*) FROM '
                             || table_name
                             || ' SUBPARTITION('
                             || subparttition_name
                             || ')';
            ELSIF parttition_name IS NOT NULL THEN
                sql_count := 'SELECT COUNT(*) FROM '
                             || table_name
                             || ' PARTITION('
                             || parttition_name
                             || ')';
            ELSE
                sql_count := 'SELECT COUNT(*) FROM ' || table_name;
            END IF;

            EXECUTE IMMEDIATE sql_count
            INTO table_count;
            out_table_name := strtoken(table_name, '.', 2);
            out_buf := out_table_name
                       || ','
                       || parttition_name
                       || ','
                       || subparttition_name
                       || ','
                       || table_count;

            dbms_output.put_line(out_buf);
            utl_file.put_line(out_handle, out_buf);
        EXCEPTION
            WHEN no_data_found THEN
                -- ファイルの終端に来た場合は、ループ終了
                -- dbms_output.put_line('ループ処理終了');
                EXIT;
        END;
    END LOOP;

    dbms_output.put_line('finished');

    -- ファイルクローズ
    utl_file.fclose(in_handle);
    utl_file.fclose(out_handle);
END;
/

　４、件数対象リスト作成

スキーマ名.テーブル名
スキーマ名.テーブル名:パーティション名
スキーマ名.テーブル名:パーティション名:サブパーティション名
というルールで作成します。

今回作成したのテスト用のはこちらです。

　５、件数集計

sqlplusに接続後、下記のコマンドを実行します。
@count_targets_row_nums.sql target_table_list.txt result_target_row_nums.csv

赤枠みたいの内容と「PL/SQL procedure successfully completed.」が表示されれば、実行成功です。

　６、csvファイル確認

出力フォーマットは
テーブル名,パーティション名,サブパーティション名,件数
です。

今回件数集計の便利ツールのシェアはここまです。
現場に活躍しているエンジニアたちにお役に立てれば嬉しいです。