Oracle : Drop Pluggable Database (PDB)
- Dropping pluggable database is similar to dropping any other regular database, you have two options while dropping pluggable database related to its datafiles
- Dropping PDB including datafiles
- Dropping PDB keeping datafiles
Before dropping pluggable database we can check PDB status by
a. 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
SQL>
b. select con_id, name,open_mode from v$containers;
SQL> set lin 1000
SQL> col name format a20
SQL> select con_id, name,open_mode from v$containers;
CON_ID NAME OPEN_MODE
---------- -------------------- ----------
1 CDB$ROOT READ WRITE
2 PDB$SEED READ ONLY
3 PDB1 READ WRITE
4 PDB2 READ WRITE
5 PDB3 READ WRITE
--> We will drop PDB3 now. Before dropping we need to close database.
SQL> alter pluggable database PDB3 close immediate;
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 MOUNTED
SQL>
if our database is multi node RAC then we can use instances=all option.
SQL> alter pluggable database PDB3 close immediate instances=all;
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
SQL> alter pluggable database PDB3 close immediate instances=all;
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 MOUNTED
SQL>
--> Dropping Database
SQL> drop pluggable database PDB3 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
SQL>
SQL> drop pluggable database PDB3 keep datafiles; --> with keep datafiles option
Pluggable database dropped.
