Oracle : Drop Pluggable Database (PDB) 19c

 

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.