4、演示在线重定义
--下面基于主键来演示在线重定义
--环境
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;
20 dbms_lock.sleep (5);
21 COMMIT;
22 END LOOP;
23* END;
24 /
--下面再开启一个新的session，用于在线重定义表
scott@USBO> set serveroutput on;
--校验表能否被重定义
scott@USBO> exec dbms_redefinition.can_redef_table('SCOTT','TB_EMP');
--开始重定义
scott@USBO> exec dbms_redefinition.start_redef_table('SCOTT', 'TB_EMP', 'TB_EMP_INT',-
> 'EMPNO EMPNO, ENAME NAME, SAL*1.10 SALARY, HIREDATE HIREDATE,DEPTNO DEPTNO');
--可以从视图user_snapshots查询到临时表的信息
scott@USBO> select name,table_name,updatable,status from user_snapshots;
NAME TABLE_NAME UPD STATUS
------------------------------ ------------------------------ --- -------
TB_EMP_INT TB_EMP_INT NO VALID
--查看表tb_emp_int，此时也有5条记录被插入
scott@USBO> select count(*) from tb_emp_int;
COUNT(*)
----------
5
--正在插入到tb_emp_int产生的日志信息，从6开始，实际上执行start_redef_table时前5条记录已经被复制到临时表
scott@USBO> select * from mlog$_tb_emp;
EMPNO SNAPTIME$$ D O CHANGE_VEC XID$$
---------- ----------------- - - ---------- ----------
6 40000101 00:00:00 I N FE 1.4074E+15
7 40000101 00:00:00 I N FE 5.6299E+14
8 40000101 00:00:00 I N FE 2.2519E+15
9 40000101 00:00:00 I N FE 1.4075E+15
scott@USBO> select * from rupd$_tb_emp;
no rows selected
--检查约束等是否已经添加到临时表
scott@USBO> select constraint_name,table_name,status from user_constraints where table_name='TB_EMP_INT';
no rows selected
--注册依赖对象
scott@USBO> exec dbms_redefinition.register_dependent_object('SCOTT', 'TB_EMP', 'TB_EMP_INT', -
> dbms_redefinition.cons_trigger, 'SCOTT', 'tr_bf_tb_emp_hiredate', 'tr_bf_tb_emp_int_hiredate');
PL/SQL procedure successfully completed.
scott@USBO> select constraint_name,table_name,status from user_constraints where table_name='TB_EMP_INT';
no rows selected
--将重定义表的依赖对象复制到临时表
scott@USBO> DECLARE
2 retval NUMBER (5);
3 BEGIN
4 DBMS_REDEFINITION.copy_table_dependents ('SCOTT', 'TB_EMP', 'TB_EMP_INT', 0,
5 copy_constraints => TRUE,
6 num_errors => retval);
7 DBMS_OUTPUT.put_line (retval);
8 END;
9 /
PL/SQL procedure successfully completed.
--查看临时表上的依赖对象，可以看到出现了以TMP$$开头的相关约束
scott@USBO> select constraint_name,table_name,status from user_constraints where table_name in('TB_EMP_INT','TB_EMP');
CONSTRAINT_NAME TABLE_NAME STATUS
------------------------------ ------------------------------ --------
SYS_C0024681 TB_EMP ENABLED
PK_TB_EMP TB_EMP ENABLED
FK_TB_EMP_DEPT_NO TB_EMP ENABLED
TMP$$_SYS_C00246810 TB_EMP_INT ENABLED
TMP$$_PK_TB_EMP0 TB_EMP_INT ENABLED
TMP$$_FK_TB_EMP_DEPT_NO0 TB_EMP_INT DISABLED
--查看表tb_emp，此时重定义表插入了36条记录
scott@USBO> select count(*) from tb_emp;
COUNT(*)
----------
36
--临时表上的记录为5
scott@USBO> select count(*) from tb_emp_int;
COUNT(*)
----------
5
--下面的过程用于同步重定义表与临时表
scott@USBO> exec dbms_redefinition.sync_interim_table('SCOTT', 'TB_EMP', 'TB_EMP_INT');
PL/SQL procedure successfully completed.
--这是同步后的结果
scott@USBO> select count(*) from tb_emp_int;
COUNT(*)
----------
39
--最后完成在线重定义，此时如果重定义表上事务没有被完成，需要等到所有事务完成
scott@USBO> exec dbms_redefinition.finish_redef_table('SCOTT', 'TB_EMP', 'TB_EMP_INT');
PL/SQL procedure successfully completed.
scott@USBO> select count(*) from tb_emp_int;
COUNT(*)
----------
100
--检验结果，可以看到列salary上的值发生了变化
scott@USBO> select new.salary new_sal, old.sal old_sal,new.deptno new_deptno,old.deptno old_detpno
2 from tb_emp new, tb_emp_int old
3 where new.empno = old.empno and rownum<=3;
NEW_SAL OLD_SAL NEW_DEPTNO OLD_DETPNO
---------- ---------- ---------- ----------
111.1 101 20 20
112.2 102 10 10
113.3 103 20 20
--验证触发器
scott@USBO> select table_name, trigger_name
2 from user_triggers;
TABLE_NAME TRIGGER_NAME
------------------------------ ------------------------------
TB_EMP TR_BF_TB_EMP_HIREDATE
TB_EMP_INT TR_BF_TB_EMP_INT_HIREDATE
--Author : Leshami
--Blog : http://blog.csdn.net/leshami
--此时临时表上的触发器被应用到重定义的表
scott@USBO> select trigger_body from user_triggers
2 where table_name = 'TB_EMP';
TRIGGER_BODY
--------------------------------------------------------------------------------
BEGIN
:new.hiredate := to_char(SYSDATE + 10,'yyyymmdd hh24:mi:ss');
END tr_bf_int_emp_hiredate;
scott@USBO> desc tb_emp
Name Null? Type
----------------------------------------------------------------------------------- -------- --------------------
EMPNO NUMBER(4)
NAME VARCHAR2(20)
HIREDATE VARCHAR2(20)
SALARY NUMBER
DEPTNO NUMBER(2)
scott@USBO> select column_name,data_type, data_default from dba_tab_columns
2 where owner='SCOTT' and table_name='TB_EMP' and column_name='DEPTNO';
Column Name Data Type DATA_DEFAULT
-------------------- ------------------------- ------------------------------------------------------------------
DEPTNO NUMBER 30
扫一扫
8804
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
