Create Pluggable Database (PDB) Manually Using SQL Prompt Oracle 19c !!
4 min readUse the CREATE PLUGGABLE DATABASE statement to create a pluggable database (PDB).
This statement enables you to perform the following tasks:
- Create a PDB by using the seed as a template. Use the
create_pdb_from_seedclause to create a PDB by using the seed in the multitenant container database (CDB) as a template. The files associated with the seed are copied to a new location and the copied files are then associated with the new PDB.
Prerequisites
You must be connected to a CDB. The CDB must be open and in READ WRITE mode.
To create a PDB or an application container, the current container must be the root and you must have the CREATE PLUGGABLE DATABASE system privilege, granted commonly.
Display the Current Container
Use the following command to check the name of the container you are connected to:
SQL> SHOW CON_NAME
CON_NAME
------------------------------
CDB$ROOT
List All Pluggable Databases (PDBs) within the CDB
Set lin 200 pages 200
Col NAME for a30
SELECT NAME, CON_ID, DBID, CON_UID, GUID
FROM V$CONTAINERS
ORDER BY CON_ID;
NAME CON_ID DBID CON_UID GUID
------------------------------ ---------- ---------- ---------- --------------------------------
CDB$ROOT 1 2485335327 1 86B637B62FDF7A65E053F706E80A27CA
PDB$SEED 2 3301369736 3301369736 28BFBEE7CADB1BA5E065000000000001
TESTPD 3 1561367893 1561367893 28BFF7DCC32F2961E065000000000001
C110 4 694902061 694902061 28C579C4ECD41A3EE065000000000001
B203 5 3116873177 3116873177 28C5518D2B2913CCE065000000000001
D220 6 2380771838 2380771838 28C579C4ECD71A3EE065000000000001
E123 7 1647378829 1647378829 28C579C4ECDA1A3EE065000000000001
7 rows selected.
Retrieve Container ID, Name, and Status of PDBs
SET lin 200 Pages 200
col PDB_NAME for a30
SELECT PDB_ID, PDB_NAME, STATUS
FROM DBA_PDBS
ORDER BY PDB_ID;
PDB_ID PDB_NAME STATUS
---------- ------------------------------ ----------
2 PDB$SEED NORMAL
3 TESTPD NORMAL
4 C110 NORMAL
5 B203 NORMAL
6 D220 NORMAL
7 E123 NEW
6 rows selected.
Finding the Open Mode of a PDB:
select name, cdb, con_id from v$database;
NAME CDB CON_ID
------------------------------ --- ----------
TEST YES 0
SQL>
SQL>
set lines 300 pages 300
col NAME format a30
select dbid,name,open_mode,TOTAL_SIZE/1024/1024 from v$pdbs;
DBID NAME OPEN_MODE TOTAL_SIZE/1024/1024
---------- ------------------------------ ---------- --------------------
3301369736 PDB$SEED READ ONLY 736
1561367893 TESTPD READ WRITE 991
694902061 C110 READ WRITE 746
3116873177 B203 READ WRITE 746
2380771838 D220 READ WRITE 746
1647378829 E123 MOUNTED 736
6 rows selected.
SQL>
SQL> show pdbs;
CON_ID CON_NAME OPEN MODE RESTRICTED
---------- ------------------------------ ---------- ----------
2 PDB$SEED READ ONLY NO
3 TESTPD READ WRITE NO
4 C110 READ WRITE NO
5 B203 READ WRITE NO
6 D220 READ WRITE NO
7 E123 MOUNTED
SQL>
select sys_context('USERENV','CON_NAME') FROM DUAL;
SYS_CONTEXT('USERENV','CON_NAME')
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
CDB$ROOT
col name format a20
select name, open_mode, restricted, open_time from v$pdbs;SQL>
NAME OPEN_MODE RES OPEN_TIME
-------------------- ---------- --- ---------------------------------------------------------------------------
PDB$SEED READ ONLY NO 08-DEC-24 03.05.22.312 PM +05:30
TESTPD READ WRITE NO 08-DEC-24 08.08.34.860 PM +05:30
C110 READ WRITE NO 08-DEC-24 09.40.13.401 PM +05:30
B203 READ WRITE NO 08-DEC-24 09.31.19.148 PM +05:30
D220 READ WRITE NO 08-DEC-24 09.41.50.817 PM +05:30
E123 MOUNTED 08-DEC-24 09.43.02.365 PM +05:30
6 rows selected.
Showing the Users in Multiple PDBs:
set lines 300 pages 300
COLUMN PDB_NAME FORMAT A15
COLUMN USERNAME FORMAT A30
SELECT p.PDB_ID, p.PDB_NAME, u.USERNAME
FROM DBA_PDBS p, CDB_USERS u
WHERE p.PDB_ID > 2 AND
p.PDB_ID = u.CON_ID
ORDER BY p.PDB_ID;
PDB_ID PDB_NAME USERNAME
---------- --------------- ------------------------------
3 TESTPD SYS
3 TESTPD SYSTEM
3 TESTPD XS$NULL
3 TESTPD LBACSYS
3 TESTPD XDB
3 TESTPD WMSYS
3 TESTPD GSMCATUSER
3 TESTPD ABC
3 TESTPD ABCD
3 TESTPD SYSKM
3 TESTPD ORDDATA
3 TESTPD ORACLE_OCM
3 TESTPD SYS$UMF
3 TESTPD SYSDG
3 TESTPD ORDSYS
4 C110 SYS
4 C110 SYSTEM
4 C110 C##ABCDE
4 C110 GSMCATUSER
4 C110 XS$NULL
4 C110 DBSNMP
4 C110 APPQOSSY
4 C110 C##ABCD
4 C110 CTXSYS
4 C110 OJVMSYS
4 C110 SI_INFORMTN_SCHEMA
4 C110 DVSYS
4 C110 DVF
4 C110 AUDSYS
4 C110 DIP
4 C110 ORDPLUGINS
4 C110 SYSDG
4 C110 ORDSYS
4 C110 WMSYS
5 B203 SYS
5 B203 SYSTEM
5 B203 C##ABCDE
5 B203 GSMCATUSER
5 B203 XS$NULL
5 B203 DBSNMP
Showing the Data Files for Each PDB in a CDB:
COLUMN PDB_ID FORMAT 999
COLUMN PDB_NAME FORMAT A8
COLUMN FILE_ID FORMAT 9999
COLUMN TABLESPACE_NAME FORMAT A10
COLUMN FILE_NAME FORMAT A60
SELECT p.PDB_ID, p.PDB_NAME, d.FILE_ID, d.TABLESPACE_NAME, d.FILE_NAME
FROM DBA_PDBS p, CDB_DATA_FILES d
WHERE p.PDB_ID = d.CON_ID
ORDER BY p.PDB_ID;
PDB_ID PDB_NAME FILE_ID TABLESPACE FILE_NAME
------ -------- ------- ---------- ------------------------------------------------------------
3 TESTPD 11 UNDOTBS1 /u01/app/oracle/oradata/TEST/testpd/undotbs01.dbf
3 TESTPD 10 SYSAUX /u01/app/oracle/oradata/TEST/testpd/sysaux01.dbf
3 TESTPD 9 SYSTEM /u01/app/oracle/oradata/TEST/testpd/system01.dbf
3 TESTPD 12 USERS /u01/app/oracle/oradata/TEST/testpd/users01.dbf
4 C110 16 SYSTEM /u01/app/oracle/oradata/TEST/c110/system01.dbf
4 C110 17 SYSAUX /u01/app/oracle/oradata/TEST/c110/sysaux01.dbf
4 C110 18 UNDOTBS1 /u01/app/oracle/oradata/TEST/c110/undotbs01.dbf
5 B203 13 SYSTEM /u01/app/oracle/oradata/TEST/b203/system01.dbf
5 B203 14 SYSAUX /u01/app/oracle/oradata/TEST/b203/sysaux01.dbf
5 B203 15 UNDOTBS1 /u01/app/oracle/oradata/TEST/b203/undotbs01.dbf
6 D220 19 SYSTEM /u01/app/oracle/oradata/TEST/d220/system01.dbf
6 D220 20 SYSAUX /u01/app/oracle/oradata/TEST/d220/sysaux01.dbf
6 D220 21 UNDOTBS1 /u01/app/oracle/oradata/TEST/d220/undotbs01.dbf
13 rows selected.
Create Pluggable Database :
SQL> create pluggable database c110 admin user scott identified by oracle FILE_NAME_CONVERT=('/u01/app/oracle/oradata/TEST/pdbseed','/u01/app/oracle/oradata/TEST/c110');
Pluggable database created.
SQL> show pdbs;
CON_ID CON_NAME OPEN MODE RESTRICTED
---------- ------------------------------ ---------- ----------
2 PDB$SEED READ ONLY NO
3 TESTPD READ WRITE NO
4 C110 MOUNTED
5 B203 READ WRITE NO
SQL> alter pluggable database c110 open;
Pluggable database altered.
SQL> show pdbs;
CON_ID CON_NAME OPEN MODE RESTRICTED
---------- ------------------------------ ---------- ----------
2 PDB$SEED READ ONLY NO
3 TESTPD READ WRITE NO
4 C110 READ WRITE NO
5 B203 READ WRITE NO
Hope it helped !! 🙂
