oracle重新定义表,Oracle 在线重定义（普通表变更为分区表）

–背景：cux_gl_interfacebak数据量过大(cux_gl_interfacebak有主键)，需按accouting_date按年分区，以下命令直接在command窗口执行即可。

–1、检查需要在线冲定义的表是否

begin

dbms_redefinition.can_redef_table(‘apps’,’cux_gl_interfacebak’,dbms_redefinition.cons_use_pk);

end;

/

或验证是否可以通过rowid方式定义

begin

–dbms_redefinition.can_redef_table(‘scott’,’tb_cablecheck_equipment_bak’,2);

dbms_redefinition.can_redef_table(‘apps’,’cux_gl_interfacebak’,dbms_redefinition.cons_use_rowid);

end;

/

–2、创建中间表

create table CUX_GL_INTERFACEBAK_1

(

source_batch_id VARCHAR2(50) not null,

source_line_id NUMBER(10),

je_group_id VARCHAR2(50) not null,

ledger_id VARCHAR2(50) not null,

accounting_date DATE not null,

process_date DATE not null,

je_category_name VARCHAR2(25) not null,

je_source_name VARCHAR2(25) not null,

currency_code VARCHAR2(15) not null,

currency_conversion_date DATE,

currency_conversion_rate NUMBER(38,2),

currency_conversion_type VARCHAR2(30),

entered_dr NUMBER(38,2),

entered_cr NUMBER(38,2),

accounted_dr NUMBER(38,2),

accounted_cr NUMBER(38,2),

actual_flag VARCHAR2(25) not null,

import_flag VARCHAR2(1) not null,

import_date VARCHAR2(25),

gl_request_id NUMBER(30),

error_message VARCHAR2(255),

doc_seq_num VARCHAR2(100),

segment1 VARCHAR2(25),

segment2 VARCHAR2(25),

segment3 VARCHAR2(25),

segment4 VARCHAR2(25),

segment5 VARCHAR2(25),

segment6 VARCHAR2(25),

segment7 VARCHAR2(25),

segment8 VARCHAR2(25),

segment9 VARCHAR2(25),

segment10 VARCHAR2(25),

segment11 VARCHAR2(25),

segment12 VARCHAR2(25),

segment13 VARCHAR2(25),

line_description VARCHAR2(240),

attribute1 VARCHAR2(25),

attribute2 VARCHAR2(150),

attribute3 VARCHAR2(150),

attribute4 VARCHAR2(150),

attribute5 VARCHAR2(150),

attribute6 VARCHAR2(150),

attribute7 VARCHAR2(150),

attribute8 VARCHAR2(150),

attribute9 VARCHAR2(150),

attribute10 VARCHAR2(150),

attribute11 VARCHAR2(150),

attribute12 VARCHAR2(150),

attribute13 VARCHAR2(150),

attribute14 VARCHAR2(150),

attribute15 VARCHAR2(150),

source_key_id NUMBER(10) not null

)

partition by range(accounting_date)(

PARTITION tb_cablecheck_equipment_p1 VALUES LESS THAN (TO_DATE(‘2017-01-01′,’YYYY-MM-DD’)),

PARTITION tb_cablecheck_equipment_p2 VALUES LESS THAN(TO_DATE(‘2018-01-01’, ‘YYYY-MM-DD’)),

PARTITION tb_cablecheck_equipment_p3 VALUES LESS THAN(TO_DATE(‘2019-01-01’, ‘YYYY-MM-DD’)),

PARTITION tb_cablecheck_equipment_p4 VALUES LESS THAN(TO_DATE(‘2020-01-01’, ‘YYYY-MM-DD’)),

PARTITION tb_cablecheck_equipment_p5 VALUES LESS THAN(TO_DATE(‘2021-01-01’, ‘YYYY-MM-DD’)),

PARTITION tb_cablecheck_equipment_p6 VALUES LESS THAN(MAXVALUE)

);

–3、进行冲定义命令

begin

dbms_redefinition.start_redef_table(‘apps’,’CUX_GL_INTERFACEBAK’,’CUX_GL_INTERFACEBAK_1′,null,2);

end;

/

–4、复制依赖对象

declare

num_errors pls_integer;

begin

dbms_redefinition.copy_table_dependents(‘apps’, ‘CUX_GL_INTERFACEBAK’,’CUX_GL_INTERFACEBAK_1′,

dbms_redefinition.cons_orig_params, true, true, true, true, num_errors);

end;

/

–5、同步中间表，保证数据的一致性

begin

dbms_redefinition.sync_interim_table(‘apps’,’CUX_GL_INTERFACEBAK’,’CUX_GL_INTERFACEBAK_1′);

end;

/

–6、完成重定义命令

begin

dbms_redefinition.finish_redef_table(‘apps’,’CUX_GL_INTERFACEBAK’,’CUX_GL_INTERFACEBAK_1′);

end;

/

–7、验证冲定义是否正常

select * from CUX_GL_INTERFACEBAK partition(tb_cablecheck_equipment_p4);

select *

from cux_gl_interfacebak partition(tb_cablecheck_equipment_p3)

where 1 = 1

and segment3 = ‘6031010101’

–8、删除表

drop table apps.CUX_GL_INTERFACEBAK_1;