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


前回のやつは、
テーブル名を与えたらそのテーブルの全項目に対する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  


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