Oracle テーブル毎のレコード件数を取得 PL/SQL

count_tables.sql

--
--     TABLE_NAME毎のレコード件数を取得
--     レコード件数の多いテーブルでは負荷が大きいので注意。
--
--  history
--    2018/03/03 索引構成表を除く
--
SET ECHO OFF
SET FEEDBACK OFF
SET LINESIZE 400
SET PAGESIZE 5000
SET TERMOUT OFF
SET TRIMSPOOL ON
SET SERVEROUTPUT ON SIZE UNLIMITED
ALTER SESSION ENABLE PARALLEL DML ;
-- SPOOL pass includes CONNECT_IDENTIFIER, USER_NAME and SYSDATE
COLUMN USER_NAME    NEW_VALUE USER_NAME
COLUMN SYSTEM_DATE  NEW_VALUE SYSTEM_DATE
SELECT USER || '_' AS USER_NAME
     , TO_CHAR(SYSDATE, 'YYYYMMDD_HH24MISS') AS SYSTEM_DATE
FROM DUAL ;
SPOOL count_tables_&&_CONNECT_IDENTIFIER._&&USER_NAME&&SYSTEM_DATE..log
-- Windows ↑,  UNIX/Linux では ↓ を有効に
-- SPOOL count_tables_\&&_CONNECT_IDENTIFIER._\&&USER_NAME\&&SYSTEM_DATE..log
SET TIMING ON
DECLARE
    CURSOR CUR_TA
    IS
        SELECT
            TA.TABLE_NAME
          , 'SELECT /*+ INDEX_FFS(TA) PARALLEL(DEFAULT) */ COUNT(*) FROM "' || TA.TABLE_NAME || '" TA' AS SQL_STRING
          , NVL2(TM.COMMENTS, ' -- ' || TO_SINGLE_BYTE(TRIM(TM.COMMENTS)), NULL) AS TABLE_COMMENTS
        FROM USER_TABLES TA
        LEFT OUTER JOIN USER_TAB_COMMENTS TM
        ON  TA.TABLE_NAME = TM.TABLE_NAME
        WHERE
            TA.DROPPED = 'NO'  -- except trash box, since Oracle10.1.0
        AND TA.IOT_TYPE IS NULL  -- except index composition table
        AND TA.TABLE_NAME NOT IN
            (
            'CHAINED_ROWS'  -- except Oracle side table
          , 'EXCEPTIONS'
          , 'PLAN_TABLE'
          , 'PLAN_TABLE$'
             )
        ORDER BY
            TA.TABLE_NAME ;

    L_COUNTS  NUMBER ;
BEGIN
    DBMS_OUTPUT.PUT_LINE('TABLE_NAME                                    COUNT COMMENT') ;
    DBMS_OUTPUT.PUT_LINE('----------------------------- --------------------- --------------------------------------------') ;
    FOR REC_TA IN CUR_TA LOOP
        EXECUTE IMMEDIATE REC_TA.SQL_STRING INTO L_COUNTS ;
        DBMS_OUTPUT.PUT_LINE(RPAD(REC_TA.TABLE_NAME, 31) || TO_CHAR(L_COUNTS, '999,999,999,999,990') || REC_TA.TABLE_COMMENTS) ;
    END LOOP ;
END ;
/
SET TIMING OFF
SPOOL OFF
SET ECHO ON
SET FEEDBACK ON
SET LINESIZE 80