通过在线重定义将普通标转成分区表

ORACLE在线对普通表进行分区步骤

    对于24X7的业务系统，需要更改表结构或者对表进行分区，可以使用oracle提供的DBMS_REDEFINITION包在线重定义来实现。同时DBMS_REDEFINITION包还可以完成以下工作：

n  修改table或者cluster的存储参数

n  移动table或者cluster到不同的表空间

n  增加、修改、删除table或者cluster的一列或列

n  增加或者删除分区(仅支持非cluster table)

n  改变分区结构

n  改变一个单表分区的物理属性，包括移动到同一个模式下不同的表空间

n  增加并行查询(parallel queries)的支持

n  重新创建table or cluster用来减少碎片

n  把堆表(heat table)变成索引组织表(IOT)，或者做反向操作

n  转换一个关系表到一张表中的object column，或者做反向操作

n  转换object table到一张关系表或者表中的object column,或者做反向操作

在线重定义表有两种方式，通过key或者rowid。

 

通过key在线重定义表的步骤(scott用户下的EMP表为例，根据DEPTNO做list分区)：

1， 查看原表的表结构，建表语句：

SQL>desc EMP;

SQL>select dbms_metadata.get_ddl(‘TABLE’,’EMP’,’SCOTT’) FROM DUAL;

    参数说明：1）对象类型，如TABLE,INDEX等；

        2）对象名称；

        3）对象所在schema；

 

2， 验证EMP是否可以在线重定义：

SQL>exec dbms_redefinition.can_redef_table('scott','emp',dbms_redefinition.cons_use_pk);

如有报错，根据错误信息具体修改。

参数说明：1）对象所在schema

      2）对象名称

      3）表示通过主键重定义，可省略，默认就是通过主键

 

3， 创建一个空的中间表EMP_TEMP，这个中间表就是期望重定义后的表结构：

SQL> CREATE TABLE EMP_TEMP

(EMPNO NUMBER(4,0) PRIMARY KEY,

 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 LIST(DEPTNO)

   (PARTITION DEPT10 VALUES (10),

    PARTITION DEPT20 VALUES (20),

   PARTITION DEPT30 VALUES (30));

    注：不需要创建约束、索引等，只需要表定义即可，因为像约束、索引等这样的依赖对象会在后续步骤中自动创建和拷贝。

 

4， 对于数据量比较大的表，最好设置合理的并行度提高性能：

SQL>alter session force parallel dmp parallel 4;

SQL>alter session force parallel query parallel 4;

 

5， 执行START_REDEF_TABLE过程：

SQL>exec dbms_redefinition.start_redef_table('scott','emp','emp_temp',null);

    

这一存储过程把原表的数据拷贝至新表，并通过物化视图不断刷新，保持原表和中间表的实时同步。

    如果在重定义表的过程中报错，可以执行ABORT_REDEF_TABLE过程终止。 

SYS>exec DBMS_REDEFINITION.ABORT_REDEF_TABLE(‘SCOTT’,’EMP’,’EMP_TEMP’); 

 

6， 复制依赖对象（索引，触发器等）：

SQL> DECLARE

num_errors PLS_INTEGER;

BEGIN

DBMS_REDEFINITION.copy_table_dependents('SCOTT','EMP','EMP_TEMP',

DBMS_REDEFINITION.cons_orig_params,TRUE,TRUE,TRUE,TRUE,num_errors);

END;

上面我们把ignore_errors设为true，表示即使中间有错误发生，存储过程不会退出，会一直运行直到结束，这时你一定要记得在下一步中查看是否有错误。

 

7， 从视图DBA_REDEFINITION_ERRORS查询上一步的错误信息：

 

8， 执行SYNC_INTERIM_TABLE过程同步数据（可选）：

SQL> exec  DBMS_REDEFINITION.SYNC_INTERIM_TABLE('SCOTT', 'EMP', 'EMP_TEMP');

 

9， 执行FINISH_REDEF_TABLE过程完成重定义表：

SQL> exec DBMS_REDEFINITION.FINISH_REDEF_TABLE('SCOTT', 'EMP', 'EMP_TEMP');

 

 

10， 删除中间表：

SQL>DROP TABLE SCOTT.EMP_TEMP CASCADE CONSTRAINTS;

11， 检查重定义后表的各项信息：

SQL>select empno,ename,deptno from scott.emp partition (dept30);  

 

SQL>select owner,constraint_name,constraint_type,table_name from dba_constraints where owner='SCOTT' and table_name='EMP' 

注：原来的主键是PK_EMP，重定义完成后主键由系统自动生成一个名字，此时可以修改主键的名字为原来名字。

SQL>alter table scott.emp rename constraint SYS_C0010797 to PK_EMP;

 

SQL> select table_name,partition_name from user_tab_partitions where table_name='EMP';

 

 

 

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/29098758/viewspace-2139432/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/29098758/viewspace-2139432/