1.操作用户需要有以下权限
GRANT EXECUTE ON DBMS_REDEFINITION TO HAGJ;
2.需要分区的表
select * from shiyu;
3.表结构
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 |
CREATE TABLE HAGJ.SHIYU ( LOGID CHAR(16 BYTE), CREATETIME CHAR(19 BYTE), LOGTYPE VARCHAR2(10 BYTE), SAFETYLEVEL INTEGER, TRANID CHAR(36 BYTE), USERID CHAR(11 BYTE), ENTID CHAR(11 BYTE), ORGID CHAR(11 BYTE), DEPTID CHAR(11 BYTE), PROGRAMID VARCHAR2(120 BYTE), LOGDESC NVARCHAR2(256), OPRESULT NVARCHAR2(256), COMPUTERNAME VARCHAR2(64 BYTE), IP VARCHAR2(40 BYTE), FORMID CHAR(16 BYTE), MILLISECONDS INTEGER, "QyUserId" CHAR(11 BYTE), QYUSER NVARCHAR2(20) ) |
4.创建一个与shiyu 表字段相同，但是分区的表
|
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 |
CREATE TABLE shiyu_temp ( LOGID CHAR(16 BYTE), CREATETIME CHAR(19 BYTE), LOGTYPE VARCHAR2(10 BYTE), SAFETYLEVEL INTEGER, TRANID CHAR(36 BYTE), USERID CHAR(11 BYTE), ENTID CHAR(11 BYTE), ORGID CHAR(11 BYTE), DEPTID CHAR(11 BYTE), PROGRAMID VARCHAR2(120 BYTE), LOGDESC NVARCHAR2(256), OPRESULT NVARCHAR2(256), COMPUTERNAME VARCHAR2(64 BYTE), IP VARCHAR2(40 BYTE), FORMID CHAR(16 BYTE), MILLISECONDS INTEGER, "QyUserId" CHAR(11 BYTE), QYUSER NVARCHAR2(20) )partition by range(CREATETIME) ( partition p1 values less than ('2017-06-01 00:00:00'), partition p2 values less than ('2017-09-01 00:00:00'), partition p3 values less than ('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.