有些时候,我们要清理数据库时,想把数据库中所有非系统自带的用户全部删除,在12C以前,我们只能通过人肉的方式,从12C 开始,多了一个字段: ORACLE_MAINTAINED如果时系统自带的,该值为Y,否则为N
就很好办了,直接使用以下语句就可以找出系统自带的用户了。
select username,account_status,ORACLE_MAINTAINED from dba_users where ORACLE_MAINTAINED='Y';
SQL> SELECT username,account_status from dba_users where ORACLE_MAINTAINED='Y';
USERNAME ACCOUNT_STATUS
------------------------- ------------------------------
SYS OPEN
AUDSYS LOCKED
SYSTEM OPEN
OUTLN LOCKED
GSMADMIN_INTERNAL LOCKED
GSMUSER LOCKED
GSMROOTUSER LOCKED
DIP LOCKED
REMOTE_SCHEDULER_AGENT LOCKED
DBSFWUSER LOCKED
ORACLE_OCM LOCKED
SYS$UMF LOCKED
DBSNMP LOCKED
APPQOSSYS LOCKED
GSMCATUSER LOCKED
GGSYS LOCKED
XDB LOCKED
ANONYMOUS EXPIRED & LOCKED
WMSYS LOCKED
OJVMSYS LOCKED
CTXSYS EXPIRED & LOCKED
ORDSYS LOCKED
ORDDATA LOCKED
ORDPLUGINS LOCKED
SI_INFORMTN_SCHEMA LOCKED
MDSYS LOCKED
OLAPSYS LOCKED
MDDATA LOCKED
LBACSYS LOCKED
DVF LOCKED
DVSYS LOCKED
SYSBACKUP LOCKED
SYSDG LOCKED
SYSKM LOCKED
SYSRAC LOCKED
XS$NULL EXPIRED & LOCKED
36 rows selected.
与一个新创建的库对比可以发现是一致的:
SQL> select username,ACCOUNT_STATUS from dba_users;
USERNAME ACCOUNT_STATUS
------------------------- ------------------------------
SYS OPEN
SYSTEM OPEN
XS$NULL EXPIRED & LOCKED
OJVMSYS LOCKED
LBACSYS LOCKED
OUTLN LOCKED
SYS$UMF LOCKED
DBSNMP LOCKED
APPQOSSYS LOCKED
DBSFWUSER LOCKED
GGSYS LOCKED
ANONYMOUS EXPIRED & LOCKED
CTXSYS EXPIRED & LOCKED
DVSYS LOCKED
DVF LOCKED
GSMADMIN_INTERNAL LOCKED
MDSYS LOCKED
OLAPSYS LOCKED
XDB LOCKED
WMSYS LOCKED
GSMCATUSER LOCKED
MDDATA LOCKED
SYSBACKUP LOCKED
REMOTE_SCHEDULER_AGENT LOCKED
GSMUSER LOCKED
SYSRAC LOCKED
GSMROOTUSER LOCKED
SI_INFORMTN_SCHEMA LOCKED
AUDSYS LOCKED
DIP LOCKED
ORDPLUGINS LOCKED
SYSKM LOCKED
ORDDATA LOCKED
ORACLE_OCM LOCKED
SYSDG LOCKED
ORDSYS LOCKED
36 rows selected.
|