Oracle database 11g 重定义表

Oracle11g 的联机重定义功能

联机条件下把普通的堆表转换成分区表（11g新特性）

例：联机创建分区表：将emp表联机重定义、要求完成两个任务、使其按照 sal分区（以2500为界）、并去掉comm列、这个过程需要建立一个临时分区表emp_temp完成复制转换、

sys下执行

create table scott.emp as select * from scott.emp;

alter table scott.emp add constraint pk_emp primary key(empno);

1) 检查原始表是否具有在线重定义资格、（要求表自包含及之前没有建立实体化视图及日志）

SQL>BEGIN
      DBMS_REDEFINITION.CAN_REDEF_TABLE('scott','emp');
    END;
    /

2) 创建一个临时分区表:emp_temp, 含有7列（删去comm列）、然后range分区、两个区以sal=2500为界、

SQL>
CREATE TABLE scott.emp_temp
  (empno        number(4) not null,
   ename        varchar2(10),
   job          varchar2(9),
   mgr          number(4),
   hiredate     date,
   sal          number(7,2),
   deptno       number(2))
PARTITION BY RANGE(sal)
   (PARTITION sal_low VALUES LESS THAN(2500),
   PARTITION sal_high VALUES LESS THAN (maxvalue));

3）启动联机重定义处理过程、

SQL>
BEGIN
  dbms_redefinition.start_redef_table('scott','emp','emp_temp',
   'empno empno,
   ename ename,
   job job,
   mgr mgr,
   hiredate hiredate,
   sal sal,
   deptno deptno');
END;
/

SQL> select count(*) from scott.emp_temp;

  COUNT(*)
----------
        14

SQL> select * from scott.emp_temp partition(sal_low);

     EMPNO ENAME      JOB              MGR  HIREDATE                        SAL     DEPTNO
---------- ---------- --------- ---------- ------ -------------   ----------------  -------                     
      7369 SMITH      CLERK                7902 1980-12-17 00:00:00        800         20
      7499 ALLEN      SALESMAN        7698 1981-02-20 00:00:00       1600        30
      7521 WARD      SALESMAN        7698 1981-02-22 00:00:00       1250        30
      7654 MARTIN   SALESMAN        7698 1981-09-28 00:00:00       1250        30
      7782 CLARK      MANAGER         7839 1981-06-09 00:00:00       2450        10
      7844 TURNER   SALESMAN        7698 1981-09-08 00:00:00       1500        30
      7876 ADAMS    CLERK                7788 1987-05-23 00:00:00       1100        20
      7900 JAMES      CLERK                7698 1981-12-03 00:00:00        950         30
      7934 MILLER     CLERK                7782 1982-01-23 00:00:00       1300        10

已选择9行。

SQL> select * from scott.emp_temp partition(sal_high);

     EMPNO ENAME      JOB              MGR HIREDATE                   SAL     DEPTNO
---------- ---------- --------- ---------- ------------------- ---------- ----------
      7566 JONES      MANAGER         7839 1981-04-02 00:00:00       2975         20
      7698 BLAKE      MANAGER         7839 1981-05-01 00:00:00       2850         30
      7788 SCOTT      ANALYST           7566 1987-04-19 00:00:00       3000         20
      7839 KING        PRESIDENT                 1981-11-17 00:00:00       5000         10
      7902 FORD       ANALYST           7566 1981-12-03 00:00:00       3000         20

已选择5行。

这个时候emp_temp的主键、索引、触发器、授权等还没有从原始表继承过来、

SQL> select constraint_name,constraint_type,table_name from user_constraints where table_name like 'emp%';

CONSTRAINT_NAME                CONSTRAINT_TYPE TABLE_NAME
------------------------------ --------------- ------------------------------
PK_emp                          P                emp
SYS_C009652                    C               emp_TEMP

4) 复制依赖对象、

这一步的作用是：临时分区表emp_temp继承原始表emp的全部属性、包括索引、约束和授权以及触发器、

SQL>
DECLARE
  num_errors PLS_INTEGER;
BEGIN
  DBMS_REDEFINITION.COPY_TABLE_DEPENDENTS('scott','emp','emp_temp',
  DBMS_REDEFINITION.CONS_ORIG_PARAMS,TRUE,TRUE,TRUE,TRUE,num_errors);
END;
/

SQL> select constraint_name,constraint_type,table_name from user_constraints where table_name like 'emp%';

CONSTRAINT_NAME                CONSTRAINT_TYPE      TABLE_NAME
--------------------------- --------------- ----------------------
PK_emp                            P                 emp
SYS_C009652                       C                 emp_TEMP
TMP$$_PK_emp0                     P                   emp_TEMP

这时候原始表emp还没有分区，

SQL> select table_name,partition_name,high_value from user_tab_partitions;

TABLE_NAME                     PARTITION_NAME                 HIGH_VALUE
------------------------------ ------------------------------ ----------------------
emp_TEMP                      SAL_HIGH                                MAXVALUE
emp_TEMP                      SAL_LOW                                 2500

5) 完成重定义过程。（相当于原始表emp与临时分区表emp_temp互换名称。）

SQL> EXECUTE dbms_redefinition.finish_redef_table('scott','emp','emp_temp');

SQL> select table_name,partition_name,high_value from user_tab_partitions;

TABLE_NAME                     PARTITION_NAME                 HIGH_VALUE
------------------------------ ------------------------------ ---------------------------
emp                           SAL_HIGH                       MAXVALUE
emp                           SAL_LOW                        2500