How to List Databases in Oracle

How do I list all databases in Oracle?

List all Oracle databases, CDBs, and PDBs using SQL*Plus or SQL Developer queries on dynamic performance views.

Welcome to the Galaxy, Guardian!

Oops! Something went wrong while submitting the form.

Description

Use dynamic performance views (V$DATABASE, V$PDBS) or the SHOW PDBS command to list all Oracle databases and pluggable databases in a multi-tenant environment.

How do I list the current database?

Run SELECT name FROM v$database; in SQL*Plus or SQL Developer. The query returns the single database name managed by your Oracle instance.

How can I list all Pluggable Databases (PDBs)?

Connect to the container database (CDB$ROOT) and execute SHOW PDBS; or query V$PDBS. Each row represents a PDB that can host its own schemas like Customers and Orders.

Why use V$PDBS instead of SHOW PDBS?

V$PDBS lets you filter, join, and aggregate inside SQL, enabling checks such as the number of PDBs in OPEN mode or joined analytics across environments.

Can I include business context in the query?

Yes. After identifying the target PDB (e.g., ECOM_PDB), switch with ALTER SESSION SET CONTAINER = ECOM_PDB; and run ecommerce queries like SELECT COUNT(*) FROM customers;.

Best practices for listing Oracle databases

Always connect as a common user (e.g., SYS or a DBA role) in the root container. Grant only SET CONTAINER and SELECT privileges required for auditing.

When should I use data dictionary views?

Use DBA_PDBS for metadata available to DBAs, or ALL_PDBS for user-level visibility. These views provide creation times, service names, and open modes in a single call.

Why How to List Databases in Oracle is important

How to List Databases in Oracle Example Usage


-- Step 1: List PDBs to find the ecommerce database
SELECT pdb_name, open_mode FROM v$pdbs;

-- Step 2: Connect to the ecommerce PDB
ALTER SESSION SET CONTAINER = ECOM_PDB;

-- Step 3: Confirm the Customers table exists
SELECT COUNT(*) AS total_customers FROM customers;

How to List Databases in Oracle Syntax


-- List current database
SELECT name FROM v$database;

-- Show all PDBs (quick)
SHOW PDBS;

-- List PDBs with additional metadata
SELECT pdb_name,
       open_mode,
       creation_scn,
       con_id
FROM   v$pdbs
ORDER  BY pdb_name;

-- Switch to a specific ecommerce PDB and verify
ALTER SESSION SET CONTAINER = ECOM_PDB;
SELECT COUNT(*) AS customer_total FROM customers;

Common Mistakes

Mistake: Running SHOW PDBS inside a PDB. It returns only that PDB, misleading you to think others are missing. Fix: Connect to CDB$ROOT first.
Mistake: Confusing schemas with databases by querying DBA_USERS. This lists users, not databases. Fix: Query V$PDBS or DBA_PDBS for databases.