如果想要删除表空间的数据文件,最好且最安全的办法是,删除表空间后重建
步骤如下:
1,导出该表空间中的所有内容;
2,删除表空间:DROP TABLESPACE <tablespace_name> INCLUDING CONTENTS;
3,重新创建新的表空间及其数据文件;
4,将导出的数据导入表空间;
但是在实际应用环境中,数据文件往往比较大,表空间重建显得不太实际。
目标:删除表空间的一个数据文件
测试环境:Oracle 11.2.0.4.0 64bit
注意:此方法仅用来删除误加的表空间数据文件,如果该数据文件中已有数据,删除会导致表空间数据的不完整,建议使用上述方法删除。
首先,ALTER DATABASE DATAFILE '***.DBF' OFFLINE DROP,这个命令用来将该数据文件从逻辑上删除,此时该文件的online_status为recover状态
SQL> SELECT file_id, file_name, status, online_status FROM Dba_Data_Files;
FILE_ID FILE_NAME STATUS ONLINE_STATUS
---------- -------------------------------------------------------------------------------- --------- -------------
4 D:\ORADATA\ORCL\USERS01.DBF AVAILABLE ONLINE
3 D:\ORADATA\ORCL\UNDOTBS01.DBF AVAILABLE ONLINE
2 D:\ORADATA\ORCL\SYSAUX01.DBF AVAILABLE ONLINE
1 D:\ORADATA\ORCL\SYSTEM01.DBF AVAILABLE SYSTEM
5 D:\ORADATA\USER_DATAFILES\TS_CG01.DBF AVAILABLE ONLINE
6 D:\ORADATA\USER_DATAFILES\TS_CG02.DBF AVAILABLE RECOVER
6 rows selected
然后直接从file$基表中把这个文件删除(可能需要重建数据文件)
SQL> select file#,status$ from file$;
FILE# STATUS$
---------- ----------
1 2
2 2
3 2
4 2
5 2
6 2
6 rows selected
SQL> delete file$ where file# = 6;
1 row deleted
SQL> commit;
Commit complete
SQL> select file#,status$ from file$;
FILE# STATUS$
---------- ----------
1 2
2 2
3 2
4 2
5 2
重启数据库
SQL> shutdown immediate;
数据库已经关闭。
已经卸载数据库。
ORACLE 例程已经关闭。
SQL> startup
ORACLE 例程已经启动。
Total System Global Area 5060792320 bytes
Fixed Size 2289960 bytes
Variable Size 3355447000 bytes
Database Buffers 1694498816 bytes
Redo Buffers 8556544 bytes
数据库装载完毕。
数据库已经打开。
SQL> select file#, status$ from file$;
FILE# STATUS$
---------- ----------
1 2
2 2
3 2
4 2
5 2
SQL> SELECT file_id, file_name, status, online_status FROM Dba_Data_Files;
FILE_ID FILE_NAME STATUS ONLINE_STATUS
---------- -------------------------------------------------------------------------------- --------- -------------
4 D:\ORADATA\ORCL\USERS01.DBF AVAILABLE ONLINE
3 D:\ORADATA\ORCL\UNDOTBS01.DBF AVAILABLE ONLINE
2 D:\ORADATA\ORCL\SYSAUX01.DBF AVAILABLE ONLINE
1 D:\ORADATA\ORCL\SYSTEM01.DBF AVAILABLE SYSTEM
5 D:\ORADATA\USER_DATAFILES\TS_CG01.DBF AVAILABLE ONLINE
可以看到online_status为recover的数据文件6已经删掉,这时候就可以去操作系统中删除该数据文件了。
如果重启数据库失败,并且报错ORA-01207,需要重建控制文件(LZ并没有遇到,没法演示)
可以参考 https://www.cnblogs.com/jyzhao/p/9075427.html
补充:
楼主后面发现在v$datafile中还是有这个数据文件,虽然不影响,但是作为强迫症患者的楼主还是决定把它去掉;
SQL> select file#, name, status from v$datafile;
FILE# NAME STATUS
---------- -------------------------------------------------------------------------------- -------
1 D:\ORADATA\ORCL\SYSTEM01.DBF SYSTEM
2 D:\ORADATA\ORCL\SYSAUX01.DBF ONLINE
3 D:\ORADATA\ORCL\UNDOTBS01.DBF ONLINE
4 D:\ORADATA\ORCL\USERS01.DBF ONLINE
5 D:\ORADATA\USER_DATAFILES\TS_CG01.DBF ONLINE
6 D:\ORADATA\USER_DATAFILES\TS_CG02.DBF RECOVER
解决的办法是还是重建控制文件
-- 备份控制文件
SQL> alter database backup controlfile to trace as 'd:\oradata\ctl.sql';
SQL> shutdown immediate;
-- nomount状态
SQL> startup nomount;
-- 重建控制文件
SQL> @D:\oradata\CTL.SQL
SQL> ALTER DATABASE OPEN;
SQL> select file#, name, status from v$datafile;
FILE# NAME STATUS
---------- -------------------------------------------------------------------------------- -------
1 D:\ORADATA\ORCL\SYSTEM01.DBF SYSTEM
2 D:\ORADATA\ORCL\SYSAUX01.DBF ONLINE
3 D:\ORADATA\ORCL\UNDOTBS01.DBF ONLINE
4 D:\ORADATA\ORCL\USERS01.DBF ONLINE
5 D:\ORADATA\USER_DATAFILES\TS_CG01.DBF ONLINE
此时v$datafile视图中就没有 file#=6 记录了。