当前位置:操作系统 > Unix/Linux >>

基于dbms_redefinition在线重定义表

基于dbms_redefinition在线重定义表
 
      Oracle 支持在线重定义表,也就是说我们可以在修改表结构(DDL)的同时进行相关的DQL、DML操作,使得前端的DML根本感觉不到表结构实际上已经发生了变化,对于用户而言是完全透明的。当然在线重定义期间,前端性能会稍微有所下降。Oracle提供的重定义包dbms_redefinition即是用与完成此操作。其实质是Oracle使用了智能物化视图及物化视图日志的方式。在对象结构重组期间,表现为一个本地对象的复制,重组期间发生的任何变化都会被刷新到最新。
 
1、在线重定义表的主要功能:
     修改表或簇的存储参数
     将表移动到相同或不同schema下不同的tablespace(如果不要求表始终可用的话,也可以直接使用alter table move 实现)
     为表添加,修改或删除列
     为表添加或删除分区,改变分区结构
     改变物化视图日志或者Streams Advanced Queuing queue 表结构
     增加并行查询支持
     重建表以减少碎片
     将堆表变为索引组织表或相反
 
2、图示在线重定义
     下面的图示便于理解是如何进行在线重定义,其本质是基于基表的一个快照
 
 
3、在线重定义的步骤  
     a、选择在线重定义的方式,基于键(主键或唯一键)还是rowid(无主键或唯一键的情形)
     b、校验表能否被在线重定义,使用过程CAN_REDEF_TABLE 
     c、创建用于在线重定义的临时表(该临时表使用新的表结构,即添加删除列,列长度变化,存储属性变化等)
     d、如果重定义的为分区表且使用rowid方式,应该为临时表开启行移动(ALTER TABLE ... ENABLE ROW MOVEMENT;)
     e、对于大型表的在线重定义可以通过启用并行以提高性能(此步骤可选)
          ALTER SESSION FORCE PARALLEL DML PARALLEL degree-of-parallelism;
          ALTER SESSION FORCE PARALLEL QUERY PARALLEL degree-of-parallelism;
     f、调用过程start_redef_table启动在线重定义,如果启动失败,应调用ABORT_REDEF_TABLE先终止查找原因后再次启动
     g、从被重定义的表复制依赖对象到临时表(triggers, indexes, materialized view logs, grants, and constraints)及统计信息到临时表
     h、同步被重定义的表到临时表(调用过程sync_interim_table,此步可选)
     i、调用过程FINISH_REDEF_TABLE完成表的在线重订义 
     j、删除临时表
 
4、演示在线重定义
[sql] 
--下面基于主键来演示在线重定义  
--环境  
scott@USBO> select * from v$version where rownum<2;  
  
BANNER  
--------------------------------------------------------------------------------  
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production  
  
--创建需要重定义的表  
scott@USBO> create table tb_emp(empno number(4) not null,ename varchar2(10),  
  2  job varchar2(10),hiredate varchar2(20),sal number(7,2),deptno number(2));  
  
--下面为其添加相关约束  
scott@USBO> alter table tb_emp add constraint pk_tb_emp primary key(empno);  
  
scott@USBO> alter table tb_emp add constraint fk_tb_emp_dept_no foreign key(deptno) references dept(deptno);  
  
--创建用于在线重定义的临时表  
--注意,empno列可以为NULL,ename变化为name且长度增加,sal变为salary,数据精度发生变化,以及deptno使用了default  
scott@USBO> create table tb_emp_int  
  2  (empno number(4),name varchar2(20),hiredate varchar2(20),salary number,deptno number(2) default 30);  
  
--基于重定义创建触发器  
scott@USBO> CREATE OR REPLACE TRIGGER tr_bf_tb_emp_hiredate  
  2     BEFORE UPDATE OF hiredate  
  3     ON tb_emp  
  4     FOR EACH ROW  
  5  BEGIN  
  6     :new.hiredate := TO_CHAR (SYSDATE, 'yyyymmdd hh24:mi:ss');  
  7  END tr_bf_tb_emp_hiredate;  
  8  /  
  
Trigger created.  
  
--基于中间表创建触发器  
scott@USBO> CREATE OR REPLACE TRIGGER tr_bf_tb_emp_int_hiredate  
  2     BEFORE UPDATE OF hiredate  
  3     ON tb_emp_int  
  4     FOR EACH ROW  
  5  BEGIN  
  6     :new.hiredate := TO_CHAR (SYSDATE + 10, 'yyyymmdd hh24:mi:ss');  
  7  END tr_bf_tb_emp_int_hiredate;  
  8  /  
  
Trigger created.  
  
--收集统计信息  
scott@USBO> exec dbms_stats.gather_table_stats('SCOTT','TB_EMP',cascade=>true);  
  
scott@USBO> exec dbms_stats.gather_table_stats('SCOTT','TB_EMP_INT',cascade=>true);  
  
scott@USBO> select table_name,num_rows from user_tables where table_name like 'TB_EMP%';  
  
TABLE_NAME                       NUM_ROWS  
------------------------------ ----------  
TB_EMP                                  0  
TB_EMP_INT                              0  
  
--对重定义表执行DML操作  
--此时使用了dbms_lock.sleep (5),也就是整个操作完成需要500s,我们在这个期间实施重定义  
scott@USBO> get ins_tb_emp.sql  
  1  DECLARE  
  2     v_deptno   NUMBER (2);  
  3  BEGIN  
  4     FOR i IN 1 .. 100  
  5     LOOP  
  6        IF MOD (i, 2) = 0  
  7        THEN  
  8           v_deptno := 10;  
  9        ELSE  
 10           v_deptno := 20;  
 11        END IF;  
 12        INSERT INTO tb_emp  
 13           SELECT i,  
 14                  'Name_' || TO_CHAR (i),  
 15                  'Job_' || TO_CHAR (i),  
 16                  TO_CHAR (SYSDATE, 'yyyymmdd hh24:mi:ss'),  
 17                  i + 100,  
 18                  v_deptno  
 19             FROM DUAL; &
CopyRight © 2022 站长资源库 编程知识问答 zzzyk.com All Rights Reserved
部分文章来自网络,