oracle 11g 将非分区表转换为分区表在线重定义

--操作的用户需要有以下的权限

GRANT CONNECT, RESOURCE TO CMIGDW;

GRANT EXECUTE ON DBMS_REDEFINITION TO CMIGDW;

GRANT ALTER ANY TABLE TO CMIGDW;

GRANT DROP ANY TABLE TO CMIGDW;

GRANT LOCK ANY TABLE TO CMIGDW;

GRANT CREATE ANY TABLE TO CMIGDW;

GRANT SELECT ANY TABLE TO CMIGDW;

GRANT CREATE ANY TRIGGER TO CMIGDW;

GRANT CREATE ANY INDEX TO CMIGDW;

--需要分区的表

select * from CMIGDW.TEST_DATE_DIM;

--表结构

create table TEST_DATE_DIM

(

  date_id   NUMBER,

  year_     NUMBER,

  half_year VARCHAR2(20),

  season_id VARCHAR2(20),

  season    VARCHAR2(20),

  month_    NUMBER,

  week      NUMBER

);

--创建一个与TEST_DATE_DIM 表字段相同，但是分区的表

create table TEST_DATE_DIM2

(

  date_id   NUMBER,

  year_     NUMBER,

  half_year VARCHAR2(20),

  season_id VARCHAR2(20),

  season    VARCHAR2(20),

  month_    NUMBER,

  week      NUMBER

)

PARTITION BY list (month_)

(

    PARTITION P1 values (201501),

    PARTITION P2 values (201502),

    PARTITION P3 values (default)

);

--开始在线重定义

Connected to Oracle Database 11g Enterprise Edition Release 11.2.0.3.0

Connected as CMIGDW@CMIG_TEST

SQL> EXEC dbms_redefinition.start_redef_table('CMIGDW', 'TEST_DATE_DIM', 'TEST_DATE_DIM2');

begin dbms_redefinition.start_redef_table('CMIGDW', 'TEST_DATE_DIM', 'TEST_DATE_DIM2'); end;

ORA-12089: 不能联机重新定义无主键的表 "CMIGDW"."TEST_DATE_DIM"

ORA-06512: 在 "SYS.DBMS_REDEFINITION", line 56

ORA-06512: 在 "SYS.DBMS_REDEFINITION", line 1490

ORA-06512: 在 line 1
需要有主键

--开始执行在线重定义

SQL> EXEC dbms_redefinition.start_redef_table('CMIGDW', 'TEST_DATE_DIM', 'TEST_DATE_DIM2');

PL/SQL procedure successfully completed

SQL>

SQL> DECLARE

  2   error_count pls_integer := 0;

  3  BEGIN

  4    dbms_redefinition.copy_table_dependents('CMIGDW', 'TEST_DATE_DIM', 'TEST_DATE_DIM2',

  5                                            0, true, false, true, false,

  6                                            error_count);

  7

  8    dbms_output.put_line('errors := ' || to_char(error_count));

  9  END;

 10  /

PL/SQL procedure successfully completed

--结束在线重定义

SQL> EXEC  dbms_redefinition.finish_redef_table('CMIGDW', 'TEST_DATE_DIM', 'TEST_DATE_DIM2');

PL/SQL procedure successfully completed

SQL> select * from  TEST_DATE_DIM PARTITION (p1);

SQL> select * from TEST_DATE_DIM2;

-- 删除中间表

DROP TABLE TEST_DATE_DIM2;