在一个高并发的场景中,可能会碰到修改表结构的情况,为了不影响对外服务,可以使用在线重定义的功能,他支持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