联机重定义表示例

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