Oracle SQL Query for listing all Schemas in a DB

Using sqlplus

sqlplus / as sysdba

run:

SELECT * FROM dba_users

Should you only want the usernames do the following:

SELECT username FROM dba_users

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.

SELECT username FROM all_users ORDER BY username;