Step by Step Drop Database Manually in Oracle

Sometimes we need to drop/delete the database, especially for the test and development databases. Usually, we drop test or development database once the application team’s testing completed and this database seems not to be in use.

Key Points to remember before dropping the databases.

=> Follow organization-level policy to drop the database like to keep database shutdown for a specific number of days or retention then drop, one full DB’s backup retention, etc.
=> Capture all datafiles. redolog files, control files, etc of the database, Follow the article here How to find all physicals files in Oracle.
=> Make sure no any application connected sessions/remote sessions to the database, click here for How to Find Sessions in Oracle All in One
=> Take one full database backup and keep it for some days or follow the retention period to keep backup.
=> Delete/disable all database’s dependents job in crontab or other tools.
=> Delete target database in OEM if you have active.

We drop databases with the following steps.

1. Set the environment.

In this steps, we have set the database environment or export database which we need to drop.

[oracle@localhost dbhome_1]$ . oraenv
ORACLE_SID = [test] ? 
ORACLE_HOME = [/home/oracle] ? /u01/app/oracle/product/19.0.0/dbhome_1
The Oracle base remains unchanged with value /u01/app/oracle

2. Verify Database.

[oracle@localhost dbhome_1]$ sqlplus / as sysdba

SQL*Plus: Release 19.0.0.0.0 - Production on Thu Dec 10 02:18:48 2020
Version 19.3.0.0.0

Copyright (c) 1982, 2019, Oracle.  All rights reserved.


Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.3.0.0.0

SQL> select name,open_mode from v$database;

NAME	  OPEN_MODE
--------- --------------------
TEST	  READ WRITE

SQL> shut immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.

3. login into the database.

Open the database in mount exclusive mode.

[oracle@localhost dbhome_1]$ sqlplus / as sysdba

SQL*Plus: Release 19.0.0.0.0 - Production on Thu Dec 10 02:21:56 2020
Version 19.3.0.0.0

Copyright (c) 1982, 2019, Oracle.  All rights reserved.

Connected to an idle instance.

SQL> startup restrict mount;
ORACLE instance started.

Total System Global Area  754971128 bytes
Fixed Size		    8901112 bytes
Variable Size		  478150656 bytes
Database Buffers	  264241152 bytes
Redo Buffers		    3678208 bytes
Database mounted.

4. Drop database.

Now we are dropping the database.

SQL> drop database;

Database dropped.

Disconnected from Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.3.0.0.0

5. Post Validate

Validate all datafiles. redolog files, control files, etc of the database as captured before drop database, it should not be available on OS level. If available delete it using the command line.
Check listener service, it should not be active.

Click here for Step by Step DROP database using DBCA (GUI) in Oracle.

This document is just for learning purpose and always validate in the LAB environment first before applying in the LIVE environment.

Hope so you like this article!
Please share your valuable feedback/comments/subscribe and follow us below and don’t forget to click on the bell icon to get the most recent update. Click here to understand more about our pursuit.