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

喜欢ヅ旅行 2024-04-18 00:26 46阅读 0赞

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博客 ” ，链接：http://blog.itpub.net/26506993/viewspace-2132312/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/26506993/viewspace-2132312/