利用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;

/* 交换表名 */

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