Oracle分区表在线重定义字段not null问题

460次阅读

共计 3809 个字符，预计需要花费 10 分钟才能阅读完成。

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)
*/