ORACLE 在线重定义普通表—>分区表

ORACLE 利用预定义包DBMS_REDEFINITION包，在线重定义普通表为分区表，包括主键对应的索引都改造为分区索引.

创建普通测试表EDEFINITION_TEST

CREATE TABLE REDEFINITION_TEST (ID NUMBER ,NAME VARCHAR2(50),CTIME DATE);

为REDEFINITION_TEST建立主键和索引

ALTER TABLE  REDEFINITION_TEST ADD CONSTRAINT PK_ID PRIMARY KEY (ID);
CREATE INDEX IND_01 ON REDEFINITION_TEST(CTIME);

为REDEFINITION_TEST插入测试表数据

DECLARE
  IN_DATE DATE := TO_DATE('2000-01-01 10:00:00', 'YYYY-MM-DD HH24:MI:SS');
BEGIN
  FOR I IN 1 .. 2000 LOOP
    INSERT INTO REDEFINITION_TEST VALUES (I, 'ABCDE', IN_DATE);
    IN_DATE := IN_DATE + 1;
  END LOOP;
  COMMIT;
END;

SELECT * FROM REDEFINITION_TEST;

两种确认可以重定义的方式

1. 表有主键，确认表可以重定义

BEGIN
  DBMS_REDEFINITION.CAN_REDEF_TABLE('CHAICHENG', 'REDEFINITION_TEST');
END;

2. 若表无主键 可以采用rowid重定义

BEGIN
  DBMS_REDEFINITION.CAN_REDEF_TABLE('CHAICHNEG', 'REDEFINITION_TEST', 2);
END;

创建重定义需要的临时表

CREATE TABLE REDEFINITION_TEST_TEMP (ID NUMBER ,NAME VARCHAR2(50),CTIME DATE)
PARTITION BY RANGE (CTIME)
(PARTITION P2000 VALUES LESS THAN (TO_DATE('2001-01-01','YYYY-MM-DD')) TABLESPACE TEST,
 PARTITION P2001 VALUES LESS THAN  (TO_DATE('2002-01-01','YYYY-MM-DD')) TABLESPACE TEST,
 PARTITION P2002 VALUES LESS THAN  (TO_DATE('2003-01-01','YYYY-MM-DD')) TABLESPACE TEST,
 PARTITION P2003 VALUES LESS THAN  (TO_DATE('2004-01-01','YYYY-MM-DD')) TABLESPACE TEST,
 PARTITION P2004 VALUES LESS THAN  (TO_DATE('2005-01-01','YYYY-MM-DD')) TABLESPACE TEST,
 PARTITION P2005 VALUES LESS THAN  (TO_DATE('2006-01-01','YYYY-MM-DD')) TABLESPACE TEST);

SELECT * FROM REDEFINITION_TEST_TEMP;
SELECT * FROM DBA_OBJECTS WHERE OBJECT_NAME LIKE '%REDEFINITION_TEST%';

开始重定义

1. 表有主键

BEGIN
  DBMS_REDEFINITION.START_REDEF_TABLE('CHAICHENG',
                                      'REDEFINITION_TEST',
                                      'REDEFINITION_TEST_TEMP');
END;

2. 若无主键不能这样重定义，需要指定以rowid重定义，示例如下：

BEGIN
  DBMS_REDEFINITION.START_REDEF_TABLE('CHAICHENG',
                                      'REDEFINITION_TEST',
                                      'REDEFINITION_TEST_TEMP',
                                      NULL,
                                      2);
END;

开始拷贝表的属性（本次未做，因为这样转换的，索引不是分区索引）(两种包的调用方式，选其一即可)

DECLARE
  error_count pls_integer := 0;
BEGIN
  DBMS_REDEFINITION.COPY_TABLE_DEPENDENTS(UNAME         => 'CHAICHENG',
                                          ORIG_TABLE    => 'REDEFINITION_TEST',
                                          INT_TABLE     => 'REDEFINITION_TEST_TEMP',
                                          IGNORE_ERRORS => TRUE,
                                          NUM_ERRORS    => error_count);
  DBMS_OUTPUT.PUT_LINE('errors := ' || TO_CHAR(error_count));
END;

---

DECLARE
  num_errors PLS_INTEGER;
BEGIN
  DBMS_REDEFINITION.COPY_TABLE_DEPENDENTS('CHAICHENG',
                                          'REDEFINITION_TEST',
                                          'REDEFINITION_TEST_TEMP',
                                          DBMS_REDEFINITION.CONS_ORIG_PARAMS,
                                          TRUE,
                                          TRUE,
                                          TRUE,
                                          TRUE,
                                          num_errors);
END;

经实验，在开始重定义之后在临时表上创建local索引，重定义完成后，主键对应的索引也是分区索引;

注意：ALTER TABLE REDEFINITION_TEST_TEMP ADD CONSTRAINT PK_ID_TEMP PRIMARY KEY (ID) USING INDEX LOCAL;会报错ORA-14039
因为：oracle不支持在分区表上创建PK主键时主键列不包含分区列，创建另外的约束（unique）也不可以。去掉LOCAL 关键字即可。

ALTER TABLE  REDEFINITION_TEST_TEMP ADD CONSTRAINT PK_ID_TEMP PRIMARY KEY (ID) USING INDEX;
CREATE INDEX  IND_01_TEMP ON  REDEFINITION_TEST_TEMP(CTIME) LOCAL;

同步数据(两种包的调用方式，选其一即可)

BEGIN
  DBMS_REDEFINITION.SYNC_INTERIM_TABLE(uname      => 'CHAICHENG',
                                       orig_table => 'REDEFINITION_TEST',
                                       int_table  => 'REDEFINITION_TEST_TEMP');
END;

---

BEGIN
  DBMS_REDEFINITION.SYNC_INTERIM_TABLE('CHAICHENG',
                                       'REDEFINITION_TEST',
                                       'REDEFINITION_TEST_TEMP');
END;

收集中间表的统计信息(可选做)

BEGIN
  DBMS_STATS.GATHER_TABLE_STATS('CHAICHENG',
                                'REDEFINITION_TEST_TEMP',
                                CASCADE => TRUE);
END;

完成重定义(两种包的调用方式，选其一即可)

BEGIN
  DBMS_REDEFINITION.FINISH_REDEF_TABLE(uname      => 'CHAICHENG',
                                       orig_table => 'REDEFINITION_TEST',
                                       int_table  => 'REDEFINITION_TEST_TEMP');
END;

---

BEGIN
  DBMS_REDEFINITION.FINISH_REDEF_TABLE('CHAICHENG',
                                       'REDEFINITION_TEST',
                                       'REDEFINITION_TEST_TEMP');
END;

修改索引，约束名称和原表一致

ALTER INDEX IND_01_TEMP RENAME TO IND_01;
ALTER INDEX  PK_ID_TEMP RENAME TO  PK_ID;
ALTER TABLE REDEFINITION_TEST RENAME CONSTRAINT PK_ID_TEMP TO PK_ID;

删除临时表

DROP TABLE REDEFINITION_TEST_TEMP PURGE;

ABORT_REDEF_TABLE使用,在FINISH_REDEF_TABLE之前，可以使用FINISH_REDEF_TABLE停止重定义

1. 停止前查询

SELECT * FROM TAB WHERE TNAME LIKE '%REDEFINITION%';
SELECT * FROM CAT WHERE TABLE_NAME LIKE '%REDEFINITION%';

SQL> SELECT * FROM  TAB WHERE TNAME LIKE '%REDEFINITION%';

TNAME			       TABTYPE	CLUSTERID
------------------------------ ------- ----------
MLOG$_REDEFINITION_TEST        TABLE
REDEFINITION_TEST	       TABLE
REDEFINITION_TEST_TEMP	       TABLE
RUPD$_REDEFINITION_TEST        TABLE

SQL> SELECT * FROM CAT WHERE TABLE_NAME LIKE '%REDEFINITION%';

TABLE_NAME		       TABLE_TYPE
------------------------------ -----------
MLOG$_REDEFINITION_TEST        TABLE
REDEFINITION_TEST	       TABLE
REDEFINITION_TEST_TEMP	       TABLE
RUPD$_REDEFINITION_TEST        TABLE

2. 停止

BEGIN
  DBMS_REDEFINITION.ABORT_REDEF_TABLE('CHAICHENG',
                                      'REDEFINITION_TEST',
                                      'REDEFINITION_TEST_TEMP');
END;

3. 停止后查询

SELECT * FROM TAB WHERE TNAME LIKE '%REDEFINITION%';
SELECT * FROM CAT WHERE TABLE_NAME LIKE '%REDEFINITION%';
SQL> SELECT * FROM  TAB WHERE TNAME LIKE '%REDEFINITION%';

TNAME			       TABTYPE	CLUSTERID
------------------------------ ------- ----------
REDEFINITION_TEST	       TABLE
REDEFINITION_TEST_TEMP	       TABLE

SQL> SELECT * FROM CAT WHERE TABLE_NAME LIKE '%REDEFINITION%';

TABLE_NAME		       TABLE_TYPE
------------------------------ -----------
REDEFINITION_TEST	       TABLE
REDEFINITION_TEST_TEMP	       TABLE