Oracle OWNER, OBJECT_TYPE, STATUS毎の件数取得

count_objects.sql

--
-- OWNER, OBJECT_TYPE, STATUS毎の件数取得
--
SET ECHO OFF
SET FEEDBACK OFF
SET LINESIZE 120
SET PAGESIZE 50000
SET TAB OFF
SET TERMOUT OFF
SET TRIMSPOOL ON
-- 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_objects_&&_CONNECT_IDENTIFIER._&&USER_NAME&&SYSTEM_DATE..log
-- Windows ↑,  UNIX/Linux では ↓ を有効に
-- SPOOL count_objects_\&&_CONNECT_IDENTIFIER._\&&USER_NAME\&&SYSTEM_DATE..log
COLUMN OWNER        FORMAT A15
COLUMN OBJECT_TYPE  FORMAT A21
COLUMN STATUS       FORMAT A10
COLUMN COUNTS       FORMAT 999,990
BREAK ON OWNER SKIP 1 ON OBJECT_TYPE
SELECT
    NVL(OB.OWNER      , 'OWNER TOTAL')       AS OWNER
  , NVL(OB.OBJECT_TYPE, 'OBJECT_TYPE TOTAL') AS OBJECT_TYPE
  , NVL(OB.STATUS     , 'STATUS TOTAL')      AS STATUS
  , COUNT(*) AS COUNTS
FROM DBA_OBJECTS OB
WHERE OB.OWNER NOT IN  -- 今回はOracle側スキーマを除く。お客様によってはこれで良いかは保証の限りではない。笑い
    (
    'ANONYMOUS'
  , 'APEX_030200'
  , 'APEX_040000'
  , 'APEX_040200'
  , 'APEX_PUBLIC_USER'
  , 'APPQOSSYS'
  , 'AURORA$JIS$UTILITY$'
  , 'AURORA$ORB$UNAUTHENTICATE'
  , 'CLUSTER_MONITOR'
  , 'CTXSYS'
  , 'DBSNMP'
  , 'DIP'
  , 'DMSYS'
  , 'EXFSYS'
  , 'FLOWS_FILES'
  , 'LBACSYS'
  , 'HR'
  , 'MDDATA'
  , 'MDSYS'
  , 'MGMT_VIEW'
  , 'OAS_PUBLIC'
  , 'ODM'
  , 'ODM_MTR'
  , 'OE'
  , 'OLAPSYS'
  , 'ORACLE_OCM'
  , 'ORDDATA'
  , 'ORDPLUGINS'
  , 'ORDSYS'
  , 'OSE$HTTP$ADMIN'
  , 'OUTLN'
  , 'OWBSYS'
  , 'OWBSYS_AUDIT'
  , 'PERFSTAT'
  , 'PM'
  , 'PUBLIC'
  , 'PUBUSR1'
  , 'QS'
  , 'QS_ADM'
  , 'QS_CB'
  , 'QS_CBADM'
  , 'QS_CS'
  , 'QS_ES'
  , 'QS_OS'
  , 'QS_WS'
  , 'REPADMIN'
  , 'RMAN'
  , 'SCOTT'
  , 'SH'
  , 'SI_INFORMTN_SCHEMA'
  , 'SPATIAL_CSW_ADMIN_USR'
  , 'SPATIAL_WFS_ADMIN_USR'
  , 'SYS'
  , 'SYSADM'
  , 'SYSMAN'
  , 'SYSTEM'
  , 'TRACESVR'
  , 'TSMSYS'
  , 'WEBSYS'
  , 'WK_TEST'
  , 'WKPROXY'
  , 'WKSYS'
  , 'WMSYS'
  , 'XDB'
  , 'XS$NULL'
    )
AND OB.OBJECT_NAME NOT LIKE 'BIN$%' -- except trash box, since Oracle10.1.0, DBA_OBJECTS にも DROPPED列が欲しい
AND OB.OBJECT_NAME NOT IN
        (
        'CHAINED_ROWS'  -- except Oracle side table
      , 'EXCEPTIONS'
      , 'PLAN_TABLE'
      , 'PLAN_TABLE$'
        )
GROUP BY CUBE
    (    ----
    OB.OWNER
  , OB.OBJECT_TYPE
  , OB.STATUS
    )
ORDER BY
    OB.OWNER
  , OB.OBJECT_TYPE
  , OB.STATUS ;
SPOOL OFF
COLUMN OWNER        CLEAR
COLUMN OBJECT_TYPE  CLEAR
COLUMN STATUS       CLEAR
COLUMN COUNTS       CLEAR
CLEAR BREAKS
SET ECHO ON
SET FEEDBACK ON
SET LINESIZE 80