请问：如何彻底删除数据文件

anber45

alter database datafile '/oracle/oracle/oradata/ora920/system02.dbf'  OFFLINE drop;

删除增加错误的数据文件后，重启数据库后，用select FILE_NAME from dba_data_files;还是可以找到相应的数据文件，
select file#,name,status from v$datafile;看到数据文件的状态是RECOVER，请问怎样可以彻底的将datafile '/oracle/oracle/oradata/ora920/system02.dbf'在数据库中删除呢？

rollingpig

alter database datafile '...'
offline drop;

sailorxue

是这样的，但执行alter database datafile filename offline drop后应在v$datafile内状态为offline，这也许是Oracle公司应解决的问题。

Once you make a datafile part of a tablespace, the datafile CANNOT be removed,  although we can use some workarounds.

If you have more than one datafile in the tablespace and you wish to keep the  objects that reside in the other datafile(s) which are part of this tablespace, then you must export all the objects  inside the affected tablespace.



广东励康  提供Oracle技术支持服务

godtears

不允许的，oracle的数据在数据文件间分配，删除了一个数据文件后别的数据文件记录的内容也不完整了，如果要删一个数据文件的话和删了个表空间再重建差不多。

sshd

you should drop the entire tablespace instead of the individual datafiles.

drop tablespace xxx including contents and datafiles;
(warning: make sure know what you are doing)

"alter database datafile '/oracle/oracle/oradata/ora920/system02.dbf' OFFLINE drop;"
does not actually drop the datafile. it only marks it as offline.