联机重定义表示例

我们的测试表结构(ID列为PK):

SQL> DESC TEST
 Name                                      Null?    Type
 ----------------------------------------- -------- -------------------
 ID                                        NOT NULL VARCHAR2(16)
 USER_NAME                                          VARCHAR2(16)
 GMT                                       NOT NULL TIMESTAMP(6)

1) 验证该表是否可以进行联机重定义(如果不可以会给出具体原因):

SQL> exec DBMS_REDEFINITION.can_REDEF_TABLE('SCOTT','TEST',1);

2) 创建中间临时表并划分分区:

CREATE TABLE test_p(
  ID                     VARCHAR2(16)  NOT NULL,
  user_name              VARCHAR2(16),
  gmt                    TIMESTAMP(6)  NOT NULL
)
PARTITION BY RANGE (gmt) (
PARTITION test_p200511 VALUES LESS THAN(TO_DATE('2005-12-01','yyyy-mm-dd')) ,
PARTITION test_p200512 VALUES LESS THAN(TO_DATE('2006-01-01','yyyy-mm-dd')) );

3) 开始重定义表

BEGIN
   DBMS_REDEFINITION.start_redef_table
      ('SCOTT','TEST','TEST_P',
       'ID         ID,
        USER_name  USER_name,
        GMT        GMT',
       DBMS_REDEFINITION.cons_use_pk
      );
END;

-- 注意条件 dbms_redefinition.cons_use_pk, 如果是根据 ROWID 做联机重定义,则用 dbms_redefinition.cons_use_rowid .

4) 创建索引限制以及触发器等.注:在10g 中,如果这些定义变化了.可以通过 DBMS_REDEFINITION.REGISTER_DEPENDENT_OBJECT() 来创建.如果这些对象定义不变化,则调用 DBMS_REDEFINITION.COPY_TABLE_DEPENDENTS() 即可.

ALTER TABLE test_p
  ADD CONSTRAINT test_p_pk PRIMARY KEY (ID)
  USING INDEX
  TABLESPACE indx;

5) 同步表内容 . 如果需要同步的数据特别大,则可能考虑通过 prebuilt table 的方法先建立物化视图.

EXEC DBMS_REDEFINITION.sync_interim_table ('SCOTT', 'test', 'test_p');

6) 结束重定义过程

EXEC DBMS_REDEFINITION.finish_redef_table ('scott', 'test', 'test_p');

7) 删除临时表 TEST_P.

drop table test_p;

*) 如果想中途停止重定义过程,则通过如下指令退出该过程:

EXEC dbms_redefinition.abort_redef_table('SCOTT', 'test','test_p');

进一步熟悉该过程的机理可以通过查询:

SELECT sql_text FROM v$sqlarea WHERE sql_text LIKE '%TEST_P%';

联机重定义表必须考虑的问题: 会占用源表两倍的空间.此外还要考虑物化视图 Log 的空间以及带来的其他开销.

参考信息:Oracle9i Database Administrator's Guide Release 2 (9.2) 第十五章.10g 关于联机重定义表的改进可以参考 Oracle Database 10g Online Data Reorganization & Redefinition