Friday, March 15, 2013

[RDBMS] List All Schemas

This post will provide information about Oracle schemas. First, its definition and then the correct SQL query to list the availables schemas.

Definition
An Oracle schema is a set of all objects owned by a user. Consequently, an schema is equivalent to a user account.

How-to
If you are interested in listing all schemas connect to the instance using a user with SYSDBA privileges. Then query the following SQL:

     SQL> SELECT USERNAME FROM DBA_USERS;



This query will retrieve the list of all users but some of them may not have created objects. So it is not the proper answer.

Try the following SQL statement:

     SQL> SELECT DISTINCT OWNER FROM DBA_OBJECTS;



This query will list you all schemas available.
Problem solved!