amar on web

Drop Database Joke

This used to be a common joke for Oracle fans on SQLServer database. SQLServer provides an option to drop databases by simply right clicking on the instance name and choosing the drop option or by issuing the command in sql query! Oracle fans mocked about it - how can someone be so stupid to provide such an option, what if the DBA clicked on it in one of those nerve-wrecking moods? The fun lasted till 10g came in. Now Oracle provides something similar, let SQL*Server fans have the final laugh!

SQL> startup restrict mount;
ORACLE instance started.

Total System Global Area  146800640 bytes
Fixed Size                   787888 bytes
Variable Size              82836048 bytes
Database Buffers           62914560 bytes
Redo Buffers                 262144 bytes
Database mounted.

SQL> drop database;
Database dropped.
Disconnected from Oracle Database 10g Enterprise Edition Release 10.1.0.2.0 - Production
With the Partitioning, OLAP and Data Mining options
This is a no-return statement, oracle deletes all the datafiles, logfiles, controlfiles and spfile. The instance should be mounted in restricted mode to fire this command. The below lines are registered in the alert.log, last words of the dying instance..

#=====alert.log=========
Thu Nov 09 10:07:27 2006
drop database
Thu Nov 09 10:07:31 2006
Deleted file D:\AMAR\PRGFILES\ORADATA\DB1\SYSTEM01.DBF
Deleted file D:\AMAR\PRGFILES\ORADATA\DB1\UNDOTBS01.DBF
Deleted file D:\AMAR\PRGFILES\ORADATA\DB1\SYSAUX01.DBF
Deleted file D:\AMAR\PRGFILES\ORADATA\DB1\USERS01.DBF
Deleted file D:\AMAR\PRGFILES\ORADATA\DB1\EXAMPLE01.DBF
Deleted file D:\AMAR\PRGFILES\ORADATA\DB1\RPDDATSMLGEN01_01.DBF
Deleted file D:\AMAR\PRGFILES\ORADATA\DB1\RPDDATSMLGEN01_02.DBF
Deleted file D:\AMAR\PRGFILES\ORADATA\DB1\RPDDATSMLGEN01_03.DBF
Deleted file D:\AMAR\PRGFILES\ORADATA\DB1\RPDDATSMLGEN01_04.DBF
Deleted file D:\AMAR\PRGFILES\ORADATA\DB1\RPDDATSMLGEN01_05.DBF
Deleted file D:\AMAR\PRGFILES\ORADATA\DB1\RPDDATSMLGEN01_06.DBF
Deleted file D:\AMAR\PRGFILES\ORADATA\DB1\RPDDATSMLGEN01_07.DBF
Deleted file D:\AMAR\PRGFILES\ORADATA\DB1\REDO01.LOG
Deleted file D:\AMAR\PRGFILES\ORADATA\DB1\REDO02.LOG
Deleted file D:\AMAR\PRGFILES\ORADATA\DB1\REDO03.LOG
Deleted file D:\AMAR\PRGFILES\ORADATA\DB1\TEMP01.DBF
Flashback Database Disabled 
Deleted file D:\AMAR\PRGFILES\ORADATA\DB1\CONTROL01.CTL
Deleted file D:\AMAR\PRGFILES\ORADATA\DB1\CONTROL02.CTL
Deleted file D:\AMAR\PRGFILES\ORADATA\DB1\CONTROL03.CTL
Completed: drop database
Thu Nov 09 10:07:31 2006
Shutting down instance (abort)
License high water mark = 1
Instance terminated by USER, pid = 2568
#===============
If for some reason, Oracle is not able to clean up a file, it will skip that one and continue with the rest. The below will be registered in the alert.log

OSD-04002: unable to open file
O/S-Error: (OS 3) The system cannot find the path specified.
ORA-01259: unable to delete datafile E:\TEST\UNDOTBS03.DBF
This feature is not stupid at it looks, it is quite handy on testing grounds. Bottom line:- don't push your DBA to the brink, keep them happy at all times! I for one am enjoying this feature in my sloppy moods (hell, I just dropped a much needed instance for this write up..).

Best viewed in medium text size. Please refresh this page (F5) to view the latest information.
This page was create on 27-dec-2006 and last updated on 27-dec-2006.
please forward all queries to [email protected]