Oracle Multitenant- Navigating CDB and PDBs Seamlessly

With Oracle Database 12c, Oracle introduced Multitenant Architecture, a major shift in how databases are structured and managed. This architecture separates the Container Database (CDB), which holds the Oracle system metadata and manages operations from the user databases, officially called Pluggable Databases (PDBs).  These containers hold the actual user data and applications. It was initially an option, but over time, Oracle has evolved it into the foundation of its database strategy. Starting with Oracle 23ai, Multitenant will no longer be optional but the default and required architecture, reinforcing Oracle’s push toward standardized, consolidated, and agile database environments.

For DBAs, this shift means understanding how to effectively work within a multitenant environment is no longer optional either.  We’ve lived and loved single instance architecture for over 4 decades and the change is never easy for such risk-averse mindsets. Navigating between the CDB and individual PDBs requires a strong grasp of context switching, scope awareness, and container-specific management techniques. A simple mistake, like importing data into the CDB instead of the designated PDB can have create significant follow-up impact and clean up. In this post, we’ll break down the essentials of working with Oracle Multitenant, walk through useful DDL examples, and share tips to help you manage PDBs efficiently and avoid common pitfalls in Oracle’s modern architecture.

Understanding the Landscape: CDB vs. PDB

• CDB (Container Database): The root container that includes system metadata, background processes, undo tablespace (unless local undo is configured), and the ability to manage all PDBs.
• PDB (Pluggable Database): The user-created databases that hold application-specific data and objects. They are portable, cloneable, and isolated, making them perfect for multi-tenant environments.

Switching Between CDB and PDB

In Oracle Multitenant environments, one of the most important commands every DBA must master is ALTER SESSION SET CONTAINER. This command allows you to switch your session context between the root container (aka CDB or CDB$ROOT) and any of the pluggable databases (PDBs) within the container database. Since operations like user creation, object management, and security configuration must often be performed within a specific container, setting the correct session context is critical to ensuring changes are applied where intended. Without using this command, you risk making modifications in the wrong scope, such as executing application-level DDL in the CDB instead of the target PDB, leading to configuration issues or security risks. Mastering container switching is essential for safe and effective multitenant administration.

The following script demonstrates status checks as part of the navigation:

-- Connect to CDB
sqlplus / as sysdba
-- Show current container
SELECT SYS_CONTEXT('USERENV','CON_NAME') AS CURRENT_CONTAINER FROM DUAL;
-- Switch to a specific PDB
ALTER SESSION SET CONTAINER = sales_pdb;
-- Confirm the container switch
SELECT SYS_CONTEXT('USERENV','CON_NAME') FROM DUAL;

Tip: Use SHOW CON_NAME in SQL*Plus to quickly verify which container you’re in.

Managing PDBs: Common DBA Tasks and DDL Examples

Opening and Closing PDBs

-- Open a specific PDB
ALTER PLUGGABLE DATABASE sales_pdb OPEN;
-- Open all PDBs
ALTER PLUGGABLE DATABASE ALL OPEN;
-- Close a specific PDB
ALTER PLUGGABLE DATABASE sales_pdb CLOSE IMMEDIATE;

Tip: Use SAVE STATE to persist the open state across CDB restarts.

ALTER PLUGGABLE DATABASE sales_pdb SAVE STATE;

Creating a New PDB (From Seed)

CREATE PLUGGABLE DATABASE new_pdb
ADMIN USER pdbadmin IDENTIFIED BY Welcome1
ROLES=(DBA);

Trick: You can use FILE_NAME_CONVERT if you’re using Oracle Managed Files (OMF) or need to override the default file locations.

Unplugging and Plugging PDBs

To unplug:

ALTER PLUGGABLE DATABASE old_pdb CLOSE IMMEDIATE;
ALTER PLUGGABLE DATABASE old_pdb UNPLUG INTO '/u01/app/oracle/unplug/old_pdb.xml';

To plug:

CREATE PLUGGABLE DATABASE restored_pdb USING '/u01/app/oracle/unplug/old_pdb.xml' COPY
FILE_NAME_CONVERT = ('/old/location/', '/new/location/');

Running DDL Inside PDBs

Make sure you’re in the correct container before issuing DDL:

-- Connect to sales_pdb
ALTER SESSION SET CONTAINER = sales_pdb;
-- Create a user and grant privileges
CREATE USER app_user IDENTIFIED BY "<password>";
GRANT CONNECT, RESOURCE TO app_user;

Tip: Many DBAs accidentally create users or objects in the CDB$ROOT thinking they’re in a PDB. Always check your container before issuing any command that creates users or makes impactful changes.

Monitoring and Querying Across Containers

If you’re trying to gather information across PDBs:

-- View open PDBs
SELECT NAME, OPEN_MODE FROM V$PDBS;
-- See all containers from CDB$ROOT
SELECT CON_ID, NAME FROM V$CONTAINERS;
-- Query a common view across all PDBs using CONTAINERS clause
SELECT * FROM CONTAINERS(SELECT COUNT(*) AS USER_COUNT FROM DBA_USERS) ORDER BY CON_ID;

Tip: Use CDB_* views (e.g., CDB_USERS, CDB_TABLESPACES) in CDB$ROOT for container-wide visibility. They include a CON_ID column so you can see which PDB the data belongs to.  Create your own joins to display the data that is relevant to the questions you need to answer at the time.

Scripting It All with SQL*Plus or SQLcl

Automating routine tasks in Oracle Multitenant becomes significantly more efficient when you leverage SQL*Plus scripting to handle container switching and command execution. By embedding ALTER SESSION SET CONTAINER commands within scripts, you can target specific PDBs and execute administrative operations such as user creation, privilege grants, or schema changes without manual intervention. This approach not only saves time but also reduces the risk of human error, especially in environments with multiple pluggable databases. SQL*Plus variables and control structures like ACCEPT, DEFINE, and WHENEVER SQLERROR can further enhance script flexibility, allowing you to build reusable, parameterized scripts that adapt to different container names and tasks. For DBAs managing large-scale Oracle environments, scripting is an essential tool for consistent, repeatable operations across all containers.

An example of this could be as follows:

SET SERVEROUTPUT ON
COLUMN con_name NEW_VALUE pdb_name
SELECT name AS con_name FROM v$pdbs WHERE open_mode = 'READ WRITE';
ALTER SESSION SET CONTAINER = &pdb_name;
-- Run your SQL
SELECT username FROM dba_users WHERE default_tablespace = 'USERS';

Summary

Oracle Multitenant for someone like myself, (multiplatform) just makes more sense.  It makes it easier for Oracle user data in container form to be managed, administered and migrated as needed for any organization.  Containers are easier for managing consolidated databases, but it requires a disciplined approach to context management and container-aware administration. By mastering simple commands and staying container-aware, DBAs can avoid common pitfalls and unlock the efficiency of multitenancy.

If you’re still holding onto classic single instance architecture and new to multitenant, start small and practice switching between containers, managing states, and running queries across containers. Once it becomes muscle memory, I promise – you’ll wonder how you ever managed without it.