How to list all database names in Oracle

1) To view database

select * from v$database;

2) To view instance

select * from v$instance;

3) To view all users

select * from all_users;


4) To view table and columns for a particular user

select tc.table_name Table_name
,tc.column_id Column_id
,lower(tc.column_name) Column_name
,lower(tc.data_type) Data_type
,nvl(tc.data_precision,tc.data_length) Length
,lower(tc.data_scale) Data_scale
,tc.nullable nullable
FROM all_tab_columns tc
,all_tables t
WHERE tc.table_name = t.table_name;

select owner from dba_tables
union
select owner from dba_views;

select username from dba_users;

QL> 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