普通表转分区表（在线重定义）

确认表是否可以分区

SQL> BEGIN

2 DBMS_REDEFINITION.CAN_REDEF_TABLE('SCOTT','EMP',DBMS_REDEFINITION.CONS_USE_PK);

3 END;

4 /

创建临时表

CREATE TABLE "SCOTT"."EMP_TMP"

( "EMPNO" NUMBER(4,0),

"ENAME" VARCHAR2(10),

"JOB" VARCHAR2(9),

"MGR" NUMBER(4,0),

"HIREDATE" DATE,

"SAL" NUMBER(7,2),

"COMM" NUMBER(7,2),

"DEPTNO" NUMBER(2,0)

)

PARTITION BY RANGE (DEPTNO)

(

PARTITION EMP_A1 VALUES LESS THAN (20),

PARTITION EMP_A2 VALUES LESS THAN (30),

PARTITION EMP_A3 VALUES LESS THAN (40),

PARTITION EMP_A4 VALUES LESS THAN (50),

PARTITION EMP_A5 VALUES LESS THAN (60)

)

开始执行数据迁移

EXEC DBMS_REDEFINITION.START_REDEF_TABLE('SCOTT', 'EMP', 'EMP_TMP');

如果可能继续对emp表进行更新写入则

BEGIN
DBMS_REDEFINITION.SYNC_INTERIM_TABLE('SCOTT', 'EMP', 'EMP_TMP');
END;
/（可选过程）

权限对象的迁移

DECLARE
num_errors PLS_INTEGER;
BEGIN
DBMS_REDEFINITION.COPY_TABLE_DEPENDENTS('SCOTT', 'EMP','EMP_TMP',
DBMS_REDEFINITION.CONS_ORIG_PARAMS, TRUE, TRUE, TRUE, TRUE, num_errors);
END;
/

查询相关错误

查询相关错误，在操作之前先检查，查询DBA_REDEFINITION_ERRORS试图查询错误：
select object_name, base_table_name, ddl_txt from DBA_REDEFINITION_ERRORS;

BEGIN
DBMS_REDEFINITION.FINISH_REDEF_TABLE('scott', 'emp', 'emp_TMP');
END;
/

如果执行过程中有错误 可以执行以下语句终止

BEGIN
DBMS_REDEFINITION.ABORT_REDEF_TABLE(uname => 'SCOTT',
orig_table => 'EMP',
int_table => 'EMP_TMP'
);
END;

检验

SQL> SELECT tablespace_name ,PARTITIONED FROM ALL_TABLES WHERE TABLE_NAME = 'EMP_TMP';

TABLESPACE_NAME PAR

------------------------------ ---

USERS NO

SQL> SELECT tablespace_name ,PARTITIONED FROM ALL_TABLES WHERE TABLE_NAME = 'EMP';

TABLESPACE_NAME PAR

------------------------------ ---

YES

查看分区信息

select table_name,

partitioning_type type,

p.subpartitioning_type,

p.partition_count

from user_part_tables p;