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:
SYSTEM SYSAUX UNDOTBS1 TEMP USERS EXAMPLE DEV_DB
It 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_DB
DATAWARE DEV_DB
DATAMART DEV_DB
STAGING DEV_DB
I am not clearly about it but typically one server has one database (with many users), if you create many databases mean that you create many instances, listeners, ... as well. So you can check your LISTENER to identify it.
In my testing I created 2 databases (dbtest and dbtest_1) so when I check my LISTENER status it appeared like this:
lsnrctl status
....
STATUS of the LISTENER
.....
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=10.10.20.20)(PORT=1521)))
Services Summary...
Service "dbtest" has 1 instance(s).
Instance "dbtest", status READY, has 1 handler(s) for this service...
Service "dbtest1XDB" has 1 instance(s).
Instance "dbtest1", status READY, has 1 handler(s) for this service...
Service "dbtest_1" has 1 instance(s).
Instance "dbtest1", status READY, has 1 handler(s) for this service... The command completed successfully
Maybe you could use this view, but i'm not sure.
select * from v$database;
But I think It will only show you info about the current db.
Other option, if the db is running in linux... whould be something like this:
SQL>!grep SID $TNS_ADMIN/tnsnames.ora | grep -v PLSExtProc
SELECT NAME FROM v$database; shows the database name in oracle
Source: Stackoverflow.com