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
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>;