Drop Pluggable Database in Oracle

Back to: Oracle DBA Tutorials

Drop Pluggable Database in Oracle with Examples

In this article, I am going to discuss Drop Pluggable Database in Oracle with Examples. Please read our previous article where we discussed Creating Pluggable Database using DBCA in Oracle with Examples.

Drop Pluggable Database in Oracle

Until now, we have learned how to create a pluggable database. We have created a pluggable database using the DBCA and using the create statement. Now, let us try to drop the pluggable databases that we created earlier. We have created these databases for training purposes or we created these databases to test the patch. But in real-time projects, we never drop the pluggable databases that contain information. Dropping a pluggable database is of two methods.

1. Dropping Pluggable Database using SQL
2. Dropping Pluggable Database using DBCA

Drop Pluggable Database using SQL:

We will drop the pluggable database using SQL statements. Go to the below folder and we can see the folder of pluggable databases. We have JAYAPDB1 and JAYAPDB2. Let us try to drop the pluggable database JAYAPDB2.

Open the terminal and connect to the database as “sqlplus / as sysdba”. Once connected to the database check the list of the pluggable databases present in the container database.

We can see the pluggable database JAYAPDB2 database is up and running. If we need to drop a database, we have to be in the CDB$ROOT database.

So, we are in the CDB$ROOT database. To drop a pluggable database there is a command to execute.

Command: DROP PLUGGABLE DATABASE JAYAPDB2 INCLUDING DATAFILES;

This is the command we use to drop the pluggable database. Let us try to run this command and check if we are getting an error.

We are getting an error saying that “ORA-65032: Pluggable database JAYAPDB2 is not closed on all instances”. So, before dropping any pluggable database we have to close the pluggable database.

Command: alter pluggable database JAYAPDB2 close;

Now, that the database is closed let us try to drop the pluggable database.

Command: DROP PLUGGABLE DATABASE JAYAPDB2 INCLUDING DATAFILES;

The pluggable database JAYAPDB2 is dropped. Let us go ahead and check whether the data files are present in the JAYAPDB2 folder or not. Go to the same path as above.

So, check the status of the folder JAYAPDB2 and we can see the folder contains nothing. So, all the data files present in this folder are deleted.

We can even try to delete this folder if needed.

Note: We have learned that if we need to drop any pluggable database using SQL, we have to be connected to the CDB$ROOT database and we can drop using the command “DROP PLUGGABLE DATABASE JAYAPDB2 INCLUDING DATAFILES;”. Even we can drop the pluggable database without dropping the datafiles and use those datafiles for future data.

Dropping Pluggable Database using DBCA:

DBCA is used not only to create databases but also can drop the databases as well. Now, let us go ahead and learn how to drop the pluggable database using DBCA. Run the command “DBCA” in the terminal.

Wait for a minute until the DBCA opens. The DBCA is opened as below select the option called “Manage Pluggable Database” and click Next.

Once you click Next you will get the options below. Select the option to delete the pluggable database and click Next.

Once you click Next it shows to select the container where you need to drop the pluggable database. We have selected the JAYADB database and clicked Next.

You will get a screen to select the pluggable database that needs to be dropped. We are selecting the pluggable database JAYAPDB1. Click Next.

Once you click Next. DBCA displays the summary information as below.

You can see the pluggable database that is going to delete and the data files associated with the pluggable database that will be deleted during the dropping of the pluggable database. If we click Finish the database will be dropped. We are not going to delete this database as we may need it in the future but showing you the process to delete the pluggable database using DBCA.

In the next article, I am going to discuss Cloning Pluggable Database in Oracle with Examples. Here, in this article, I try to explain Drop Pluggable Database in Oracle with Examples and I hope you enjoy this Drop Pluggable Database in Oracle article.