Oracle 在线重定义

  在一个高并发的场景中，可能会碰到修改表结构的情况，为了不影响对外服务，可以使用在线重定义的功能，他支持1、基于主键  2、基于rowid的重定义，具体示例如下：

-- 1、建立表结构

SQL> l
  1  CREATE TABLE test(
  2	 id int primary key,
  3	 msg1 varchar2(20),
  4	 msg2 varchar2(30)
  5  )
  6  PARTITION BY RANGE(id)
  7  (
  8    PARTITION part01 VALUES LESS THAN (10000),
  9    PARTITION part02 VALUES LESS THAN (20000),
 10    PARTITION part03 VALUES LESS THAN (30000),
 11    PARTITION part04 VALUES LESS THAN (MAXVALUE)
 12* )
SQL> /


Table created.

--2、创建序列

SQL> CREATE SEQUENCE seqtest INCREMENT BY 1 START WITH 1 NOMAXVALUE NOCYCLE CACHE 1000; 
Sequence created.

--3、填充数据

SQL> ed
Wrote file afiedt.buf
  1  BEGIN
  2    FOR i IN 1..40000 LOOP
  3	 INSERT INTO TEST VALUES(seqtest.nextval,seqtest.nextval||'aaa',seqtest.nextval||'bbb');
  4	 IF MOD(i,10000)=0 THEN
  5	   COMMIT;
  6	 END IF;
  7    END LOOP;
  8* END;
  9  /
PL/SQL procedure successfully completed.

--4、重定义测试

SQL> l
  1  BEGIN
  2    dbms_redefinition.can_redef_table('zybi','test',dbms_redefinition.cons_use_pk);
  3* END;
SQL> /


PL/SQL procedure successfully completed.

--5、创建测试表，加入新字段msg3 varchar2(40)

SQL> l
  1  CREATE TABLE test2(
  2	 id int primary key,
  3	 msg1 varchar2(20),
  4	 msg2 varchar2(30),
  5	 msg3 varchar2(40)
  6  )
  7  PARTITION BY RANGE(id)
  8  (
  9    PARTITION part01 VALUES LESS THAN (10000),
 10    PARTITION part02 VALUES LESS THAN (20000),
 11    PARTITION part03 VALUES LESS THAN (30000),
 12    PARTITION part04 VALUES LESS THAN (MAXVALUE)
 13* )
SQL> /


Table created.

--6、开始做表映射

SQL> l
  1  BEGIN
  2    dbms_redefinition.start_redef_table(
  3		'ZYBI',
  4		'TEST',
  5		'TEST2',
  6		'ID ID,MSG1 MSG1,MSG2 MSG2,MSG1||MSG2 MSG3',
  7		dbms_redefinition.cons_use_pk);
  8* END;
SQL> /


PL/SQL procedure successfully completed.

--7、为临时表创建相应的约束索引等

SQL> ed
Wrote file afiedt.buf


  1  DECLARE
  2	 errcnt NUMBER;
  3  BEGIN
  4	 errcnt := 0;
  5	 dbms_redefinition.copy_table_dependents(
  6		uname => 'ZYBI',
  7		orig_table => 'TEST',
  8		int_table => 'TEST2',
  9		copy_indexes => dbms_redefinition.cons_orig_params,
 10		num_errors => errcnt,
 11		ignore_errors => TRUE
 12	      );
 13	 dbms_output.put_line(errcnt);
 14* END;
 15  /


PL/SQL procedure successfully completed.

--8、查看错误信息,因为我在创建临时表的时候，已经建立了主键，所以这里报错，忽略

SQL> select ddl_txt from DBA_REDEFINITION_ERRORS;


DDL_TXT
--------------------------------------------------------------------------------
CREATE UNIQUE INDEX "ZYBI"."TMP$$_SYS_C00166330" ON "ZYBI"."TEST2" ("ID")
  PCT


ALTER TABLE "ZYBI"."TEST2" ADD CONSTRAINT "TMP$$_SYS_C00166330" PRIMARY KEY ("ID

--9、启动表同步

SQL> l
  1  BEGIN
  2    dbms_redefinition.sync_interim_table('ZYBI','TEST','TEST2');
  3* END;
SQL> /


PL/SQL procedure successfully completed.

--10、数据插入测试同步情况,插入下面数据再执行9步，就能看到同步效果

SQL> INSERT INTO TEST VALUES(40001,'x','y');


1 row created.


SQL> commit;


Commit complete.


SQL> SELECT * FROM TEST2 WHERE ID=40001;


no rows selected


SQL> exec dbms_redefinition.sync_interim_table('ZYBI','TEST','TEST2');


PL/SQL procedure successfully completed.
SQL> SELECT * FROM TEST2 WHERE ID=40001;


	ID MSG1  MSG2  MSG3
---------- ----- ----- -----
     40001 x	 y     xy

--11、完成表同步

SQL> l
  1  BEGIN
  2    dbms_redefinition.finish_redef_table('zybi','test','test2');
  3* END;
SQL> /


PL/SQL procedure successfully completed.

--12、验证表数据

SQL> SELECT * FROM TEST WHERE ID=40001;


	ID MSG1  MSG2  MSG3
---------- ----- ----- -----
     40001 x	 y     xy