How to display databases in Oracle 11g using SQL * Plus

Question

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

How to show available databases in Oracle?

Answer 1

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

Answer 2

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

Answer 3

Perhaps you could use this view, but I'm not sure.

 select * from v$database; 

But I think that it will only show you information about the current db.

Another option if db works on linux ... something like this:

 SQL>!grep SID $TNS_ADMIN/tnsnames.ora | grep -v PLSExtProc 

Answer 4

Oracle does not have a simple database model such as MySQL or MS SQL Server. I believe that the closest thing is to request tablespaces and their corresponding users.

For example, I have a DEV_DB tablespace with all my "databases" inside them:

 SQL> SELECT TABLESPACE_NAME FROM USER_TABLESPACES; 

Result:

 SYSTEM SYSAUX UNDOTBS1 TEMP USERS EXAMPLE DEV_DB 

You can also request users in all table spaces:

 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 

Answer 5

I don’t quite understand, but as a rule, one server has one database (with many users), if you create many databases, then you create many instances, listeners .... Therefore, you can check your LISTENER to identify him.

In my testing, I created 2 databases ( dbtest and dbtest_1 ), so when I check the status of LISTENER, it looks like this:

 lsnrctl status 

....

LISTENING STATUS

.....

(DESCRIPTION = (ADDRESS = (PROTOCOL = tcp) (HOST = 10.10.20.20) (PORT = 1521)))

Summary of Services ...

The dbtest service has 1 instance.

Instance "dbtest", status READY, has 1 handler for this service ...

The dbtest1XDB service has 1 instance (s).

Instance "dbtest1", status READY, has 1 handler for this service ...

The dbtest_1 service has 1 instance (s).

Instance "dbtest1", status READY, has 1 handler for this service ... Command completed successfully