以下为整个实验过程,主要是把一个表通过在线重定义改为分区表
创建源表
create table wjf_user_objects as select * from user_objects
创建索引、约束、触发器(为了验证在线重定义对这些信息的复制情况)
create index idx_wjf_user_objects_wjf_001 on wjf_user_objects(object_name);alter table wjf_user_objects add constraint con_wjf_user_objects_wjf_001 unique(object_name,object_type);
select count(*) from wjf_user_objects ----3519
创建中间表
create table wjf_user_object_zjb partition by list(object_type)(partition par_table values('TABLE'),
partition par_index values('INDEX'),
partition par_other values(default))
as select * from wjf_user_objects where 1=0
创建主键
alter table wjf_user_objects add primary key(object_id)alter table wjf_user_object_zjb add primary key(object_id)
重定义定义
exec dbms_redefinition.can_redef_table(uname => 'ykyh1203',tname => 'wjf_user_objects',options_flag => dbms_redefinition.cons_use_pk);开始重定义转化
exec dbms_redefinition.start_redef_table(uname => 'ykyh1203',orig_table => 'wjf_user_objects',int_table => 'wjf_user_object_zjb')查询相关物化视图(在线重定义的内部实现是基于物化视图,此处只做查验)
select owner,mview_name from user_mviews;
select log_owner,master,log_table from user_mview_logs;
复制依赖对象(默认复制索引、约束、权限、触发器信息)
DECLAREnum_errors PLS_INTEGER;
BEGIN
DBMS_REDEFINITION.COPY_TABLE_DEPENDENTS('ykyh1203', 'wjf_user_objects','wjf_user_object_zjb',DBMS_REDEFINITION.CONS_ORIG_PARAMS,true,true,true,true,num_errors);
END;
/