如何删除数据文件

如何删除数据文件


q:我一个表空间里面有个数据文件想删除,怎么做?

a:
最好不要做这样的操作,oracle只提供了offline drop方式。你可以把他resize到很小

FYI:
Oracle? Database Administrator’s Guide 11g Release 1 (11.1)B28310-04

Dropping Datafiles

You use the DROP DATAFILE and DROP TEMPFILE clauses of the ALTER TABLESPACE command to drop a single datafile or tempfile. The datafile must be empty. (A datafile is considered to be empty when no extents remain allocated from it.) When you drop a datafile or tempfile, references to the datafile or tempfile are removed from the data dictionary and control files, and the physical file is deleted from the file system or Automatic Storage Management (ASM) disk group.

The following example drops the datafile identified by the alias example_df3.f in the ASM disk group DGROUP1. The datafile belongs to the example tablespace.

ALTER TABLESPACE example DROP DATAFILE ‘+DGROUP1/example_df3.f’;
The next example drops the tempfile lmtemp02.dbf, which belongs to the lmtemp tablespace.
ALTER TABLESPACE lmtemp DROP TEMPFILE ‘/u02/oracle/data/lmtemp02.dbf’;
This is equivalent to the following statement:

ALTER DATABASE TEMPFILE ‘/u02/oracle/data/lmtemp02.dbf’ DROP
INCLUDING DATAFILES;
See Oracle Database SQL Language Reference for ALTER TABLESPACE syntax details.

Restrictions for Dropping Datafiles

The following are restrictions for dropping datafiles and tempfiles:
?
The database must be open.
数据库必须是open状态。
?
If a datafile is not empty, it cannot be dropped.
如果数据文件非空,就不能被删除。
If you must remove a datafile that is not empty and that cannot be made empty by dropping schema objects, you must drop the tablespace that contains the datafile.

–DROP tablespace tbs_name including contents and datafiles;

–alter tablespace test drop datafile 8;
命令不能drop 非空的数据文件, 如果要drop 某个数据文件,需要先把对象移除走,等drop 完成后,再移回来。

1.确定某表空间中相应数据文件中的对象
SELECT owner ownr,
segment_name name,
segment_type TYPE,
extent_id exid,
file_id fiid,
block_id blid,
blocks blks
FROM dba_extents
WHERE file_id = 4
ORDER BY block_id;
2.alter table table_name move tablespace tbs_name;

 

?
You cannot drop the first or only datafile in a tablespace.
不能删除表空间中1号数据文件
This means that DROP DATAFILE cannot be used with a bigfile tablespace.

?
You cannot drop datafiles in a read-only tablespace.
无法删除只读文件
?
You cannot drop datafiles in the SYSTEM tablespace.
无法删除system 表空间的数据文件
?
If a datafile in a locally managed tablespace is offline, it cannot be dropped.
无法删除一个lmt的离线数据文件。

标签