删除数据库 - with DROP DATABASE Statement

Oracle 10g 增加了drop database 命令，这样我们删除数据库会方便很多。 不过DROP DATABASE还是有一定的限制条件的：

一些错误

1.ORA-01586: database must be mounted EXCLUSIVE and not open for this operation
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

2. ORA-12719: operation requires database is in RESTRICTED mode
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 database close;
SQL> alter system enable restricted session;
System altered.

SQL> drop database;
Database dropped.

或者正确的命令

SQL>startup mount

SQL>alter system enable restricted session;

SQL>DROP database;

限制条件(sql reference 原文)：
drop database

• You must have the SYSDBA system privilege to issue this statement.
• The databasemust be mounted in exclusive and restricted mode
• it must be closed.

Restricted mode:
The RESTRICTED SESSION clause lets you restrict logon to Oracle.
You can use this clause regardless of whether your instance has the database dismounted or mounted, open or closed.

Restricting Session Logons:

Example You may want to restrict logons if you are performing application maintenance and you want only application developers with RESTRICTED SESSION system privilege to log on.

To restrict logons, issue the following statement:
ALTER SYSTEM ENABLE RESTRICTED SESSION;
You can then terminate any existing sessions using the KILL SESSION clause of the ALTER SYSTEM statement.
After performing maintenance on your application, issue the following statement to allow any user with CREATE SESSION system privilege to log on:ALTER SYSTEM DISABLE RESTRICTED SESSION;
RESTRICTED SESSION,允许你以约束的方式登录数据库。你可以使用这个子句无论数据库是否挂载或者是否打开。

总结-有四点：

1、拥有SYSDBA 权限。
2、数据以独享的方式被挂载， Oracle 启动存在两种方式:独占和共享，独占启动的选项是exclusive，表示只允许一个例程使用该数据。共享启动的参数是shared,表示允许多个例程并行使用该数据库。    
3、以RESTRICTED SESSION 登录数据库。
4、数据库处于关闭状态。


DROP DATABASE该命令的作用：

• When you issue this statement, Oracle Database drops the database and deletes all control files and datafiles listed in the control file.
• If the database used a serverparameter file (spfile), it is also deleted.
• Archived logs and backups are not removed, but you can use Recovery Manager (RMAN) to remove them.
• If the database is on raw disks, this statement does not delete the actual raw disk special files.

删除数据库 - with DROP DATABASE Statement,布布扣,bubuko.com