确认表是否可以分区
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;
