Oracle分區表線上重定義欄位not null問題

Oracle分區表線上重定義欄位not null問題

Oracle通過DBMS_REDEFINITION進行線上重定義表，是基於物化視圖的方式將資料同步到新結構的中間表中，然後通過改名實現。 其中DBMS_REDEFINITION.COPY_TABLE_DEPENDENTS預存程序實現將相關依賴資訊也複製到中間表，但如果源表中有not null這種約束，就要注意。

以下測試：

環境:
os:CentOS 6.6
db:11.2.0.4

--建測試表源表
create table scott.tb_source as select * from dba_objects;
--修改源表兩個欄位為not null,以在後續步驟中產生錯誤
alter table scott.tb_source modify owner not null;
alter table scott.tb_source modify object_name not null;
--更新源表日期欄位，打散資料分布
update scott.tb_source
set created=to_date('20150101','yyyymmdd')+dbms_random.value(1,1000);
commit;

--建測試表中間表，表結構為最終源表想轉換的表結構
--此處測試用的是有子分區的分區表，無子分區的分區表也可以
create table scott.tb_mid
(
  owner          VARCHAR2(30) not null,
  object_name    VARCHAR2(128) not null,
  subobject_name VARCHAR2(30),
  object_id      NUMBER,
  data_object_id NUMBER,
  object_type    VARCHAR2(19),
  created        DATE,
  last_ddl_time  DATE,
  timestamp      VARCHAR2(19),
  status        VARCHAR2(7),
  temporary      VARCHAR2(1),
  generated      VARCHAR2(1),
  secondary      VARCHAR2(1),
  namespace      NUMBER,
  edition_name  VARCHAR2(30)
)
partition by range (created)
subpartition by list (owner)
(
PARTITION p_2015 VALUES LESS THAN (to_date('20160101','yyyymmdd'))
(subpartition p_2015_sys values('SYS'),
subpartition p_2015_system values('SYSTEM'),
subpartition p_2015_other values(default)
),
PARTITION p_2016 VALUES LESS THAN (to_date('20170101','yyyymmdd'))
(subpartition p_2016_sys values('SYS'),
subpartition p_2016_system values('SYSTEM'),
subpartition p_2016_other values(default)
),
PARTITION p_max VALUES LESS THAN (maxvalue)
(subpartition p_max_sys values('SYS'),
subpartition p_max_system values('SYSTEM'),
subpartition p_max_other values(default)
)
);

--線上重定義
--1.檢查是否可以對源表進行重定義
--此處的options_flag根據源表上有主鍵選DBMS_REDEFINITION.cons_use_pk或1，無主鍵DBMS_REDEFINITION.cons_use_rowid或2
BEGIN
  DBMS_REDEFINITION.CAN_REDEF_TABLE ('scott', 'tb_source', options_flag=>DBMS_REDEFINITION.cons_use_rowid);
END;
--2.開並行（可選）
alter session force parallel dml parallel 4;
alter session force parallel query parallel 4;

--3.開始線上重組
--此處的options_flag根據源表上有主鍵選DBMS_REDEFINITION.cons_use_pk或1，無主鍵DBMS_REDEFINITION.cons_use_rowid或2
BEGIN
  DBMS_REDEFINITION.START_REDEF_TABLE('scott','tb_source','tb_mid',options_flag=>DBMS_REDEFINITION.cons_use_rowid);
END;

--4.複製表上的相關依賴資訊，如index,trigger,constraint,privilege,statistics
--該預存程序參數如下：
/*PROCEDURE copy_table_dependents(uname              IN  VARCHAR2,
                                  orig_table        IN  VARCHAR2,
                                  int_table          IN  VARCHAR2,
                                  copy_indexes      IN  PLS_INTEGER := 1,
                                  copy_triggers      IN  BOOLEAN := TRUE,
                                  copy_constraints  IN  BOOLEAN := TRUE,
                                  copy_privileges    IN  BOOLEAN := TRUE,
                                  ignore_errors      IN  BOOLEAN := FALSE,
                                  num_errors        OUT PLS_INTEGER,
                                  copy_statistics    IN  BOOLEAN := FALSE,
                                  copy_mvlog        IN  BOOLEAN := FALSE);*/
--下面在調用該預存程序時ignore_errors=>true,忽略複製依賴資訊時的錯誤                             
DECLARE
  num_errors PLS_INTEGER;
BEGIN
  DBMS_REDEFINITION.COPY_TABLE_DEPENDENTS ('scott','tb_source','tb_mid',
    DBMS_REDEFINITION.CONS_ORIG_PARAMS, TRUE, TRUE, TRUE, TRUE, num_errors);
END;

--5.查看報錯資訊
--由於有not null約束，所以報以下錯誤。
--此問題的解決方案：1.忽略，只要只是報關天not null約束錯誤，因為其實中間表上的欄位已經not null
--              2.在建中間表的時候把not null就去掉，這樣就會不出現此錯誤                                                      
select object_name, base_table_name, to_char(ddl_txt) from DBA_REDEFINITION_ERRORS;
/*
OBJECT_NAME      BASE_TABLE_NAME      TO_CHAR(DDL_TXT)
SYS_C0011143    TB_SOURCE          ALTER TABLE "SCOTT"."TB_MID" MODIFY ("OBJECT_NAME" CONSTRAINT "TMP$$_SYS_C00111430" NOT NULL ENABLE NOVALIDATE)
SYS_C0011142    TB_SOURCE          ALTER TABLE "SCOTT"."TB_MID" MODIFY ("OWNER" CONSTRAINT "TMP$$_SYS_C00111420" NOT NULL ENABLE NOVALIDATE)
*/


--6.同步源表到中間表,此過程可根據源表資料變化情況同步多次或0次
BEGIN
  DBMS_REDEFINITION.SYNC_INTERIM_TABLE ('scott','tb_source','tb_mid');
END;

--7.完成線上重組
BEGIN
  DBMS_REDEFINITION.FINISH_REDEF_TABLE ('scott','tb_source','tb_mid');
END;

--8.刪除中間表
drop table scott.tb_mid purge;