【ORACLE】テーブル容量の確認２

前回のやつは、
テーブル名を与えたらそのテーブルの全項目に対するNVL(LENGHB(COLUMN_NAME),0)を作ってくれるが、
その後先頭に「SELECT」と最後尾に「FROM [テーブル名]」を自分でつけて自分でSQLを流すという作業が必要だった。
そこまで含めて完全自動化できそうだったのでやってみた。

---

declare

    nvc_select_str nvarchar2(2000);
    nvc_target_table_name nvarchar2(100);

    nmb_return number(20);

    cursor cur_make_query(tab_name nvc_target_table_name%TYPE) is
    select
        a.COLUMN_NAME
       ,case when a.COLUMN_ID = 1 then N'sum('  else N'+' end
        || N'nvl(lengthb(' || a.COLUMN_NAME || N'),0)'
        || case when a.COLUMN_ID = b.COLUMN_COUNT then ')'  else null end as LENGTHB_COL
    from ALL_TAB_COLUMNS a
       , (select
             x.TABLE_NAME
          ,  count(x.COLUMN_NAME) as COLUMN_COUNT
          from USER_TAB_COLUMNS x
          group by
              x.TABLE_NAME
          ) b
    where 0=0
      and a.OWNER = user
      and a.TABLE_NAME = tab_name
      and a.TABLE_NAME = b.TABLE_NAME
    order by
       a.COLUMN_ID
    ;

    rec_make_query cur_make_query%ROWTYPE;

begin

    nvc_target_table_name := :tab_name;

    nvc_select_str := 'select ';

    open cur_make_query(nvc_target_table_name);

    loop
        fetch cur_make_query into rec_make_query;
        exit when cur_make_query%NOTFOUND;

        nvc_select_str := nvc_select_str || rec_make_query.LENGTHB_COL;

    end loop;

    nvc_select_str := nvc_select_str || ' from ' || nvc_target_table_name;

    DBMS_OUTPUT.PUT_LINE(nvc_select_str);
    execute immediate to_char(nvc_select_str) into nmb_return;
    DBMS_OUTPUT.PUT_LINE(to_char(nmb_return));

end;
/

↑要するにこれコピって流すだけで良い。
前回紹介したSQLはカーソル「cur_make_query」に組み込まれている。

以下注釈。
●このSQLは前回書いたようにテーブルの全カラムに関してNVL(LENGTHB([COLUMN_NAME]),0)をとって後でそれをサマリするようになっている。
内部的には変数「nvc_select_str」にそれが格納されることになるが、
nvarchar2(2000)で定義しているので「SELECT」と「FROM [テーブル名]」を含めて2000文字を超える場合は使えない。
CLOBとかにすればもう少し汎用的になるかな？
●調査対象のテーブル名はバインド変数で定義されているので、事前にバインド変数の設定が必要。
どうせ一過性のものだし、埋め込んでもいいかもしれない。
内部的には変数「nvc_target_table_name」にその値が埋め込まれている。

sqlplusから流すときは、

SQL> variable tab_name nvarchar2(100);
SQL> execute :tab_name := 'TEST_TABLE';

↑を事前に実行した後で↑のコードを貼りつけて実行という感じか。
手軽ですな。
結果は↓のようになる。

select sum(nvl(lengthb(TEST_COLUMN1),0)+nvl(lengthb(TEST_COLUMN2),0)+nvl(lengthb(TEST_COLUMN3),0)) from TEST_TABLE
3918394

全部で２行出るが、上の方の行は投げたSQLそのもの
下の方の行は結果のバイト数。
この例では3918394バイト。