oracle實驗記錄 （線上重定義表）

fufuh2o發表於2009-09-14
Oracle


有以下幾個功能
1.修改表storage引數,
2.移動到其它tablespace,
3.加parallelism 查詢,
4.增加刪除分割槽,
5.rebulid table減少碎片
6.HEAP TABLE 轉為IOT(索引組織表,表就是index,index就是表)或反向操作,
7.add or drop一個列

 

主要就是使用下面的包
SQL> desc dbms_redefinition;
PROCEDURE ABORT_REDEF_TABLE~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~強制退出
引數名稱                       型別                    輸入/輸出預設值?
------------------------------ ----------------------- ------ --------
 UNAME                          VARCHAR2                IN
 ORIG_TABLE                     VARCHAR2                IN
 INT_TABLE                      VARCHAR2                IN
 PART_NAME                      VARCHAR2                IN     DEFAULT
PROCEDURE CAN_REDEF_TABLE
引數名稱                       型別                    輸入/輸出預設值?
------------------------------ ----------------------- ------ --------
 UNAME                          VARCHAR2                IN
 TNAME                          VARCHAR2                IN
 OPTIONS_FLAG                   BINARY_INTEGER          IN     DEFAULT
 PART_NAME                      VARCHAR2                IN     DEFAULT
PROCEDURE COPY_TABLE_DEPENDENTS
引數名稱                       型別                    輸入/輸出預設值?
------------------------------ ----------------------- ------ --------
 UNAME                          VARCHAR2                IN
 ORIG_TABLE                     VARCHAR2                IN
 INT_TABLE                      VARCHAR2                IN
 COPY_INDEXES                   BINARY_INTEGER          IN     DEFAULT
 COPY_TRIGGERS                  BOOLEAN                 IN     DEFAULT
 COPY_CONSTRAINTS               BOOLEAN                 IN     DEFAULT
 COPY_PRIVILEGES                BOOLEAN                 IN     DEFAULT
 IGNORE_ERRORS                  BOOLEAN                 IN     DEFAULT
 NUM_ERRORS                     BINARY_INTEGER          OUT
 COPY_STATISTICS                BOOLEAN                 IN     DEFAULT
PROCEDURE FINISH_REDEF_TABLE
引數名稱                       型別                    輸入/輸出預設值?
------------------------------ ----------------------- ------ --------
 UNAME                          VARCHAR2                IN
 ORIG_TABLE                     VARCHAR2                IN
 INT_TABLE                      VARCHAR2                IN
 PART_NAME                      VARCHAR2                IN     DEFAULT
PROCEDURE REGISTER_DEPENDENT_OBJECT
引數名稱                       型別                    輸入/輸出預設值?
------------------------------ ----------------------- ------ --------
 UNAME                          VARCHAR2                IN
 ORIG_TABLE                     VARCHAR2                IN
 INT_TABLE                      VARCHAR2                IN
 DEP_TYPE                       BINARY_INTEGER          IN
 DEP_OWNER                      VARCHAR2                IN
 DEP_ORIG_NAME                  VARCHAR2                IN
 DEP_INT_NAME                   VARCHAR2                IN
PROCEDURE START_REDEF_TABLE
引數名稱                       型別                    輸入/輸出預設值?
------------------------------ ----------------------- ------ --------
 UNAME                          VARCHAR2                IN
 ORIG_TABLE                     VARCHAR2                IN
 INT_TABLE                      VARCHAR2                IN
 COL_MAPPING                    VARCHAR2                IN     DEFAULT
 OPTIONS_FLAG                   BINARY_INTEGER          IN     DEFAULT
 ORDERBY_COLS                   VARCHAR2                IN     DEFAULT
 PART_NAME                      VARCHAR2                IN     DEFAULT
PROCEDURE SYNC_INTERIM_TABLE
引數名稱                       型別                    輸入/輸出預設值?
------------------------------ ----------------------- ------ --------
 UNAME                          VARCHAR2                IN
 ORIG_TABLE                     VARCHAR2                IN
 INT_TABLE                      VARCHAR2                IN
 PART_NAME                      VARCHAR2                IN     DEFAULT
PROCEDURE UNREGISTER_DEPENDENT_OBJECT
引數名稱                       型別                    輸入/輸出預設值?
------------------------------ ----------------------- ------ --------
 UNAME                          VARCHAR2                IN
 ORIG_TABLE                     VARCHAR2                IN
 INT_TABLE                      VARCHAR2                IN
 DEP_TYPE                       BINARY_INTEGER          IN
 DEP_OWNER                      VARCHAR2                IN
 DEP_ORIG_NAME                  VARCHAR2                IN
 DEP_INT_NAME                   VARCHAR2                IN

 

關於重定義有2種方法
1.基於primary key(defalut方法)
2.基於rowid (IOT 不能用),重定義後會有一個隱藏列(M_row$$ ,儲存表裡面被操作行的ROWID，作用和PK是一樣的)

過程是
先使用
1.dbms_redefinition.CAN_REDEF_TABLE 看看能 重定義不,比如少primary key,就報錯
2.在一個schema中建立一箇中間表(中間表建立為重定義後的結構,分割槽啊,加列啊之類)
3.dbms_redefinition.START_REDEF_TABLE執行重定義 ,要有原始表名,中間表名,列對映規則,重定義方法
關於對映規則:未提供規則,oracle 認為所有包括在中間表中的列用語表的重定義 若給出對映規則方法,只考慮規則方法中的列, 如果未給重定義方法 default使用primary
4.在中間表上建立index,trigger,constraint,然後授權,重定義完成時 中間表的上index ,trigger,constraint和授權 會替換到重定義表上 ,中間表中disable的約束在重定義表

中為enable
5.如果 dbms_redefinition.START_REDEF_TABLE 到FINISH_REDEF_TABLE期間有大量DML  這樣的話可多執行幾次SYNC_INTERIM_TABLE ,減少最後這步FINISH_REDEF_TABLE鎖定表時

間
6. dbms_redefinition.finish_REDEF_TABLE~完成重定義,過程中原始表會被加上X LOCK 加的時間長短 和表資料量有關係
7.可以重新命名index,constraint ,trigger, 要是定義方法用了rowid會有m_row$$列,  可以alter table XX set unused m_row$$(先在資料字典標記未用) 後 drop unused

columns


完成之後原始表使用中間表的屬性 進行重新定義,在中間表建立的index,trigger,constraint 和授權 原始表上的index,trigger,constraint(enable)在中間表drop時候也drop了,
中間表中 constraint disable 到原始表中為enable狀態

 

具體實驗

SQL> conn xh/a831115
已連線。
SQL> create table t1 (a int);

表已建立。

SQL>  declare
  2   begin
  3   for i in 1..1000 loop
  4   insert into t1 values(i);
  5   end loop;
  6   commit;
  7   end;
  8  /

PL/SQL 過程已成功完成。

SQL> alter table t1 add constraint pk_t primary key(a);

表已更改。
SQL>
SQL>  create or replace trigger tri_t1
  2   before insert on t1 for each row
  3   begin
  4   null;
  5   end;
  6  /

觸發器已建立


建立中間表

  1* create table t2 (a int,b int )partition by range(a)(partition p1 values less t
han (100),partition p2 values less than (200),partition p3 values less than (maxval
ue))
  2  ;  小於100的在P1分割槽，小於200的在P2分割槽,其它在P3分割槽,還新加了一列 b

表已建立。
SQL> COL high_value format a30
SQL> select partition_name,high_value from user_tab_partitions where table_name='T2
';

PARTITION_NAME                 HIGH_VALUE
------------------------------ ------------------------------
P1                             100
P2                             200
P3                             MAXVALUE
SQL> alter table t2 add constraint pk_t2 primary key(a);

表已更改。


SQL> execute dbms_redefinition.CAN_REDEF_TABLE('XH','T1',DBMS_REDEFINITION.CONS_USE
_PK);

PL/SQL 過程已成功完成。~~~~~~~~~~~沒錯誤 可以重定義


SQL> execute dbms_redefinition.START_REDEF_TABLE('XH','T1','T2','a a,0 b',DBMS_REDE
FINITION.CONS_USE_PK);

PL/SQL 過程已成功完成。
關於列規則
 可以看 這個例子 'empno empno, ename ename, job job, deptno+10 deptno, 0 bonus'   empno列名沒變  後兩個一樣, depton+10 deptno  列中數加10(還可以其它運算) 名

字沒變,0 BOUNS
新加BOUNS列
SQL> select count(*) from t2;

  COUNT(*)
----------
      1000

 


SQL> insert into t1 values(1001);~~~~~~~~~~~~此時還有DML 繼續操作原始表

已建立 1 行。

SQL> commit;

提交完成。

SQL> select count(*) from t2;

  COUNT(*)
----------
      1000

SQL> select count(*) from t1;

  COUNT(*)
----------
      1001

SQL> exec dbms_redefinition.sync_interim_table('XH','T1','T2');~同步一下下

PL/SQL 過程已成功完成。

SQL> select count(*) from t2;~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

  COUNT(*)
----------
      1001


給中間表 加trigger

  1   create or replace trigger tri_t2
  2     before insert on t1 for each row
  3     begin
  4     null;
  5*    end;
SQL> /

觸發器已建立

SQL>  execute dbms_redefinition.FINISH_REDEF_TABLE('XH','T1','T2');~~~~~~~~~這是會LOCK 表(X)

PL/SQL 過程已成功完成。

SQL> desc t1;
 名稱                                      是否為空? 型別
 ----------------------------------------- -------- ----------------------------
 A                                         NOT NULL NUMBER(38)~~~~~~~~~~~~~~~~~~~~~~~~~轉過來了
 B                                                  NUMBER(38)

SQL> desc t2;
 名稱                                      是否為空? 型別
 ----------------------------------------- -------- ----------------------------
 A                                         NOT NULL NUMBER(38)

SQL> select partition_name,high_value from user_tab_partitions where table_name='T2
';

未選定行
SQL> select partition_name,high_value from user_tab_partitions where table_name='T1
';

PARTITION_NAME                 HIGH_VALUE
------------------------------ ------------------------------
P1                             100
P2                             200
P3                             MAXVALUE

SQL> SELECT COUNT(*) FROM T1;

  COUNT(*)
----------
      1001

SQL> SELECT COUNT(*) FROM T2;

  COUNT(*)
----------
      1001


如果有CONSTRAINT的話也都轉換了中間表DISABLE 到 原始表ENABLE
INDEX,TRIGGER 都會轉換過來

參考

http://download.oracle.com/docs/cd/B19306_01/server.102/b14231/tables.htm#sthref2362

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

相關文章