利用REDEFINITION将普通表转换成分区表案例

create table TMP_XX_NOLOT(  fgs                   VARCHAR2(8) not null,  lotno_id              VARCHAR2(15) not null,  owner_no              VARCHAR2(3) not null,  goods_id              VARCHAR2(10) not null,  goods_lotno           VARCHAR2(50) not null,  production_date       DATE not null,  valid_until           DATE not null,  print_production_date VARCHAR2(100),  print_valid_until     VARCHAR2(100),  approval_no           VARCHAR2(100),  reportbill_no         VARCHAR2(100),  sterilization_lotno   VARCHAR2(100))partition by list (fgs)(  partition P_HL_HRB values ('HL-HRB')  ,  partition P_CHONGQ values ('CHONGQ')   ,  partition P_AH_HFE values ('AH-HFE')   ,  partition P_SHAND values ('SHAND')   ,  partition P_OTHER VALUES (DEFAULT) );

/权限需要 /

grant execute on DBMS_REDEFINITION to wlpt ;  grant   EXECUTE_CATALOG_ROLE to wlpt ;  --Execute privileges on the DBMS_REDEFINITION package are granted to EXECUTE_CATALOG_ROLE.grant CREATE TABLE to wlpt ;grant CREATE MATERIALIZED VIEW to wlpt ;  /* 说明同步过程  需要物化视图  */grant CREATE INDEX to wlpt ;grant CREATE TRIGGER to wlpt;/*GRANT CONNECT, RESOURCE TO wlpt;GRANT EXECUTE ON DBMS_REDEFINITION TO wlpt;GRANT ALTER ANY TABLE TO wlpt;GRANT DROP ANY TABLE TO wlpt ;GRANT LOCK ANY TABLE TO wlpt;GRANT CREATE ANY TABLE TO wlpt;GRANT SELECT ANY TABLE TO wlpt;GRANT CREATE ANY TRIGGER TO wlpt;GRANT CREATE ANY INDEX TO wlpt ;*/

–The CREATE TRIGGER privilege is also required to execute the COPY_TABLE_DEPENDENTS procedure.
BEGIN

DBMS_REDEFINITION.CAN_REDEF_TABLE('WLPT','XX_NOLOT',DBMS_REDEFINITION.CONS_USE_PK);END;

/*Start the redefinition process
对应字段才能同步数据，否则只能同步部分数据 */
/* dbms_redefinition.cons_use_pk 与 CONS_USE_ROWID
/* 自定义方法:cons_use_pk 根据primary key
CONS_USE_ROWID 根据rowid
*/

BEGIN dbms_redefinition.start_redef_table('WLPT', 'XX_NOLOT', 'TMP_XX_NOLOT');END;

/* 自动制复制triggers, indexes, materialized view logs, grants, , constraints */

DECLARE  num_errors PLS_INTEGER;BEGIN  DBMS_REDEFINITION.COPY_TABLE_DEPENDENTS('WLPT','XX_NOLOT', 'TMP_XX_NOLOT',DBMS_REDEFINITION.CONS_ORIG_PARAMS, TRUE, TRUE, TRUE, TRUE, num_errors);END;

/* 同步XX_NOLOT数据到TMP_XX_NOLOT */
–a.查看复制表的属性

select object_name, base_table_name, ddl_txt from DBA_REDEFINITION_ERRORS;

–b.同步数据

BEGIN  DBMS_REDEFINITION.SYNC_INTERIM_TABLE('WLPT', 'XX_NOLOT', 'TMP_XX_NOLOT');END;

/* 交换表名 */

BEGINDBMS_REDEFINITION.FINISH_REDEF_TABLE('WLPT', 'XX_NOLOT', 'TMP_XX_NOLOT');END;