Geek Answers Handbook

List of all users excluding default users

I need a list of all users except oracle users by default.

Select Username from all_users;

This query will give me the result, but I want only manually created users. Since there are more than 50 users in my database, I cannot go through each user.

I tried:

http://www.orafaq.com/wiki/List_of_default_database_users From here I got a list of all users by default, so I had to skip all users who use the where clause in the above query, for example

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

Or is there any quick way to do this?

-Nagendra

+6
source share
4 answers

According to Oracle Database 12c, this is a list of default users to avoid and get details about the rest of the users.

Please check the link below to find out 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');
+2
source

you could do something with the creation date, the likelihood that all users of the system will be created during the installation of the database, additional users may be created in the following days / weeks / months. Thus, a query that shows all users created after the created date can help you.

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

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

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

eg,

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

should provide you with a list of tables owned by third-party Oracle users.

+1
source

I solved this problem by getting users who were created after the database creation date.

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

More articles:


All Articles