Oracle在线重定义表

－－建新表
create table oam.SMS_CDR_TMP
(
    SMS_CDR_ID              INTEGER primary key,
  PAY_TIME                VARCHAR2(14),
  SERVICE_SCENARIOUS      INTEGER,
  CHARGED_PARTY           VARCHAR2(20),
  CALLING_PARTY           VARCHAR2(20),
  CALLED_PARTY            VARCHAR2(20),
  ORIGNIAL_CALLING_PARTY  VARCHAR2(20),
  ORIGNIAL_CALLED_PARTY   VARCHAR2(20),
  PAY_FLAG                INTEGER,
  SESSION_BEGIN_TIME      VARCHAR2(14),
  SESSION_TERMINATED_TIME VARCHAR2(14),
  TERMINATED_CAUSE        INTEGER,
  BALANCE_INFO            VARCHAR2(512),
  ACCUMLATOR_INFO         VARCHAR2(512),
  TARIFF_INFO             VARCHAR2(512),
  SMS_CADDRESS            VARCHAR2(20),
  SM_LENGTH               INTEGER,
  RESULT                  INTEGER,
  MOMT_FLAG               VARCHAR2(2),
  SMS_TYPE                INTEGER
)
tablespace OAM
 partition by range(SESSION_BEGIN_TIME)
(
partition p201010 values less than ('201011') tablespace oam,
partition p201011 values less than ('201012') tablespace oam,
partition p201012 values less than ('201101') tablespace oam,
partition p201101 values less than ('201102') tablespace oam,
partition p201102 values less than ('201103') tablespace oam,
partition p201103 values less than ('201104') tablespace oam,
partition p201104 values less than ('201105') tablespace oam,
partition p201105 values less than ('201106') tablespace oam,
partition p201106 values less than ('201107') tablespace oam,
partition p201107 values less than ('201108') tablespace oam,
  partition pmax values less than (maxvalue) tablespace oam
)
;


－－在线重定义
EXEC DBMS_REDEFINITION.CAN_REDEF_TABLE('OAM', 'SMS_CDR', DBMS_REDEFINITION.CONS_USE_PK);

EXEC DBMS_REDEFINITION.START_REDEF_TABLE('OAM', 'SMS_CDR', 'SMS_CDR_TMP', 'SMS_CDR_ID     SMS_CDR_ID , PAY_TIME PAY_TIME , SERVICE_SCENARIOUS SERVICE_SCENARIOUS , CHARGED_PARTY CHARGED_PARTY , CALLING_PARTY CALLING_PARTY , CALLED_PARTY CALLED_PARTY , ORIGNIAL_CALLING_PARTY ORIGNIAL_CALLING_PARTY , ORIGNIAL_CALLED_PARTY ORIGNIAL_CALLED_PARTY , PAY_FLAG PAY_FLAG , SESSION_BEGIN_TIME SESSION_BEGIN_TIME , SESSION_TERMINATED_TIME SESSION_TERMINATED_TIME, TERMINATED_CAUSE TERMINATED_CAUSE , BALANCE_INFO BALANCE_INFO , ACCUMLATOR_INFO ACCUMLATOR_INFO , TARIFF_INFO TARIFF_INFO , SMS_CADDRESS SMS_CADDRESS , SM_LENGTH SM_LENGTH , RESULT RESULT , MOMT_FLAG MOMT_FLAG , SMS_TYPE SMS_TYPE', DBMS_REDEFINITION.CONS_USE_PK);

DECLARE
num_errors PLS_INTEGER;
BEGIN
DBMS_REDEFINITION.COPY_TABLE_DEPENDENTS('OAM', 'SMS_CDR','SMS_CDR_TMP',
   DBMS_REDEFINITION.CONS_ORIG_PARAMS, TRUE, TRUE, TRUE, TRUE, num_errors);
END;
 
EXEC DBMS_REDEFINITION.FINISH_REDEF_TABLE('OAM', 'SMS_CDR', 'SMS_CDR_TMP');