在线重定义

接到开发部同事通知，需要将某张表改成索引组织表，在此记录过程：

首先测试源表是否能重定义：

SQL> begin

  2  dbms_redefinition.can_redef_table('xxx','t');

  3  end;

  4  /

PL/SQL procedure successfully completed.

SQL>

没有问题，下面创建中间表：

SQL> create table t_new 

  2  (oau_id VARCHAR2(255 CHAR),

  3  oau_value VARCHAR2(255 CHAR),

  4  aut_id VARCHAR2(255 CHAR),

  5  org_id VARCHAR2(255 CHAR),

  6  constraint pk_id primary key (oau_id))

  7  organization index;

Table created.

SQL>

然后开始重定义过程：

开始：

SQL> exec dbms_redefinition.start_redef_table('xxx','t','t_NEW');

PL/SQL procedure successfully completed.

同步：

SQL> execute dbms_redefinition.sync_interim_table('xxx','t','t_NEW');

PL/SQL procedure successfully completed.

结束：

SQL> EXEC DBMS_REDEFINITION.FINISH_REDEF_TABLE('xxx','t','t_NEW');

PL/SQL procedure successfully completed.

SQL>

查看是否成功：

SQL> set linesize 160

SQL> set pagesize 50000

SQL> set autot traceonly

SQL> select * from t;

49 rows selected.

Execution Plan

----------------------------------------------------------

Plan hash value: 1316144183

------------------------------------------------------------------------------

| Id  | Operation            | Name  | Rows  | Bytes | Cost (%CPU)| Time     |

------------------------------------------------------------------------------

|   0 | SELECT STATEMENT     |       |    49 | 50372 |     2   (0)| 00:00:01 |

|   1 |  INDEX FAST FULL SCAN| PK_ID |    49 | 50372 |     2   (0)| 00:00:01 |

------------------------------------------------------------------------------

Note

-----

   - dynamic sampling used for this statement (level=2)

Statistics

----------------------------------------------------------

        339  recursive calls

          0  db block gets

         61  consistent gets

          0  physical reads

          0  redo size

       3439  bytes sent via SQL*Net to client

        553  bytes received via SQL*Net from client

          5  SQL*Net roundtrips to/from client

          6  sorts (memory)

          0  sorts (disk)

         49  rows processed

成功！

相当方便

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

转载于:http://blog.itpub.net/25915379/viewspace-736031/