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 fulfill 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
RAC DB
For a RAC database, you have to close the PDB on all instances. Which means, you should additionally specify INSTANCES=ALL clause.
SQL> alter pluggable database ORCLPDB2 close immediate instances=all;
It may cause ORA-65025, if you close the PDB incompletely in a RAC database.
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.
To create a new pluggable database, there're more consideration.