Drop a database

There are times when you require to drop your existing database for more than one reason.  Dropping a database is not a tough job at all, if you are very sure that which database you should drop.

Problem Statement:

1. How to drop database.
2. Status  or mode of database in which it can be dropped
3. Step by Step explanation of dropping a database

Approach:

To drop a database it should be in mount stage with exclusive & restrict mode.  We can see this with an example however before going for example lets collect some information from system so that we can compare pre & post stages of database drop.

Lets find all the database data files which existing on the system:

[oracle@mishika ~]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.1.0 Production on Wed Sep 13 12:03:23 2017

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

Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 – 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> select file_name from dba_data_files;

FILE_NAME
——————————————————————————–
/u01/app/oracle/oradata/orcl/users01.dbf
/u01/app/oracle/oradata/orcl/undotbs01.dbf
/u01/app/oracle/oradata/orcl/sysaux01.dbf
/u01/app/oracle/oradata/orcl/system01.dbf
/u01/app/oracle/oradata/orcl/example01.dbf
/u01/app/oracle/oradata/orcl/mishika_data01.dbf
/u01/app/oracle/oradata/orcl/mishika_idx01.dbf
/u01/app/oracle/oradata/orcl/mishika_ind_idx01.dbf
/u01/app/oracle/oradata/orcl/rmcfrntend_data01.dbf
/u01/app/oracle/oradata/orcl/rmcfrntend_idx01.dbf
/u01/app/oracle/oradata/orcl/idx_hma_001_a.dbf
/u01/app/oracle/oradata/orcl/idx_hma_001_b.dbf
/u01/app/oracle/oradata/orcl/data_hma_002_a.dbf
/u01/app/oracle/oradata/orcl/data_hma_002_b.dbf
/u01/app/oracle/oradata/orcl/idx_hma_002_a.dbf
/u01/app/oracle/oradata/orcl/idx_hma_002_b.dbf
/u01/app/oracle/oradata/orcl/data_hma_003_a.dbf
/u01/app/oracle/oradata/orcl/data_hma_003_b.dbf
/u01/app/oracle/oradata/orcl/idx_hma_003_a.dbf
/u01/app/oracle/oradata/orcl/idx_hma_003_b.dbf
/u01/app/oracle/oradata/orcl/data_hma_004_a.dbf
/u01/app/oracle/oradata/orcl/data_hma_004_b.dbf
/u01/app/oracle/oradata/orcl/idx_hma_004_a.dbf
/u01/app/oracle/oradata/orcl/idx_hma_004_b.dbf
/u01/app/oracle/oradata/orcl/data_hma_005_a.dbf
/u01/app/oracle/oradata/orcl/data_hma_005_b.dbf
/u01/app/oracle/oradata/orcl/idx_hma_005_a.dbf
/u01/app/oracle/oradata/orcl/idx_hma_005_b.dbf
/u01/app/oracle/oradata/orcl/data_hma_006_a.dbf
/u01/app/oracle/oradata/orcl/data_hma_006_b.dbf
/u01/app/oracle/oradata/orcl/idx_hma_006_a.dbf
/u01/app/oracle/oradata/orcl/idx_hma_006_b.dbf
/u01/app/oracle/oradata/orcl/data_hma_007_a.dbf
/u01/app/oracle/oradata/orcl/data_hma_007_b.dbf
/u01/app/oracle/oradata/orcl/idx_hma_007_a.dbf
/u01/app/oracle/oradata/orcl/idx_hma_007_b.dbf
/u01/app/oracle/oradata/orcl/data_hma_008_a.dbf
/u01/app/oracle/oradata/orcl/data_hma_008_b.dbf
/u01/app/oracle/oradata/orcl/idx_hma_008_a.dbf
/u01/app/oracle/oradata/orcl/idx_hma_008_b.dbf
/u01/app/oracle/oradata/orcl/data_hma_001_a.dbf
/u01/app/oracle/oradata/orcl/data_hma_001_b.dbf

42 rows selected.

SQL>

Verifying the same files on file system:

[oracle@mishika oracle]$ ls -ltr /u01/app/oracle/oradata/orcl/

total 91949464
-rw-r—–. 1 oracle oinstall 18253619200 Sep 13 12:04 mishika_data01.dbf
-rw-r—–. 1 oracle oinstall 18253619200 Sep 13 12:04 mishika_idx01.dbf
-rw-r—–. 1 oracle oinstall 18253619200 Sep 13 12:04 rmcfrntend_data01.dbf
-rw-r—–. 1 oracle oinstall 18253619200 Sep 13 12:04 mishika_ind_idx01.dbf
-rw-r—–. 1 oracle oinstall 18253619200 Sep 13 12:04 rmcfrntend_idx01.dbf
-rw-r—–. 1 oracle oinstall 411049984 Sep 13 12:04 idx_hma_001_a.dbf
-rw-r—–. 1 oracle oinstall 448798720 Sep 13 12:04 idx_hma_001_b.dbf
-rw-r—–. 1 oracle oinstall 61874176 Sep 13 12:04 data_hma_002_b.dbf
-rw-r—–. 1 oracle oinstall 92282880 Sep 13 12:04 data_hma_002_a.dbf
-rw-r—–. 1 oracle oinstall 190849024 Sep 13 12:04 idx_hma_002_b.dbf
-rw-r—–. 1 oracle oinstall 242229248 Sep 13 12:04 idx_hma_002_a.dbf
-rw-r—–. 1 oracle oinstall 29368320 Sep 13 12:04 data_hma_003_b.dbf
-rw-r—–. 1 oracle oinstall 34611200 Sep 13 12:04 data_hma_003_a.dbf
-rw-r—–. 1 oracle oinstall 11542528 Sep 13 12:04 idx_hma_003_b.dbf
-rw-r—–. 1 oracle oinstall 13639680 Sep 13 12:04 idx_hma_003_a.dbf
-rw-r—–. 1 oracle oinstall 32514048 Sep 13 12:04 data_hma_004_a.dbf
-rw-r—–. 1 oracle oinstall 22028288 Sep 13 12:04 idx_hma_004_a.dbf
-rw-r—–. 1 oracle oinstall 46145536 Sep 13 12:04 data_hma_004_b.dbf
-rw-r—–. 1 oracle oinstall 35659776 Sep 13 12:04 idx_hma_004_b.dbf
-rw-r—–. 1 oracle oinstall 79699968 Sep 13 12:04 data_hma_005_a.dbf
-rw-r—–. 1 oracle oinstall 99622912 Sep 13 12:04 idx_hma_005_b.dbf
-rw-r—–. 1 oracle oinstall 90185728 Sep 13 12:04 idx_hma_005_a.dbf
-rw-r—–. 1 oracle oinstall 81797120 Sep 13 12:04 data_hma_005_b.dbf
-rw-r—–. 1 oracle oinstall 155197440 Sep 13 12:04 data_hma_006_a.dbf
-rw-r—–. 1 oracle oinstall 10493952 Sep 13 12:04 idx_hma_006_a.dbf
-rw-r—–. 1 oracle oinstall 113254400 Sep 13 12:04 data_hma_006_b.dbf
-rw-r—–. 1 oracle oinstall 10493952 Sep 13 12:04 idx_hma_006_b.dbf
-rw-r—–. 1 oracle oinstall 16785408 Sep 13 12:04 data_hma_007_a.dbf
-rw-r—–. 1 oracle oinstall 26222592 Sep 13 12:04 data_hma_007_b.dbf
-rw-r—–. 1 oracle oinstall 38805504 Sep 13 12:04 idx_hma_007_a.dbf
-rw-r—–. 1 oracle oinstall 59777024 Sep 13 12:04 idx_hma_007_b.dbf
-rw-r—–. 1 oracle oinstall 39854080 Sep 13 12:04 data_hma_008_a.dbf
-rw-r—–. 1 oracle oinstall 47194112 Sep 13 12:04 data_hma_008_b.dbf
-rw-r—–. 1 oracle oinstall 16785408 Sep 13 12:04 idx_hma_008_a.dbf
-rw-r—–. 1 oracle oinstall 23076864 Sep 13 12:04 idx_hma_008_b.dbf
-rw-r—–. 1 oracle oinstall 151003136 Sep 13 12:04 data_hma_001_a.dbf
-rw-r—–. 1 oracle oinstall 155197440 Sep 13 12:04 data_hma_001_b.dbf
-rw-r—–. 1 oracle oinstall 151003136 Sep 13 12:04 users01.dbf
-rw-r—–. 1 oracle oinstall 155197440 Sep 13 12:04 undotbs01.dbf
-rw-r—–. 1 oracle oinstall 151003136 Sep 13 12:04 sysaux01.dbf
-rw-r—–. 1 oracle oinstall 155197440 Sep 13 12:04 system01.dbf
-rw-r—–. 1 oracle oinstall 151003136 Sep 13 12:04 example01.dbf
[oracle@mishika oracle]$

Looking for the current status of the database

SQL> select status from v$instance;

STATUS
————
OPEN

SQL>

Since the status of the current database is OPEN, we should be change the status to “EXCLUSIVE RESTRICT”. To achieve this, first we have to shutdown the database.

To shutdown database log with SYS.

[oracle@mishika ~]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.1.0 Production on Wed Sep 13 12:03:23 2017

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

Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 – 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> shutdown immediate;

Database closed.
Database dismounted.
ORACLE instance shut down. </span

Starting database in the required mode:

SQL> startup mount exclusive restrict;

ORACLE instance started.

Total System Global Area 2.6991E+10 bytes
Fixed Size 2213976 bytes
Variable Size 1.7985E+10 bytes
Database Buffers 8858370048 bytes
Redo Buffers 145174528 bytes
Database mounted.
SQL>

Once database is available in “EXCLUSIVE RESTRICT” mode, we can drop this database using drop database command.

SQL> drop database;

Database dropped.

Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 – 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SQL>

We can verify the files which should not exists anymore.

[oracle@mishika oracle]$ ls -ltr /u01/app/oracle/oradata/orcl/
total 0
[oracle@mishika oracle]$