用于列出数据库中所有模式的 Oracle SQL 查询

Question

我想删除 Oracle DB 上一些未使用的模式。

如何查询所有架构名称？

Answer 1

使用 sqlplus

sqlplus / as sysdba

运行：

SELECT * 
FROM dba_users

如果您只想要用户名，请执行以下操作：

SELECT username 
FROM dba_users

Answer 2

最有可能的是，您希望

SELECT username
  FROM dba_users

这将向您显示系统中的所有用户（以及所有潜在的模式）。如果您对“模式”的定义允许模式为空，那么这就是您想要的。但是，可能存在语义区别，即人们只想将某事物称为模式（如果它实际上拥有至少一个对象），以便排除数百个永远不会拥有任何对象的用户帐户。在这种情况下，

SELECT username
  FROM dba_users u
 WHERE EXISTS (
    SELECT 1
      FROM dba_objects o
     WHERE o.owner = u.username )

假设创建模式的人对于分配默认表空间是明智的，并且假设您对 Oracle 提供的模式不感兴趣，则可以通过在 default_tablespace 上添加谓词来过滤掉这些模式，即

SELECT username
  FROM dba_users
 WHERE default_tablespace not in ('SYSTEM','SYSAUX')

或者，

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')

遇到系统中有人错误地为非系统用户提供 default_tablespace 为 SYSTEM 的情况并不少见，因此请确保之前的假设成立尝试以这种方式过滤掉 Oracle 提供的模式。

Answer 3

SELECT username FROM all_users ORDER BY username;

Answer 4

select distinct owner 
from dba_segments
where owner in (select username from dba_users where default_tablespace not in ('SYSTEM','SYSAUX'));

Answer 5

下面的sql列出了oracle中安装后创建的所有schema
ORACLE_MAINTAINED='N' 是过滤器。此列是 12c 中的新增内容。

select distinct username,ORACLE_MAINTAINED from dba_users where ORACLE_MAINTAINED='N';

Answer 6

怎么样：

SQL> select * from all_users;

它将返回所有用户/模式的列表、他们的 ID 和在数据库中创建的日期：

USERNAME                          USER_ID CREATED
------------------------------ ---------- ---------
SCHEMA1                         120 09-SEP-15
SCHEMA2                         119 09-SEP-15
SCHEMA3                         118 09-SEP-15

Answer 7

以下任一 SQL 将返回 Oracle DB 中的所有模式。

1. 从 all_tables 中按所有者分组选择所有者；
2. 从 all_tables 中选择不同的所有者；

Answer 8

如果您想搜索架构名称，那么

SELECT * FROM all_tables WHERE OWNER  LIKE '%SCHEMA_NAME%'

如果您想搜索特定表，那么 -

SELECT * FROM all_tables WHERE TABLE_NAME  LIKE '%TABLE_NAME%'