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
      40
RECOVER

SQL> 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
      40
RECOVER

使用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博客

Oracle 11g删除数据文件时报错'ORA-01916: keyword ONLINE, OFFLINE, RESIZE'

最新文章