Help us understand the problem. What is going on with this article?

Oracle 純粋にユーザー側のスキーマのみを取得したい PL/SQL

More than 1 year has passed since last update.

本来、データ・ディクショナリ・ビュー 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 ;
Papageno
Oracle テーブル設計を含む基本設計以降の工程, SQL, PL/SQL, パフォーマンス・チューニング
Why not register and get more from Qiita?
  1. We will deliver articles that match you
    By following users and tags, you can catch up information on technical fields that you are interested in as a whole
  2. you can read useful information later efficiently
    By "stocking" the articles you like, you can search right away