Drop Oracle 9i Database

Drop Oracle 9i Database

As many of us might expect dropping database would be quite straight forward and intuitively the command should be something like this -

DROP DATABASE <INSTANCE_NAME>;

Well, even I was of the same opinion and tried it on Oracle database version 9i. I was surprised to see 9i wont support DROP DATABASE statement. However, I am glad to figure out how to deal with such situation. 
To proceed further we will need to query few in built dynamic views to find out exact location of the physical database files (Datafiles, Redo Logs, Archive Logs, and Control Files). 

To find out exact location of the datafiles we need to query v$datafile -

SQL> select name, status from v$datafile

NAME                                                         STATUS
------------------------------------------------------------ -------
/database/oradata/TOYDB/system/SYS_TOYDB_001.dbf              SYSTEM
/database/oradata/TOYDB/system/SYS_TOYDB_002.dbf              SYSTEM
/database/oradata/TOYDB/rbs/RBS_001_TOYDB_001.dbf             ONLINE
/database/oradata/TOYDB/rbs/RBS_001_TOYDB_002.dbf             ONLINE
/database/oradata/TOYDB/temp/TEMP_001_TOYDB_001.dbf           ONLINE
/database/oradata/TOYDB/users/TOOL_001_TOYDB_001.dbf          ONLINE
/database/oradata/TOYDB/users/DRSYS_001_TOYDB_001.dbf         ONLINE
/database/oradata/TOYDB/users/TABLES_TS_001_TOYDB.DBF         ONLINE
/database/oradata/TOYDB/users/INDEXES_TS_001_TOYDB.DBF        ONLINE
/database/oradata/TOYDB/temp/TEMP_TS_001_TOYDB.DBF            ONLINE

10 rows selected.
SQL>

To find out location of redo logs -

SQL> desc v$logfile
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 GROUP#                                             NUMBER
 STATUS                                             VARCHAR2(7)
 TYPE                                               VARCHAR2(7)
 MEMBER                                             VARCHAR2(513)
 SQL> ;
  1* select group#, member, type, status from v$logfile
SQL> /
    GROUP# MEMBER                                             TYPE    STATUS
---------- -------------------------------------------------- ------- -------
         2 /database/oradata/TOYDB/redo/LOG_002_TOYDB_001.rdo  ONLINE  STALE
         2 /database/oradata/TOYDB/redo/LOG_002_TOYDB_002.rdo  ONLINE  STALE
         1 /database/oradata/TOYDB/redo/LOG_001_TOYDB_001.rdo  ONLINE
         1 /database/oradata/TOYDB/redo/LOG_001_TOYDB_002.rdo  ONLINE
SQL>

To find out the location of control file -

SQL> desc v$controlfile
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 STATUS                                             VARCHAR2(7)
 NAME                                               VARCHAR2(513)
SQL> select name, status from v$controlfile;
NAME                                                         STATUS
------------------------------------------------------------ -------
/database/oradata/TOYDB/control/CTRL_001_TOYDB.ctl
/database/oradata/TOYDB/control/CTRL_002_TOYDB.ctl
SQL>

Finally confirm if database is archive log mode -

SQL> archive log list
Database log mode              No Archive Mode
Automatic archival             Disabled
Archive destination            /product/oracle/base/9.2.0-Std-64b/dbs/arch
Oldest online log sequence     7
Current log sequence           8
SQL>

If database is in archive log mode then we will further need the location of archive log files. Since the database is running in no archive log mode, we can proceed directly with the dropping database. 
Below are the steps to follow --- Shutdown the database instance, if not already done- Go to the locations of datafiles and delete them using OS delete command- Go to the locations of redo logs and delete them using OS delete command- Go to the location of control files and delete them using OS delete command- Finally go to $ORACLE_HOME/dbs and delete parameter file and password file using OS delete command

Supraj:/database/oradata% ls -lrt TOYDB
total 8
-rw-r--r--   1 oracle     dba           1152 Mar 11  2010 create_TOYDB.sql
drwxr-xr-x   2 oracle     dba             96 Jan 15  2011 control
drwxr-xr-x   2 oracle     dba           1024 Jan 15  2011 users
drwxr-xr-x   2 oracle     dba           1024 Jan 15  2011 redo
drwxr-xr-x   7 oracle     dba             96 Jan 15  2011 adm
drwxr-xr-x   2 oracle     dba             96 Jan 15  2011 system
drwxr-xr-x   2 oracle     dba             96 Jan 15  2011 rbs
drwxr-xr-x   2 oracle     dba             96 Jan 15  2011 temp


Supraj:/database/oradata% rm -r TOYDB
Supraj:/database/oradata% ls -lrt TOYDB
TOYDB not found
Supraj:/database/oradata%