How to Drop a Pluggable Database (PDB)

DROP PLUGGABLE DATABASE

Since Pluggable Database (PDB) becomes more flexible than dedicated databases, we may create a PDB for some specific purpose, use it for a while, then drop the PDB. That how we complete the life cycle of a PDB.

Let's check current statues of PDBs.

SQL> conn / as sysdba
Connected.
SQL> show pdbs;

    CON_ID CON_NAME                       OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
         2 PDB$SEED                       READ ONLY  NO
         3 ORCLPDB1                       READ WRITE NO
         4 ORCLPDB2                       READ WRITE NO

Close PDB

We'd like to drop the last PDB, a newly created, testing purposed PDB. But before actually dropping it, we have to stop all activities of the PDB.

SQL> alter pluggable database ORCLPDB2 close immediate;

Pluggable database altered.

SQL> show pdbs;

    CON_ID CON_NAME                       OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
         2 PDB$SEED                       READ ONLY  NO
         3 ORCLPDB1                       READ WRITE NO
         4 ORCLPDB2                       MOUNTED

For a RAC database, you have to close the PDB on all nodes.

SQL> alter pluggable database ORCLPDB2 close immediate instances=all;

Drop PDB Including Files

To drop the PDB including the data files, we can do this.

SQL> drop pluggable database orclpdb2 including datafiles;

Pluggable database dropped.

All files are removed, but the directory is still there.

The space will be released, even though you're using ASM disk group.

Before Dropping PDB

[grid@primary01 grid]$ asmcmd lsdg
State    Type    Rebal  Sector  Logical_Sector  Block       AU  Total_MB  Free_MB  Req_mir_free_MB  Usable_file_MB  Offline_disks  Voting_files  Name
MOUNTED  EXTERN  N         512             512   4096  4194304  20971520  2365524                0         2365524              0             N  DATA/
...

After Dropping PDB

[grid@primary01 grid]$ asmcmd lsdg
State    Type    Rebal  Sector  Logical_Sector  Block       AU  Total_MB  Free_MB  Req_mir_free_MB  Usable_file_MB  Offline_disks  Voting_files  Name
MOUNTED  EXTERN  N         512             512   4096  4194304  20971520  8009472                0         8009472              0             N  DATA/
...

Drop PDB Keep Files

The default behavior of DROP PLUGGABLE DATABASE is to keep all data files. But doing this, you should unplug the PDB before dropping it in this mode:

SQL> alter pluggable database orclpdb2 unplug into '/oradata/orclpdb2.pdb';

Pluggable database altered.

Now, we can drop it.

SQL> drop pluggable database orclpdb2;

Pluggable database dropped.

Or explicitly keep data files.

SQL> drop pluggable database orclpdb2 keep datafiles;

Pluggable database dropped.

Leave a Reply

Your email address will not be published. Required fields are marked *