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%

Rename Tablespace and Datafile 

Rename tablespace is quite straight forward, as you could expect

ALTER TABLESPACE tablespace_name RENAME TO new_tablespace_name;

Once the tablespace is rename, many a times we would require to change the datafiles associated with the tablespace name. This could be because of organization policy or may be because of senior DBA insists on that.

To rename a datafile we have to follow below logical steps in given sequence --

- Take tablespace offline
    ALTER TABLESPACE tablespace_name OFFLINE;
 
- Physically rename the file using operating system command prompt
    mv /database/oracle/CLYP/data/DAT_001.dbf /database/oracle/CLYP/data/DAT_005_CLYP_001.dbf
 
- Rename the datafiles internally on SQL command prompt. This will make oracle to access the underlying datafile when the particular tablespace is addressed
    ALTER TABLESPACE tablespace_name RENAME DATAFILE '/database/oracle/CLYP/data/DAT_001.dbf' to '/database/oracle/CLYP/data/DAT_005_CLYP_001.dbf';
 
- Take tablespace online
    ALTER TABLESPACE tablespace_name ONLINE;

SQL used to genereate the script is as follows --

select 'ALTER TABLESPACE '
       ||tablespace_name
       ||' OFFLINE; '
       ||'!mv '||file_name||' '||replace(file_name,'REC1','CLYP')||'; '
       ||'ALTER TABLESPACE '
       ||tablespace_name
       ||' RENAME DATAFILE '''
       ||file_name
       ||''' to '''
       ||replace(file_name,'REC1','CLYP')
       ||''';'
       ||' ALTER TABLESPACE '
       ||tablespace_name
       ||' ONLINE;'
 from dba_data_files
where tablespace_name like '%CLYP'
order by tablespace_name;


Please note, the above script need to be altered before using it. This script was developed considering certain granules and environment details. The whole purpose of this SQL script is for the illustration that generating shell/scripts using the SQL queries can save repetitive work and will help in reducing typo errors.