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. 從你自己的網站。