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 optionsThis 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.DBFThis 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.