Drop Database

In version 10g, Oracle introduced the drop database SQL statement. This removes all datafiles, online redo log files, control files and server parameter files. I created a database with the Database Configuration Assistant so that I could try out this new command. To make it easier to demonstrate, I put the datafiles, online redo log files and control files all in the same directory:

SQL> col file_name format a55
SQL> select file_name from dba_data_files;

FILE_NAME
-------------------------------------------------------
C:\DOCUMENTS AND SETTINGS\ANDREW\TEST10\USERS01.DBF
C:\DOCUMENTS AND SETTINGS\ANDREW\TEST10\SYSAUX01.DBF
C:\DOCUMENTS AND SETTINGS\ANDREW\TEST10\UNDOTBS01.DBF
C:\DOCUMENTS AND SETTINGS\ANDREW\TEST10\SYSTEM01.DBF

SQL> select file_name from dba_temp_files;

FILE_NAME
-------------------------------------------------------
C:\DOCUMENTS AND SETTINGS\ANDREW\TEST10\TEMP01.DBF
  
SQL> col member format a50
SQL> select member from v$logfile;

MEMBER
--------------------------------------------------
C:\DOCUMENTS AND SETTINGS\ANDREW\TEST10\REDO03.LOG
C:\DOCUMENTS AND SETTINGS\ANDREW\TEST10\REDO02.LOG
C:\DOCUMENTS AND SETTINGS\ANDREW\TEST10\REDO01.LOG

SQL> col name format a55
SQL> select name from v$controlfile;

NAME
-------------------------------------------------------
C:\DOCUMENTS AND SETTINGS\ANDREW\TEST10\CONTROL01.CTL
C:\DOCUMENTS AND SETTINGS\ANDREW\TEST10\CONTROL02.CTL
C:\DOCUMENTS AND SETTINGS\ANDREW\TEST10\CONTROL03.CTL

SQL>

It’s easier to see in the screen print below (click to enlarge it):
   

The server parameter file was in a different directory:

SQL> col value format a50
SQL> select value from v$parameter where name = 'spfile';

VALUE
--------------------------------------------------
C:\ORACLE\PRODUCT\10.2.0\DB_1\DBS\SPFILETEST10.ORA

SQL>

Then I tried to drop the database:

SQL> set lines 60
SQL> drop database;
drop database
*
ERROR at line 1:
ORA-01586: database must be mounted EXCLUSIVE and not open for this operation

SQL>

I followed the instructions and tried again: 

C:\Documents and Settings\Andrew>set ORACLE_SID=TEST10

C:\Documents and Settings\Andrew>sqlplus / as sysdba

SQL*Plus: Release 10.2.0.1.0 - Production on Fri Dec 24 09:35:34 2010

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

Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options

SQL> shutdown
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup mount exclusive
ORACLE instance started.

Total System Global Area 612368384 bytes
Fixed Size 1250452 bytes
Variable Size 176163692 bytes
Database Buffers 432013312 bytes
Redo Buffers 2940928 bytes
Database mounted.
SQL> drop database;
drop database
*
ERROR at line 1:
ORA-12719: operation requires database is in RESTRICTED mode

SQL>

Then putting the database into restricted session mode appeared to have the desired effect:

SQL> alter system enable restricted session;

System altered.

SQL> drop database;

Database dropped.
Disconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options
SQL>

The datafiles were removed:

C:\Documents and Settings\Andrew\test10>dir
Volume in drive C has no label.
Volume Serial Number is 18E4-B972

Directory of C:\Documents and Settings\Andrew\test10

24/12/2010 10:01 <DIR> .
24/12/2010 10:01 <DIR> ..
              0 File(s) 0 bytes
              2 Dir(s) 11,539,795,968 bytes free

C:\Documents and Settings\Andrew\test10>

And so was the spfile:

C:\oracle\product\10.2.0\db_1\dbs>dir
Volume in drive C has no label.
Volume Serial Number is 18E4-B972

Directory of C:\oracle\product\10.2.0\db_1\dbs

24/12/2010 10:00 <DIR> .
24/12/2010 10:00 <DIR> ..
              0 File(s) 0 bytes
              2 Dir(s) 11,539,415,040 bytes free

C:\oracle\product\10.2.0\db_1\dbs>