在线重定义表

oracle 9i提供了dbms_redefinition包来实现数据库的表的在线重定义功能。在实际的应用上，我们可以利用这个包来进行：（1）

堆表与分区之间进行转换。（2）重建表以减少HWM。10g能shrink，9i如果用move tablespace and rebuild index在move的时候会

锁表，如果想实现在线降低HWM，估计只能用这个了。（3）在线更改表结构，如更改列的前后顺序，将column_a，column_b改成

column_b，column_a。

下面就来看看这个包的内容：

CREATE OR REPLACE PACKAGE SYS.dbms_redefinition IS
  ------------
  --  OVERVIEW
  --
  -- This package provides the API to perform. an online, out-of-place
  -- redefinition of a table
 
  --- =========
  --- CONSTANTS
  --- =========
  -- Constants for the options_flag parameter of start_redef_table
  cons_use_pk    CONSTANT BINARY_INTEGER := 1;
  cons_use_rowid CONSTANT BINARY_INTEGER := 2;
 
  -- NAME:     can_redef_table - check if given table can be re-defined
  -- INPUTS:   uname        - table owner name
  --           tname        - table name
  --           options_flag - flag indicating user options to use
  PROCEDURE can_redef_table(uname        IN VARCHAR2,
                            tname        IN VARCHAR2,
                            options_flag IN BINARY_INTEGER := 1);
 
  -- NAME:     start_redef_table - start the online re-organization
  -- INPUTS:   uname        - schema name
  --           orig_table   - name of table to be re-organized
  --           int_table    - name of interim table
  --           col_mapping  - select list col mapping
  --           options_flag - flag indicating user options to use
  PROCEDURE start_redef_table(uname        IN VARCHAR2,
                              orig_table   IN VARCHAR2,
                              int_table    IN VARCHAR2,
                              col_mapping  IN VARCHAR2 := NULL,
                              options_flag IN BINARY_INTEGER := 1);
 
  -- NAME:     finish_redef_table - complete the online re-organization
  -- INPUTS:   uname        - schema name
  --           orig_table   - name of table to be re-organized
  --           int_table    - name of interim table
  PROCEDURE finish_redef_table(uname          IN VARCHAR2,
                               orig_table     IN VARCHAR2,
                               int_table      IN VARCHAR2);
 
  -- NAME:     abort_redef_table - clean up after errors or abort the
  --                               online re-organization
  -- INPUTS:   uname        - schema name
  --           orig_table   - name of table to be re-organized
  --           int_table    - name of interim table
  PROCEDURE abort_redef_table(uname       IN VARCHAR2,
                              orig_table   IN VARCHAR2,
                              int_table    IN VARCHAR2);
 
  -- NAME:     sync_interim_table - synchronize interim table with the original
  --                                table
  -- INPUTS:   uname        - schema name
  --           orig_table   - name of table to be re-organized
  --           int_table    - name of interim table
  PROCEDURE sync_interim_table(uname       IN VARCHAR2,
                               orig_table  IN VARCHAR2,
                               int_table   IN VARCHAR2);
END;
需要说明的是，如果采用主键作为在线重定义的关联，options_flag 为1，此为默认值，如果表中没有主键，可以用rowid作为在线

重定义的关联，但是options_flag 要为2;col_mapping为需要对应的字段，如需要将原表（orig_table ）的id字段和中间临时表（int_table）的col_id 字段对于，则需要col_mapping=>’ID COL_ID’，如果完全对应，col_mapping为null，此为默认值。

1、将普通表定义成分区表，并添加字段。

test@ORCL> begin
  2  dbms_redefinition.can_redef_table('test','emp',
  3  dbms_redefinition.cons_use_rowid);
  4  end;
  5  /

PL/SQL 过程已成功完成。

test@ORCL> create table redef_emp
  2  (
  3  empno number(5),
  4  ename varchar2(15) not null,
  5  job varchar2(10),
  6  mgr number(5),
  7  hiredate date default(sysdate),
  8  sal number(7,2),
  9  deptno number(3) not null,
 10  bonus number(7,2) default(1000))
 11  partition by range(empno)
 12  (
 13  partition emp7800 values less than (7800) tablespace test,
 14  partition emp8000 values less than (8000) tablespace test1
 15  );

表已创建。

test@ORCL> create index emp_index on redef_emp(empno,ename);

索引已创建。

test@ORCL> begin
  2  dbms_redefinition.start_redef_table('test','emp','redef_emp',
  3  'empno empno,ename ename,job job,deptno+10 deptno,0 bonus',
  4  dbms_redefinition.cons_use_rowid);
  5  end;
  6  /

PL/SQL 过程已成功完成。

test@ORCL> declare
  2  num_errors pls_integer;
  3  begin
  4  dbms_redefinition.copy_table_dependents('test','emp','redef_emp',
  5  dbms_redefinition.cons_orig_params,true,true,true,true,num_errors);
  6  end;
  7  /

PL/SQL 过程已成功完成。

test@ORCL> select object_name,base_table_name,ddl_txt from
  2  dba_redefinition_errors;

OBJECT_NAME                    BASE_TABLE_NAME
------------------------------ ------------------------------
DDL_TXT
--------------------------------------------------------------------------------
EMP_IDX                        EMP
CREATE INDEX "TEST"."TMP$$_EMP_IDX0" ON "TEST"."REDEF_EMP" ("EMPNO", "ENAME")
  PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS
  STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
  TABLESPACE "TEST"

test@ORCL> begin
  2  dbms_redefinition.sync_interim_table('test','emp','redef_emp');
  3  end;
  4  /

PL/SQL 过程已成功完成。

test@ORCL> begin
  2  dbms_redefinition.finish_redef_table('test','emp','redef_emp');
  3  end;
  4  /

PL/SQL 过程已成功完成。

test@ORCL> drop table redef_emp purge;

表已删除。

test@ORCL> select * from emp;

     EMPNO ENAME           JOB               MGR HIREDATE              SAL     DEPTNO      BONUS
---------- --------------- ---------- ---------- -------------- ---------- ---------- ----------
      7369 SMITH           CLERK                 06-8月 -08                        30          0
      7499 ALLEN           SALESMAN              06-8月 -08                        40          0
      7521 WARD            SALESMAN              06-8月 -08                        40          0
      7566 JONES           MANAGER               06-8月 -08                        30          0
      7654 MARTIN          SALESMAN              06-8月 -08                        40          0
      7698 BLAKE           MANAGER               06-8月 -08                        40          0
      7782 CLARK           MANAGER               06-8月 -08                        20          0
      7788 SCOTT           ANALYST               06-8月 -08                        20          0
      7839 KING            PRESIDENT             06-8月 -08                        20          0
      7844 TURNER          SALESMAN              06-8月 -08                        40          0
      7876 ADAMS           CLERK                 06-8月 -08                        30          0
      7900 JAMES           CLERK                 06-8月 -08                        40          0
      7902 FORD            ANALYST               06-8月 -08                        30          0
      7934 MILLER          CLERK                 06-8月 -08                        20          0

已选择14行。

2、将普通表定义成对象表
test@ORCL>create table customer
 2  (
 3  cid number primary key,
 4  name varchar2(30),
 5  street varchar2(100),
 6  city varchar2(30),
 7  state varchar2(2),
 8  zip number(5)
 9   );

test@ORCL> insert into customer select object_id,substr(object_name,1,29),object_type,owner,generated,
 2 data_object_id from dba_objects where object_id is not null;

test@ORCL> create type addr_t as object(
  2  street varchar2(100),
  3  city varchar2(30),
  4  state varchar2(2),
  5  zip number(5,0));
  6  /

类型已创建。

test@ORCL> begin
  2  dbms_redefinition.can_redef_table('test','customer',
  3  dbms_redefinition.cons_use_pk);
  4  end;
  5  /

PL/SQL 过程已成功完成。

test@ORCL> create table int_customer(
  2  cid number,
  3  name varchar2(30),
  4  addr addr_t);

表已创建。

test@ORCL> begin
  2  dbms_redefinition.start_redef_table(
  3  uname=>'test',
  4  orig_table=>'customer',
  5  int_table=>'int_customer',
  6  col_mapping=>'cid cid,name name,addr_t(street,city,state,zip) addr');
  7  end;
  8  /

PL/SQL 过程已成功完成。

test@ORCL> declare
  2  num_errors pls_integer;
  3  begin
  4  dbms_redefinition.copy_table_dependents(
  5  'test','customer','int_customer',dbms_redefinition.cons_orig_params,
  6  true,true,true,false,num_errors,true);
  7  end;
  8  /
PL/SQL 过程已成功完成。

test@ORCL> begin
  2  dbms_redefinition.sync_interim_table('test','customer','int_customer');
  3  end;
  4  /

PL/SQL 过程已成功完成。

test@ORCL> begin
  2  dbms_redefinition.finish_redef_table('test','customer','int_customer');
  3  end;
  4  /

test@ORCL> drop table int_customer;

表已删除。

test@ORCL> desc customer
 名称                                                  是否为空? 类型
 ----------------------------------------------------- -------- ---------------

 CID                                                            NUMBER
 NAME                                                           VARCHAR2(30)
 ADDR                                                           ADDR_T

还有两个例子我还没有弄明白，待续

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/10805681/viewspace-421325/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/10805681/viewspace-421325/