oracle dbms_redefinition,ORACLE普通表转换成分区表的操作——在线重定义表(DBMS_REDEFINITION)...

1.操作用户需要有以下权限

GRANT EXECUTE ON DBMS_REDEFINITION TO HAGJ;

2.需要分区的表

select * from shiyu;

3.表结构

Oracle PL/SQL

1

2

3

4

5

6

7

8

9

10

11

12

13

14

15

16

17

18

19

20

21

CREATETABLEHAGJ.SHIYU

(

LOGID        CHAR(16BYTE),

CREATETIME   CHAR(19BYTE),

LOGTYPE      VARCHAR2(10BYTE),

SAFETYLEVEL  INTEGER,

TRANID       CHAR(36BYTE),

USERID       CHAR(11BYTE),

ENTID        CHAR(11BYTE),

ORGID        CHAR(11BYTE),

DEPTID       CHAR(11BYTE),

PROGRAMID    VARCHAR2(120BYTE),

LOGDESC      NVARCHAR2(256),

OPRESULT     NVARCHAR2(256),

COMPUTERNAME VARCHAR2(64BYTE),

IP           VARCHAR2(40BYTE),

FORMID       CHAR(16BYTE),

MILLISECONDS INTEGER,

"QyUserId"   CHAR(11BYTE),

QYUSER       NVARCHAR2(20)

)

4.创建一个与shiyu 表字段相同，但是分区的表

Oracle PL/SQL

1

2

3

4

5

6

7

8

9

10

11

12

13

14

15

16

17

18

19

20

21

22

23

24

25

26

27

CREATETABLEshiyu_temp

(

LOGID        CHAR(16BYTE),

CREATETIME   CHAR(19BYTE),

LOGTYPE      VARCHAR2(10BYTE),

SAFETYLEVEL  INTEGER,

TRANID       CHAR(36BYTE),

USERID       CHAR(11BYTE),

ENTID        CHAR(11BYTE),

ORGID        CHAR(11BYTE),

DEPTID       CHAR(11BYTE),

PROGRAMID    VARCHAR2(120BYTE),

LOGDESC      NVARCHAR2(256),

OPRESULT     NVARCHAR2(256),

COMPUTERNAME VARCHAR2(64BYTE),

IP           VARCHAR2(40BYTE),

FORMID       CHAR(16BYTE),

MILLISECONDS INTEGER,

"QyUserId"   CHAR(11BYTE),

QYUSER       NVARCHAR2(20)

)partitionbyrange(CREATETIME)

(

partitionp1valueslessthan('2017-06-01 00:00:00'),

partitionp2valueslessthan('2017-09-01 00:00:00'),

partitionp3valueslessthan('2017-12-01 00:00:00')

);

5.开始在线重定义

SQL> EXEC dbms_redefinition.start_redef_table(‘HAGJ’, ‘SHIYU’,’SHIYU_TEMP’);

BEGIN dbms_redefinition.start_redef_table(‘HAGJ’, ‘SHIYU’,’SHIYU_TEMP’); END;

*

ERROR at line 1:

ORA-12089: cannot online redefine table “HAGJ”.”SHIYU” with no primary key

ORA-06512: at “SYS.DBMS_REDEFINITION”, line 56

ORA-06512: at “SYS.DBMS_REDEFINITION”, line 1498

ORA-06512: at line 1

6.原表需要有主键,这里先添加主键

SQL> alter table shiyu add constraint pk_shiyu primary key(logid);

7.开始执行在线重定义

SQL> EXEC dbms_redefinition.start_redef_table(‘HAGJ’, ‘SHIYU’,’SHIYU_TEMP’);

PL/SQL procedure successfully completed.

8.如果表的数据很多，7步的时候可能会很长，这期间系统可能会继续对表SHIYU进行写入或者更新数据，那么可以执行以下的语句，这样在执行最后一步的时候可以避免长时间的锁定(该过程可选可不选)

BEGIN

DBMS_REDEFINITION.SYNC_INTERIM_TABLE(‘HAGJ’, ‘SHIYU’,’SHIYU_TEMP’);

END;

/

SQL> DECLARE

error_count pls_integer := 0;

BEGIN

dbms_redefinition.copy_table_dependents(‘HAGJ’, ‘SHIYU’,’SHIYU_TEMP’,0, true, false, true, false,error_count);

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

END;

/

PL/SQL procedure successfully completed.

9.结束在线重定义

SQL>    EXEC  dbms_redefinition.finish_redef_table(‘HAGJ’, ‘SHIYU’,’SHIYU_TEMP’);

PL/SQL procedure successfully completed

10.查看是否转换成功

SQL> select count(*) from shiyu partition(P2);

COUNT(*)

———-

2248424

SQL> select count(*) from shiyu partition(p3);

COUNT(*)

———-

3081

SQL>

11.删除中间表

SQL> drop table shiyu_temp;

Table dropped.