语法:
DROP TABLESPACE tablespace
[ INCLUDING CONTENTS [ {AND | KEEP} DATAFILES ]
[ CASCADE CONSTRAINTS ]
] ;
drop tablespace xxx 会出现如下错误:
ORA-01116: error in opening database file 89
ORA-01110: data file 89: '/templv/osm/OSM_TABP_00.dbf'
ORA-27041: unable to open file
Linux Error: 2: No such file or directory
首先将表空间文件改为offline就可以了!
SQL>alter database datafile '/templv/osm/OSM_TABP_00.dbf' offline drop;
drop tablespace osm_tabp_0 including contents
DROP TABLESPACE tablespace
[ INCLUDING CONTENTS [ {AND | KEEP} DATAFILES ]
[ CASCADE CONSTRAINTS ]
] ;
drop tablespace xxx 会出现如下错误:
ORA-01116: error in opening database file 89
ORA-01110: data file 89: '/templv/osm/OSM_TABP_00.dbf'
ORA-27041: unable to open file
Linux Error: 2: No such file or directory
Additional information: 3
首先将表空间文件改为offline就可以了!
SQL>alter database datafile '/templv/osm/OSM_TABP_00.dbf' offline drop;
是每一个文件都要offline才可以删除.
drop tablespace osm_tabp_0 including contents
就OK了!
如果需要恢复 参考下面步骤
解决步骤是:重新创建一个数据文件,然后recover过来就可以了,不过前提是日志文件还在。
1. startup mount
2. alter database create datafile'/opt/oracle/oradata/test/testdata.dbf';
3. set autorecovery on;
4. recover datafile '/opt/oracle/oradata/test/testdata.dbf';
5. alter database datafile '/opt/oracle/oradata/test/testdata.dbf' online;
6. alter database open;