How to display databases in Oracle 11g using SQL*Plus
Oracle does not have a simple database model like MySQL or MS SQL Server. I find the closest thing is to query the tablespaces and the corresponding users within them.
For example, I have a DEV_DB tablespace with all my actual 'databases' within them:
SQL> SELECT TABLESPACE_NAME FROM USER_TABLESPACES;Resulting in:
SYSTEMSYSAUXUNDOTBS1TEMPUSERSEXAMPLEDEV_DBIt is also possible to query the users in all tablespaces:
SQL> select USERNAME, DEFAULT_TABLESPACE from DBA_USERS;Or within a specific tablespace (using my DEV_DB tablespace as an example):
SQL> select USERNAME, DEFAULT_TABLESPACE from DBA_USERS where DEFAULT_TABLESPACE = 'DEV_DB';ROLES DEV_DBDATAWARE DEV_DBDATAMART DEV_DBSTAGING DEV_DB