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.

Oracle 11g刪除資料檔案時報錯'ORA-01916: keyword ONLINE, OFFLINE, RESIZE'

相關文章