As a database administrator, you might come across creating a pluggable database, here is the method to create a pluggable database (PDB), Follow the below steps to create a pluggable database using the default pluggable database PDB$SEED
Step 1: Connect to the Container database using admin privileges
[oracle@localhost
Desktop]$ sqlplus sys/***@free as sysdba
SQL*Plus:
Release 23.0.0.0.0 - Developer-Release on Sun Mar 24 06:50:54 2024
Version
23.2.0.0.0
Copyright
(c) 1982, 2023, Oracle. All rights reserved.
Connected
to:
Oracle
Database 23c Free, Release 23.0.0.0.0 - Developer-Release
Version
23.2.0.0.0
Step 2 : Check
existing container databases including pluggable databases
SQL>
show pdbs
CON_ID
CON_NAME OPEN
MODE RESTRICTED
----------
------------------------------ ---------- ----------
2
PDB$SEED READ
ONLY NO
3
FREEPDB1 READ
WRITE NO
Here we can see one default pluggable database and
one application pdb (freepdb1)
Check the container id of each container database,
this will help to identify datafiles/temp file etc associated with a particular
container database
SQL>
select name,con_id,dbid from v$containers;
NAME CON_ID DBID
--------------------
---------- ----------
CDB$ROOT 1
1408182090
PDB$SEED 2 871956951 <--
create a new pdb using this
FREEPDB1 3
1423580300
Check the datafile and temp file associated with
PDB$SEED to identify the path of datafiles for the new database
SQL>
select name from v$datafile where con_id=2;
NAME
-----------------------------------------------------------------
/opt/oracle/oradata/FREE/pdbseed/system01.dbf
/opt/oracle/oradata/FREE/pdbseed/sysaux01.dbf
/opt/oracle/oradata/FREE/pdbseed/undotbs01.dbf
SQL>
select name from v$tempfile where con_id=2;
NAME
-----------------------------------------------------------------
/opt/oracle/oradata/FREE/pdbseed/temp01.dbf
Step 3: Create
a directory for new pluggable database datafiles
[oracle@localhost
Desktop]$
[oracle@localhost
Desktop]$ mkdir -p /opt/oracle/oradata/FREE/pdb2
[oracle@localhost
Desktop]$
Step 4 : Create
a pluggable database
SQL>
create pluggable database PDB2 admin user pdb2_user identified by
"test123"
default
tablespace PDB2_DEFAULT
datafile
'/opt/oracle/oradata/FREE/pdb2/PDB2_DEFAULT.dbf' size 100m autoextend on
file_name_convert=('/opt/oracle/oradata/FREE/pdbseed','/opt/oracle/oradata/FREE/pdb2'); 2 3 4
Pluggable
database created.
SQL>
show pdbs
CON_ID
CON_NAME OPEN
MODE RESTRICTED
----------
------------------------------ ---------- ----------
2
PDB$SEED READ
ONLY NO
3
FREEPDB1 READ
WRITE NO
4
PDB2 MOUNTED
Step 5:
Open pluggable database
SQL>
alter pluggable database PDB2 open;
Pluggable
database altered.
SQL>
show pdbs
CON_ID
CON_NAME OPEN
MODE RESTRICTED
----------
------------------------------ ---------- ----------
2
PDB$SEED READ
ONLY NO
3
FREEPDB1 READ
WRITE NO
4
PDB2 READ
WRITE NO
Here we can see PDB2 pluggable database has been created successfully.