Oracle SQL Query для перечисления всех схем в БД

Скорее всего, вы хотите

SELECT username
  FROM dba_users

Это покажет вам всех пользователей в системе (и, следовательно, все возможные схемы). Если ваше определение "schema" допускает, чтобы схема была пустой, это то, что вы хотите. Тем не менее, может существовать семантическое различие, когда люди хотят только что-то назвать схемой, если на самом деле она владеет хотя бы одним объектом, поэтому исключаются сотни учетных записей пользователей, которые никогда не будут владеть объектами. В этом случае

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

Предполагая, что тот, кто создал схемы, был разумным в отношении назначения табличных пространств по умолчанию и предполагая, что вас не интересуют схемы, которые поставлял Oracle, вы можете отфильтровывать эти схемы, добавляя предикаты в default_tablespace, i.e.

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

Не очень редко встречается система, где кто-то неправильно дал несистемному пользователю a default_tablespace of SYSTEM, однако, будьте уверены, что предположения сохраняются, прежде чем пытаться отфильтровать поставляемый Oracle схемы таким образом.

SELECT username FROM all_users ORDER BY username;

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

Как насчет:

SQL> select * from all_users;

он вернет список всех пользователей/схем, их идентификатор и дату, созданные в БД:

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

Ниже в sql перечислены все схемы в оракуле, созданные после установки ORACLE_MAINTAINED = 'N' - фильтр. Этот столбец является новым в 12c.

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

Любой из следующих SQL вернет всю схему в Oracle DB.

• select owner FROM all_tables group by owner;
• select distinct owner FROM all_tables;