oracle 線上重組表

 

Oracle 9i後推出了DBMS_REDEFINITION軟體包，這個軟體包提供了線上重定義表的功能，可以在保持表可以更新的同時重新組織表的結構，內部原理是基於MV實現的，線上重定義表提供了很多好處：

 

#重組表資料，壓縮空間

#線上把表重一個空間遷移到另外一個空間

#把表從普通錶轉換為分割槽表或者由分割槽錶轉換為普通表

#線上刪除欄位

#線上修改一些表引數，一些需要重組才生效的，如initrans.

 

 

---建立測試表

create table test (a int,b int);

---建立模擬資料

declare i integer;

begin

      for  i  in 1..100 loop

         insert into test values(i,100-i);

     end loop;

commit;

end;

/

 

---建立一個觸發器，模擬表有觸發器的情況

create table audit_test(id number);

create or replace trigger tr_test

 before insert or update or delete on test

 for each row

begin

 update audit_test set id=id+1;

 end;

/

 

     

 

線上重定義表的步驟：

#選擇重定義的方式，有基於pk和基於rowid的方式，10G以上才支援ROWID的方式，10GR2以上才會自動unset m_row$$列

 

#檢查表是否可以線上重定義(使用dbms_redifinition.can_redef_table過程）

 

SQL> exec dbms_redefinition.can_redef_table('TEST','TEST');

BEGIN dbms_redefinition.can_redef_table('TEST','TEST'); END;

 

*

ERROR at line 1:

ORA-12089: cannot online redefine table "TEST"."TEST" with no primary key

ORA-06512: at "SYS.DBMS_REDEFINITION", line 137

ORA-06512: at "SYS.DBMS_REDEFINITION", line 1479

ORA-06512: at line 1

提示沒有PK，給test加上一個pk

 

SQL> alter table test add constraint pk_test_id primary key (a);

 

Table altered.

 

SQL> exec dbms_redefinition.can_redef_table('TEST','TEST');

 

PL/SQL procedure successfully completed.

 

可以發現可以重定義test了。

 

#建立需要重定義的中間表

SQL> create table int_test

  2  (a int,b int ,c int)

  3  partition by range(a)

  4  (partition p10 values less than (50),

  5   partition p20 values less than (100),

  6   partition p30 values less than (150),

  7   partition p40 values less than (200));

 

Table created.

 

 

 

#執行線上重定義：

---授予執行這個過程的許可權:

create any table

alter any table

drop any table

lock any table

select any table

 

 

SQL> execute dbms_redefinition.start_redef_table('TEST','TEST','INT_TEST','a a,b b,0 c');

 

PL/SQL procedure successfully completed.

 

SQL>

 

# 在新建表上建立PK或則是唯一索引

 

SQL> alter table int_test add constraint pk_test_id1 primary key (a);

 

Table altered.

 

SQL>

 

 

 

#執行表同步，這步不是必須的，重構完成執行finish會自動呼叫，如果資料量很大，中間手動執行同步可以加快切換。

 

SQL> execute dbms_redefinition.sync_interim_table('TEST','TEST','INT_TEST');

 

PL/SQL procedure successfully completed.

 

SQL>

 

# 檢查新表的許可權，許可權需要手工授予

#完成重構

execute dbms_redefinition.FINISH_REDEF_TABLE('TEST','TEST','INT_TEST');

 

 

注意：對於所有資料字典中的這兩個表，只是完成了名字的對換，原表上的特性還需手工遷移過來，如索引，tirrger等。

 

 

最後的工作刪除原表（原表現在為int_test 已經換名）。