Introduction
This article covers manually creating a Pluggable Database (PDB) in several different ways using SQL, plus renaming and dropping a PDB once it exists.
Prerequisites
- An RMAN backup, so you have a rollback path if anything in this article needs to be undone.
- Free disk space, which depends on the size of the PDB you're creating or cloning.
What This Guide Covers
Jump to any section directly:
- Creating a clone PDB from an existing PDB
- Creating a blank PDB
- Creating a clone PDB from an existing PDB in a different container database
- Renaming a PDB
- Dropping a PDB
Clone the Existing PDB
Cloning creates a full, independent copy of an existing PDB, datafiles included. Here we clone a new PDB named TESTPDB from the existing ORCLPDB. The precheck below captures size, object validity, and row counts so you have a baseline to confirm the clone matches its source afterward.
Take precheck:
SQL> show pdbs
CON_ID CON_NAME OPEN MODE RESTRICTED
---------- ------------------------------ ---------- ----------
2 PDB$SEED READ ONLY NO
3 ORCLPDB READ WRITE NO
SQL> select name from v$datafile where con_id=3;
NAME
--------------------------------------------------
/u01/app/oracle/oradata/ORCL/orclpdb/system01.dbf
/u01/app/oracle/oradata/ORCL/orclpdb/sysaux01.dbf
/u01/app/oracle/oradata/ORCL/orclpdb/undotbs01.dbf
/u01/app/oracle/oradata/ORCL/orclpdb/users01.dbf
/u01/app/oracle/oradata/ORCL/orclpdb/users02.dbf
/u01/app/oracle/oradata/ORCL/orclpdb/OGGPDB01.dbf
/u01/app/oracle/oradata/ORCL/orclpdb/audit_data01.dbf
7 rows selected.
SQL> select name from v$tempfile where con_id=3;
NAME
--------------------------------------------------
/u01/app/oracle/oradata/ORCL/orclpdb/temp01.dbf
SQL> alter session set container=ORCLPDB;
Session altered.
SQL> select sum(BYTES)/1024/1024/1024 as "Size(GB)" from dba_data_files;
Size(GB)
----------
2.43359375
SQL> select sum(BYTES)/1024/1024/1024 as "Size(GB)" from dba_segments;
Size(GB)
----------
1.11358643
SQL> select status,count(*) from dba_objects group by status;
STATUS COUNT(*)
------- ----------
VALID 73176
INVALID 12
SQL> select USERNAME,ACCOUNT_STATUS,EXPIRY_DATE,PROFILE from dba_users where oracle_maintained='N';
USERNAME ACCOUNT_STATUS EXPIRY_DA PROFILE
------------------------------ -------------------------------- --------- --------------------
PDBADMIN OPEN 21-JUL-25 DEFAULT
C##CLONE OPEN DEFAULT
CDBTEST OPEN DEFAULT
TESTAUDIT OPEN 19-FEB-26 DEFAULT
C##TEST OPEN DEFAULT
TEST OPEN 26-NOV-25 DEFAULT
C##OGG OPEN DEFAULT
OGG OPEN 29-NOV-25 DEFAULT
8 rows selected.
SQL> select count(*) from test.COMPANY;
COUNT(*)
----------
7
Create directory for PDB datafiles:
[oracle@orcl ~]$ mkdir -p /u01/app/oracle/oradata/ORCL/testpdb
Clone the PDB:
SQL> show pdbs
CON_ID CON_NAME OPEN MODE RESTRICTED
---------- ------------------------------ ---------- ----------
2 PDB$SEED READ ONLY NO
3 ORCLPDB READ WRITE NO
SQL> CREATE PLUGGABLE DATABASE TESTPDB from ORCLPDB
FILE_NAME_CONVERT=('/u01/app/oracle/oradata/ORCL/orclpdb','/u01/app/oracle/oradata/ORCL/testpdb');
Pluggable database created.
SQL> show pdbs
CON_ID CON_NAME OPEN MODE RESTRICTED
---------- ------------------------------ ---------- ----------
2 PDB$SEED READ ONLY NO
3 ORCLPDB READ WRITE NO
4 TESTPDB MOUNTED
SQL> alter PLUGGABLE DATABASE TESTPDB open;
Pluggable database altered.
SQL> show pdbs
CON_ID CON_NAME OPEN MODE RESTRICTED
---------- ------------------------------ ---------- ----------
2 PDB$SEED READ ONLY NO
3 ORCLPDB READ WRITE NO
4 TESTPDB READ WRITE NO
Take postcheck:
SQL> show pdbs
CON_ID CON_NAME OPEN MODE RESTRICTED
---------- ------------------------------ ---------- ----------
2 PDB$SEED READ ONLY NO
3 ORCLPDB READ WRITE NO
4 TESTPDB READ WRITE NO
SQL> set lines 333 pages 333
SQL> col name for a50
SQL> select name from v$datafile where con_id=4;
NAME
--------------------------------------------------
/u01/app/oracle/oradata/ORCL/testpdb/system01.dbf
/u01/app/oracle/oradata/ORCL/testpdb/sysaux01.dbf
/u01/app/oracle/oradata/ORCL/testpdb/undotbs01.dbf
/u01/app/oracle/oradata/ORCL/testpdb/users01.dbf
/u01/app/oracle/oradata/ORCL/testpdb/users02.dbf
/u01/app/oracle/oradata/ORCL/testpdb/OGGPDB01.dbf
/u01/app/oracle/oradata/ORCL/testpdb/audit_data01.dbf
7 rows selected.
SQL> select name from v$tempfile where con_id=4;
NAME
--------------------------------------------------
/u01/app/oracle/oradata/ORCL/testpdb/temp01.dbf
SQL> alter session set container=TESTPDB;
Session altered.
SQL> show pdbs
CON_ID CON_NAME OPEN MODE RESTRICTED
---------- ------------------------------ ---------- ----------
4 TESTPDB READ WRITE NO
SQL> select sum(BYTES)/1024/1024/1024 as "Size(GB)" from dba_data_files;
Size(GB)
----------
2.43359375
SQL> select sum(BYTES)/1024/1024/1024 as "Size(GB)" from dba_segments;
Size(GB)
----------
1.11358643
SQL> select status,count(*) from dba_objects group by status;
STATUS COUNT(*)
------- ----------
VALID 73263
INVALID 12
SQL> select USERNAME,ACCOUNT_STATUS,EXPIRY_DATE,PROFILE from dba_users where oracle_maintained='N';
USERNAME ACCOUNT_STATUS EXPIRY_DA PROFILE
------------------------------ -------------------------------- --------- --------------------
PDBADMIN OPEN 21-JUL-25 DEFAULT
C##CLONE OPEN DEFAULT
CDBTEST OPEN DEFAULT
TESTAUDIT OPEN 19-FEB-26 DEFAULT
C##TEST OPEN DEFAULT
TEST OPEN 26-NOV-25 DEFAULT
C##OGG OPEN DEFAULT
OGG OPEN 29-NOV-25 DEFAULT
8 rows selected.
SQL> select count(*) from test.COMPANY;
COUNT(*)
----------
7
The matching row counts and object-status figures between the precheck and postcheck confirm TESTPDB is a faithful copy of ORCLPDB — same data, same schema state, just under a new PDB name and its own datafiles.
Blank PDB Creation
Unlike cloning, a blank PDB starts empty and is created from the PDB$SEED template rather than copying an existing PDB's data.
Create directory for PDB datafiles.
[oracle@orcl ~]$ mkdir -p /u01/app/oracle/oradata/ORCL/testpdb2
Create blank PDB:
SQL> show pdbs
CON_ID CON_NAME OPEN MODE RESTRICTED
---------- ------------------------------ ---------- ----------
2 PDB$SEED READ ONLY NO
3 ORCLPDB READ WRITE NO
4 TESTPDB READ WRITE NO
SQL>
SQL> CREATE PLUGGABLE DATABASE TESTPDB2 ADMIN USER pdb_admin IDENTIFIED BY YourP@ssw0rd
FILE_NAME_CONVERT=('/u01/app/oracle/oradata/ORCL/pdbseed','/u01/app/oracle/oradata/ORCL/testpdb2');
Pluggable database created.
SQL> show pdbs
CON_ID CON_NAME OPEN MODE RESTRICTED
---------- ------------------------------ ---------- ----------
2 PDB$SEED READ ONLY NO
3 ORCLPDB READ WRITE NO
4 TESTPDB READ WRITE NO
5 TESTPDB2 MOUNTED
SQL>
SQL> ALTER PLUGGABLE DATABASE testpdb2 OPEN;
Warning: PDB altered with errors.
Check PDB violations:
SQL> set lines 333 pages 333
SQL> col NAME for a20
SQL> col CAUSE for a30
SQL> col MESSAGE for a70
SQL> select name,cause,type,message,status,action from PDB_PLUG_IN_VIOLATIONS where name='PROD';
NAME CAUSE TYPE MESSAGE STATUS
--------- ------------- ------ ----------------------------------------------------------------------------- ---------
TESTPDB2 Sync Failure ERROR Sync PDB failed with ORA-959 during 'alter user c##ogg quota unlimited on OGG'. PENDING
We need to create one tablespace OGG for user c##ogg as this database is being used for Golden Gate.
Then try to open the PDB:
SQL> ALTER SESSION SET CONTAINER=testpdb2;
Session altered.
SQL> CREATE TABLESPACE ogg
DATAFILE '/u01/app/oracle/oradata/ORCL/testpdb2/ogg01.dbf'
SIZE 100M AUTOEXTEND ON;
Tablespace created.
SQL> ALTER SESSION SET CONTAINER=CDB$ROOT;
Session altered.
SQL> ALTER PLUGGABLE DATABASE testpdb2 close immediate;
Pluggable database altered.
SQL> ALTER PLUGGABLE DATABASE testpdb2 OPEN;
Pluggable database altered.
SQL> show pdbs
CON_ID CON_NAME OPEN MODE RESTRICTED
---------- ------------------------------ ---------- ----------
2 PDB$SEED READ ONLY NO
3 ORCLPDB READ WRITE NO
4 TESTPDB READ WRITE NO
5 TESTPDB2 READ WRITE NO
SQL> ALTER PLUGGABLE DATABASE testpdb2 save state;
Pluggable database altered.
The ORA-959 violation here is a genuinely useful troubleshooting scenario: because PDB$SEED doesn't carry the OGG tablespace this environment's common users expect, the new PDB fails to sync until that tablespace is created inside it. Creating ogg and cycling the PDB closed and open again resolves the violation, as the final clean show pdbs confirms.
Clone the Existing PDB via Database Link
You can also clone a PDB from a different container database entirely, using a database link instead of a local source. This example clones ORCLPDB from source container ORCLDC into target container TESTDB.
| Source | ORCLDC |
|---|---|
| Target | TESTDB |
Create a user in source CDB database and provide the required grants:
SQL> def
DEFINE _DATE = "11-AUG-25" (CHAR)
DEFINE _CONNECT_IDENTIFIER = "orcldc" (CHAR)
DEFINE _USER = "SYS" (CHAR)
DEFINE _PRIVILEGE = "AS SYSDBA" (CHAR)
DEFINE _SQLPLUS_RELEASE = "1927000000" (CHAR)
DEFINE _EDITOR = "vi" (CHAR)
DEFINE _O_VERSION = "Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.27.0.0.0" (CHAR)
DEFINE _O_RELEASE = "1927000000" (CHAR)
SQL> show pdbs
CON_ID CON_NAME OPEN MODE RESTRICTED
---------- ------------------------------ ---------- ----------
2 PDB$SEED READ ONLY NO
3 ORCLPDB READ WRITE NO
SQL> CREATE USER c##clone IDENTIFIED BY YourP@ssw0rd;
User created.
SQL> GRANT CREATE PLUGGABLE DATABASE TO c##clone CONTAINER=ALL;
Grant succeeded.
Create a public database link in target with source userr & its TNS alias:
SQL> def
DEFINE _DATE = "11-AUG-25" (CHAR)
DEFINE _CONNECT_IDENTIFIER = "testdb" (CHAR)
DEFINE _USER = "SYS" (CHAR)
DEFINE _PRIVILEGE = "AS SYSDBA" (CHAR)
DEFINE _SQLPLUS_RELEASE = "1927000000" (CHAR)
DEFINE _EDITOR = "vi" (CHAR)
DEFINE _O_VERSION = "Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.27.0.0.0" (CHAR)
DEFINE _O_RELEASE = "1927000000" (CHAR)
SQL>
SQL> show pdbs
CON_ID CON_NAME OPEN MODE RESTRICTED
---------- ------------------------------ ---------- ----------
2 PDB$SEED READ ONLY NO
3 TESTPDB READ WRITE NO
SQL> create database link PDB_LINK connect to c##clone identified by YourP@ssw0rd using 'orclpdb';
Database link created.
SQL> select * from dual@PDB_LINK;
D
-
X
Create directory for PDB datafiles:
[oracle@orcl ~]$ mkdir -p /u01/app/oracle/oradata/TESTDB/orclpdb
Now create the PDB via database link and open it:
SQL> create pluggable database ORCLPDB from ORCLPDB@PDB_LINK
file_name_convert=('/u01/app/oracle/oradata/ORCL/orclpdb','/u01/app/oracle/oradata/TESTDB/orclpdb');
Pluggable database created.
SQL> show pdbs
CON_ID CON_NAME OPEN MODE RESTRICTED
---------- ------------------------------ ---------- ----------
2 PDB$SEED READ ONLY NO
3 TESTPDB READ WRITE NO
5 ORCLPDB MOUNTED
SQL> alter pluggable database ORCLPDB open;
Pluggable database altered.
SQL> alter pluggable database ORCLPDB save state;
Pluggable database altered.
SQL> show pdbs
CON_ID CON_NAME OPEN MODE RESTRICTED
---------- ------------------------------ ---------- ----------
2 PDB$SEED READ ONLY NO
3 TESTPDB READ WRITE NO
5 ORCLPDB READ WRITE NO
The select * from dual@PDB_LINK check matters here: it confirms the database link actually connects before you invest time in the clone itself, so a bad TNS alias or credential surfaces immediately rather than mid-clone.
Rename the PDB
There are two ways to rename a PDB, and they differ in one important respect: whether the underlying datafiles get renamed along with it.
Method 1: Renaming via global_name
This is the quicker method, but it only renames the PDB itself — the datafiles keep their original names and paths. Here TESTPDB is renamed to MYPDB.
Here we will rename TESTPDB to MYPDB:
SQL> show pdbs
CON_ID CON_NAME OPEN MODE RESTRICTED
---------- ------------------------------ ---------- ----------
2 PDB$SEED READ ONLY NO
3 ORCLPDB READ WRITE NO
4 TESTPDB READ WRITE NO
SQL>
SQL> ALTER PLUGGABLE DATABASE testpdb CLOSE IMMEDIATE;
Pluggable database altered.
SQL> alter pluggable database TESTPDB open restricted;
Pluggable database altered.
SQL> select name, open_mode, restricted from v$pdbs;
NAME OPEN_MODE RES
-------------------- ---------- ---
PDB$SEED READ ONLY NO
ORCLPDB READ WRITE NO
TESTPDB READ WRITE YES
SQL> alter session set container=TESTPDB;
Session altered.
SQL> alter pluggable database rename global_name to mypdb;
Pluggable database altered.
SQL> select name, open_mode, restricted from v$pdbs;
NAME OPEN_MODE RES
-------------------- ---------- ---
MYPDB READ WRITE YES
SQL> ALTER PLUGGABLE DATABASE MYPDB CLOSE IMMEDIATE;
Pluggable database altered.
SQL> ALTER PLUGGABLE DATABASE MYPDB open;
Pluggable database altered.
SQL> ALTER PLUGGABLE DATABASE MYPDB save state;
Pluggable database altered.
SQL> show pdbs
CON_ID CON_NAME OPEN MODE RESTRICTED
---------- ------------------------------ ---------- ----------
4 MYPDB READ WRITE NO
SQL>
SQL> conn /as sysdba
Connected.
SQL>
SQL> show pdbs
CON_ID CON_NAME OPEN MODE RESTRICTED
---------- ------------------------------ ---------- ----------
2 PDB$SEED READ ONLY NO
3 ORCLPDB READ WRITE NO
4 MYPDB READ WRITE NO
SQL>
Opening the PDB in restricted mode before the rename prevents application sessions from connecting mid-change, and the brief close/open cycle afterward ensures the new name is fully in effect. Note that this method does not change the location or name of the datafiles — if you want to rename the datafiles too, use the ALTER DATABASE RENAME FILE command, or use the Unplug and Plug method below, which renames both the PDB and its datafiles in one pass.
Method 2: Unplug and Plug (Renames Datafiles Too)
This method drops the PDB from its current container and re-creates it from an XML metadata file, using FILE_NAME_CONVERT to place the datafiles under their new name at the same time.
Create directory for datafiles:
[oracle@orcl ~]$ mkdir -p /u01/app/oracle/oradata/ORCL/mypdb
Fetch TESTPDB details:
SQL> show pdbs
CON_ID CON_NAME OPEN MODE RESTRICTED
---------- ------------------------------ ---------- ----------
2 PDB$SEED READ ONLY NO
3 ORCLPDB READ WRITE NO
4 TESTPDB READ WRITE NO
SQL>
SQL> select name from v$datafile where con_id=4;
NAME
--------------------------------------------------
/u01/app/oracle/oradata/ORCL/testpdb/system01.dbf
/u01/app/oracle/oradata/ORCL/testpdb/sysaux01.dbf
/u01/app/oracle/oradata/ORCL/testpdb/undotbs01.dbf
/u01/app/oracle/oradata/ORCL/testpdb/users01.dbf
/u01/app/oracle/oradata/ORCL/testpdb/users02.dbf
/u01/app/oracle/oradata/ORCL/testpdb/OGGPDB01.dbf
/u01/app/oracle/oradata/ORCL/testpdb/audit_data01.dbf
7 rows selected.
SQL> select name from v$tempfile where con_id=4;
NAME
--------------------------------------------------
/u01/app/oracle/oradata/ORCL/testpdb/temp01.dbf
Unplug the PDB:
SQL> show pdbs
CON_ID CON_NAME OPEN MODE RESTRICTED
---------- ------------------------------ ---------- ----------
2 PDB$SEED READ ONLY NO
3 ORCLPDB READ WRITE NO
4 TESTPDB READ WRITE NO
SQL> ALTER PLUGGABLE DATABASE TESTPDB CLOSE IMMEDIATE;
Pluggable database altered.
SQL> ALTER PLUGGABLE DATABASE TESTPDB UNPLUG INTO '/home/oracle/testpdb.xml';
Pluggable database altered.
Drop the PDB & keep datafiles:
SQL> DROP PLUGGABLE DATABASE TESTPDB KEEP DATAFILES;
Pluggable database dropped.
SQL> show pdbs
CON_ID CON_NAME OPEN MODE RESTRICTED
---------- ------------------------------ ---------- ----------
2 PDB$SEED READ ONLY NO
3 ORCLPDB READ WRITE NO
Now Plug the PDB & rename datafiles:
SQL> CREATE PLUGGABLE DATABASE MYPDB
USING '/home/oracle/testpdb.xml'
FILE_NAME_CONVERT =
(
'/u01/app/oracle/oradata/ORCL/testpdb',
'/u01/app/oracle/oradata/ORCL/mypdb'
);
Pluggable database created.
SQL> show pdbs
CON_ID CON_NAME OPEN MODE RESTRICTED
---------- ------------------------------ ---------- ----------
2 PDB$SEED READ ONLY NO
3 ORCLPDB READ WRITE NO
6 MYPDB MOUNTED
SQL>
Verify the details & open the PDB:
SQL> select name from v$datafile where con_id=6;
NAME
------------------------------------------------------------
/u01/app/oracle/oradata/ORCL/mypdb/system01.dbf
/u01/app/oracle/oradata/ORCL/mypdb/sysaux01.dbf
/u01/app/oracle/oradata/ORCL/mypdb/undotbs01.dbf
/u01/app/oracle/oradata/ORCL/mypdb/users01.dbf
/u01/app/oracle/oradata/ORCL/mypdb/users02.dbf
/u01/app/oracle/oradata/ORCL/mypdb/OGGPDB01.dbf
/u01/app/oracle/oradata/ORCL/mypdb/audit_data01.dbf
7 rows selected.
SQL> select name from v$tempfile where con_id=6;
NAME
------------------------------------------------------------
/u01/app/oracle/oradata/ORCL/mypdb/temp01.dbf
SQL> alter pluggable DATABASE MYPDB open;
Pluggable database altered.
SQL> alter pluggable DATABASE MYPDB save state;
Pluggable database altered.
SQL> show pdbs
CON_ID CON_NAME OPEN MODE RESTRICTED
---------- ------------------------------ ---------- ----------
2 PDB$SEED READ ONLY NO
3 ORCLPDB READ WRITE NO
6 MYPDB READ WRITE NO
Comparing the datafile paths before and after — testpdb versus mypdb — confirms this method renamed both the PDB and its physical files, unlike Method 1 above. The KEEP DATAFILES option on the drop step is what preserves the files for the subsequent plug-in step; using INCLUDING DATAFILES instead would delete them.
Drop the Existing PDB
Finally, dropping a PDB permanently removes it. INCLUDING DATAFILES deletes the underlying files as well, so make sure you have the RMAN backup from the prerequisites before running this against anything other than a disposable test PDB.
SQL> show pdbs
CON_ID CON_NAME OPEN MODE RESTRICTED
---------- ------------------------------ ---------- ----------
2 PDB$SEED READ ONLY NO
3 ORCLPDB READ WRITE NO
6 MYPDB READ WRITE NO
SQL> ALTER PLUGGABLE DATABASE MYPDB CLOSE IMMEDIATE;
Pluggable database altered.
SQL> DROP PLUGGABLE DATABASE MYPDB INCLUDING DATAFILES;
Pluggable database dropped.
SQL> show pdbs
CON_ID CON_NAME OPEN MODE RESTRICTED
---------- ------------------------------ ---------- ----------
2 PDB$SEED READ ONLY NO
3 ORCLPDB READ WRITE NO
SQL>
The PDB must be closed before it can be dropped, which is why the CLOSE IMMEDIATE step comes first. The final show pdbs confirms MYPDB is gone and only the original ORCLPDB remains.
Thank you for reading!
I hope this content has been helpful to you. Your feedback and suggestions are always welcome. Feel free to leave a comment or reach out with any queries.
Abhishek Shrivastava
Oracle DBA with hands-on experience managing production Data Guard, RAC, GoldenGate, and APEX environments. I write practical, tested installation and troubleshooting guides based on real deployment work.
Comments
Post a Comment