使用Dbms_Redefinition转换普通表-》分区表

 

SQL>  CREATE TABLE unpar_table (
  2  id NUMBER(10),
  3  create_date DATE,
  4  name VARCHAR2(100)
  5  );

Table created.

SQL> ALTER TABLE unpar_table ADD (
  2  CONSTRAINT unpar_table_pk PRIMARY KEY (id)
  3  );

Table altered.

SQL> SQL> CREATE INDEX create_date_ind ON unpar_table(create_date);

Index created.

SQL>  EXEC DBMS_STATS.gather_table_stats(USER, 'unpar_table', cascade => TRUE);

PL/SQL procedure successfully completed.

SQL> CREATE TABLE par_table (
  2  id NUMBER(10),
  3  create_date DATE,
  4  name VARCHAR2(100)
  5  )
  6  PARTITION BY RANGE (create_date)
  7  (PARTITION unpar_table_2005 VALUES LESS THAN (TO_DATE('01/01/2005', 'DD/MM/YYYY')),
  8  PARTITION unpar_table_2006 VALUES LESS THAN (TO_DATE('01/01/2006', 'DD/MM/YYYY')),
  9  PARTITION unpar_table_2007 VALUES LESS THAN (MAXVALUE));

Table created.

SQL>  EXEC Dbms_Redefinition.can_redef_table(USER, 'unpar_table');

PL/SQL procedure successfully completed.

SQL> BEGIN
  2  DBMS_REDEFINITION.start_redef_table(
  3  uname => USER, 
  4  orig_table => 'unpar_table',
  5  int_table => 'par_table');
  6  END;
  7  /

PL/SQL procedure successfully completed.

SQL> BEGIN
  2  dbms_redefinition.sync_interim_table(
  3  uname => USER, 
  4  orig_table => 'unpar_table',
  5  int_table => 'par_table');
  6  END;
  7  /

PL/SQL procedure successfully completed.

SQL>  ALTER TABLE par_table ADD (
  2  CONSTRAINT unpar_table_pk2 PRIMARY KEY (id)
  3  );

Table altered.

SQL> SQL> CREATE INDEX create_date_ind2 ON par_table(create_date);

Index created.

SQL> EXEC DBMS_STATS.gather_table_stats(USER, 'par_table', cascade => TRUE);

PL/SQL procedure successfully completed.

SQL>  BEGIN
  2  dbms_redefinition.finish_redef_table(
  3  uname => USER,
  4  orig_table => 'unpar_table',
  5  int_table => 'par_table');
  6  END;
  7  /

PL/SQL procedure successfully completed.

SQL> DROP TABLE par_table; 

Table dropped.

SQL> ALTER TABLE unpar_table RENAME CONSTRAINT unpar_table_pk2 TO unpar_table_pk;

Table altered.

SQL> ALTER INDEX create_date_ind2 RENAME TO create_date_ind;

Index altered.

SQL> desc unpar_table
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 ID                                        NOT NULL NUMBER(10)
 CREATE_DATE                                        DATE
 NAME                                               VARCHAR2(100)

SQL> SELECT partition_name
  2  FROM user_tab_partitions
  3  WHERE table_name ='UNPAR_TABLE';

PARTITION_NAME
------------------------------
UNPAR_TABLE_2005
UNPAR_TABLE_2006
UNPAR_TABLE_2007

SQL> SELECT partitioned
  2  FROM user_tables
  3  WHERE table_name ='UNPAR_TABLE';

PAR
---
YES

 

11g的Dbms_Redefinition增强了很多：

1.Online table redefinition supports tables with materialized views and view logs

2.Online table redefinition supports triggers with ordering dependency

3.Minimizing dependent recompilations