mysql触发器修改表分区_在线重定义分区表

--创建普通表以及中间分区表

CREATE TABLE yz.T (ID NUMBER PRIMARY KEY, TIME DATE);

INSERT INTO yz.T SELECT object_id, CREATED FROM DBA_OBJECTS ;

commit;

CREATE TABLE yz.T_NEW (ID number , TIME date) PARTITION BY RANGE (TIME)

(PARTITION P1 VALUES LESS THAN (TO_DATE('2010-4-20', 'YYYY-MM-DD')),

PARTITION P2 VALUES LESS THAN (TO_DATE('2014-1-10', 'YYYY-MM-DD')),

PARTITION P3 VALUES LESS THAN (TO_DATE('2014-3-28', 'YYYY-MM-DD')),

PARTITION P4 VALUES LESS THAN (MAXVALUE));

1、检查表是否能够在线重定义

有主键

EXEC DBMS_REDEFINITION.can_redef_table(uname => 'YZ',tname => 'T',options_flag => dbms_redefinition.cons_use_pk);

无主键使用rowid

EXEC DBMS_REDEFINITION.can_redef_table(uname => 'YZ',tname => 'T',options_flag => dbms_redefinition.cons_use_rowid);

2、开始在线重定义

exec dbms_redefinition.start_redef_table(uname => 'yz',orig_table => 't',int_table => 't_new');

3、同步数据

EXEC dbms_redefinition.sync_interim_table('YZ','T','T_NEW');

4、在中间表上创建与源表对应的索引、外键、触发器、统计信息等

--自动

DECLARE

num_errors PLS_INTEGER;

BEGIN

DBMS_REDEFINITION.COPY_TABLE_DEPENDENTS('yz', 't', 't_new',DBMS_REDEFINITION.CONS_ORIG_PARAMS, TRUE, TRUE, TRUE, TRUE, num_errors);

END;

/

注：中间表上创建索引不是分区索引

--手动

alter table t_new add constraint pk_objects_new primary key (id) using index local;

查看在重定义错误

select object_name, base_table_name, ddl_txt from DBA_REDEFINITION_ERRORS;

5、结束重定义

exec dbms_redefinition.finish_redef_table(uname => 'yz',orig_table => 't',int_table => 't_new');

6、重定义过程中有错误回滚

exec DBMS_REDEFINITION.ABORT_REDEF_TABLE ('YZ','T','T_NEW');

7、删除临时表

truncate table t_new;

drop table t_new purge

8、验证分区

select * from user_tab_partitions;

注：在线重定义分区表不能通过goldengate 同步，抽取进程、投递进程都有t_new相关操作记录，在复制进程端报错

OCI Error ORA-01732: data manipulation operation not legal on this view (status = 1732). INSERT /*+ RESTRICT_ALL_REF_CONS */ INTO "YZ"."T_NEW" ("ID","TIME") VALUES (:a0,:a1)

Operation failed at seqno 1 rba 31929987

Discarding record on action DISCARD on error 1732

Problem replicating YZ.T_NEW to YZ.T_NEW

Mapping problem with insert record (target format)...

*

ID = 862

TIME = 2013-08-24 11:37:45

*

参考mos GoldenGate Replicat Fails With SQL Error 1732 Mapping on Materialized View (Doc ID 1381948.1)