在线重定义表

ORACLE数据库支持在线(不关库)修改表的各种物理属性和逻辑属性,包括责增加/更改/删除分区、更改表空间、增加列、删除列、将表改为IOT表等等。

个人觉得将未分区的表重定义为分区表是用的最多的,因为一些DB前期设计未考虑周全,很多大表到后期都需要分区。(对大表做在线重定义的时候,个人建议尽量少做基于原表的大的查询或大的DML操作)

现在来简单测试一下这个强大的功能:

 

--实验环境

14:23:42 SCOTT@orcl> select * from v$version;

BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
PL/SQL Release 11.2.0.1.0 - Production
CORE    11.2.0.1.0      Production
TNS for 32-bit Windows: Version 11.2.0.1.0 - Production
NLSRTL Version 11.2.0.1.0 - Production

已选择5行。

 --创建原始表

14:07:36 SCOTT@orcl> create table admin_emp(
14:07:54   2  empno number(4),ename varchar2(10),job varchar2(9),deptno number(4));

表已创建。

已用时间:  00: 00: 01.01

我们来使用主键的方式进行重定义:

  在线重定义支持按键和按ROWID方式重定义。其中按键可以是按主键或有唯一索引的键

--给原始表加主键

14:13:53 SCOTT@orcl> alter table admin_emp add primary key(empno);

表已更改。

14:14:17 SCOTT@orcl> desc admin_emp
 名称                                                  是否为空? 类型
 ----------------------------------------------------- -------- -------------

 EMPNO                                                 NOT NULL NUMBER(4)
 ENAME                                                          VARCHAR2(10)
 JOB                                                            VARCHAR2(9)
 DEPTNO                                                         NUMBER(4)

--验证表是否能被在线重定义
14:14:26 SCOTT@orcl> CONN /AS SYSDBA
已连接。
14:14:39 SYS@orcl> BEGIN
14:14:43   2    DBMS_REDEFINITION.CAN_REDEF_TABLE('SCOTT','admin_emp',
14:14:43   3        DBMS_REDEFINITION.CONS_USE_PK);
14:14:43   4  END;
14:14:43   5  /

PL/SQL 过程已成功完成。

14:15:09 SYS@orcl> conn scott/tiger
已连接。
14:15:15 SCOTT@orcl> --创建中间表
14:15:33 SCOTT@orcl> CREATE TABLE int_admin_emp
14:16:44   2          (empno      NUMBER(5) PRIMARY KEY,
14:16:44   3           ename      VARCHAR2(15) NOT NULL,
14:16:44   4           job        VARCHAR2(10),
14:16:44   5           mgr        NUMBER(5),
14:16:44   6           hiredate   DATE DEFAULT (sysdate),
14:16:44   7           sal        NUMBER(7,2),
14:16:44   8           deptno     NUMBER(3) NOT NULL,
14:16:44   9           bonus      NUMBER (7,2) DEFAULT(1000))
14:16:44  10       PARTITION BY RANGE(empno)
14:16:44  11         (PARTITION emp1000 VALUES LESS THAN (1000) TABLESPACE SCOTT_TBS,
14:16:44  12          PARTITION emp2000 VALUES LESS THAN (2000) TABLESPACE USERS);

表已创建。

已用时间:  00: 00: 00.32
14:16:46 SCOTT@orcl> --开始重定义处理
14:17:04 SCOTT@orcl> conn /as sysdba
已连接。
14:17:25 SYS@orcl> BEGIN
14:17:40   2    DBMS_REDEFINITION.START_REDEF_TABLE('SCOTT', 'admin_emp','int_admin_emp',
14:17:40   3         'empno empno, ename ename, job job, deptno+10 deptno, 0 bonus',
14:17:40   4          dbms_redefinition.cons_use_pk);
14:17:40   5  END;
14:17:40   6  /

PL/SQL 过程已成功完成。

已用时间:  00: 00: 10.84
14:17:52 SYS@orcl> --复制依赖对象
14:18:29 SYS@orcl> DECLARE
14:18:29   2  num_errors PLS_INTEGER;
14:18:29   3  BEGIN
14:18:29   4    DBMS_REDEFINITION.COPY_TABLE_DEPENDENTS('SCOTT', 'admin_emp','int_admin_emp',
14:18:29   5     DBMS_REDEFINITION.CONS_ORIG_PARAMS, TRUE, TRUE, TRUE, TRUE, num_errors);
14:18:29   6  END;
14:18:29   7  /

PL/SQL 过程已成功完成。

 

14:19:10 SYS@orcl> --同步中间表
14:19:49 SYS@orcl> BEGIN
14:19:49   2    DBMS_REDEFINITION.SYNC_INTERIM_TABLE('scott', 'admin_emp', 'int_admin_emp');
14:19:49   3  END;
14:19:49   4  /

PL/SQL 过程已成功完成。

已用时间:  00: 00: 00.06
14:19:50 SYS@orcl> --完成重定义
14:20:13 SYS@orcl> BEGIN
14:20:13   2    DBMS_REDEFINITION.FINISH_REDEF_TABLE('scott', 'admin_emp', 'int_admin_emp');
14:20:13   3  END;
14:20:13   4  /

PL/SQL 过程已成功完成。

已用时间:  00: 00: 00.95
14:20:17 SYS@orcl> conn scott/tiger
已连接。
--发现表结构已经改变 表在线重定义成功
14:20:26 SCOTT@orcl> desc admin_emp
 名称                                                  是否为空? 类型
 ----------------------------------------------------- -------- -------------------------------

 EMPNO                                                 NOT NULL NUMBER(5)
 ENAME                                                 NOT NULL VARCHAR2(15)
 JOB                                                            VARCHAR2(10)
 MGR                                                            NUMBER(5)
 HIREDATE                                                       DATE
 SAL                                                            NUMBER(7,2)
 DEPTNO                                                NOT NULL NUMBER(3)
 BONUS                                                          NUMBER(7,2)


--查看是否已经重定义为分区表
14:22:42 SCOTT@orcl> select * from admin_emp partition(emp1000);

未选定行

已用时间:  00: 00: 00.01

--

注意:在线重定义表需要大量空闲空间。

         在线重定义表的时候可以做DML操作。

         从10gR2开始,Oracle支持单个分区表的在线重定义

 重定义完成后,中间表(包括表上的索引)仍旧是存在的,如果占用空间大,需要手工删除

一般使用包DBMS_REDEFINITION 或EM执行在线重定义。