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

1.操作用户需要有以下权限
GRANT EXECUTE ON DBMS_REDEFINITION TO HAGJ;
2.需要分区的表
select * from shiyu;

3.表结构

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

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.

发表回复