Corredor

ウェブ、プログラミングの勉強メモ。

Oracle DB の全テーブルのレコード数と最終更新日時を求めてみる

以前こんな記事を書いたのだが、それの発展版。

neos21.hatenablog.com

大抵の業務システムの DB だと、レコードの最終更新日時を記録するカラムを作ってあると思う。ココではそういうカラムを DATE 型の updated_at カラムとして全テーブルに作ってあるものとする。

テーブルの一覧に対し、テーブルごとのレコード件数と、そのテーブル内で一番新しい updated_at の日時を出力してみる。ぼくはこのスクリプトを、「動きがよく分からない更新処理で何が変わるのか」を調べるために使っていた。

以下をまるっと TableList.sql とでも名付けて保存し、必要なときに Start (@) で実行すれば保存できる。

Set echo off
Set feedback off
Set heading off
Set lines 32767
Set pages 50000
Set newpage none
Set trimspool on
Spool C:\TableList.csv
SELECT 'NO, TABLE_NAME, COUNT, UPDATED_AT' FROM DUAL;
SELECT
    ROWNUM       || ',' ||
    TABLE_NAME   || ',' ||
    COUNT        || ',' ||
    UPDATED_AT
FROM
    (
        SELECT
            TABLE_NAME,
            TO_NUMBER(
              EXTRACTVALUE(
                XMLTYPE(
                  DBMS_XMLGEN.GETXML('SELECT COUNT(*) C FROM ' || TABLE_NAME)
                ),
                '/ROWSET/ROW/C'
              )
            ) COUNT,
            EXTRACTVALUE(
              XMLTYPE(
                DBMS_XMLGEN.GETXML('SELECT DECODE(MAX(updated_at), NULL, ''-'', TO_CHAR(MAX(updated_at), ''YYYY-MM-DD HH24:MI:SS'')) U FROM ' || TABLE_NAME)
              ),
              '/ROWSET/ROW/U'
            ) UPDATED_AT
        FROM
            USER_TABLES
        ORDER BY
            TABLE_NAME
    );
Spool Off

ポイントは以下のとおり。

  • 先頭の Set コマンドたちで出力形式を調整している。大抵のコマンドは以下で紹介した。 neos21.hatenablog.com
  • Set feedback off で、「○件選択されました」といったメッセージを非表示にする。
  • Set heading off で、列名を出力しないようにしている。その代わりに、SELECT … FROM DUAL の行で、列名となる文言を固定出力している。
  • Set newpage none で、Spool 時の先頭の空白行を除去するようにしている。
  • SELECT 結果は CSV 形式で出力できるよう、(カラム) || ',' || (カラム) と、カンマ区切りでカラムを繋げて出力するようにしている。そのため全体を副問合せにしているが、カンマ区切りで出力するつもりがないのであれば、副問合せの部分だけ実行すれば良い。
  • FROM USER_TABLES の後に WHERE 句を書けば、取得するテーブル・取得しないテーブルを選択できる。updated_at カラムがないテーブルを SELECT しようとするとコケるので、適宜絞っておく。

おわり。