92

With help of this command show databases; I can see databases in MySQL.

How to show the available databases in Oracle?

Alex K
  • 22,315
  • 19
  • 108
  • 236
Nubkadiya
  • 3,285
  • 13
  • 40
  • 45

5 Answers5

109

SELECT NAME FROM v$database; shows the database name in oracle

Steve Chambers
  • 37,270
  • 24
  • 156
  • 208
Shan
  • 1,867
  • 1
  • 14
  • 5
  • 2
    I think that this is what the question was asking. Or at least this is what I think of when I hear "database"...as opposed to schema (or user). – Randy Stegbauer Jan 12 '15 at 19:50
  • 3
    this should be the answer – Ameya Deshpande Oct 31 '18 at 09:28
  • If e.g. you run Oracle Database Express Edition this prints something like `XE`, i.e. the unique name that uniquely identifies your instance/database (is it called SID?). On the other side, MySQL's `show databases` command prints what MySQL calls database aka schema (roughly what you get with `SELECT USERNAME FROM DBA_USERS` in Oracle). – Álvaro González Nov 21 '19 at 12:17
68

You can think of a MySQL "database" as a schema/user in Oracle. If you have the privileges, you can query the DBA_USERS view to see the list of schemas:

SELECT * FROM DBA_USERS;
Gryu
  • 2,102
  • 2
  • 16
  • 29
dpbradley
  • 11,645
  • 31
  • 34
  • 23
    Some oracle databases have a lot of users without objects. For these, and alternative is SELECT DISTINCT OWNER FROM ALL_OBJECTS; – Gary Myers Jun 09 '10 at 23:26
  • 1
    +1 nice alternative, just be aware that if you are not a privileged user and issue this query, you will see only those OWNER's where you have a privilege on at least one of their objects. – dpbradley Jun 10 '10 at 12:54
  • 5
    Query to DBA_USERS gives a "ORA-00942: table or view does not exist", maybe permission issue? The one in the comment "SELECT DISTINCT OWNER FROM ALL_OBJECTS;" is working with same access credentials. db 11g – a1an Jul 18 '12 at 09:31
  • 2
    `SELECT * FROM DBA_USERS;` – Felipe Augusto Feb 19 '18 at 01:47
36

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
Thomas Bratt
  • 48,038
  • 36
  • 121
  • 139
13

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
Jonathan
  • 11,809
  • 5
  • 57
  • 91
  • First one not working in 11g: "ORA-00942: table or view does not exist" – a1an Jul 18 '12 at 09:27
  • 2
    Hi @a1an. Without doubt, v$database exists in 11g. Check if you're executing the query with rights enought. Here you have the documentation about v$database for oracle 11g. http://docs.oracle.com/cd/B28359_01/server.111/b28320/dynviews_1086.htm Algo, here you have a post with an example about the use of v%database in oracle 11g. http://neeraj-dba.blogspot.com.es/2011/10/switchover-to-physical-standby-database.html Good Luck! – Jonathan Jul 18 '12 at 10:35
  • 2
    Probably jsut a permissions issue, executing it as sysdba works – a1an Jul 18 '12 at 15:23
  • Ok @a1an, just give permissions for select on that view. grant select on v_$database to youruser; PLEASE NOTE the underscore! v$database is a synonym and you can't grant proviledgest for synonyms (It can end in a ORA-02030 error). – Jonathan Jul 18 '12 at 19:26
1

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

GabrielOshiro
  • 7,986
  • 4
  • 45
  • 57
BongSey
  • 171
  • 1
  • 6