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

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

 早速、始めましょう。

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

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を使用しています。

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

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

ファンクション STRTOKEN

 3、件数集計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;
/

 4、件数対象リスト作成

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

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

 5、件数集計

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

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

 6、csvファイル確認


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

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

Last modified: 2023-01-22

Author