只能用于数据文件中的数据无需恢复的情况下
SQL> alter database datafile '/opt/TZWX.dbf' offline drop;
Database altered.
SQL> alter database open;
Database altered.
SQL> drop tablespace TZWX including contents;
Tablespace dropped.
[oracle@test11 oracle]$ sqlplus /nolog
SQL*Plus: Release 9.2.0.4.0 - Production on Sat Aug 6 13:26:25 2005
Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.
SQL> connect / as sysdba;
Connected to an idle instance.
SQL> startup mount;
ORACLE instance started.
Total System Global Area 236000356 bytes
Fixed Size 451684 bytes
Variable Size 201326592 bytes
Database Buffers 33554432 bytes
Redo Buffers 667648 bytes
Database mounted.
SQL> alter database datafile 14 offline;
alter database datafile 14 offline
*
ERROR at line 1:
ORA-01145: offline immediate disallowed unless media recovery enabled
SQL> alter datafile 14 offline;
alter datafile 14 offline
*
ERROR at line 1:
ORA-00940: invalid ALTER command
SQL> recover datafile 14;
ORA-00283: recovery session canceled due to errors
ORA-01110: data file 14: '/opt/TZWX.dbf'
ORA-01157: cannot identify/lock data file 14 - see DBWR trace file
ORA-01110: data file 14: '/opt/TZWX.dbf'
SQL> alter database datafile 14 offline;
alter database datafile 14 offline
*
ERROR at line 1:
ORA-01145: offline immediate disallowed unless media recovery enabled
SQL> alter database open;
alter database open
*
ERROR at line 1:
ORA-01157: cannot identify/lock data file 14 - see DBWR trace file
ORA-01110: data file 14: '/opt/TZWX.dbf'
SQL> alter database datafile'/opt/TZWX.dbf' offline;
alter database datafile'/opt/TZWX.dbf' offline
*
ERROR at line 1:
ORA-01145: offline immediate disallowed unless media recovery enabled
SQL> alter database datafile '/opt/TZWX.dbf' offline;
alter database datafile '/opt/TZWX.dbf' offline
*
ERROR at line 1:
ORA-01145: offline immediate disallowed unless media recovery enabled
SQL> alter database datafile '/opt/TZWX.dbf' offline immediate;
alter database datafile '/opt/TZWX.dbf' offline immediate
*
ERROR at line 1:
ORA-00933: SQL command not properly ended
SQL> alter database datafile '/opt/TZWX.dbf' offline drop;
Database altered.
SQL> alter database open;
Database altered.
数据库打开后还需删除该数据文件所在的表空间
SQL> select tablespace_name,status from dba_tablespaces;
TABLESPACE_NAME STATUS
------------------------------ ---------
SYSTEM ONLINE
UNDOTBS1 ONLINE
TEMP ONLINE
CWMLITE ONLINE
DRSYS ONLINE
EXAMPLE ONLINE
INDX ONLINE
ODM ONLINE
TOOLS ONLINE
USERS ONLINE
XDB ONLINE
TABLESPACE_NAME STATUS
------------------------------ ---------
SZJLT ONLINE
GAMETEST ONLINE
SZJLT_CHAT ONLINE
TZWX ONLINE
15 rows selected.
SQL> drop user tzwx cascade;
User dropped.
SQL> drop tablespace TZWX ;
drop tablespace TZWX
*
ERROR at line 1:
ORA-01549: tablespace not empty, use INCLUDING CONTENTS option
SQL> rop tablespace TZWX including contents;
SP2-0734: unknown command beginning "rop tables..." - rest of line ignored.
SQL> drop tablespace TZWX including contents;
Tablespace dropped.
SQL>
#注意：要先删除用户，然后再删除已经offline drop的表空间.
参考：http://www.oracle.com.cn/viewthread.php?tid=12097&highlight=cannot%2Bidentify%2Flock%2Bdata%2Bfile