資料庫空間重整方案

在過去釋放空間的辦法中是一直使用呼叫儲存過程(通過delete刪除部分資料後)釋放空間 ，但是空間一直無法釋放的實際狀況下（在oracle 資料庫中表是由一個或多個extents組成,每增加或擴充套件一個extent,高水位標誌(hwm)朝上上移動一個高位,其以下的空間就是資料空間的容量.當delete操作時,只是把資料刪除，而並不能讓這個hwm下降。所以空間一直無法釋放）。在此種特殊的情況下，為了在72資料庫上釋放空間 必須重整空間，進而釋放空間，來達到有效利用空間的目的。

第一 強烈建議升級資料（把現有的oracle 9i 升級到oracle 10g）升級方法有以下兩種：

A：利用oracle提供的一個升級實用程式dbua(database upgrade assistant)直接將9i的資料庫升級到10g

B：新建一個10g的資料庫,利用oracle的匯入/匯出(imp/exp)實用程式遷移資料

 

對以上兩種方法的比較

 

類 型 方 法	方法	範圍	優點	缺點
A	第一 在原來裝有oracle 9i的os上安裝oracle 10g的軟體(不需要安裝10g的資料庫) 第二 通過運10g的資料庫升級助手實用程式dbua來完成oracle 9i到oracle 10g的升級過程	資料量比較大的情況下	第一 升級速度迅速 第二 保留原有資料庫的物理結構(使用者系統許可權，表空間等),不用再次規劃資料庫的邏輯和物理結構	升級一旦失敗，原來資料庫會損壞，所以在升級前必須在測試環境下做好測試準備工作，以此來防備在正式的升級過程中出現的意外
B	第一 首先把oracle9i的資料庫的資料匯出(exp)出來 第二 然後安裝一個oracle 10g的資料庫，把匯出的資料匯入(imp)到新 oracle 10g的資料庫中	資料量不是很大的情況下	第一 資料不會丟失，資料始終是安全儲存的 第二 沒有升級的風險	第一 匯入匯出的時間漫長 第二 重新規劃資料庫的物理和邏輯結構(資料庫，資料檔案，表空間等)

 

第二 使用oracle 10g 的  shrink的新特性(聯機段空間回收(shrinking database segments online))來重整表空間

     聯機段收縮僅對自動段空間管理(assm)表空間中的表有效，shringk的實質就是對錶執行一系列的dml操作，對於表的末端找到的 ，就刪除它們，並在表的頂端重新插入。通過這樣的操作後，可以填滿表段中的空閒空間，而把表的剩餘空間保留在表的末端，隨後oracle可以重置該表的高水位標誌位(hwm)，以此來釋放自由的空間。但是由於段空間重整是通過dml操作來完成的，所以會額外滋生出大量的redo表空間

Alter table table_name enable row movement；

Alter table table_name shrink space；

第三 使用定時匯出匯入資料

     A 依然利用已有的儲存過程定期刪除不需要的資料

    B 通過使用 oracle 資料庫所在的os 利用shelll指令碼定時匯出指定某張表的資料（已經有定時匯出表資料的shell指令碼）

     C 利用oracle 的 job條用儲存過程來徹底truncate表

     D 再利用oracle 資料庫所在的os shell指令碼匯入truncate後的表

   以上三步是徹底重整表空間，讓hwm重置，達到快速釋放表空間。這樣也杜絕了使用delete無法釋放表空間的目的。

第四 聯機重定義表(online redofine table)

  A   移動需要調整的表到相同 Schema 下的 不同表空間內，以此來釋放delete後的空閒空間

1.        驗證該表是否可以進行聯機重定義(如果不可以會給出具體原因):

exec DBMS_REDEFINITION.can_REDEF_TABLE(''schema'',''tablename'',1);

2.        建立中間臨時表:

Create table tablename as select * from tablename_old(被驗證的聯機重定義表)

3.        定義表

BEGIN

     DBMS_REDEFINITION.start_redef_table

       ('' schema '',''table'',''tablename_old'',

       ''tablename的欄位         tablename_old的欄位,

        ……

        '',

       DBMS_REDEFINITION.cons_use_rowid(pk)

       );

   END;

4.        建立索引限制以及觸發器等

DBMS_REDEFINITION.COPY_TABLE_DEPENDENTS()

ALTER TABLE tablename_old

     ADD CONSTRAINT index_name PRIMARY KEY (ID)

     USING INDEX

     TABLESPACE tablespace_name;

5.        同步表資料內容並建立物化檢視

   EXEC DBMS_REDEFINITION.sync_interim_table (''schema'', ''tablename'', ''tablename_old'');

  

6.        結束重定義過程

EXEC DBMS_REDEFINITION.finish_redef_table (''schema'', ''tablename'', ''tablename_old'');

7.        刪除表

Drop table tablename_old;

注：   如果想中途停止重定義過程,則通過如下指令退出該過程:

EXEC dbms_redefinition.abort_redef_table(“schema”, “tablename”,”tablename_old”);

B   重建表以便減少碎片，通過減少碎片來充分空閒空間

1.          檢視錶空間碎片超過30%的schema

 select * from (

Select owner,table_name,(num_rows*avg_row_len/8000),blocks,

(num_rows*avg_row_len/8000)/( decode(blocks,0,1,null,1,blocks)) bb

from dba_tables where owner in ('schema') and TEMPORARY='N') tt

where tt.bb<0.7 and blocks>10;

2.        通過rename具體的的table，然後在建立表，最後插入rename後的表的資料，以此來徹底釋放表的空閒空間

第五 使用rename+insert的組合來釋放表的空閒空間

     對於實時使用的資料庫表，我們的業務型別主要是屬於以寫為主，也就是說是不斷的往表中insert資料。可以通過rename將表更名，然後按原來的結構重建表，此時插入的資料可以恢復，同事該過程的操作非常的迅速，對資料庫影響很小。隨後可以將rename表總的資料插入回來。這樣就完成了資料整理，從而hwm降低重置。從而釋放了表的空閒空間。

第六 壓縮以存在的分割槽表和表空間

    壓縮表可以使同樣的資料佔用更少的空間，它對空間的使用、效能的提高都有積極的作用，壓縮表比沒有壓縮的表在全表掃描時，邏輯讀明顯減少了。對錶進行了壓縮後，表段佔用的空間也小了。這些block被讀到buffer時仍然是壓縮的狀態，所以這樣可以減少資料庫讀取的IO，也可以認為壓縮表相當於擴充了SGA，因為同樣的資料用了更少的記憶體。一個壓縮的表可以儲存在更少的資料塊中，從而節省了寶貴的儲存空間，由此使用更少的資料塊也意味著效能的提高。在i/o受到一定限制的環境中，對一個壓縮的表進行查詢通常是可以更快的完成，因為它讀取的資料塊少了。(目前在72db上的表空間和分割槽表都沒有壓縮)

A.       壓縮一個已分割槽的表

對已分割槽的表進行壓縮時有以下兩種方法

a)       表級別上壓縮(alter table table-name compress)

b)       分割槽級別上壓縮(alter table partition_name move partition partition-value compress)

B.       使現有的表空間轉化為壓縮表空間

alter tablespace tablespace_name default compress;

 

 

 

以上各種的辦法都是從空間重整這點出發，以此來釋放空閒空間。讓寶貴的磁碟空間能高效的利用。然而在這個過程中，其他的問題也有滋生的情況，比如redo表空間，undo表空間等都會相繼的出現不足，所以在處理時。必須首先擴容這些空間的容量達到我們重整空間的目的。以上各個細節如有不足之處請指正，以便進一步的修正，更新。

來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/20976446/viewspace-674114/，如需轉載，請註明出處，否則將追究法律責任。