Oracle database 11g 重定义表

一时失言乱红尘 2022-05-30 09:50 181阅读 0赞

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