Oracle SQL Query for listing all Schemas in a DB

Question

I wanted to delete some unused schemas on our oracle DB.

How can I query for all schema names ?

Answer 1

Using sqlplus

sqlplus / as sysdba

run:

SELECT * 
FROM dba_users

Should you only want the usernames do the following:

SELECT username 
FROM dba_users

Answer 2

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.

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_tablespace of SYSTEM, though, so be certain that the assumptions hold before trying to filter out the Oracle-delivered schemas this way.

Answer 3

SELECT username FROM all_users ORDER BY username;

Answer 4

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.

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

Answer 5

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

Answer 6

How about :

SQL> select * from all_users;

it will return list of all users/schemas, their ID's and date created in DB :

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

Answer 7

Either of the following SQL will return all schema in Oracle DB.

1. select owner FROM all_tables group by owner;
2. select distinct owner FROM all_tables;

Answer 8

IF YOU WANT TO SEARCH FOR THE SCHEMA NAME THEN

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

iF YOU WANT TO SEARCH FOR PERTICULAR TABLE THEN -

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