List of all the User excluding default Users

Question

I required list of all the users excluding the default oracle users.

Select Username from all_users;

This query will give me the result , but I want only manually created users. As there are 50+ users in my database I can't traverse through each and every user.

I tried :

http://www.orafaq.com/wiki/List_of_default_database_users From here I got list of all default users so whether I required to skip all the users using where clause in above query like

Select Username from all_users where Username NOT IN ('List All Default
Users Given By Oracle');

Or is there any quick way to do it ?

-Nagendra

Answer 1

As per Oracle Database 12c, these are the list of default users to avoid and get the detail of rest all other users.

please verify at below link to know more: https://docs.oracle.com/database/121/NTDBI/startrdb.htm#NTDBI2845

select username from dba_users where username not in ('ANONYMOUS'
,'APEX_040200'
,'APEX_PUBLIC_USER'
,'APPQOSSYS'
,'AUDSYS'
,'BI'
,'CTXSYS'
,'DBSNMP'
,'DIP'
,'DVF'
,'DVSYS'
,'EXFSYS'
,'FLOWS_FILES'
,'GSMADMIN_INTERNAL'
,'GSMCATUSER'
,'GSMUSER'
,'HR'
,'IX'
,'LBACSYS'
,'MDDATA'
,'MDSYS'
,'OE'
,'ORACLE_OCM'
,'ORDDATA'
,'ORDPLUGINS'
,'ORDSYS'
,'OUTLN'
,'PM'
,'SCOTT'
,'SH'
,'SI_INFORMTN_SCHEMA'
,'SPATIAL_CSW_ADMIN_USR'
,'SPATIAL_WFS_ADMIN_USR'
,'SYS'
,'SYSBACKUP'
,'SYSDG'
,'SYSKM'
,'SYSTEM'
,'WMSYS'
,'XDB'
,'SYSMAN'
,'RMAN'
,'RMAN_BACKUP'
,'OWBSYS'
,'OWBSYS_AUDIT'
,'APEX_030200'
,'MGMT_VIEW'
,'OJVMSYS');

Answer 2

Starting from Oracle 12c r1, you can use the new ORACLE_MAINTAINED column in ALL_USERS and DBA_USERS views

https://docs.oracle.com/database/121/REFRN/GUID-DDD25C8F-7EC9-46BC-ABEA-529C64FA09E2.htm

e.g.

select * from dba_tables where owner in 
 (select username from all_users where oracle_maintained = 'N')

should give you the list of tables owned by non-internal Oracle users.

Answer 3

you could possible do something with the creation date, the likelihood is that the system users would all have been created when the DB was setup, the additional users may have been created on following days/weeks/months. So a query that shows all the users created after the min created date might help you.

SELECT username 
  FROM dba_users 
 WHERE TRUNC(created) > (SELECT MIN(TRUNC(created)) 
                           FROM dba_users);

Answer 4

I resolved this retrieving the users that was created after the database create date

SELECT usr.username
  FROM sys.dba_users usr
 WHERE usr.created > (SELECT created FROM sys.v_$database)