How to drop a database from SQL*PLUS (without DBCA)

I need to remove a database created only for test purpose using RMAN duplicate command. DBCA is not able to identify this database because no info was available on the inventory neither on oratab file.

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

That’s all.

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s