oracle 分区表 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;

原文：http://blog.itpub.net/28539951/viewspace-2120248/