How to list all Database names in Oracle

List all DB names along with basic details:

SQL> select DBID, NAME, OPEN_MODE from v$database;

      DBID NAME      OPEN_MODE
———- ——— ———-
2337018765 PRODDB01      READ WRITE

SQL>

List all DB names along with all details:

SQL> select * from v$database;

      DBID NAME      CREATED   RESETLOGS_CHANGE# RESETLOGS
———- ——— ——— —————– ———
PRIOR_RESETLOGS_CHANGE# PRIOR_RES LOG_MODE     CHECKPOINT_CHANGE#
———————– ——— ———— ——————
ARCHIVE_CHANGE# CONTROL CONTROLFI CONTROLFILE_SEQUENCE# CONTROLFILE_CHANGE#
————— ——- ——— ——————— ——————-
CONTROLFI OPEN_RESETL VERSION_T OPEN_MODE
——— ———– ——— ———-
2337018765 PRODDB01      11-AUG-06                 1 11-AUG-06
                      0           NOARCHIVELOG            5362658
        5321718 CURRENT 11-AUG-06                 33359             5362658
11-APR-14 NOT ALLOWED 11-AUG-06 READ WRITE

SQL>

List Instance names along with basic details:

SQL> select instance_name, status, database_status from v$instance;

INSTANCE_NAME STATUS DATABASE_STATUS

—————- ——- —————–

INS03 MOUNTED ACTIVE

SQL>

List Instance names along with all details:

SQL> select * from v$instance;

INSTANCE_NUMBER INSTANCE_NAME

————— —————-

HOST_NAME

—————————————————————-

VERSION STARTUP_T STATUS PAR THREAD# ARCHIVE LOG_SWITCH_

—————– ——— ——- — ———- ——- ———–

LOGINS SHU DATABASE_STATUS INSTANCE_ROLE

———- — —————– ——————

1 INS03

dev.test.lab

8.1.7.4.0 11-APR-14 OPEN NO 1 STOPPED

ALLOWED NO ACTIVE PRIMARY_INSTANCE

SQL>

List all Users in the DB:

SQL> select * from all_users;

USERNAME                          USER_ID CREATED
—————————— ———- ———
SYS                                     0 11-AUG-06
SYSTEM                                  5 11-AUG-06
OUTLN                                  11 11-AUG-06
DBSNMP                                 16 11-AUG-06
GINFOBOX                               20 11-AUG-06
APPSERV                                25 11-AUG-06

6 rows selected.

SQL>

List all Tables in the DB Or Search for specific tables:

SQL> SELECT table_name FROM user_tables;

TABLE_NAME
——————————
TABSUBPART$
TRIGGER$
TRIGGERCOL$
TRIGGERJAVAC$
TRIGGERJAVAF$
TRIGGERJAVAM$
TRIGGERJAVAS$
TRUSTED_LIST$
TS$
TSQ$
TYPE$

..

..

…

TABLE_NAME
——————————
_default_auditing_options_

166 rows selected.

SQL>