Oracle

Oracle在线重定义表

Oracle数据库提供了一种机制，可以在不明显影响表的可用性情况下进行表结构更改。当表在线重定义时，在重定义过程中，可以对表进行查询或DML。该表仅在非常小的窗口期以独占模式被锁定，该窗口独立于表格的大小和重定义的复杂性，并且对用户完全透明。在线重定义表需要大量的空闲空间，大致为被重定义表的二倍空间，如果添加新列则需要更多的空间。

在线重定义表能够实现以下效果：

  • 修改表或集群的存储参数
  • 将表或群集移动到不同的表空间
  • 在表或群集中添加，修改或删除一列或多列
  • 添加或删除分区支持（仅限非Cluster表）
  • 更改分区结构
  • 更改单个表分区的物理属性，包括将其移动到同一模式中的其他表空间
  • 更改物化视图日志或Oracle Streams高级队列队列表的物理属性
  • 添加对并行查询的支持
  • 重新创建表或群集以减少碎片
  • 将普通表（堆组织）更改为索引组织表，或者相反
  • 将关系表转换为包含对象列的表格，或者做相反的操作
  • 将对象表转换为关系表或具有对象列的表，或者相反

先决条件

除去包的执行权限，还需要用到如下权限：

  • DBMS_REDEFINITIONEXECUTE_CATALOG_ROLE
  • CREATE ANY TABLE
  • ALTER ANY TABLE
  • DROP ANY TABLE
  • LOCK ANY TABLE
  • SELECT ANY TABLE
  • CREATE ANY TRIGGER
  • CREATE ANY INDEX

重定义步骤

  1. 选择重定义的方法 Key：选择用于重新定义的主键或伪主键。伪主键是唯一键。所有组件列都有非空约束。对于此方法，重定义前后的表版本应具有相同的主键列。这是重新定义的首选和默认的方法 ROWID：如果没有key可用，请使用此方法。在这种方法中，将一个隐藏的列名M_ROW$$添加到重定义后的表。建议在重定义完成后删除该列或将其标记为未使用。如果COMPATIBLE设置为10.2或更高，在重定义最后阶段会自动设置该列未使用。可以通过ALTER TABLE …. DROP UNUSED COLUMNS删除
  2. 通过调用CAN_REDEF_TABLE过程验证是否可以在线重定义该表
  3. 创建一个空的临时表(同一schema下)，如果要删除列，则不要包含在临时表中；如果要增加列，请将列定义添加到临时表中；如果要修改列，请在临时表中设置
  4. (可选)如果表比较大，可以通过并行来提高性能。 alter session force parallel dml parallel degree-of-parallelism; alter session force parallel query parallel degree-of-parallelism;
  5. 调用过程START_REDEF_TABLE启动重定义，并提供如下信息：
    • 要重定义的表的用户名和表名
    • 临时表名称
    • 列映射
    • 重定义方法。DBMS_REDEFINITION.CONS_USE_PK用于指示应使用主键或伪主键完成重定义。DBMS_REDEFINITION.CONS_USE_ROWID用于指示应使用rowid完成重定义
    • （可选）用于排序行的列
    • 如果仅重新定义分区表的单个分区，则设置分区名称
  6. 选择下列两种方法之一，将被重定义的表的相关对象统计信息复制到临时表中 自动创建相关对象：使用COPY_TABLE_DEPENDENTS过程可以在临时表上自动创建相关对象。此过程还会注册依赖对象 手动创建相关对象：手动在临时表上创建相关对象，然后注册它们
  7. 执行FINISH_REDEF_TABLE过程完成表的重定义
  8. 等待重定义结束，删除临时表

示例

hr.admin_emp表包含empno，ename，job，deptno列，现在要求重定义规则如下：

  • 增加mgr，hiredate，sal，bonus列
  • 新列bonus初始化为0
  • deptno列的值增加10
  • 重定义表对列empno进行范围分区

验证表是否可以在线重定义

1
2
3
4
5

BEGIN
DBMS_REDEFINITION.CAN_REDEF_TABLE('hr','admin_emp',
      DBMS_REDEFINITION.CONS_USE_PK);
END;
/

创建临时表

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12

CREATE TABLE hr.int_admin_emp
        (empno      NUMBER(5) PRIMARY KEY,
         ename      VARCHAR2(15) NOT NULL,
         job        VARCHAR2(10),
         mgr        NUMBER(5),
         hiredate   DATE DEFAULT (sysdate),
         sal        NUMBER(7,2),
         deptno     NUMBER(3) NOT NULL,
         bonus      NUMBER (7,2) DEFAULT(1000))
     PARTITION BY RANGE(empno)
       (PARTITION emp1000 VALUES LESS THAN (1000) TABLESPACE admin_tbs,
        PARTITION emp2000 VALUES LESS THAN (2000) TABLESPACE admin_tbs2);

开始重定义

1
2
3
4
5
6

BEGIN 
DBMS_REDEFINITION.START_REDEF_TABLE（'hr'，'admin_emp'，'int_admin_emp'，
       'empno empno，ename ename，job job，deptno + 10 deptno，0 bonus'，
        dbms_redefinition.cons_use_pk）; 
END; 
/

复制相关对象

1
2
3
4
5
6

DECLARE 
num_errors PLS_INTEGER; 
BEGIN 
DBMS_REDEFINITION.COPY_TABLE_DEPENDENTS（'hr'，'admin_emp'，'int_admin_emp'，
DBMS_REDEFINITION.CONS_ORIG_PARAMS ，TRUE，TRUE，TRUE，TRUE，num_errors）; 
END;

ignore_errors参数设置为TRUE是因为临时表是用主键约束创建的。当COPY_TABLE_DEPENDENT尝试从原始表中复制主键约束和索引时，会发生错误。您可以忽略这些错误，但必须运行下一步中显示的查询以查看是否存在其他错误。

检查错误

1
2
3
4
5
6

DECLARE
num_errors PLS_INTEGER;
BEGIN
DBMS_REDEFINITION.COPY_TABLE_DEPENDENTS('hr', 'admin_emp','int_admin_emp',
   DBMS_REDEFINITION.CONS_ORIG_PARAMS, TRUE, TRUE, TRUE, TRUE, num_errors);
END;

同步临时表(可选)

1
2
3
4

BEGIN 
DBMS_REDEFINITION.SYNC_INTERIM_TABLE（'hr'，'admin_emp'，'int_admin_emp'）; 
END; 
/

完成重定义

1
2
3
4

BEGIN 
DBMS_REDEFINITION.FINISH_REDEF_TABLE（'hr'，'admin_emp'，'int_admin_emp'）; 
END; 
/

待重定义完成，删除临时表

更多在线重定义信息请查阅Redefining Tables Online

comments powered by Disqus