Drop Pluggable Database

Use the DROP PLUGGABLE DATABASE statement to drop a pluggable database (PDB).

When you drop a PDB, the control file of the multitenant container database (CDB) is modified to remove all references to the dropped PDB and its data files. Archived logs and backups associated with the dropped PDB are not deleted. You can delete them using Oracle Recovery Manager (RMAN), or you can retain them in case you subsequently want to perform point-in-time recovery of the PDB.

Log in to the root container.

SQL> show con_name

CON_NAME
------------------------------
CDB$ROOT







List the Pluggable databases exists in the Multitenant container database using "show pdbs" command, here in this example, I am going to drop PDB3 Pluggable Database.







SQL> show pdbs

    CON_ID CON_NAME              OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
     2 PDB$SEED              READ ONLY  NO
     3 PDB3               MOUNTED







To drop a Pluggable Database from your container database, you run the DROP PLUGGABLE DATABASE command being logged into the root container with SYSDBA privilege.




Here in my demo presentation, I am using the INCLUDING DATAFILES clause with DROP PLUGGABLE DATABASE command as I don't want to preserve the data files for the PDB being dropped at all.







SQL> drop pluggable database pdb3 including datafiles;

Pluggable database dropped.







Note : There is another option to drop the Pluggable Database with KEEP DATAFILES clause - use this option if you want to preserve the data files for the PDB being dropped and later you have the luck to the point in time recovery of the dropped PDB since its associated backup files exists out there. Only temp files for the PDB will be deleted from OS file system since that would be no longer required in the incomplete recovery of that Pluggable Database.




From the Container database(CDB) alert log, you can see that all associated data files for the Pluggable Database(PDB) being dropped are deleted from the OS file system permanently.







Sun Aug 19 20:28:55 2018
Deleted file /u01/app/oracle/oradata/Win2Linux_data_D-CDB3_I-680903414_TS-USERS_FNO-9_0htafbrg.dbf
.
.
Deleted file /u01/app/oracle/oradata/Win2Linux_data_D-CDB3_I-680903414_TS-SYSAUX_FNO-8_0dtafbpb.dbf
Deleted file /u01/app/oracle/oradata/Win2Linux_data_D-CDB3_I-680903414_TS-SYSTEM_FNO-7_0ftafbqu.dbf
Completed: drop pluggable database pdb3 including datafiles
Sun Aug 19 20:29:52 2018







Here we can validate the Pluggable databases again using the SQL>show pdbs command and can see that PDB3 is dropped now.







SQL> show pdbs

    CON_ID CON_NAME              OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
     2 PDB$SEED              READ ONLY  NO
SQL>