本来、データ・ディクショナリ・ビュー DBA_USERSのスキーマはOracle側のスキーマか、ユーザーで作ったスキーマか区別する列があるべきである。オラクル・サポートにはその識別ができるよう過去、何度か列の追加を Enhanced Request(Oracle USAに改善依頼を出すという意味)を出した。

Oracle12.1.0からDBA_USERS reference にCOMMON, ORACLE_MAINTAINEDが追加された。

DBA_USERS

COLUMN USERNAME FORMAT A30
SELECT
    US.USERNAME
  , US.COMMON
  , US.ORACLE_MAINTAINED
FROM DBA_USERS US
ORDER BY
    US.USERNAME ;

USERNAME                       COMMON OR
------------------------------ ------ --
ANONYMOUS                      YES    Y
APEX_040200                    YES    Y
APEX_PUBLIC_USER               YES    Y
APPQOSSYS                      YES    Y
AUDSYS                         YES    Y
BI                             NO     N
-- 中略
PERFSTAT                       NO     N
-- 中略
SCOTT                          NO     N
SH                             NO     N
SI_INFORMTN_SCHEMA             YES    Y
SPATIAL_CSW_ADMIN_USR          YES    Y
SPATIAL_WFS_ADMIN_USR          YES    Y
SYS                            YES    Y
SYSBACKUP                      YES    Y
SYSDG                          YES    Y
SYSKM                          YES    Y
SYSTEM                         YES    Y
USERNAME1                      NO     N
USERNAME2                      NO     N
WMSYS                          YES    Y
XDB                            YES    Y
XS$NULL                        YES    Y

xx行が選択されました。

だが、BI, PERFSTAT, SCOTT などもユーザー側のスキーマとしては対象外としたい。

SELECT ...
FROM DBA_USERS US
WHERE US.USERNAME IN('USERNAME1', 'USERNAME2', ...)

これでは、ユーザー側のスキーマが増えるとWHERE句の修正も必要になる。

ユーザー用スキーマのテーブルを用意する方法もあるが、CREATE USER, DROP USERの度にユーザー用スキーマのテーブルへの更新が必要になる。

Oracle OWNER, OBJECT_TYPE, STATUS毎の件数取得
のようにユーザー用スキーマ以外のスキーマを並べるのも、市販のパッケージを追加したり、Oracleのバージョンが変わると各SQLの修正が必要になるからあまり保守性がよろしくない。

ユーザー用スキーマ以外用のテーブルを作ってユーザー用スキーマ以外用のテーブルに存在しないユーザーが純粋なユーザー用のスキーマ、って判断するって方法もある。だが、お客様の中には新たにテーブルを作ることに異常に高い壁があるところが多いのでSTORED PACKAGEを長年使っている。

これだとOracleのバージョンが変わっても同じSTORED PACKAGEを使えるから、インスタンスが何十、何百、ってヘビーなデータセンターでもちょっと運用が楽になる。できれば管理用のスキーマに置かせてもらえると(CREATE USER も敷居が高いところも少なくないが)、管理が容易。

PKG_USERS_SCHEMA

CREATE OR REPLACE PACKAGE PKG_USERS_SCHEMA
--
-- Oracle側スキーマを除く、ユーザー側で作成したスキーマだけを取得
--
--   ややパフォーマンスは悪いが当PACKAGEを使って、なるべくメンテナンスフリーに。
--
--  history
--    2018/03/13 PARALLEL_ENABLE, DETERMINISTIC を追加
--
IS
    TYPE TYPE_SCHEMA IS TABLE OF VARCHAR2(30) ;  -- スキーマ名を配列でRETURNするためのデータ型の宣言
    FUNCTION GET_USERS_SCHEMA
        RETURN PKG_USERS_SCHEMA.TYPE_SCHEMA PIPELINED PARALLEL_ENABLE DETERMINISTIC ;
END PKG_USERS_SCHEMA ;
/
SHOW ERROR PACKAGE PKG_USERS_SCHEMA
CREATE OR REPLACE PACKAGE BODY PKG_USERS_SCHEMA
IS
    FUNCTION GET_USERS_SCHEMA
        RETURN PKG_USERS_SCHEMA.TYPE_SCHEMA PIPELINED PARALLEL_ENABLE DETERMINISTIC
    IS
        CURSOR CUR_US
        IS
            SELECT US.USERNAME
            FROM DBA_USERS US
            WHERE US.USERNAME NOT IN
                ( -- except Oracle side owner
                'ANONYMOUS'  -- 当プログラムはまだOracle側のスキーマが漏れているかもしれない
              , 'APEX_030200'
              , 'APEX_040000'
              , 'APEX_040200'
              , 'APEX_PUBLIC_USER'
              , 'APPQOSSYS'
              , 'AUDSYS'
              , 'BI'
              , 'CLUSTER_MONITOR'
              , 'CTXSYS'
              , 'DBSNMP'
              , 'DIP'
              , 'DMSYS'
              , 'DVF'
              , 'DVSYS'
              , 'EXFSYS'
              , 'FLOWS_FILES'
              , 'GSMADMIN_INTERNAL'
              , 'GSMCATUSER'
              , 'GSMUSER'
              , 'HR'
              , 'IX'
              , 'LBACSYS'
              , 'MDDATA'
              , 'MDSYS'
              , 'OAS_PUBLIC'
              , 'ODM'
              , 'ODM_MTR'
              , 'OE'
              , 'OJVMSYS'
              , 'OLAPSYS'
              , 'ORACLE_OCM'
              , 'ORDDATA'
              , 'ORDPLUGINS'
              , 'ORDSYS'
              , 'QS'
              , 'QS_ADM'
              , 'QS_CB'
              , 'QS_CBADM'
              , 'QS_CS'
              , 'QS_ES'
              , 'QS_OS'
              , 'QS_WS'
              , 'OUTLN'
              , 'OWBSYS'
              , 'OWBSYS_AUDIT'
              , 'PERFSTAT'
              , 'PM'
              , 'PUBLIC'
              , 'REPADMIN'
              , 'RMAN'
              , 'SCOTT'
              , 'SH'
              , 'SI_INFORMTN_SCHEMA'
              , 'SPATIAL_CSW_ADMIN_USR'
              , 'SPATIAL_WFS_ADMIN_USR'
              , 'SYS'
              , 'SYSADM'
              , 'SYSBACKUP'
              , 'SYSDG'
              , 'SYSKM'
              , 'SYSMAN'
              , 'SYSTEM'
              , 'TRACESVR'
              , 'TSMSYS'
              , 'WEBSYS'
              , 'WK_TEST'
              , 'WKPROXY'
              , 'WKSYS'
              , 'WMSYS'
              , 'XDB'
              , 'XS$NULL'
           -- , 'XXXXXXXX'  -- XXXXXXXX : 当PACKAGEを置くスキーマも表示対象外とする時
                ) ;
    BEGIN
        FOR REC_US IN CUR_US LOOP
            PIPE ROW(REC_US.USERNAME) ;
        END LOOP ;
        RETURN ;
    EXCEPTION
        WHEN OTHERS THEN
            RETURN ;
    END GET_USERS_SCHEMA ;

END PKG_USERS_SCHEMA ;
/
SHOW ERROR PACKAGE BODY PKG_USERS_SCHEMA
-- PUBLIC SYNONYM に
CREATE OR REPLACE PUBLIC SYNONYM PKG_USERS_SCHEMA FOR PKG_USERS_SCHEMA ;
-- 全スキーマから実行できるように
GRANT EXECUTE ON  PKG_USERS_SCHEMA TO PUBLIC ;

GRANT SELECT ANY TABLE TO 当PACKAGEを置くスキーマ ;
では DBA_USERS ビューは見えないので(要確認)
GRANT SELECT ON DBA_USERS TO 当PACKAGEを置くスキーマ ;
も必要。Oracle11.2.0辺りからセキュリティが強化されたためか？(要オラクル・サポートに確認)

-- 使用例
SELECT
    OB.OWNER || '.' || OB.OBJECT_NAME AS OBJECT_NAME
  , OB.OBJECT_TYPE
  , OB.STATUS
FROM(
    SELECT COLUMN_VALUE AS OWNER FROM TABLE(PKG_USERS_SCHEMA.GET_USERS_SCHEMA)  -- ユーザーが作成しーマのみ
    ) MS
INNER JOIN DBA_OBJECTS OB
ON  OB.OWNER  = MS.OWNER
...
ORDER BY 1 ;