DROP Pluggable Database

Theory from Oracle Doc:


Drop a PDB when you want to move the PDB to a new CDB or when you no longer need it. When you drop a PDB, the control file of the CDB is modified to eliminate all references to the dropped PDB. Archived redo log files and backups associated with the PDB are not removed, but you can use Oracle Recovery Manager (RMAN) to remove them.

When dropping a PDB, you can either keep or delete the PDB's data files by using one of the following clauses of the DROP PLUGGABLE DATABASE statement:

• KEEP DATAFILES, the default, retains the data files. The PDB temp file is removed even when KEEP DATAFILES is specified because the temp file is no longer needed. When KEEP DATAFILES is specified, the PDB must be unplugged.

• INCLUDING DATAFILES removes the data files from disk. If a PDB was created with the SNAPSHOT COPY clause, then you must specify INCLUDING DATAFILES when you drop the PDB.


Prerequisites

The following prerequisites must be met:

• The PDB must be in mounted mode, or it must be unplugged.
• The current user must have SYSDBA or SYSOPER administrative privilege, and the privilege must be either commonly granted or locally granted in the PDB. The user must exercise the privilege using AS SYSDBA or AS SYSOPER at connect time.



To drop a PDB:

1 - In SQL*Plus, ensure that the current container is the CDB root, or, for an application PDB, the application root that contains the application PDB. If the PDB is plugged into the CDB root, then the current container must be the CDB root. If the PDB is plugged into an application root, then the current container must be that application root or the CDB root. If you are dropping an application container, then the current container must be the CDB root, and the application container must not have any application PDBs plugged into it.

2 - Run the DROP PLUGGABLE DATABASE statement and specify the PDB to drop.

Example : Dropping PDB salespdb While Keeping Its Data Files

SQL> DROP PLUGGABLE DATABASE pdb_name KEEP DATAFILES;

Example : Dropping PDB salespdb and Its Data Files

SQL> DROP PLUGGABLE DATABASE pdb_name INCLUDING DATAFILES;

Demonstration: 

We many DEV Pluggable databases that we created in the previous article - Click here to read how to create many PDBs in bulk using OEM.

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 PDB_DEV2                       READ WRITE NO
         6 PDB_DEV3                       READ WRITE NO
         7 PDB_DEV4                       READ WRITE NO
         8 PDB_DEV5                       READ WRITE NO
        10 PDB_DEV1                       READ WRITE NO




Closing/shutting down all PDBs that needs to be dropped:

SQL> alter pluggable database PDB_DEV1 close;

Pluggable database altered.

SQL> alter pluggable database PDB_DEV2 close;

Pluggable database altered.

SQL> alter pluggable database PDB_DEV3 close;

Pluggable database altered.

SQL> alter pluggable database PDB_DEV4 close;

Pluggable database altered.

SQL> alter pluggable database PDB_DEV5 close;

Pluggable database altered.

Validate if all are in MOUNTED state now.

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 PDB_DEV2                       MOUNTED
         6 PDB_DEV3                       MOUNTED
         7 PDB_DEV4                       MOUNTED
         8 PDB_DEV5                       MOUNTED
        10 PDB_DEV1                       MOUNTED




Drop the PDBs with clause "including datafiles"

SQL> drop pluggable database PDB_DEV1 including datafiles;

Pluggable database dropped.

SQL> drop pluggable database PDB_DEV2 including datafiles;

Pluggable database dropped.

SQL> drop pluggable database PDB_DEV3 including datafiles;

Pluggable database dropped.

SQL> drop pluggable database PDB_DEV4 including datafiles;

Pluggable database dropped.

SQL> drop pluggable database PDB_DEV5 including datafiles;

Pluggable database dropped.

Validate if PDBs are dropped and gone successfully.

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>




Hope it helps, subscribe to this blog to stay updated on latest Oracle Technologies and new articles.

Twitter : @rajsoft8899
Linkedin : https://www.linkedin.com/in/raj-kumar-kushwaha-5a289219/