How to Create, Unplug and Drop a Pluggable Database (PDB) manually?

The multitenant feature introduced in Oracle Database 12c enables a container database (CDB) to manage several distinct pluggable databases (PDBs). This article discusses the available options for creating, unplugging, or dropping a pluggable database within an existing container database.

To begin with, ensure you are connected to ROOT container.

SQL> alter session set container = CDB$ROOT;

Session altered.

A Pluggable Database (PDB) can be created with 2 options, (there are other methods as well but just mentioning 2 to keep this post short)

1) Using CREATE_FILE_DEST

SQL> CREATE PLUGGABLE DATABASE pdb3 ADMIN USER pdb_adm IDENTIFIED BY PdbP$#wd3 CREATE_FILE_DEST='C:\app\oracle\oradata';

Pluggable database created.

OR

2) Using FILE_NAME_CONVERT

SQL> CREATE PLUGGABLE DATABASE pdb4 ADMIN USER pdb_adm IDENTIFIED BY PdbP$#wd4 FILE_NAME_CONVERT=('C:\app\oracle\oradata\cdb1\pdbseed\','C:\app\oracle\oradata\cdb1\pdb4\');

Pluggable database created.

You can quickly list your PDBs using the below query.

SQL> show pdbs;

    CON_ID CON_NAME                       OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
         2 PDB$SEED                       READ ONLY  NO
         3 PDB1                           MOUNTED
         4 PDB2                           MOUNTED
         5 PDB3                           MOUNTED
         6 PDB4                           MOUNTED

Open your PDBs to see if everything is OK. They should be able to open in READ WRITE mode.

SQL> alter pluggable database all open;

Pluggable database altered.

SQL> show pdbs;

    CON_ID CON_NAME                       OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
         2 PDB$SEED                       READ ONLY  NO
         3 PDB1                           READ WRITE NO
         4 PDB2                           READ WRITE NO
         5 PDB3                           READ WRITE NO
         6 PDB4                           READ WRITE NO

You can also view detailed information for these PDBs by using DBA_PDBS & V$PDBS views. I am just using few columns but you can explore more depending upon your need.

SQL> set lines 200 pages 200;
SQL> col PDB_NAME for a30;
SQL> select PDB_NAME, STATUS, DBID, CON_ID from dba_pdbs;

PDB_NAME                       STATUS           DBID     CON_ID
------------------------------ ---------- ---------- ----------
PDB1                           NORMAL     2577144351          3
PDB$SEED                       NORMAL     2821277789          2
PDB2                           NORMAL      892763305          4
PDB3                           NORMAL     4043792862          5
PDB4                           NORMAL     2157528180          6

SQL> col name for a30;
SQL> select name, open_mode, local_undo from V$pdbs;

NAME                           OPEN_MODE  LOCAL_UNDO
------------------------------ ---------- ----------
PDB$SEED                       READ ONLY           1
PDB1                           READ WRITE          1
PDB2                           READ WRITE          1
PDB3                           READ WRITE          1
PDB4                           READ WRITE          1

Note: The newly created PDBs must be opened once in READ WRITE mode to complete the integration with CDB.

Unplug a Pluggable Database

There are 2 ways to unplug,

• Unplug to .pdb archive
• Unplug to .xml file

Unplug to .pdb archive

SQL> alter pluggable database pdb3 close;

Pluggable database altered.

SQL> alter pluggable database pdb3 unplug into 'C:\app\oracle\oradata\pdb3.pdb';

Pluggable database altered.

PDB archive secures all needed files in single archive. Hence you can drop it now including the datafiles at this point since they are all present in the archive file.

Unplug to .xml file

SQL> alter pluggable database pdb4 close;

Pluggable database altered.

SQL> alter pluggable database pdb4 unplug into 'C:\app\oracle\oradata\pdb4.xml';

Pluggable database altered.

You can verify the PDB & XML files creation on specified location.

PS C:\Users\parvi> dir C:\app\oracle\oradata

Mode                 LastWriteTime         Length Name
----                 -------------         ------ ----
d-----        09-01-2025     22:22                CDB1
d-----        04-12-2022     14:00                CDB2
d-----        06-01-2025     22:04                copydb
d-----        06-01-2025     21:09                PRACTICE
-a----        09-01-2025     22:45       94261218 pdb3.pdb
-a----        09-01-2025     22:49           6705 pdb4.xml

Unlike PDB archive, you need to secure this metadata XML and copy the PDB4 datafiles to someplace safe before you delete it.

Dropping PDBs

After you have secured the required files, you can delete both PDBs.

SQL> drop pluggable database pdb3 including datafiles;

Pluggable database dropped.

SQL> drop pluggable database pdb4 including datafiles;

Pluggable database dropped.

SQL> show pdbs;

    CON_ID CON_NAME                       OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
         2 PDB$SEED                       READ ONLY  NO
         3 PDB1                           READ WRITE NO
         4 PDB2                           READ WRITE NO

I hope this helps !!