Container Databases (CDB) and Pluggable Databases (PDBs)

Overview

In this workshop you will:
- Understand Oracle’s multitenant architecture (CDB$ROOT, PDB$SEED, and PDBs).
- Perform core operations on pluggable databases (open/close, switch container, create/drop).
- Manage common users (C##...) vs local users (PDB-scoped) and practice privilege assignment.

Notation
- Terminal: commands in bash blocks (prompt example: [oracle@dbserver ~]$).
- SQL*Plus: commands in sql blocks (run after connecting with sqlplus).

Quick reference: Operations on PDBs

These are the core operations you will use to manage pluggable databases. Replace:
- pdb_name with your PDB name (in this workshop: ORCLPDB1).
- admin / password with the local admin credentials for a new PDB.
- '/target_path/' with a server-side directory where Oracle can create the PDB datafiles.

SQL*Plus:

-- Open a PDB (make it available for connections)
ALTER PLUGGABLE DATABASE pdb_name OPEN;

-- Close a PDB
ALTER PLUGGABLE DATABASE pdb_name CLOSE;

-- "Connect" to a PDB inside the same SQL*Plus session (switch current container)
ALTER SESSION SET CONTAINER = pdb_name;

-- Create a new PDB (typically cloned from PDB$SEED)
CREATE PLUGGABLE DATABASE pdb_name
  ADMIN USER admin IDENTIFIED BY password
  ROLES = (DBA)
  CREATE_FILE_DEST = '/target_path/';

-- Drop a PDB and delete its datafiles (irreversible)
DROP PLUGGABLE DATABASE pdb_name INCLUDING DATAFILES;

Notes:
- OPEN / CLOSE / CREATE / DROP are typically run while connected as SYSDBA in CDB$ROOT.
- ALTER SESSION SET CONTAINER = ... switches containers; it does not use the listener. To connect directly as an application user, use the PDB service (for example: sqlplus app_user/app_pass@localhost:1521/orclpdb1).

1. Oracle multitenant architecture

In Oracle’s multitenant architecture, a Container Database (CDB) comprises:
- Root Container (CDB$ROOT): system metadata, common data dictionary views, and global information shared by all PDBs.
- Seed Container (PDB$SEED): a read‑only template used to create new PDBs.
- Pluggable Databases (PDBs): isolated databases hosted inside the CDB (PDB1, PDB2, …).

Cette architecture améliore l'extensibilité et permet une gestion simplifiée des bases de données dans un environnement consolidé.

CDB
├── CDB$ROOT
├── PDB$SEED   (read-only template)
├── PDB1
├── PDB2
└── PDBn

2. Pluggable Database (PDB)

A PDB is an autonomous database hosted inside a CDB. It has its own:
- Schemas
- Tables
- Objects
- User sessions

Because each PDB is isolated, you can:
- Add/drop/migrate PDBs more easily.
- Partition resources efficiently.
- Simplify upgrades, backups and lifecycle operations.

3. Operations on PDBs

3.1 Identify the current container and list PDBs

SHOW CON_NAME
SHOW PDBS

3.2 Open and close a PDB

ALTER PLUGGABLE DATABASE pdb_name OPEN;
ALTER PLUGGABLE DATABASE pdb_name CLOSE;

3.3 Connect (switch) to a PDB

ALTER SESSION SET CONTAINER = pdb_name;

3.4 Create a PDB (from `PDB$SEED`)

CREATE PLUGGABLE DATABASE pdb_name
  ADMIN USER admin IDENTIFIED BY password
  ROLES = (DBA)
  CREATE_FILE_DEST = '/target_path/';

Notes:
- CREATE_FILE_DEST must point to a valid server-side directory where Oracle can create the PDB datafiles.
- The exact clause(s) can vary depending on your lab setup (FILE_NAME_CONVERT, PATH_PREFIX, etc.).

3.5 Drop a PDB

DROP PLUGGABLE DATABASE pdb_name INCLUDING DATAFILES;

4. Users in a CDB: common vs local

Oracle distinguishes between common users and local users:

4.1 Common users (`C##...`)

A common user exists across the whole CDB (in CDB$ROOT and all PDBs):
- Naming convention: must start with C## (for example C##test).
- Created in CDB$ROOT.
- Privileges are granted per container (a common user can be powerful in one PDB and restricted in another).

Example:

-- Run in CDB$ROOT
CREATE USER c##admin_user IDENTIFIED BY password;

Granting a privilege in a specific PDB:

ALTER SESSION SET CONTAINER = pdb_name;
GRANT CREATE SESSION TO c##admin_user;

4.2 Local users (PDB-scoped)

A local user is created inside one PDB and exists only in that PDB:
- It does not exist in CDB$ROOT or other PDBs.
- Ideal for application users and end users that should remain inside one database.

Example:

-- Run after switching to a PDB
ALTER SESSION SET CONTAINER = pdb_name;
CREATE USER app_user IDENTIFIED BY app_pass;

5. Exercises (assume `ORCLPDB1`)

We will assume that our pluggable database of study is ORCLPDB1 (service: orclpdb1).

Exercise 1: Create a common user and a local user

Objective
- Create a common user C##test in CDB$ROOT and grant it CREATE SESSION in the root.
- Create a local user app_user in ORCLPDB1 and grant it the DBA role.

Steps

1) Connect as SYSDBA and confirm you are in the root:
Terminal ([oracle@dbserver ~]$):

sqlplus / AS SYSDBA

SQL*Plus:

SHOW CON_NAME

2) Create the common user in CDB$ROOT:

ALTER SESSION SET CONTAINER = CDB$ROOT;

CREATE USER c##test IDENTIFIED BY test;
GRANT CREATE SESSION TO c##test;

3) Create the local user in ORCLPDB1:

ALTER SESSION SET CONTAINER = ORCLPDB1;

CREATE USER app_user IDENTIFIED BY app_pass;
GRANT DBA TO app_user;

Verification

From the root, list common users:

ALTER SESSION SET CONTAINER = CDB$ROOT;
SELECT username, common FROM cdb_users WHERE common = 'YES';

In ORCLPDB1, list users and check roles/privileges:

ALTER SESSION SET CONTAINER = ORCLPDB1;
SELECT username FROM dba_users;

SELECT * FROM dba_role_privs WHERE grantee IN ('APP_USER','C##TEST');
SELECT * FROM dba_sys_privs  WHERE grantee IN ('APP_USER','C##TEST');

Exercise 2: Privileges on tables (common user vs local user)

Tables used in this exercise:

CREATE TABLE departments (
  dept_id    NUMBER PRIMARY KEY,
  dept_name  VARCHAR2(100)
);

CREATE TABLE employees (
  emp_id     NUMBER PRIMARY KEY,
  name       VARCHAR2(50),
  dept_id    NUMBER,
  email      VARCHAR2(100),
  join_date  DATE,
  CONSTRAINT fk_emp_dept
    FOREIGN KEY (dept_id) REFERENCES departments(dept_id)
);

Objective
- Create the tables and insert some rows as app_user.
- Give C##test the ability to view EMPLOYEES.
- Create a local user test in ORCLPDB1 and give it the ability to view and modify both tables.
- Connect as C##test and test and display the tables according to the granted privileges.

Steps

1) Start the listener:
- To return from SQL*Plus to the terminal, use EXIT in SQL*Plus.

Terminal ([oracle@dbserver ~]$):

lsnrctl start

2) Check listener status (verify it is running on the database server):

Terminal ([oracle@dbserver ~]$):

lsnrctl status

From lsnrctl status, confirm:
- The listener is READY and listening on the expected port (usually 1521).
- The orclpdb1 service appears under Services Summary (service registration).

If orclpdb1 is missing from the services list, open it and force registration (as SYSDBA):

ALTER PLUGGABLE DATABASE ORCLPDB1 OPEN;
ALTER SYSTEM REGISTER;

3) Ensure C##test can connect to ORCLPDB1 (privileges are container-specific):

ALTER SESSION SET CONTAINER = ORCLPDB1;
GRANT CREATE SESSION TO c##test;

4) Connect to ORCLPDB1 through the listener as app_user, then create and populate tables:
Terminal ([oracle@dbserver ~]$):

sqlplus app_user/app_pass@localhost:1521/orclpdb1

SQL*Plus:

CREATE TABLE departments (
  dept_id    NUMBER PRIMARY KEY,
  dept_name  VARCHAR2(100)
);

CREATE TABLE employees (
  emp_id     NUMBER PRIMARY KEY,
  name       VARCHAR2(50),
  dept_id    NUMBER,
  email      VARCHAR2(100),
  join_date  DATE,
  CONSTRAINT fk_emp_dept
    FOREIGN KEY (dept_id) REFERENCES departments(dept_id)
);

INSERT INTO departments VALUES (10, 'Sales');
INSERT INTO departments VALUES (20, 'IT');

INSERT INTO employees VALUES (1, 'Ahmed',  10, 'ahmed@sales.company.com', DATE '2020-05-10');
INSERT INTO employees VALUES (2, 'Ali',    10, 'ali@sales.company.com',   DATE '2021-01-15');
INSERT INTO employees VALUES (3, 'Mohamed', 20, 'mohamed@it.company.com',   DATE '2019-03-22');

COMMIT;

5) As app_user, grant privileges:

GRANT SELECT ON employees TO c##test;

CREATE USER test IDENTIFIED BY test;
GRANT CREATE SESSION TO test;
GRANT SELECT, INSERT, UPDATE, DELETE ON departments TO test;
GRANT SELECT, INSERT, UPDATE, DELETE ON employees   TO test;

6) As C##test, connect through the listener and view EMPLOYEES:
Terminal ([oracle@dbserver ~]$):

sqlplus c##test/test@localhost:1521/orclpdb1

SQL*Plus:

SELECT * FROM app_user.employees;

7) As test, connect through the listener, then view and modify the tables:
Terminal ([oracle@dbserver ~]$):

sqlplus test/test@localhost:1521/orclpdb1

SQL*Plus:

SELECT * FROM app_user.departments;
SELECT * FROM app_user.employees;

UPDATE app_user.departments
SET    dept_name = 'Information Technology'
WHERE  dept_id = 20;

COMMIT;

Verification

As a DBA in ORCLPDB1, check grants for C##TEST and TEST:

ALTER SESSION SET CONTAINER = ORCLPDB1;
SELECT owner, table_name, grantee, privilege
FROM   dba_tab_privs
WHERE  owner = 'APP_USER'
  AND  grantee IN ('C##TEST','TEST')
ORDER BY table_name, grantee, privilege;

Verify which common users have privileges on EMPLOYEES:

SELECT grantee, privilege
FROM   dba_tab_privs
WHERE  owner = 'APP_USER'
  AND  table_name = 'EMPLOYEES'
  AND  grantee LIKE 'C##%';

6. Takeaways

A CDB is made of CDB$ROOT, PDB$SEED, and multiple PDBs.
PDB operations are done with ALTER PLUGGABLE DATABASE ... and ALTER SESSION SET CONTAINER = ....
Common users (C##...) exist in all containers, but privileges are still granted per container.
Local users exist only in one PDB, which is ideal for application isolation.

Container Databases (CDB) and Pluggable Databases (PDBs)

Overview

Quick reference: Operations on PDBs

1. Oracle multitenant architecture

2. Pluggable Database (PDB)

3. Operations on PDBs

3.1 Identify the current container and list PDBs

3.2 Open and close a PDB

3.3 Connect (switch) to a PDB

3.4 Create a PDB (from PDB$SEED)

3.5 Drop a PDB

4. Users in a CDB: common vs local

4.1 Common users (C##...)

4.2 Local users (PDB-scoped)

5. Exercises (assume ORCLPDB1)

Exercise 1: Create a common user and a local user

Exercise 2: Privileges on tables (common user vs local user)

6. Takeaways

3.4 Create a PDB (from `PDB$SEED`)

4.1 Common users (`C##...`)

5. Exercises (assume `ORCLPDB1`)