How to Remove and Drop Datafiles from Tablespace in Oracle Database如何删除和下拉datafiles从表在Oracle数据库

Oracle RDBMS databases stores data logically in the form of tablespaces and physically in the form of data files.甲骨文RDBMS的数据库存储数据，在逻辑上的形式，表及身体，在形式的数据文件。 The datafiles are added to the tablespaces as database space requirement grows bigger.该datafiles被添加到该表作为数据库空间的要求，更大的增长。 However, there are several reasons you may want to remove or delete data files from a tablespace.不过，有几个原因，您可能会想要移除或删除数据文件从表。 Such as accidentally add a wrongly sized or unwanted datafile to a tablespace, or the data space usage has became smaller and some data files want to be removed, or attempt to recover Oracle database which fails to start due to missing or corrupted datafiles by removing them, Oracle does not provide an easy way or user interface to delete or drop datafiles from a tablespace.如不小心添加一个错误的大中型或有害datafile一个表，或数据空间的使用已成为规模较小的一些数据文件要被删除，或企图以收回Oracle数据库，其中无法启动，因丢失或损坏的datafiles消除他们，甲骨文公司并没有提供一个简单的方法或用户界面，删除或下降datafiles从表。 Once a datafile is made part of a tablespace, it can no longer be detached or removed from the tablespace, albeit there are several workarounds.一旦datafile作出的一个组成部分，表，它再也不能超脱或删除表，虽然有几个变通办法。

How to Completely Drop the Whole Tablespace with All Datafiles 如何完全下降，整个表的所有datafiles

The easiest way to drop a or multiple datafiles is by dropping the entire tablespace together with its datafiles.最简单的方式，减少一或多个datafiles是由下降，整个表连同其datafiles 。 If you no longer need the data contents of the tablespace, the following command will drop the tablespace, the datafile, and the tablespace’s contents from the data dictionary.如果您不再需要的数据内容表，下面的命令将下降表， datafile ，并表的内容从数据字典。 All of the objects that where contained in that tablespace are permanently removed.所有的物体，如载于该表是永久删除。

DROP TABLESPACE <tablespace name> INCLUDING CONTENTS AND DATAFILES; 下拉表<tablespace名称，包括的内容和datafiles ;

If you don’t specify “AND DATAFILES”, Oracle will not drop the physical datafile after the DROP TABLESPACE command, but you can always delete the files from operating system shell (If the data files are locked, restart the server).如果您不指定“和datafiles ” ，甲骨文将不会下降，物理datafile后的下拉表的命令，但您随时可以删除这些文件从操作系统壳牌（如果数据文件被锁定，重新启动服务器） 。

But always perform a backup of the database, as the “DROP TABLESPACE” command is irreversible.但总是执行备份的数据库，作为“下拉表”命令是不可逆转的。 It’s also a good practice to check and identify how many datafiles a tablespace actually has before performing the drop action in order to avoid mistake.这也是一个很好的做法，检查，并确定有多少datafiles一表，其实之前表演的下拉行动，以避免错误。

How to Check How Many Datafiles a Tablespace Has 如何检查有多少datafiles一表已

To determine and identify all datafiles that link to a tablespace, use the following query, with tablespace name in capital letter:以确定，并查明所有datafiles链接到表，请使用下面的查询，与表的名称在大写字母：

SELECT file_name, tablespace_name FILE_NAME的选择， tablespace_name
FROM dba_data_files 从dba_data_files
WHERE tablespace_name =’<tablespace name>’; 凡tablespace_name = ' <tablespace名称' ;

If a tablespace contains multiple datafiles, and you just want to drop one or some of the datafiles and keep the remaining datafiles together with the objects and contents, the objects or data must be exported for the affected table space.如果一个表包含多个datafiles ，你只想下降的一个或部分的datafiles并保持其余datafiles连同对象和内容，对象或数据，必须出口，为受影响的表空间。 Once exported, the tablespace can be dropped with above “DROP TABLESPACE” command.一旦出口，表可以下降，与上述“下拉表”命令。 Then, recreate the tablespace with the datafile(s) required (that you initially want to keep), and then import the objects into the recreated tablespace.然后，重新表与datafile （ ）所需的（即您最初要保留） ，然后导入物体到重现表。

If one or more datafiles is missing after a recovery process or accidental deletion, you can use ALTER DATABASE DATAFILE <datafile name> OFFLINE DROP command to make the datafile offline so that database can starts up after which the troubled tablespace can be dropped.如果一个或多个datafiles是失踪后的复苏过程中或意外删除，您可以使用的ALTER DATABASE datafile <datafile名称离线下拉命令，使datafile离线，使数据库可以启动后，其中的困扰表可以下降。 (See （见 instruction to recover from missing datafiles指示收回从失踪datafiles .) ） 。

How to Resize a Datafile to Minimum Size 如何调整datafile ，以最小尺寸

Another alternative to drop the datafile is by shrinking the size of datafile instead of dropping.另一种选择放弃datafile是通过缩小规模datafile而不是下降。 This option is only possible if there is no extents in the datafile.这个方案是唯一可行的，如果有任何程度，在datafile 。 If there are none, it’s possible to resize the data file down to a very small file (2 blocks), where Oracle database will no longer create any extent in the datafile.如果没有，很可能要调整的数据文件到一个很小的文件（ 2块） ， Oracle数据库将不再产生任何程度上，在datafile 。 However, this workaround does not remove the datafile from tablespace nor delete the file physically, but it reduce the risk as no data will be truncated as only empty blocks are reduced.然而，这种替代不会删除datafile从表或删除的档案身体，但减少的风险，作为没有数据将被截断，因为只有空座减少。 It simply makes it unusable and takes up almost close to no disk space.它只是使得使用，而且占用了几乎接近没有磁盘空间。 To resize a datafile, use the following query:要调整datafile ，使用下列查询：

alter database datafile ‘<datafile name>’ resize 8M; 改变数据库datafile ' <datafile名称> '调整八米;

How to Determine the Extents inside Datafile 如何确定程度内datafile

To check and determine the extents that exists in a datafile, use this query:检查和确定程度的存在在一个datafile ，使用此查询：

SELECT owner, segment_name 选取业主， segment_name
FROM dba_extents a, dba_data_files b 从dba_extents一， dba_data_files b
WHERE a.file_id = b.file_id 凡a.file_id = b.file_id
AND b.file_name = ‘<datafile name>’ 和b.file_name = ' <datafile名称> '

IMPORTANT : This is a machine translated page which is provided "as is" without warranty. 重要说明 ：这是一个机器翻译网页是“按原样”提供的担保。 Machine translation may be difficult to understand.机器翻译可能很难理解。 Please refer to请参阅 original English article原来的英语文章 whenever possible.只要有可能。

Share and contribute or get technical support and help at分享和贡献，或取得技术的支持和帮助，在 My Digital Life Forums 我的数字生活论坛 . 。

Related Articles相关文章

• How Drop Tablespace and Recover Oracle Database When Accidentally Delete Datafile如何下拉表和恢复Oracle数据库时，不小心删除datafile
• How to Rename or Move Oracle Tablespace Datafile to Another Location如何重新命名或移动甲骨文表datafile到另一个位置
• ORA-25153 Temporary Tablespace is Empty Error in Oracle库存- 25153临时表是空的错误，在Oracle
• Oracle ORA-01658 Unable to Create INITIAL Extent for Segment in Tablespace Error甲骨文库存- 01658无法建立初步的程度，为部分在表错误
• Delete, Remove or Drop Oracle Stored Packages with Drop Package删除，移除或下降， Oracle存储套件与下拉包
• Oracle Database Link Oracle数据库的连接
• ORA-02449 Oracle Drop Table Error库存- 02449甲骨文下拉表错误
• Oracle Database Import Error 3113/3114 Oracle数据库的进口错误三千一百一十四分之三千一百十三
• Change Oracle Database User Password改变Oracle数据库的用户密码
• Check Oracle Version检查的Oracle版本

This entry was posted on Saturday, August 4th, 2007 at 12:29 am and is filed under 此项目被张贴在周六， 2007年8月4日在上午12时29分，并提交下 Databases 数据库 . 。 You can follow any responses to this entry through the 您可以按照任何的反应，此项目通过 RSS 2.0 2.0 feed. 喂养。 You can 您可以 leave a response 留下的回应 , or ，或 trackback Trackback跟踪 from your own site. 从你自己的网站。