用于列出数据库中所有模式的 Oracle SQL 查询
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/4833459/
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
Oracle SQL Query for listing all Schemas in a DB
提问by vicsz
I wanted to delete some unused schemas on our oracle DB.
我想删除我们的 oracle DB 上一些未使用的模式。
How can I query for all schema names ?
如何查询所有模式名称？
回答by a_horse_with_no_name
Using sqlplus
使用 sqlplus
sqlplus / as sysdba
sqlplus / 作为 sysdba
run:
跑：
SELECT * FROM dba_users
Should you only want the usernames do the following:
如果您只希望用户名执行以下操作：
SELECT username FROM dba_users
回答by Justin Cave
Most likely, you want
最有可能的是，你想要
SELECT username
FROM dba_users
That will show you all the users in the system (and thus all the potential schemas). If your definition of "schema" allows for a schema to be empty, that's what you want. However, there can be a semantic distinction where people only want to call something a schema if it actually owns at least one object so that the hundreds of user accounts that will never own any objects are excluded. In that case
这将显示系统中的所有用户（以及所有潜在的模式）。如果您对“架构”的定义允许架构为空，那么这就是您想要的。但是，可能存在语义上的区别，即人们只想将某个东西称为模式，前提是它实际上拥有至少一个对象，这样就排除了数百个永远不会拥有任何对象的用户帐户。在这种情况下
SELECT username
FROM dba_users u
WHERE EXISTS (
SELECT 1
FROM dba_objects o
WHERE o.owner = u.username )
Assuming that whoever created the schemas was sensible about assigning default tablespaces and assuming that you are not interested in schemas that Oracle has delivered, you can filter out those schemas by adding predicates on the default_tablespace, i.e.
假设创建模式的人对分配默认表空间是明智的，并且假设您对 Oracle 提供的模式不感兴趣，您可以通过在 上添加谓词来过滤掉这些模式default_tablespace，即
SELECT username
FROM dba_users
WHERE default_tablespace not in ('SYSTEM','SYSAUX')
or
或者
SELECT username
FROM dba_users u
WHERE EXISTS (
SELECT 1
FROM dba_objects o
WHERE o.owner = u.username )
AND default_tablespace not in ('SYSTEM','SYSAUX')
It is not terribly uncommon to come across a system where someone has incorrectly given a non-system user a default_tablespaceof SYSTEM, though, so be certain that the assumptions hold before trying to filter out the Oracle-delivered schemas this way.
它是不是非常少见，遇到在那里有人错误地给非系统用户的系统default_tablespace的SYSTEM，虽然如此，可以肯定，假设试图筛选出了Oracle交付模式这种方式之前举行。
回答by suhprano
SELECT username FROM all_users ORDER BY username;
回答by FeRtoll
select distinct owner
from dba_segments
where owner in (select username from dba_users where default_tablespace not in ('SYSTEM','SYSAUX'));
回答by mauek unak
How about :
怎么样 ：
SQL> select * from all_users;
it will return list of all users/schemas, their ID's and date created in DB :
它将返回所有用户/模式的列表，他们的 ID 和在 DB 中创建的日期：
USERNAME USER_ID CREATED
------------------------------ ---------- ---------
SCHEMA1 120 09-SEP-15
SCHEMA2 119 09-SEP-15
SCHEMA3 118 09-SEP-15
回答by sidnakoppa
Below sql lists all the schema in oracle that are created after installation ORACLE_MAINTAINED='N' is the filter. This column is new in 12c.
下面sql列出了oracle中安装后创建的所有schema ORACLE_MAINTAINED='N'是过滤器。此列是 12c 中的新增内容。
select distinct username,ORACLE_MAINTAINED from dba_users where ORACLE_MAINTAINED='N';
回答by Sreeju
Either of the following SQL will return all schema in Oracle DB.
以下任一 SQL 将返回 Oracle DB 中的所有模式。
select owner FROM all_tables group by owner;select distinct owner FROM all_tables;
select owner FROM all_tables group by owner;select distinct owner FROM all_tables;
