通過線上重定義來增加大表列預設值

之前的文章討論過11G中關於大表增加列的新特性  ,

其中提到11G之前，可以通過線上重定義表的方式，來進行列的增加（且有預設值）。

線上重定義許可權需求：

grant create any table to 使用者;
grant alter any table to 使用者;
grant drop any table to 使用者;
grant lock any table to 使用者;
grant select any table to 使用者;
grant create any trigger to 使用者;
grant create any index to 使用者;

1.SQL擷取原始表的DDL

可通過如下設定，將storage 子句去除，也可以不去執行

begin
    Dbms_Metadata.Set_Transform_Param(Dbms_Metadata.Session_Transform,
                                    'SQLTERMINATOR',
                                    True);
  Dbms_Metadata.Set_Transform_Param(Dbms_Metadata.Session_Transform,
                                    'STORAGE',
                                    False);
end;

擷取原始表的DDL語句（這裡例子為T）

set pagesize 5000
set long 50000
Select Dbms_Metadata.Get_Ddl(Object_Type => 'TABLE', Name => 'T')  from dual
union all
select Dbms_Metadata.Get_Dependent_Ddl(Object_Type => 'CONSTRAINT',Base_Object_Name => 'T')  from dual
union all
select Dbms_Metadata.Get_Dependent_Ddl(Object_Type => 'INDEX',Base_Object_Name => 'T')  from dual
union all
select Dbms_Metadata.Get_Dependent_Ddl('OBJECT_GRANT', 'T', 'SCOTT')  from dual;

2.將擷取的語句中的 表名T，替換為T2,之後建立。
 
3.給t2表 增加欄位（帶有預設值）

alter table t2 add MrDai varchar2(10) default 'MrDai';

4.檢查一下能否T表能否進行重定義，如果執行成功，則表示可以，否則將會報錯顯示為什麼不可以

exec Dbms_Redefinition.Can_Redef_Table(USER, 'T');

5.開始重定義

注意:如原始表有未提交的事物,該過程會一直在等待,等待事件為enq: TX - row lock contention

exec  dbms_redefinition.start_redef_table(uname => USER,orig_table => 'T',int_table => 'T2',options_flag => DBMS_REDEFINITION.cons_use_pk);

6.完成重定義

exec dbms_redefinition.finish_redef_table(uname=>USER,orig_table=>'T',int_table=>'T2');

查看重定義以後的t

SQL> desc t;
 Name        Null?    Type
 ----------------------------------------- -------- ----------------------------
 OWNER          VARCHAR2(30)
 OBJECT_NAME        VARCHAR2(128)
 SUBOBJECT_NAME         VARCHAR2(30)
 OBJECT_ID      NOT NULL NUMBER
 .
 .
 .
 MRDAI          VARCHAR2(10)

已經添加列完成。