使用DBMS_REDEFINITION对已存在的表按月自动分区

参考1
参考2
参考3

创建分区临时表

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');

创建索引和约束

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, 在执行该步时出现报错，将ignore_errors设置为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;
/

验证是否已经分区

stocktaking表已经被分区

SELECT partitioned FROM user_tables WHERE table_name = 'stocktaking';

-- 删除临时表stocktaking2
DROP TABLE stocktaking2;