參考1
參考2
參考3

創(chuàng)建分區(qū)臨時表

CREATE TABLE stocktaking2 (
  id                   NUMBER(10) not null,
  app_source           NUMBER(10) not null,
  batch_no             VARCHAR2(30) not null,
  stocktaking_date     DATE not null,
  inventory_class_id   NUMBER(10),
  inventory_class_code VARCHAR2(30) not null,
  inventory_class_name NVARCHAR2(100),
  store_id             NUMBER(10),
  store_code           VARCHAR2(30) not null,
  store_name           NVARCHAR2(100) not null,
  store_en_name        NVARCHAR2(100),
  item_id              NUMBER(10),
  item_code            VARCHAR2(30) not null,
  item_name            NVARCHAR2(100) not null,
  item_unit            VARCHAR2(30) not null,
  item_cost            NUMBER(20,6) not null,
  inventory_type_code  VARCHAR2(30) not null,
  inventory_type_name  NVARCHAR2(100),
  inventory_qty        NUMBER(20,6) not null,
  inventory_amount     NUMBER(20,6) not null,
  currency_id          NUMBER(10),
  currency_code        VARCHAR2(30) not null,
  comments             NVARCHAR2(100),
  status               NUMBER(10),
  imp_date             DATE not null,
  proc_status          NUMBER(10) not null,
  approve_status       NUMBER(10),
  approve_by           NUMBER(10),
  create_date          DATE not null,
  create_by            NUMBER(10) not null,
  update_date          DATE,
  update_by            NUMBER(10)
)
PARTITION BY RANGE (imp_date)
INTERVAL(NUMTOYMINTERVAL(1, 'MONTH'))
( PARTITION p0 VALUES LESS THAN (TO_DATE('2016-01-01', 'YYYY-MM-DD'))--,
  --PARTITION p1 VALUES LESS THAN (TO_DATE('2016-02-01', 'YYYY-MM-DD')),
  --PARTITION p2 VALUES LESS THAN (TO_DATE('2016-03-01', 'YYYY-MM-DD')),
  --PARTITION p3 VALUES LESS THAN (TO_DATE('2016-04-01', 'YYYY-MM-DD')),
  --PARTITION p4 VALUES LESS THAN (TO_DATE('2016-05-01', 'YYYY-MM-DD')) 
);

由于這里使用了Oracle 11g的INTERVAL功能，所以PARTITION語句可以只寫一個

檢查是否可以進行REDEFINITION

EXEC DBMS_REDEFINITION.can_redef_table(USER, 'stocktaking');

開始 REDEFINITION

EXEC DBMS_REDEFINITION.start_redef_table(USER, 'stocktaking', 'stocktaking2');

創(chuàng)建索引和約束

SET SERVEROUTPUT ON
DECLARE
  l_errors  NUMBER;
BEGIN
  DBMS_REDEFINITION.copy_table_dependents(
    uname            => USER,
    orig_table       => 'stocktaking',
    int_table        => 'stocktaking2',
    copy_indexes     => DBMS_REDEFINITION.cons_orig_params,
    copy_triggers    => TRUE,
    copy_constraints => TRUE,
    copy_privileges  => TRUE,
    ignore_errors    => TRUE,
    num_errors       => l_errors,
    copy_statistics  => FALSE,
    copy_mvlog       => FALSE);
    
  DBMS_OUTPUT.put_line('Errors=' || l_errors);
END;
/

如果ignore_errors => FALSE, 在執(zhí)行該步時出現(xiàn)報錯，將ignore_errors設(shè)置為TRUE，忽略該錯誤。

• ORA-01442: column to be modified to NOT NULL is already NOT NULL

完成 REDEFINITION

BEGIN
  dbms_redefinition.finish_redef_table(
    uname      => USER,        
    orig_table => 'stocktaking',
    int_table  => 'stocktaking2');
END;
/

驗證是否已經(jīng)分區(qū)

stocktaking表已經(jīng)被分區(qū)

SELECT partitioned FROM user_tables WHERE table_name = 'stocktaking';

-- 刪除臨時表stocktaking2
DROP TABLE stocktaking2;