Drop Pluggable Database in Oracle

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.

Drop Pluggable Database using SQL

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.

Drop Pluggable Database using SQL

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.

Drop Pluggable Database in Oracle with Examples

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.

Drop Pluggable Database in Oracle with Examples

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;

Drop Pluggable Database in Oracle with Examples

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

Command: DROP PLUGGABLE DATABASE JAYAPDB2 INCLUDING DATAFILES;

Drop Pluggable Database in Oracle with Examples

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.

Drop Pluggable Database in Oracle with Examples

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.

Drop Pluggable Database in Oracle using SQL

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.

Dropping Pluggable Database using DBCA

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

Dropping Pluggable Database using DBCA

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

Dropping Pluggable Database using DBCA in Oracle

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.

Dropping Pluggable Database using DBCA in Oracle

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

Dropping Pluggable Database using DBCA in Oracle with Examples

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

Dropping Pluggable Database using DBCA in Oracle with Examples

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.

Leave a Reply

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