Oracle 10g資料檔案的收縮與資料對象跨資料表空間的遷移

1.Oracle10g資料庫檔案的收縮：

  1.1 問題：

    一個資料庫裡面有上百萬條資料，使用Delete語句刪除大部分資料後，資料庫檔案所佔空間並不會縮小釋放多餘的空間。

    如使用RESIZE語句對資料檔案大小做調整，如下：

    >ALTER DATABASE DATAFILE 'D:/Oracle10g/oradata/ORDB/NEWDB.ORA' RESIZE 100M;

    則會報一個錯誤：ORA-03297: 檔案包含在請求的 RESIZE 值以外使用的資料。

    這主要涉及到Oracle的邏輯儲存和段空間管理以及資料庫塊中的資料分布。

    Oralce有一個High Water Mark（高水位線），它表示Oracle對象使用空間最高水位線。

    當插入了資料以後，HWM就會上漲；Delete語句刪除資料後，HWH並沒有降低。

    HWH資料庫塊分布在調整範圍之外，此時使用RESIZE命令會報錯。

 

  1.2 方法：

    網上查到資料說通過Move Tablespace移動表和索引後，可以重設表的HWH，然後再使用Resize就可以縮減資料庫檔案。
 
    但我在Oracle10g中嘗試多次，都沒有成功。另外，資料庫如果有Long型的欄位，是不能使用Move命令的。
 
    好在是Oracle10g，有shrink！ :)

    Shrink的文法如下：
 
    --------------------

    alter table <table_name> shrink space [ <null> | compact | cascade ];
    alter table <table_name> shrink space compcat;
    說明：收縮表，移動塊中資料位元置到一起，但 High Water Mark 保持不變

    alter table <tablespace_name> shrink space;
    說明：收縮表，降低 High Water Mark

    alter table <tablespace_name> shrink space cascade;
    說明：收縮表，降低 High Water Mark，並且把相關索引也收縮一下。

    alter index idxname shrink space;
    說明：回縮索引

    --------------------

 

  1.3 操作：

    對所有資料表空間中的所有表執行一次shrink命令，需要根據shrink文法，構造shrink命令。

    此時，為了構建shrink命令，可以使用如下SQL語句來查詢資料表空間資料庫檔案的File_ID:

    ---------------------

    select * from dba_data_files;

    ---------------------

    另外，在執行shrink前，必須開啟資料表的行遷移功能，SQL語句如下：

    ---------------------

    alter table Tbl_Name enable row movement;

    ---------------------
    注意：該SQL語句會造成參考資料表Tbl_Name的對象(如預存程序、包、視圖等)變為無效，後續需要重新編譯一次確認是否有效。

    構造shrink命令，建立create_shrink.sql檔案，其內容如下：

    -------------------
    set heading off;
    set echo off;
    set feedback off;
    set termout on;
    spool c:/shrink.sql;
    select 'alter table THAMS.'|| table_name || ' enable row movement; ' from user_tables where tablespace_name='THAMS';
    select 'alter table THAMS.'|| table_name || ' shrink space CASCADE;' from user_tables where tablespace_name='THAMS';
    select 'alter index THAMS.'|| segment_name || ' shrink space;' from dba_extents where segment_type='INDEX' and file_id=14;
    spool off;
    -------------------

    以資料表空間的預設使用者THAMS登入資料庫，成功執行create_shrink.sql後，得到shrink.sql檔案。

    以sys/system身份登入資料庫，執行shrink.sql檔案後，在OEMC中查看錶空間，會探索資料庫資料表空間的“已使用”比例大大下降。
  
    此時，再經過EXP/IMP後，會得到一個佔用空間很小的資料庫檔案。

1 2 下一頁