oracle 除默认用户外的所有用户列表
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/37338237/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
List of all the User excluding default Users
提问by Nagendra Nigade
I required list of all the users excluding the default oracle users.
我需要所有用户的列表，不包括默认的 oracle 用户。
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.
这个查询会给我结果，但我只想要手动创建的用户。由于我的数据库中有 50 多个用户，我无法遍历每个用户。
I tried :
我试过 ：
http://www.orafaq.com/wiki/List_of_default_database_usersFrom here I got list of all default users so whether I required to skip all the users using where clause in above query like
http://www.orafaq.com/wiki/List_of_default_database_users从这里我得到了所有默认用户的列表，所以我是否需要在上面的查询中使用 where 子句跳过所有用户，如
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
-Nagendra
回答by Rajesh Chaudhary
As per Oracle Database 12c, these are the list of default users to avoid and get the detail of rest all other users.
根据 Oracle 数据库 12c，这些是要避免的默认用户列表，并获取其他所有用户的详细信息。
please verify at below link to know more: https://docs.oracle.com/database/121/NTDBI/startrdb.htm#NTDBI2845
请通过以下链接验证以了解更多信息：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');
回答by R. Du
Starting from Oracle 12c r1, you can use the new ORACLE_MAINTAINED column in ALL_USERS and DBA_USERS views
从 Oracle 12c r1 开始，您可以在 ALL_USERS 和 DBA_USERS 视图中使用新的 ORACLE_MAINTAINED 列
https://docs.oracle.com/database/121/REFRN/GUID-DDD25C8F-7EC9-46BC-ABEA-529C64FA09E2.htm
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.
应该为您提供非内部 Oracle 用户拥有的表列表。
回答by davegreen100
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);
回答by Agnaldo Povoa
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)
