Drop Database In Oracle 11 Without Using DBCA
If, when you come to remove an Oracle database, you find that DBCA is not available perhaps due to some installation issue or missing files then do not fret as it is still possible to remove the database by following these steps…
1 Set the Oracle SID
D:\Oracle\product\11.2.0\dbhome_1\BIN>set ORACLE_SID=TESTDB2 Connect to the database and verify the name
D:\Oracle\product\11.2.0\dbhome_1\BIN>sqlplus / as sysdba
SQL*Plus: Release 11.2.0.2.0 Production on Fri Nov 25 09:38:04 2016
Copyright (c) 1982, 2010, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SQL> select name from v$database;
NAME
---------
TESTDB3 Shutdown the database
SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.4 Startup the database in exclusive mode
SQL> startup mount exclusive restrict
ORACLE instance started.
Total System Global Area 2137886720 bytes
Fixed Size 2254896 bytes
Variable Size 1577060304 bytes
Database Buffers 553648128 bytes
Redo Buffers 4923392 bytes
Database mounted.5 Drop the database
SQL> drop database;
Database dropped.
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64 bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing optionsYou can now check that the database has been dropped by checking that the database files have been removed. Check also that the services have been removed, if they are still listed then you can open a DOS prompt with administrator privileges and then use sc delete <service name>
How to drop a database from SQL*PLUS (without DBCA)
Let's drop this database (so deleting its datafiles) simply using sql*plus tool.
[oracle@plutone ~]$ export ORACLE_SID=DUP11G
[oracle@plutone ~]$ sqlplus / as sysdba
SQL*Plus: Release 11.2.0.1.0 Production on Fri Sep 24 15:35:35 2010
Copyright (c) 1982, 2009, Oracle. All rights reserved.
Connected to an idle instance.
idle> startup
ORACLE instance started.
Total System Global Area 217157632 bytes
Fixed Size 2211928 bytes
Variable Size 159387560 bytes
Database Buffers 50331648 bytes
Redo Buffers 5226496 bytes
Database mounted.
Database opened.
idle> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
idle> startup mount exclusive restrict
ORACLE instance started.
Total System Global Area 217157632 bytes
Fixed Size 2211928 bytes
Variable Size 159387560 bytes
Database Buffers 50331648 bytes
Redo Buffers 5226496 bytes
Database mounted.
idle> 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
idle> exit
Let's see at file system level what's happening during this process...
Here are the datafiles and controlfiles of DUP11G database just one second before executing the DROP DATABASE command.
[oracle@plutone DUP11G]$ ll
total 3949168
-rw-r----- 1 oracle oinstall 7872512 Sep 24 09:50 users01.dbf
-rw-r----- 1 oracle oinstall 1918902272 Sep 24 09:50 soe.dbf
-rw-r----- 1 oracle oinstall 52429312 Sep 24 09:50 redo03.log
-rw-r----- 1 oracle oinstall 52429312 Sep 24 09:50 redo02.log
-rw-r----- 1 oracle oinstall 104865792 Sep 24 09:50 example01.dbf
-rw-r----- 1 oracle oinstall 195043328 Sep 24 09:52 temp01.dbf
-rw-r----- 1 oracle oinstall 592453632 Sep 24 10:02 undotbs01.dbf
-rw-r----- 1 oracle oinstall 713039872 Sep 24 10:02 system01.dbf
-rw-r----- 1 oracle oinstall 524296192 Sep 24 10:02 sysaux01.dbf
-rw-r----- 1 oracle oinstall 52429312 Sep 24 10:02 redo01.log
-rw-r----- 1 oracle oinstall 10076160 Sep 24 10:03 control02.ctl
-rw-r----- 1 oracle oinstall 10076160 Sep 24 10:03 control01.ctl
The same info few seconds after DROP DATABASE command was issued. All datafiles were already deleted.
[oracle@plutone DUP11G]$ ll
total 19712
-rw-r----- 1 oracle oinstall 10076160 Sep 24 15:38 control02.ctl
-rw-r----- 1 oracle oinstall 10076160 Sep 24 15:38 control01.ctl
After sql*plus stated "Database dropped" also the controlfile are deleted.
[oracle@plutone DUP11G]$ ll
total 0
