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

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

大抵の業務システムの 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

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

おわり。