联机重定义表示例

Oracle 9i 之后可以进行联机重定义表(Online Redefine Tables,或”在线重定义表”).该特性从某种程度上提供了一定的高可用性.通过该功能可以做到:

  • 修改表的存储参数
  • 移动该表到相同 Schema 下的 不同表空间内
  • 添加并行查询支持
  • 添加或删除分区
  • 重建表以便减少碎片
  • 在普通表和索引组织(index-organized)表之间互相转换
  • 添加或删除列

做一个从普通表到分区表之间的转换操作.可以用 DBA 用户操作.如果是普通用户需要有DBMS_REDEFINITION 包的可执行权限以及如下权限:

* CREATE ANY TABLE
* ALTER  ANY TABLE
* DROP   ANY TABLE
* LOCK   ANY TABLE
* SELECT ANY TABLE 

下面通过一个例子来简单演练一把.假定目前产品库有一个非分区表 TEST. 准备对把该表联机修改为分区表.


我们的测试表结构(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


  • http://www.orawh.com wanghai

    终于又见技术文章了,赞

  • http://www.openrss.net/ Fenng

    呵呵,太简单.备忘

  • http://www.dbanotes.net Fenng

    http://orafaq.com/node/4
    一篇英文的文章,可以供参考

  • http://www.dbanotes.net Fenng

    Subject: How to Re-Organize a Table Online
    Metalink Note:177407.1
    Yet another Refer

  • http://www.dbanotes.net Fenng

    When rebuild index
    How Oracle Ensures Consistency
    ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
    When the ONLINE keyword is specified as a part of an alter index or
    create index command a temporary journal table to record changes made to
    the base table is created. The journal is an IOT (Index Organized Table)
    table type.
    A Row Share Table Lock(RS) is held on the table during the index creation.
    The index creation process so as to ensure read consistency and avoid any
    ORA-1555s adopts a modified read algorithm.
    Oracle merges the changes entered in the journal at the end of the index
    build process. This merge by which changed rows are incorporated into the
    new index, is done while the table is still online.
    This is accomplished by scanning the journal table and operating on a per
    row basis. Operations are committed every 20 rows. Locked rows are
    skipped. Oracle may make multiple passes over the journal table to
    process previously locked rows