- Introduction: In this article, we will create a PDB manually using SQL command in different ways. We will also see how to rename and drop the PDB manually using SQL.
- Prerequisites:
- RMAN backup for rollback purpose.
- Disk space requirement is depending upon the PDB size.
- Summary of activities: We will do below acivities with PDB.
- Creating a clone PDB from an existing PDB.
- Creating a blank PDB.
- Creating a clone PDB from an existing PDB of different container database.
- Renaming a PDB.
- Dropping a PDB.
- Now let's proceed to perform manual PDB creation step by step:
Clone the existing PDB.
1. We will create a clone PDB "TESTPDB" from existing PDB "ORCLPDB".
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
Blank PDB creation.
2. Now we will create a blank PDB. The blank PDB will use the PDB$SEED template for its datafiles.
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 Oracle#123
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.
Clone the existing PDB via Database Link.
3. We can also clone the the existing PDB from a different database via database link. So let's procced for that.Environemnt:
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 oracle#123;
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 oracle#123 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
Rename the PDB.
4. Now we will rename the existing PDB. For this we need to modify global_name parameter for that particular PDB. So let's procced for that.
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>
Further if you want to rename the datafile while renaming the PDB, you can use Unplug & Plug method which is described below.
5. Here we will use Unplug & Plug method for renaming the PDB.
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
Drop the existing PDB.
6. Now we will drop the 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>
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
Comments
Post a Comment