在Oracle 进行运维时,有时我们需要排除系统用户,在Oracle 11g以前的版本,可以通过如下查询来查询非系统用户:
SQL>set pages 200
SQL>select username,default_tablespace from dba_users where username not in ('SYS','SYSTEM','OUTLN','FLOWS_FILES','MDSYS','ORDSYS','EXFSYS','DBSNMP','WMSYS','APPQOSSYS','APEX_030200','ORDDATA','CTXSYS','ANONYMOUS','XDB','ORDPLUGINS','SI_INFORMTN_SCHEMA','OLAPSYS','ORACLE_OCM','XS$NULL','MDDATA','DIP','APEX_PUBLIC_USER','SPATIAL_CSW_ADMIN_USR','SPATIAL_WFS_ADMIN_USR','MGMT_VIEW','SCOTT','SYSMAN','OWBSYS_AUDIT','OWBSYS');
在Oracle 12c以后,Oracle 增强了dba_users视图,引入了INHERITED列。
https://docs.oracle.com/en/database/oracle/oracle-database/12.2/refrn/DBA_USERS.html
Indicates whether the user definition was inherited from another container (YES) or not (NO)。
This column is available starting with Oracle Database 12c Release 2 (12.2.0.1).
所以我们可以直接利用该字段来查询非系统用户:
SQL>select username from dba_users where INHERITED=’NO’;
当然,也可以利用如下SQL来实现:
SQL>SELECT username,account_status from dba_users where account_status = ‘OPEN’ and username not like ‘%SYS%’ and username not like ‘C##%’ and username not like ‘PDBADMIN’ and username not like ‘DBSNMP’;
这里主要是多租户这块的用户,关于该特性的更多说明,可以参考我的数据:
《Oracle 18c 必须掌握的新特性》
https://www.cndba.cn/dave/article/3453
版权声明:本文为博主原创文章,未经博主允许不得转载。