本来、データ・ディクショナリ・ビュー DBA_USERSのスキーマはOracle側のスキーマか、ユーザーで作ったスキーマか区別する列があるべきである。オラクル・サポートにはその識別ができるよう過去、何度か列の追加を Enhanced Request(Oracle USAに改善依頼を出すという意味)を出した。
Oracle12.1.0からDBA_USERS reference にCOMMON, ORACLE_MAINTAINEDが追加された。
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 も敷居が高いところも少なくないが)、管理が容易。
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 ;