Oracle OWNER, OBJECT_TYPE, OBJECT_NAME一覧

list_objects.sql

-- #!/bin/sh
-- #@(#) \$Id list_objects.sh, v1.0
-- #
-- # su - oracle
-- # export ORACLE_SID=xxxx
-- # export ORACLE_HOME=/opt/app/oracle/product/12.2.0
-- #
-- # @../sql/list_objects.sql
-- #
-- #export NLS_LANG=JAPANESE_JAPAN.AL32UTF8
-- ${ORACLE_HOME}/bin/sqlplus / as sysdba << EOF
--
-- OWNER, OBJECT_TYPE, OBJECT_NAME一覧
--
SET ECHO OFF
SET FEEDBACK OFF
SET HEADING ON
SET LINESIZE 200
SET PAGESIZE 50000
SET TERMOUT ON
SET TRIMSPOOL ON
ALTER SESSION SET NLS_DATE_FORMAT = 'YYYY/MM/DD HH24:MI:SS' ;
-- 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 list_objects_&&_CONNECT_IDENTIFIER._&&USER_NAME&&SYSTEM_DATE..log
-- Windows ↑,  UNIX/Linux では ↓ を有効に
-- SPOOL list_objects_\&&_CONNECT_IDENTIFIER._\&&USER_NAME&&\SYSTEM_DATE..log
COLUMN OBJECT_TYPE       FORMAT A20
COLUMN RN                FORMAT 999,990
                             -- ↓OWNER.TABLE_NAMEの長さは現場対応
COLUMN OBJECT_NAME       FORMAT A45
COLUMN SUBOBJECT_NAME    FORMAT A45
COLUMN TIMESTAMP         FORMAT A19
COLUMN STATUS            FORMAT A10
-- COLUMN CONVERTED_LENGTHB FORMAT 99990
BREAK ON OWNER ON OBJECT_TYPE
SELECT 
    ROW_NUMBER() OVER(PARTITION BY A.OWNER
                                 , CASE
                                       WHEN A.OBJECT_TYPE = 'PACKAGE BODY' THEN 'PACKAGE'
                                       WHEN A.OBJECT_TYPE = 'TYPE BODY'    THEN 'TYPE'
                                       ELSE A.OBJECT_TYPE
                                   END
                                   ORDER BY A.OWNER, A.OBJECT_TYPE_SORT, A.OBJECT_NAME) AS RN
  , A.OWNER || '.' || A.OBJECT_NAME AS OBJECT_NAME
  , A.OBJECT_TYPE
  , A.SUBOBJECT_NAME
  , A.CREATED
  , A.LAST_DDL_TIME
  , A.TIMESTAMP
  , A.STATUS
FROM(
    SELECT
        OB.OWNER
      , OB.OBJECT_TYPE
      , CASE
            WHEN OB.OBJECT_TYPE IN('PACKAGE', 'TYPE') THEN
                OB.OBJECT_TYPE || OB.OBJECT_NAME
            WHEN OB.OBJECT_TYPE = 'PACKAGE BODY' THEN
                'PACKAGE' || OB.OBJECT_NAME || ' BODAY'
            WHEN OB.OBJECT_TYPE = 'TYPE BODY' THEN
                'TYPE' || OB.OBJECT_NAME || ' BODAY'
            ELSE
                OB.OBJECT_TYPE
        END AS OBJECT_TYPE_SORT
      , OB.OBJECT_NAME
      , OB.SUBOBJECT_NAME
      , OB.CREATED
      , OB.LAST_DDL_TIME
      , OB.TIMESTAMP
      , DECODE(OB.STATUS, 'VALID', NULL, OB.STATUS) AS STATUS
    FROM(
        SELECT COLUMN_VALUE AS OWNER FROM TABLE(PKG_USERS_SCHEMA.GET_USERS_SCHEMA)  -- ユーザーが作成したスキーマのみ
        ) MS
    INNER JOIN DBA_OBJECTS OB  -- DBA_OBJECTSにDROPPED列を追加して欲しい。
    ON  OB.OWNER  = MS.OWNER
    WHERE NOT EXISTS  -- except trash box, since Oracle10.1.0
        (
        SELECT NULL
        FROM DBA_RECYCLEBIN RB
        WHERE RB.OWNER       = MS.OWNER
        AND   RB.OWNER       = OB.OWNER
        AND   RB.TYPE        = OB.OBJECT_TYPE
        AND   RB.OBJECT_NAME = OB.OBJECT_NAME
        )
--  AND OB.OBJECT_NAME NOT IN
--      (
--      'CHAINED_ROWS'  -- except Oracle side table
--    , 'EXCEPTIONS'
--    , 'PLAN_TABLE'
--    , 'PLAN_TABLE$'
--      )
    ORDER BY
        OB.OWNER
      , OB.OBJECT_TYPE
      , OB.OBJECT_NAME
      , OB.SUBOBJECT_NAME
      , OB.STATUS
    ) A ;
SPOOL OFF
COLUMN OBJECT_TYPE       CLEAR
COLUMN RN                CLEAR
COLUMN OBJECT_NAME       CLEAR
COLUMN SUBOBJECT_NAME    CLEAR
COLUMN TIMESTAMP         CLEAR
COLUMN STATUS            CLEAR
CLEAR BREAK
SET ECHO ON
SET FEEDBACK ON
SET HEADING ON
SET TIMING OFF
SET TERMOUT ON
SET LINESIZE 80
-- exit
-- EOF
-- #export NLS_LANG=en_US
-- # ---------------------- End of list_objects.sh ------------------------------

参考
Oracle 純粋にユーザー側のスキーマのみを取得