Oracle 10g新增DROP DATABASE命令

在10g以前，要徹底刪除資料庫，只有兩個方法，一個是利用DBCA圖形化工具刪除資料庫，另外一個就是關閉資料庫後，手工刪除資料檔案、控制檔案以及記錄檔的方法。

從10g開始，Oracle提供了DROP DATABASE的文法，使得資料庫的刪除變得非常的簡單。

不過DROP DATABASE還是有一定的限制條件的：

SQL> conn / as sysdba
Connected.
SQL> drop database;
drop database
*
ERROR at line 1:
ORA-01586: database must be mounted EXCLUSIVE and not open for this operation

SQL> alter database close;

Database altered.

SQL> drop database;
drop database
*
ERROR at line 1:
ORA-12719: operation requires database is in RESTRICTED mode

SQL> alter system enable restricted session;

System altered.

SQL> drop database;

Database dropped.

Disconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - 64bit Production
With the Partitioning, OLAP and Data Mining options
SQL> exit
[oracle@bjtest ~]$ ps -ef|grep test102
oracle 15805 14913 0 06:13 pts/1 00:00:00 grep test102

資料庫必須處於MOUNT狀態，且設定了RESTRICTED SESSION。執行了DROP DATABASE命令後，Oracle自動刪除控制檔案，已經控制檔案中記錄的資料檔案和線上重做記錄檔，如果資料庫使用了SPFILE，那麼SPFILE檔案也會刪除。這個命令不會刪除歸檔檔案和備份檔案。

在alert檔案中，可以看到drop database對應的操作：

drop database
Mon May 18 06:13:01 2009
Deleted file /data/oradata/test102/system01.dbf
Deleted file /data/oradata/test102/undotbs1.dbf
Deleted file /data/oradata/test102/sysaux01.dbf
Deleted file /data/oradata/test102/redo01.log
Deleted file /data/oradata/test102/redo02.log
Deleted file /data/oradata/test102/redo03.log
Deleted file /data/oradata/test102/redo04.log
Deleted file /data/oradata/test102/redo05.log
Deleted file /data/oradata/test102/redo06.log
Deleted file /data/oradata/test102/redo07.log
Deleted file /data/oradata/test102/temp01.dbf
Instance terminated by USER, pid = 15795
Deleted file /data/oradata/test102/control01.ctl
Completed: drop database
Mon May 18 06:13:08 2009
Shutting down instance (abort)
License high water mark = 1