Oracle: Show Databases – SQL*Plus

Oracle has no databases but schemas and users (that are essentially the same thing).

The schema/user in Oracle can be considered as an equivalent to a “database” concept in MySQL, PostgreSQL or MS SQL.

In this note i will show how to list all Oracle “databases” (equivalent to SHOW DATABASES command in MySQL), get the current schema name and how to switch to a different schemas using the command-line interface of SQL*Plus.

Cool Tip: Show tables in Oracle! Read more →

Show Databases in Oracle

Show all “databases” in Oracle, i.e. schemas/users (requires privileges on dba_users):

SQL> SELECT username AS schema_name
     FROM dba_users
     ORDER BY username;

If the current user doesn’t have enough privileges, you may get the following error:

ORA-00942: table or view does not exist

List Oracle “databases” visible to the current user:

SQL> SELECT username AS schema_name
     FROM all_users
     ORDER BY username;

List Oracle “databases” owned by the current user:

SQL> SELECT username AS schema_name
     FROM user_users
     ORDER BY username;

Get the name of the current database schema you are connected to in Oracle:

SQL> SELECT user AS schema_name
     FROM dual;

Switch to a different schema:

SQL> ALTER session
     SET current_schema = <schema_name>;