重庆思庄Oracle、Redhat认证学习论坛

标题: 如何区分哪些用户是ORACLE系统提供的？ [打印本页]

作者: 刘泽宇 时间: 2023-6-27 14:55
标题: 如何区分哪些用户是ORACLE系统提供的？
有些时候，我们要清理数据库时，想把数据库中所有非系统自带的用户全部删除，在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.

欢迎光临重庆思庄Oracle、Redhat认证学习论坛 (http://bbs.cqsztech.com/)