11G的online redefinition有了不少功能增強，相信大家都在其他地方早已經看到這些概念，於是不再廢話，直接來看一下我們可能常常需要問到的幾種實際例子。

1.Add column

11G的alter table add column操作已經有了不小改進，尤其是對add column with default value採用ecol$表儲存default value從而避免了對整表的update操作，但也因此引入了不斷對ecol$表的查詢的overhead。於是，此時我們想到了online redefinition。

--建測試表的SQL(分別建立各種相關的object用來測試):

--按照官方文件，trigger是不會自動recompile的，其他一般在online redefinition之後都為valid的狀態，省去了DBA的麻煩。

create table hao(object_id,object_name,status) tablespace test_assm
as select object_id,object_name,status from dba_objects;

alter table hao add constraint haopk primary key (object_id);

create public synonym synhao for hao;

create view viewhao as select * from hao;

create or replace trigger thao before insert on hao for each row
declare
v_d hao%rowtype;
begin
select * into v_d from hao where rownum=1;
end;
/

--建立TMPHAO作為中間表，並且add new column

SQL> create table tmphao as select * from hao where 1=2;

Table created.

SQL> alter table tmphao add object_id2 number;

Table altered.

SQL> select object_name,object_type,status from user_objects;

OBJECT_NAM OBJECT_TYPE         STATUS
---------- ------------------- -------
HAO        TABLE               VALID
HAOPK      INDEX               VALID
THAO       TRIGGER             VALID
TMPHAO     TABLE               VALID
VIEWHAO    VIEW                VALID

SQL> BEGIN
2 DBMS_REDEFINITION.CAN_REDEF_TABLE(UNAME=>'HAO',TNAME=>'HAO',OPTIONS_FLAG=>DBMS_REDEFINITION.CONS_USE_PK);
3 END;
4 /

PL/SQL procedure successfully completed.

--這裡我設定新加的column的default value為0

SQL> BEGIN
2 DBMS_REDEFINITION.START_REDEF_TABLE('HAO', 'HAO','TMPHAO',
3 'object_id object_id,object_name object_name,status status,0 object_id2',dbms_redefinition.cons_use_pk);
4 END;
5 /

PL/SQL procedure successfully completed.

--我們看到開始重定義時，為了保證在該表的併發dml操作，其實是透過建立MV log來保證的。

SQL> select table_name from user_tables;

TABLE_NAME
------------------------------
HAO
MLOG$_HAO
RUPD$_HAO
TMPHAO

SQL> insert into hao values(1986000,'1986','1');

1 row created.

SQL> commit;

Commit complete.

SQL> select DMLTYPE$$,OLD_NEW$$ from MLOG$_HAO;

D O
- -
I N

SQL> DECLARE
2 num_errors PLS_INTEGER;
3 BEGIN
4 DBMS_REDEFINITION.COPY_TABLE_DEPENDENTS('HAO', 'HAO','TMPHAO',
5 DBMS_REDEFINITION.CONS_ORIG_PARAMS, TRUE, TRUE, TRUE, TRUE, num_errors);
6 END;
7 /

PL/SQL procedure successfully completed.

SQL> select object_name, base_table_name, ddl_txt from
2 DBA_REDEFINITION_ERRORS;

no rows selected

--而DBMS_REDEFINITION.SYNC_INTERIM_TABLE的作用有點像對MV的一次fast refresh，以減少finish online redefinition的lock時間。

SQL> BEGIN
2 DBMS_REDEFINITION.SYNC_INTERIM_TABLE('HAO', 'HAO', 'TMPHAO');
3 END;
4 /

PL/SQL procedure successfully completed.

SQL> select DMLTYPE$$,OLD_NEW$$ from MLOG$_HAO;

no rows selected

SQL> BEGIN
2 DBMS_REDEFINITION.FINISH_REDEF_TABLE('HAO', 'HAO', 'TMPHAO');
3 END;
4 /

PL/SQL procedure successfully completed.

SQL> select table_name from user_tables;

TABLE_NAME
------------------------------
HAO
TMPHAO

--這裡我們看到view和trigger為INVALID，trigger可以理解，但view為什麼呢？

--原來是因為view裡有select *的字樣，oracle自動轉換為當時的具體欄位，所以這點要注意。

--而trigger裡也有select *，但是並沒有轉換為當時的具體欄位。

SQL> select object_name,object_type,status from user_objects;

OBJECT_NAME          OBJECT_TYPE         STATUS
-------------------- ------------------- -------
HAO                  TABLE               VALID
HAOPK                INDEX               VALID
THAO                 TRIGGER             INVALID
TMP$$_HAOPK0         INDEX               VALID
TMP$$_THAO0          TRIGGER             INVALID
TMPHAO               TABLE               VALID
VIEWHAO              VIEW                INVALID

SQL> select text from user_views;

TEXT
--------------------------------------------------------------------------------
select "OBJECT_ID","OBJECT_NAME","STATUS" from hao

SQL> select trigger_body from user_triggers where trigger_name='THAO';

TRIGGER_BODY
--------------------------------------------------------------------------------
declare
v_d hao%rowtype;
begin
select * into v_d from hao where rownum=1;
end;

--我們可以透過觸發view和trigger一次，就可以讓其自動recompile為VALID狀態。

SQL> select * from VIEWHAO where rownum=1;

OBJECT_ID OBJECT_NAME STATUS
---------- -------------------- -------
105 ACCESS$ VALID

SQL> insert into hao values(999999,'999999','9','0');

1 row created.

SQL> insert into tmphao values(999999,'999999','9');

1 row created.

SQL> select object_name,object_type,status from user_objects;

OBJECT_NAME          OBJECT_TYPE         STATUS
-------------------- ------------------- -------
HAO                  TABLE               VALID
HAOPK                INDEX               VALID
THAO                 TRIGGER             VALID
TMP$$_HAOPK0         INDEX               VALID
TMP$$_THAO0          TRIGGER             VALID
TMPHAO               TABLE               VALID
VIEWHAO              VIEW                VALID

--synonym也為INVALID狀態，只需引用一次即可。

SQL> select OBJECT_NAME,OBJECT_TYPE,STATUS from dba_objects where OBJECT_NAME='SYNHAO';

OBJECT_NAME OBJECT_TYPE STATUS
-------------------- ------------------- -------
SYNHAO SYNONYM INVALID

SQL> show parameter compatible

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
compatible string 11.2.0.1
SQL> select * from SYNHAO where rownum=1;

OBJECT_ID OBJECT_NAME STATUS OBJECT_ID2
---------- -------------------- ------- ----------
105 ACCESS$ VALID 0

SQL> select OBJECT_NAME,OBJECT_TYPE,STATUS from dba_objects where OBJECT_NAME='SYNHAO';

OBJECT_NAME OBJECT_TYPE STATUS
-------------------- ------------------- -------
SYNHAO SYNONYM VALID

--除此之外，我在《線上重定義表導致constraint變成novalidate》裡專門提到過這種情況：

SQL> l
1 select CONSTRAINT_NAME,TABLE_NAME,STATUS,VALIDATED from user_constraints
2* where CONSTRAINT_NAME='HAOPK'
SQL> /

CONSTRAINT_NAME TABLE_NAME STATUS VALIDATED
------------------------------ ------------------------------ -------- -------------
HAOPK HAO ENABLED NOT VALIDATED

SQL> alter table hao enable validate primary key;

Table altered.

SQL> select CONSTRAINT_NAME,TABLE_NAME,STATUS,VALIDATED from user_constraints
2 where CONSTRAINT_NAME='HAOPK'
3 ;

CONSTRAINT_NAME TABLE_NAME STATUS VALIDATED
------------------------------ ------------------------------ -------- -------------
HAOPK HAO ENABLED VALIDATED

--如果我們在online redefinition過程中遇到任何Error，都可以立即終止：

BEGIN
DBMS_REDEFINITION.ABORT_REDEF_TABLE('HAO', 'HAO','TMPHAO');
END;
/

2.普通錶轉為partition table

--建立TMPHAO2為一個range partition table作為中間表

create table tmphao2
(OBJECT_ID NUMBER,
OBJECT_NAME VARCHAR2(128),
STATUS VARCHAR2(7),
OBJECT_ID2 NUMBER)
partition by range(OBJECT_ID)
(partition p1 values less than(10),
partition p2 values less than(20),
partition p3 values less than(maxvalue)
);

--同樣的命令，只是記得檢視所有invalid的objects。

SQL> BEGIN
2 DBMS_REDEFINITION.CAN_REDEF_TABLE(UNAME=>'HAO',TNAME=>'HAO',OPTIONS_FLAG=>DBMS_REDEFINITION.CONS_USE_PK);
3 END;
4 /

PL/SQL procedure successfully completed.

SQL> BEGIN
2 DBMS_REDEFINITION.START_REDEF_TABLE('HAO', 'HAO','TMPHAO2',
3 '*',dbms_redefinition.cons_use_pk);
4 END;
5 /

SQL> DECLARE
2 num_errors PLS_INTEGER;
3 BEGIN
4 DBMS_REDEFINITION.COPY_TABLE_DEPENDENTS('HAO', 'HAO','TMPHAO2',
5 DBMS_REDEFINITION.CONS_ORIG_PARAMS, TRUE, TRUE, TRUE, TRUE, num_errors);
6 END;
7 /

PL/SQL procedure successfully completed.

SQL> select object_name, base_table_name, ddl_txt from
2 DBA_REDEFINITION_ERRORS;

no rows selected

SQL> BEGIN
2 DBMS_REDEFINITION.FINISH_REDEF_TABLE('HAO', 'HAO', 'TMPHAO2');
3 END;
4 /

PL/SQL procedure successfully completed.

SQL> select PARTITION_NAME from user_tab_partitions where TABLE_NAME='HAO';

PARTITION_NAME
------------------------------
P1
P2
P3

3.更改被index的column name。

--這時我在前面的HAO的object_id2上建立index。

SQL> create index haoidx2 on HAO(object_id2) local;

Index created.

--在TMPHAO3中將object_id2改為object_id3

--如果直接DBMS_REDEFINITION.COPY_TABLE_DEPENDENTS，會報錯說找不到object_id2這個column。

--所以需要我們手工register

create table tmphao3
(OBJECT_ID NUMBER,
OBJECT_NAME VARCHAR2(128),
STATUS VARCHAR2(7),
OBJECT_ID3 NUMBER)
partition by range(OBJECT_ID)
(partition p1 values less than(10),
partition p2 values less than(20),
partition p3 values less than(maxvalue)
);

SQL> create index haoidx3 on tmphao3(object_id3) local;

Index created.

exec DBMS_REDEFINITION.CAN_REDEF_TABLE(UNAME=>'HAO',TNAME=>'HAO',OPTIONS_FLAG=>DBMS_REDEFINITION.CONS_USE_PK);

BEGIN
DBMS_REDEFINITION.START_REDEF_TABLE('HAO', 'HAO','TMPHAO3',
'OBJECT_ID OBJECT_ID,OBJECT_NAME OBJECT_NAME,STATUS STATUS,OBJECT_ID2 OBJECT_ID3',dbms_redefinition.cons_use_pk);
END;
/

BEGIN
DBMS_REDEFINITION.REGISTER_DEPENDENT_OBJECT(
uname => 'HAO',
orig_table => 'HAO',
int_table => 'TMPHAO3',
dep_type => DBMS_REDEFINITION.CONS_INDEX,
dep_owner => 'HAO',
dep_orig_name => 'HAOIDX2',
dep_int_name => 'HAOIDX3');
END;
/

DECLARE
num_errors PLS_INTEGER;
BEGIN
DBMS_REDEFINITION.COPY_TABLE_DEPENDENTS('HAO', 'HAO','TMPHAO3',
DBMS_REDEFINITION.CONS_ORIG_PARAMS, TRUE, TRUE, TRUE, TRUE, num_errors);
END;
/

SQL> select object_name, base_table_name, ddl_txt from
2   DBA_REDEFINITION_ERRORS;

no rows selected

exec DBMS_REDEFINITION.FINISH_REDEF_TABLE('HAO', 'HAO', 'TMPHAO3');

SQL> l
1* select INDEX_NAME,COLUMN_NAME from user_ind_columns where TABLE_NAME='HAO'
SQL> /

INDEX_NAME                     COLUMN_NAM
------------------------------ ----------
HAOIDX2                        OBJECT_ID3
HAOPK                          OBJECT_ID

4.更改表所在的tablespace

過程完全如前。只是中間表建立在所期望的tablespace上。

5.long轉換為clob，long raw轉換為blob

create table hao2 (id number primary key,name long);
insert into hao2 select object_id,'hao' from dba_objects;

create table tmp_hao2 (id number,name clob);

BEGIN
DBMS_REDEFINITION.START_REDEF_TABLE('HAO', 'HAO2','TMP_HAO2',
'id id,to_lob(name) name',dbms_redefinition.cons_use_pk);
END;
/

DECLARE
num_errors PLS_INTEGER;
BEGIN
DBMS_REDEFINITION.COPY_TABLE_DEPENDENTS('HAO', 'HAO2','TMP_HAO2',
DBMS_REDEFINITION.CONS_ORIG_PARAMS, TRUE, TRUE, TRUE, TRUE, num_errors);
END;
/

SQL> select object_name, base_table_name, ddl_txt from
2   DBA_REDEFINITION_ERRORS;

no rows selected

exec DBMS_REDEFINITION.FINISH_REDEF_TABLE('HAO', 'HAO2', 'TMP_HAO2');

SQL> desc hao2
Name                                      Null?    Type
----------------------------------------- -------- ----------------------------
ID                                        NOT NULL NUMBER
NAME                                               CLOB

11G online redefinition的幾個常用例項

相關文章