Sometimes it’s the little differences that make something really cool, and I was wondering why this hasn’t made it into the Oracle dictionary before.

Have you ever asked yourself which out of the 30 or so accounts in the database were maintained by Oracle or in other words were Oracle internal and to be left alone? I did so on many occasions especially when it comes to the options I do not regularly see in the database. DBA_USERS lists all accounts in the database, user managed as well as Oracle managed. The below is the definition of the 11g view:

SQL> desc dba_users
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 USERNAME                                  NOT NULL VARCHAR2(30)
 USER_ID                                   NOT NULL NUMBER
 PASSWORD                                           VARCHAR2(30)
 ACCOUNT_STATUS                            NOT NULL VARCHAR2(32)
 LOCK_DATE                                          DATE
 EXPIRY_DATE                                        DATE
 DEFAULT_TABLESPACE                        NOT NULL VARCHAR2(30)
 TEMPORARY_TABLESPACE                      NOT NULL VARCHAR2(30)
 CREATED                                   NOT NULL DATE
 PROFILE                                   NOT NULL VARCHAR2(30)
 INITIAL_RSRC_CONSUMER_GROUP                        VARCHAR2(30)
 EXTERNAL_NAME                                      VARCHAR2(4000)
 PASSWORD_VERSIONS                                  VARCHAR2(8)
 EDITIONS_ENABLED                                   VARCHAR2(1)
 AUTHENTICATION_TYPE                                VARCHAR2(8)

SQL>

OK so in 10g and 11g you could check v$sysaux_occupants and join it against DBA_USERS and draw your conclusions from there…

SQL> desc v$sysaux_occupants
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 OCCUPANT_NAME                                      VARCHAR2(64)
 OCCUPANT_DESC                                      VARCHAR2(64)
 SCHEMA_NAME                                        VARCHAR2(64)
 MOVE_PROCEDURE                                     VARCHAR2(64)
 MOVE_PROCEDURE_DESC                                VARCHAR2(64)
 SPACE_USAGE_KBYTES                                 NUMBER

SQL>

But that’s a bit cumbersome in my opinion. Now with Oracle 12c there is something really cool. Compare the definition of DBA_USERS with the one from above:

SQL> desc dba_users
 Name                                            Null?    Type
 ----------------------------------------------- -------- --------------------------------
 USERNAME                                        NOT NULL VARCHAR2(128)
 USER_ID                                         NOT NULL NUMBER
 PASSWORD                                                 VARCHAR2(4000)
 ACCOUNT_STATUS                                  NOT NULL VARCHAR2(32)
 LOCK_DATE                                                DATE
 EXPIRY_DATE                                              DATE
 DEFAULT_TABLESPACE                              NOT NULL VARCHAR2(30)
 TEMPORARY_TABLESPACE                            NOT NULL VARCHAR2(30)
 CREATED                                         NOT NULL DATE
 PROFILE                                         NOT NULL VARCHAR2(128)
 INITIAL_RSRC_CONSUMER_GROUP                              VARCHAR2(128)
 EXTERNAL_NAME                                            VARCHAR2(4000)
 PASSWORD_VERSIONS                                        VARCHAR2(12)
 EDITIONS_ENABLED                                         VARCHAR2(1)
 AUTHENTICATION_TYPE                                      VARCHAR2(8)
 PROXY_ONLY_CONNECT                                       VARCHAR2(1)
 COMMON                                                   VARCHAR2(3)
 LAST_LOGIN                                               TIMESTAMP(9) WITH TIME ZONE
 ORACLE_MAINTAINED                                        VARCHAR2(1)

SQL>

In addition to the fields used for the new multi-tenancy option you also find a flag at the bottom named “ORACLE_MAINTAINED”-bingo!

SQL> select count(oracle_maintained),oracle_maintained
  2  from dba_users
  3  group by oracle_maintained;

COUNT(ORACLE_MAINTAINED) O
------------------------ -
                      35 Y
                       1 N

SQL>

And voila!

SQL> select username,oracle_maintained,account_status
  2  from dba_users
  3  order by oracle_maintained,username;

USERNAME                       O ACCOUNT_STATUS
------------------------------ - --------------------------------
SCOTT                          N OPEN
ANONYMOUS                      Y EXPIRED & LOCKED
APEX_040200                    Y EXPIRED & LOCKED
APEX_PUBLIC_USER               Y EXPIRED & LOCKED
APPQOSSYS                      Y EXPIRED & LOCKED
AUDSYS                         Y EXPIRED & LOCKED
CTXSYS                         Y EXPIRED & LOCKED
DBSNMP                         Y EXPIRED & LOCKED
DIP                            Y EXPIRED & LOCKED
DVF                            Y EXPIRED & LOCKED
DVSYS                          Y EXPIRED & LOCKED
FLOWS_FILES                    Y EXPIRED & LOCKED
GSMADMIN_INTERNAL              Y EXPIRED & LOCKED
GSMCATUSER                     Y EXPIRED & LOCKED
GSMUSER                        Y EXPIRED & LOCKED
LBACSYS                        Y EXPIRED & LOCKED
MDDATA                         Y EXPIRED & LOCKED
MDSYS                          Y EXPIRED & LOCKED
OJVMSYS                        Y EXPIRED & LOCKED
OLAPSYS                        Y EXPIRED & LOCKED
ORACLE_OCM                     Y EXPIRED & LOCKED
ORDDATA                        Y EXPIRED & LOCKED
ORDPLUGINS                     Y EXPIRED & LOCKED
ORDSYS                         Y EXPIRED & LOCKED
OUTLN                          Y EXPIRED & LOCKED
SI_INFORMTN_SCHEMA             Y EXPIRED & LOCKED
SPATIAL_CSW_ADMIN_USR          Y EXPIRED & LOCKED
SPATIAL_WFS_ADMIN_USR          Y EXPIRED & LOCKED
SYS                            Y OPEN
SYSBACKUP                      Y EXPIRED & LOCKED
SYSDG                          Y EXPIRED & LOCKED
SYSKM                          Y EXPIRED & LOCKED
SYSTEM                         Y OPEN
WMSYS                          Y EXPIRED & LOCKED
XDB                            Y EXPIRED & LOCKED
XS$NULL                        Y EXPIRED & LOCKED

36 rows selected.

SQL>

Note that I opened the SCOTT account. This a dbca-created database based on the General_Purpose.dbc template. In other words it has all the options in it, you might (should!) see less of these system managed accounts.

The same logic applies to certain other dictionary views as well, I came across oracle_maintained in DBA_ROLES as well. There might be others, time to check dict_columns for a column_name = ‘ORACLE_MAINTAINED’. You find it in

• (ALL|USER|DBA)_OBJECTS(_AE)
• (ALL|USER|DBA)_USERS
• DBA_ROLES
• DBA_INVALID_OBJECTS