Oracle Drop Database

In this Oracle tutorial, we will learn how to drop databases in oracle 19c & 21c. Also, we will cover these topics.

• Oracle drop database
• Oracle SQL drop database
• Oracle drop database link
• Oracle grant drop database link
• Oracle sql developer drop database
• Oracle dbca drop database silent
• Oracle drop database including backups
• Oracle drop database link not found
• Oracle drop pluggable database force
• Oracle 19c drop database including contents and datafiles
• Oracle database drop tablespace including contents and datafiles
• Oracle RMAN drop database including backups

Oracle drop database

Oracle drop database means removing or deleting the oracle database. In an organization, test databases are dropped after the purpose is served. Apart from that, there are very rare chances that an active organization drops its database.

While installing the oracle database on windows, a database folder is created which keeps a record of all the databases.

By default, the database folder has information of the orcl database which is created while installing the oracle database. But later when a developer creates the new database(s) then this folder is appended with that information.

Once the database is deleted from the command then files for that database can be removed from this folder.

Use the below command to view the current database name:

SELECT NAME
FROM V$DATABASE;

If you want to switch between databases using sqlplus then type these commands on the command prompt. Antone and ORCL are the two databases we are switching between.

Note: Due to some reason command SET ORACLE_SID=<database name> won’t work if you will add space before and after the equal sign.

-- change database
set ORACLE_SID=ORCL

-- login to oracle
sqlplus / as sysdba

-- view current database name
SELECT name
FROM v$database;

-- exit from the oracle
exit

-- change database
set ORACLE_SID=Antone

-- login to oracle
sqlplus / as sysdba

-- view current database name
SELECT name
FROM v$database;

The below image shows the implementation of the above script. Yellow texts are the commands or scripts whereas red marked text is the output.

In this example, we have shown how to change the database in oracle database 21c and 19c. First, we have set the database to ORCL and then changed it to Antone.

Note: we have already created two databases and we have switched between them.

Oracle database can be deleted by writing script on SQL developer tool or SQL Plus and the alternative way is using Graphical User Interface provided by database Configuration Assistant. We will discuss both ways in the upcoming sections.

Read: Oracle Create User

Oracle SQL drop database

So far we have learned the meaning of dropping a database, the reason for deleting a database, and how to view and switch between databases in oracle database 21c and 19c.

In this section, we will write the SQL query to drop the oracle database. We are working on the oracle database version 21c but the code will work on other versions as well.

Step 1. Connect to the database you wish to drop with system privileges. In our case, the database name is Antone. Replace the word Antone with your database name.

-- CONNECT TO THE DATABASE
SET ORACLE_SID=ANTONE      -- (WINDOWS USER)
EXPORT ORACLE_SID=ANTONE   -- (LINUX USER)

-- LOGIN AS SYSDBA
SQLPLUS / AS SYSDBA

-- CHECK CONNECTED DATABASE
SELECT NAME FROM V$DATABASE;

Step 2. Run the below query and make a note of directories. After dropping the database verify if files in these directories are also deleted. If not then manually delete them.

SELECT MEMBER FROM V$LOGFILE;
SELECT NAME FROM V$CONTROLFILE;

Step 3. Before deleting the database it is important to Shutdown the database so that database can be isolated from system resources.

-- SHUTDOWN ANTONE
SHUTDOWN IMMEDIATE;

Step 4. While deleting the database if you are getting an error: ORA-01586 then probably you have skipped this step. Use the below script to Start the database in Exclusive mode.

STARTUP MOUNT EXCLUSIVE RESTRICT;

Step 5. This is the final command to Drop the database.

-- BYE ANTONE
DROP DATABASE;

Step 6. Refer to step 2 visit the directories and Manually delete the files if any.

Read: Oracle check database status

Oracle drop database link

Oracle database link allows communication between two or more databases. You can execute information on the table in another database using a database link.

Database link not only provides access to the tables in other oracle databases but also to non-oracle databases like SQL, MySQL, etc.

Database link can be dropped using the below command, here remote is the name of the database link provided while creating it:

-- FOR PUBLIC DATABASE
DROP PUBLIC DATABASE LINK remote; 


-- FOR PRIVATE DATABASE 
DROP DATABASE LINK remote; 

Please note that Oracle drop database link in another schema is not possible because Oracle won’t allow dropping a database link in another user’s schema.

Oracle database link allows connecting with the table in another data. You can view, delete, update, data remotely using the oracle database link.

In oracle database 21c if you have privileges to create a database link then you are authorized to drop that database link. So there are no special privileges for dropping the database link.

Use the below script to grant a privilege to create a database link in oracle database 21c:

GRANT CREATE DATABASE LINK to <user>;

Read: Connect to oracle database

Oracle SQL developer drop database

Oracle SQL developer tool provides command and GUI-based interfaces to execute huge oracle queries. In this section, we will learn how to drop databases using the SQL developer tool.

We are going to write a script on SQL developer tool to drop the database named Antone. Here are the steps:

Step 1: Connect to the Antone database as a sys user with dba roles. Make sure you are not connected with the PDBs because won’t allow you to delete the entire database.

Run the below script to delete the database using the SQL developer tool in oracle database 19c and 21c.

-- CHECK CONNECTED DATABASE
SELECT NAME FROM V$DATABASE;

-- files on system
SELECT MEMBER FROM V$LOGFILE;
SELECT NAME FROM V$CONTROLFILE;

-- SHUTDOWN ANTONE
SHUTDOWN IMMEDIATE;

STARTUP MOUNT EXCLUSIVE RESTRICT;

-- BYE ANTONE
DROP DATABASE;

Read: How to Install Oracle on Docker

Oracle dbca drop database silent

Oracle Database Configuration Assistant (dbca) provides GUI based interface to create, configure, delete and manage databases in oracle databases 21c and 19c.

We already have a database: SalesDB which we are going to drop using the DBCA tool on the windows 10 operating system.

The below steps will show how to drop a database using the DBCA tool in oracle databases 21c and 19c.

Step 1. Start DBCA tool in administrator mode on Windows 10.
Start -> Database Configuration Assistant -> Run as Administrator

Step 2. Select the ‘Delete database’ radio button and click on the Next button.

Step 3. Select the database you want to delete and then provide the password for the sys user and in the end, click on the Next button.

Step 4. Click on the check box if enrolled in cloud control earlier. If not, leave it as it is and click on the Nex button.

Step 5. The summary page shows all the configurations you have chosen in the previous steps. To change any configuration click on the back button and if not then click on the Finish button.

Step 6. Please be sure before clicking the Yes on the prompt. The selected database will start deleting from the system. All the associated files will also be deleted.

Step 7. Click on the close button to terminate the wizard. The SalesDB database has been deleted from the system. If you will still try to connect to the deleted database you will get ERROR: ORA-12560: TNS:protocol adapter error.

Read: Oracle Add Row Number

Oracle drop database including backups

Backups in oracle keep the copy of the database which can be used in case original data is deleted or infected.

In oracle, to drop the database with backups use the keyword ‘including backups‘ after drop database. Follow the steps in the section – Oracle SQL drop database. On the 5th step of this section write the below script:

DROP DATABASE INCLUDING BACKUPS;

In the last section of this blog, we have demonstrated a drop database with backup with examples in the oracle database.

Read: Oracle to_char Date Format

Oracle drop database link not found

Database links are created for a purpose and once the purpose is served it is recommended to delete them. While deleting the database link if you are getting the below error:

ORA-02024: database link not found

This means that your shared pool is not flushed that is why you are getting garbage values here. to fix this follow these steps:

Step 1. Connect as a sys user

connect / as sysdba 

Step 2. Run this script to update and set the database name. This will fill up the void space if any.

UPDATE props$ SET value$='<global database name>' 
WHERE name='global database name';

Step 3. Run this flush command multiple times on the SQL plus or SQL developer tool to clear the shared pool.

ALTER SYSTEM FLUSH SHARED_POOL;

Step 4. Repeat Step 2 again and it should work this time. Make sure there is no space before and after the equal sign. Not sure but we tried multiple combinations and also those combinations worked where we didn’t provide space after equals to sign.

After following these steps try to drop the database link again in oracle. If still facing the error please write us with the error code and screenshot of the scenario.

Read: Oracle Add Foreign Key

Oracle drop pluggable database force

A Pluggable database is the portable collection of non-schema objects, schema objects, and schemas. Container database (CDB) contains Pluggable database(s) (PDB) which can be plugged and removed as per the requirements.

Using Drop Pluggable Database statement, pdbs can be removed/deleted, or dropped from the oracle database 21c.

While deleting a PDB you can choose to either remove the data files or keep the datafiles by adding a keyword including datafiles or keep datafiles at the end of the statement.

There is two way to drop the pluggable database in oracle 21c:

• Drop pdb using SQL PLUS (command-line)
• Drop pdb using DBCA tool (Graphical User Interface)

Moving forward we will cover both the ways to drop pluggable databases in oracle also we will cover an example for dropping a pluggable database in oracle 21c.

Drop pdb using SQL PLUS (command-line)

Oracle SQL Plus is a command-line-based tool that allows interacting with the oracle database. You can type the queries and the result will appear on the black screen white font window.

Below syntax shows how to drop a pluggable database. The syntax is divided into two parts to perform with or without data files.

-- delete data files as well
DROP PLUGGABLE DATABASE <pdb1>
INCLUDING DATAFILES;

-- keep the data files
DROP PLUGGABLE DATABAE <pdb1>
KEEP DATAFILES;

Steps to drop oracle pluggable database (PDB) using SQL PLUS:

Step 1: Set the database and log in as a sys user or any common user who has privileges to drop the pdbs.

-- select database
SET ORACLE_SID=LABSB

-- login as sys user
sqlplus / as sysdba

Step 2: Close the PDB before you can drop it. If the open mode of PDB is ‘mount’ that means PDB is closed.

COLUMN NAME FORMAT A30

-- view pdb
SELECT NAME, OPEN_MODE, RESTRICTED
FROM V$PDBS;

-- close pdb
ALTER PLUGGABLE DATABSE LABPDB1 CLOSE;

-- view pdb
SELECT NAME, OPEN_MODE, RESTRICTED
FROM V$PDBS;

The below image shows the implementation of steps 1 and 2. The yellow text in the image is the script. the first arrow shows PDB is in an open state as it is in Read-Write mode. The next arrow shows the changed mode to Mount which means close.

Step 3.1: Follow either 3.1 or 3.2, but won’t work. In this, we are removing the pluggable database with all the data files.

Step 3.2: This is an alternative method where you save the data stored in the PDB. It is important to unplug the portable database before you can proceed with this method.
In the first script, we have unplugged a PDB and stored a file on a desktop.

In the next script, the pluggable database is dropped with the keep datafiles option.

If you are getting the below error while removing the PDB that means you need to login to another PDB or container in the same user. But make sure they have privileges to delete.

ERROR at line 1:
ORA-65040: operation not allowed from within a pluggable database

Drop pdb using DBCA tool (Graphical User Interface)

Step 1: Start the DBCA tool by clicking on the start button and typing Database Configuration Assistant.

Step 2: From the Select Database Operation wizard select the Manage Pluggable Databases radio button and click on the Next button.

Step 3: Select Delete a Pluggable database from the manage pluggable database wizard and click on the Next button.

Step 4: Select the source database and provide the user name and password for that user. This username and password were created at the time of creating this database. Click on the Next button.

Step 5: Select the Pluggable database wizard will show the list of all the pluggable databases available inside the selected source database. One PDB can be deleted at a time, select the radio button and click on the Next.

Step 6: This is the summary page based upon all the settings and configurations made so far. If you want to make any changes click on the back button otherwise click on the Finish button.

Step 7: Once you have clicked on the Finish button in the previous step,
PDB will start deleting for the system and once completed you will see the below window.

In this way, you can drop the pluggable databases using the DBCA tool in oracle database 21c. Follow steps from 1-5 to view the existing pluggable databases (PDBs).

Read: Oracle add primary key

Oracle 19c drop database including contents and datafiles

Oracle 19c and oracle 21c follow the same set of steps to drop databases. In the above sections, we have shared steps for oracle database 21c and the same can be followed for oracle database 19c.

If you will remove the oracle 19c database using command-line or script then all the data files are not removed and you have to do it manually.

We don’t advise you to use any third-party software to remove the oracle data files from your system. The right way to drop databases including datafiles is using DBCA.

Database Configuration Assist (DBCA) provides GUI based tool to drop databases including data files in oracle 19c.

Step-by-step instructions are provided in the section – Oracle dbca drop database silent.

Also, check: Alter Table Add Column Oracle

Oracle database drop tablespace including contents and datafiles

Logical storage units in oracle are called tablespace. It stores and maintains all the data in the form of a database in an oracle database. Datafiles in the tablespace are the physical structure based upon the operating system.

By adding Include contents and datafiles with drop statements, a tablespace can be removed completely.

Below is the syntax to delete the tablespace in oracle database 21c:

DROP TABLESPACE <tablespace_name>
INCLUDING CONTENTS AND DATAFILES;

Oracle RMAN drop database including backups

Oracle RMAN allows taking backup of the data. It is a powerful tool and used by most professionals to keep and track the back in the oracle database.

In order to drop the database including backups in oracle database 12c, 19c, and 21c follow the below steps:

Step 1: Set the oracle database that you want to delete. Start a command prompt and type the below script. Change Labsb with your database name.

SET ORACLE_SID=LABSB

Step 2: Start oracle RMAN by typing the same keyword and then connect to the target database using the below script:

RMAN

CONNECT TARGET /

Step 3: We need to enable the restricted session in order to delete the database with backup in oracle database 12c, 19c, and 21c. The below three commands will shutdown the current session then mount the database and then alter the session.

-- shutdown the cuirrent instance
SHUTDOWN IMMEDIATE

-- start the instance with mount
STARTUP MOUNT;

-- alter session
SQL 'ALTER SYSTEM ENABLE RISTRICTED SESSION';

The below image shows the practical implementation of all the steps we have covered so far:

Step 4: Final step is to delete the database. A database can be deleted using the below command. It is optional to add NOPROMPT as this option will automatically say yes to all the prompts.

DROP DATABASE 
INCLUDING BACKUPS
NOPROMPT;

Also, take a look at some more Oracle tutorials.

• Database Size in Oracle
• Oracle Change Database Name
• How to create table in Oracle
• Oracle Database vs MySQL 
• Number Datatype in Oracle Database
• Connect to Oracle Database using Python

In this tutorial, we have learned how to drop databases in oracle 19c & 21c. Also, we have covered these topics.

• Oracle drop database
• Oracle SQL drop database
• Oracle drop database link
• Oracle grant drop database link
• Oracle SQL developer drop database
• Oracle dbca drop database silent
• Oracle drop database including backups
• Oracle drop database link not found
• Oracle drop pluggable database force
• Oracle 19c drop database including contents and datafiles
• Oracle database drop tablespace including contents and datafiles
• Oracle RMAN drop database including backups