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;更多Oracle相关信息见Oracle 专题页面 http://www.linuxidc.com/topicnews.aspx？tid=12本文永久更新链接地址