スキーマ内にある全テーブルのレコード数を、1テーブルずつSELECT COUNT(*)を書くことなくまとめて確認する方法です。
「統計情報から概算値を取得する方法」と「実際に全件カウントして正確な値を取得する方法」の2通りを紹介します。
方法1：統計情報から概算件数を取得する（高速）
USER_TABLESビューのNUM_ROWS列には、統計情報取得時点でのおおよそのレコード数が保持されています。
テーブルを直接スキャンしないため、テーブル数や1テーブルあたりの件数が多い場合でも一瞬で結果が返ります。
|
1 2 3 4 5 6 7 |
SELECT TABLE_NAME, NUM_ROWS FROM USER_TABLES ORDER BY TABLE_NAME; |
実行結果（例）
| TABLE_NAME | NUM_ROWS |
|---|---|
| MST_SHOHIN | 1200 |
| MST_USER | 150 |
| TRN_ORDER | 45678 |
NUM_ROWSはあくまで統計情報を取得した時点の値です。その後に行われたINSERTやDELETEは反映されないため、実際の件数とズレが生じることがあります。
最新の状態に近づけたい場合は、事前に以下を実行して統計情報を更新しておいてください。
|
1 |
EXEC DBMS_STATS.GATHER_SCHEMA_STATS(USER); |
統計情報が一度も取得されていないテーブルは、NUM_ROWSがNULLのまま表示されます。この場合は方法2を使用するか、統計情報取得後にあらためて実行してください。
方法2：実際に全件カウントして正確な件数を取得する
統計情報に頼らず、その時点の正確な件数を知りたい場合は、各テーブルに対して実際にCOUNT(*)を実行します。
テーブル名はUSER_TABLESから取得し、動的SQL（EXECUTE IMMEDIATE）で1テーブルずつカウントしていきます。
|
1 2 3 4 5 6 7 8 9 10 11 |
SET SERVEROUTPUT ON DECLARE V_CNT NUMBER; BEGIN FOR REC IN (SELECT TABLE_NAME FROM USER_TABLES ORDER BY TABLE_NAME) LOOP EXECUTE IMMEDIATE 'SELECT COUNT(*) FROM ' || REC.TABLE_NAME INTO V_CNT; DBMS_OUTPUT.PUT_LINE(RPAD(REC.TABLE_NAME, 30) || V_CNT); END LOOP; END; / |
実行結果（例）
|
1 2 3 |
MST_SHOHIN 1200 MST_USER 150 TRN_ORDER 45678 |
SQL*Plusで実行結果（DBMS_OUTPUT.PUT_LINEの内容）を画面に表示するには、事前に「SET SERVEROUTPUT ON」の実行が必要です。
解説
- テーブル名は実行するまで分からない（動的に変わる）ため、通常のSELECT文には書けません。そこで、テーブル名を文字列として組み立ててEXECUTE IMMEDIATEで実行する「動的SQL」を使用しています。
- RPADはテーブル名を30桁の固定幅にそろえるために使用しています。テーブル名が長い場合は桁数を調整してください。
- 方法2はテーブルの件数が多い（数百万件を超えるなど）場合、全件をスキャンする分だけ時間がかかります。おおまかな件数で十分な場合は、方法1の利用をおすすめします。
備考
- 自分以外のユーザ（スキーマ）のテーブルも対象にしたい場合は、USER_TABLESの代わりにALL_TABLES（OWNER列で対象スキーマを絞り込む）や、DBA_TABLES（DBA権限が必要）を使用してください。
- USER_TABLESにはビューやマテリアライズドビューは含まれません。ビューの件数も確認したい場合は、USER_VIEWSなど別のディクショナリビューと組み合わせる必要があります。
- 件数の変動を継続的に把握したい場合は、都度方法2を実行するよりも、DBMS_STATS.GATHER_SCHEMA_STATSで定期的に統計情報を更新し、方法1で確認する運用の方が負荷を抑えられます。
関連記事
- [ORACLE] 統計情報が取得された日時を確認する
- [DBMSパッケージ] ユーザ単位で統計情報を更新する（DBMS_STATS.GATHER_SCHEMA_STATS）
- [DBMSパッケージ] テーブル単位で統計情報を更新する（DBMS_STATS.GATHER_TABLE_STATS）