Oracle 11g删除数据文件时报错SQL> alter database datafile '/database/I3DPCI/data/TEST.dbf' drop;alter database datafile '/database/I3DPCI/data/TEST.dbf' drop *ERROR at line 1:ORA-01916: keyword ONLINE, OFFLINE, RESIZE, AUTOEXTEND or END/DROP expected报错的原因是语法不正确，需要在语句后面加上offline for drop或offline drop，但是这个语法只是将数据离线，而并不是真正的删除数据文件SQL> alter database datafile '/database/I3DPCI/data/TEST.dbf' offline for drop;Database altered.SQL> select * from dba_data_files where tablespace_name='TEST';FILE_NAME-------------------------------------------------------------------------------- FILE_ID TABLESPACE_NAME BYTES BLOCKS STATUS---------- ------------------------------ ---------- ---------- ---------RELATIVE_FNO AUT MAXBYTES MAXBLOCKS INCREMENT_BY USER_BYTES USER_BLOCKS------------ --- ---------- ---------- ------------ ---------- -----------ONLINE_-------/database/I3DPCI/data/TEST.dbf 40 TEST AVAILABLE 40RECOVERSQL> alter database datafile '/database/I3DPCI/data/TEST.dbf' offline drop;Database altered.SQL> select * from dba_data_files where tablespace_name='TEST';FILE_NAME-------------------------------------------------------------------------------- FILE_ID TABLESPACE_NAME BYTES BLOCKS STATUS---------- ------------------------------ ---------- ---------- ---------RELATIVE_FNO AUT MAXBYTES MAXBLOCKS INCREMENT_BY USER_BYTES USER_BLOCKS------------ --- ---------- ---------- ------------ ---------- -----------ONLINE_-------/database/I3DPCI/data/TEST.dbf 40 TEST AVAILABLE 40RECOVER使用alter tablespace .. drop语法，如果表空间中只剩下一个数据文件，会报错SQL> alter tablespace test drop datafile '/database/I3DPCI/data/TEST.dbf';alter tablespace test drop datafile '/database/I3DPCI/data/TEST.dbf'*ERROR at line 1:ORA-03261: the tablespace TEST has only one file使用drop tablespace语法删除表空间SQL> drop tablespace test including contents cascade constraints;Tablespace dropped.
来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/26506993/viewspace-2132312/，如需转载，请注明出处，否则将追究法律责任。
转播
