`

Oracle的在线重定义表功能

 
阅读更多

在一个高可用系统中,如果需要改变一个表的定义是一件比较棘手的问题,尤其是对于7×24 系统。Oracle提供的基本语法基本可以满足一般性修改,但是对于把普通堆表改为分区表,把索引组织表修改为堆表等操作就无法完成了。而且,对于被大量 DML语句访问的表,幸运的是,Oracle从9i版本开始提供了在线重定义表功能,通过调用DBMS_REDEFINITION包,可以在修改表结构的 同时允许DML操作。


在线重定义表具有以下功能:

修改表的存储参数;

可以将表转移到其他表空间;

增加并行查询选项;

增加或删除分区;

重建表以减少碎片;

将堆表改为索引组织表或相反的操作;

增加或删除一个列。

调用DBMS_REDEFINITION 包需要EXECUTE_CATALOG_ROLE角色,除此之外,还需要CREATE ANY TABLE、ALTER ANY TABLE、DROP ANY TABLE、LOCK ANY TABLE和SELECT ANY TABLE的权限。

在线重定义表的步骤如下:

1.选择一种重定义方法:

存在两种重定义方法,一种是基于主键、另一种是基于ROWID。ROWID的方式不能用于索引组织表,而且重定义后会存在隐藏列M_ROW$$。默认采用主键的方式。

2.调用 DBMS_REDEFINITION.CAN_REDEF_TABLE()过程,如果表不满足重定义的条件,将会报错并给出原因。

3.在用一个方案中建立一个空的中间表,根据重定义后你期望得到的结构建立中间表。比如:采用分区表,增加了COLUMN等。

4.调用DBMS_REDEFINITION.START_REDEF_TABLE()过程,并提供下列参数:被重定义的表的名称、中间表的名称、列的映射规则、重定义方法。

如果映射方法没有提供,则认为所有包括在中间表中的列用于表的重定义。如果给出了映射方法,则只考虑映射方法中给出的列。如果没有给出重定义方法,则认为使用主键方式。

5.在中间表上建立触发器、索引和约束,并进行相应的授权。任何包含中间表的完整性约束应将状态置为disabled。

当重定义完成时,中间表上建立的触发器、索引、约束和授权将替换重定义表上的触发器、索引、约束和授权。中间表上disabled的约束将在重定义表上enable。

6. (可选)如果在执行DBMS_REDEFINITION.START_REDEF_TABLE()过程和执行 DBMS_REDEFINITION.FINISH_REDEF_TABLE()过程直接在重定义表上执行了大量的DML操作,那么可以选择执行一次或多 次的SYNC_INTERIM_TABLE()过程,以减少最后一步执行FINISH_REDEF_TABLE()过程时的锁定时间。

7.执行DBMS_REDEFINITION.FINISH_REDEF_TABLE()过程完成表的重定义。这个过程中,原始表会被独占模式锁定一小段时间,具体时间和表的数据量有关。

执行完FINISH_REDEF_TABLE()过程后,原始表重定义后具有了中间表的属性、索引、约束、授权和触发器。中间表上disabled的约束在原始表上处于enabled状态。

8.(可选)可以重命名索引、触发器和约束。对于采用了ROWID方式重定义的表,包括了一个隐含列M_ROW$$。推荐使用下列语句经隐含列置为UNUSED状态或删除。

ALTER TABLE TABLE_NAME SET UNUSED (M_ROW$$);

ALTER TABLE TABLE_NAME DROP UNUSED COLUMNS;

下面是进行重定义操作后的结果:

原始表根据中间表的属性和特性进行重定义;

START_REDEF_TABLE()和FINISH_REDEF_TABLE()操作之间在中间表上建立的触发器、索引、约束和授权,现在定义在原始表上。中间表上disabled的约束在原始表上处于enabled状态。

原始表上定义的触发器、索引、约束和授权建立在中间表上,并会在删除中间表时删除。原始表上原来enabled状态的索引,建立在中间表上,并处于disabled状态。

任何定义在原始表上的存储过程和游标都会变为INVALID,当下次调用时后自动进行编译。

如果执行过程中出现错误或者人为选择退出的话,可以执行DBMS_REDEFINITION.ABORT_REDEF_TABLE()过程。

举个在线重定义使用的例子:


SQL> create table test_primary (id number primary key);

表已创建。

SQL> insert into test_primary select rownum from dba_objects;

已创建6264行。

SQL> commit;

提交完成。

SQL> create table orgin_table (id number, fid number, name varchar2(30), create_date date)
  2  tablespace users;

表已创建。

SQL> insert into orgin_table select rownum, rownum, object_name, created from dba_objects
  2  where owner = 'SYS';

已创建4034行。

SQL> commit;

提交完成。

SQL> alter table orgin_table add constraint pk_orgin_table primary key (id);

表已更改。

SQL> create or replace trigger tr_orgin_table
  2  before insert on orgin_table for each row
  3  begin
  4  null;
  5  end;
  6  /

触发器已创建

SQL> create index ind_orgin_table_create_date on orgin_table(create_date);

索引已创建。

SQL> alter table orgin_table add constraint f_orgin_primary_id foreign key (fid)
  2  references test_primary (id);

表已更改。

SQL> begin
  2  dbms_redefinition.can_redef_table('yangtk', 'orgin_table', dbms_redefinition.cons_use_pk);
  3  end;
  4  /

PL/SQL 过程已成功完成。

SQL> create table inter_table
  2  (id number, fid number, new_name varchar2(30), create_date date, comments varchar2(1000))
  3  tablespace yangtk
  4  partition by range (create_date)
  5  (partition p1 values less than (to_date('2005-1-1', 'yyyy-mm-dd')),
  6  partition p2 values less than (maxvalue));

表已创建。

SQL> alter table inter_table add constraint pk_inter_table primary key (id);

表已更改。

SQL> begin
  2  dbms_redefinition.start_redef_table('yangtk', 'orgin_table', 'inter_table',
  3  'ID ID, FID FID, NAME NEW_NAME, CREATE_DATE CREATE_DATE, ''NO COMMENTS'' COMMENTS',
  4  DBMS_REDEFINITION.CONS_USE_PK);
  5  END;
  6  /

PL/SQL 过程已成功完成。

SQL> SELECT COUNT(*) FROM INTER_TABLE;

  COUNT(*)
----------
      4034

SQL> COL COMMENTS FORMAT A20
SQL> ALTER SESSION SET NLS_DATE_FORMAT = 'YYYY-MM-DD HH24:MI:SS';

会话已更改。

SQL> SELECT * FROM INTER_TABLE WHERE ID = 1;

        ID        FID NEW_NAME     CREATE_DATE         COMMENTS
---------- ---------- ------------ ------------------- --------------------
         1          1 ACCESS$      2004-10-18 16:14:23 NO COMMENTS

SQL> INSERT INTO ORGIN_TABLE
  2  SELECT 4034+ROWNUM, 4034+ROWNUM, OBJECT_NAME, CREATED FROM DBA_OBJECTS
  3  WHERE OWNER = 'SYSTEM';

已创建404行。

SQL> COMMIT;

提交完成。

SQL> begin
  2  dbms_redefinition.sync_interim_table('yangtk', 'orgin_table', 'inter_table');
  3  end;
  4  /

PL/SQL 过程已成功完成。

SQL> select count(*) from INTER_TABLE;

  COUNT(*)
----------
      4438

SQL> alter table inter_table add constraint fk_inter_primary_id foreign key (fid)
  2  references test_primary (id) disable;

表已更改。

SQL> create index ind_inter_table_date on inter_table(create_date);

索引已创建。

SQL> create index ind_inter_table_fid on inter_table(fid);

索引已创建。

SQL> create or replace trigger tr_inter_table
  2  before insert on inter_table for each row
  3  begin
  4  null;
  5  end;
  6  /

触发器已创建

SQL> begin
  2  dbms_redefinition.finish_redef_table('yangtk', 'orgin_table', 'inter_table');
  3  end;
  4  /

PL/SQL 过程已成功完成。

SQL> desc orgin_table
 名称                            是否为空? 类型
 ------------------------------- -------- ---------------
 ID                              NOT NULL NUMBER
 FID                                      NUMBER
 NEW_NAME                                 VARCHAR2(30)
 CREATE_DATE                              DATE
 COMMENTS                                 VARCHAR2(1000)

SQL> select table_name, partition_name from user_tab_partitions where table_name = 'ORGIN_TABLE';

TABLE_NAME                     PARTITION_NAME
------------------------------ ------------------------------
ORGIN_TABLE                    P1
ORGIN_TABLE                    P2

SQL> select table_name, constraint_name, status from user_constraints
  2  where table_name in ('ORGIN_TABLE', 'INTER_TABLE');

TABLE_NAME           CONSTRAINT_NAME            STATUS
-------------------- -------------------------- --------
INTER_TABLE          PK_ORGIN_TABLE             ENABLED
INTER_TABLE          F_ORGIN_PRIMARY_ID         DISABLED
ORGIN_TABLE          PK_INTER_TABLE             ENABLED
ORGIN_TABLE          FK_INTER_PRIMARY_ID        ENABLED

SQL> SELECT COUNT(*) FROM ORGIN_TABLE;

  COUNT(*)
----------
      4438

SQL> SELECT COUNT(*) FROM INTER_TABLE;

  COUNT(*)
----------
      4438

SQL> DROP TABLE INTER_TABLE;

表已丢弃。

 

分享到:
评论

相关推荐

    在线表重定义——Oracle 10g系列专栏(二).pdf

    在线表重定义是Oracle 10g系列专栏中的一项重要功能,允许用户在线更改表结构,而不需要锁定表。这种功能对于维持Oracle数据库的高可用性非常重要。 传统的表维护动作,如导入、导出动作等,都需要锁定表,导致用户...

    oracle普通表转化为分区表的方法

    总的来说,Oracle的在线重定义功能提供了一种高效且低影响的方式将普通表转换为分区表,尤其适用于大型表,它可以显著提升数据管理效率和查询性能。在进行这种转换时,应根据具体的业务需求和数据规模选择合适的方法...

    在线重定义

    在线重定义(Online Redefinition)是Oracle数据库提供的一种功能,允许用户在不影响应用程序正常运行的情况下,对数据库表进行重构。这一过程主要依赖于DBMS_REDEFINITION包,它包含了一系列的PL/SQL过程,使得...

    Oracle分区表详解

    - **现有表转换限制**:无法直接将已有的表转换为分区表,但可通过 Oracle 提供的在线重定义表功能实现转换。 #### 三、Oracle 分区方法 ##### 1. 范围分区(Range Partitioning) 范围分区基于某一列的值范围来...

    Oracle分区表用法

    - 无法直接将已存在的表转换为分区表,需使用在线重定义表功能。 - 分区管理可能增加数据库复杂性。 4. **表分区的几种类型及操作方法** - **范围分区(Range Partitioning)**:根据分区键的范围划分数据。...

    oracle表空间变动注意事项

    2. **使用在线重定义**:对于大型表或者不允许停机的情况,可以考虑使用在线重定义功能。 ```sql CREATE TABLE new_table AS SELECT * FROM old_table; ALTER TABLE old_table RENAME TO old_table_backup; ...

    Oracle堆表转换成分区表方案

    2. **判断是否可重定义**:使用`DBMS_REDEFINITION.CAN_REDEF_TABLE`函数检查目标表是否支持重定义。 3. **启动重定义过程**:调用`DBMS_REDEFINITION.START_REDEF_TABLE`函数启动重定义流程。 4. **同步数据**:...

    oracle表分区详细讲解

    - 已经存在的表不能直接转化为分区表,但Oracle提供了在线重定义功能。 #### 四、表分区的类型及操作方法 1. **范围分区**:最常用的分区类型之一,根据分区键的范围将数据映射到各个分区。通常使用日期作为分区键...

    ORACLE-分区表的用法

    - 已有的表不能直接转换为分区表,但Oracle提供了在线重定义表的功能。 - 分区表的设计和维护可能比非分区表更加复杂。 #### 4. 表分区的几种类型及操作方法 - **范围分区**:范围分区是最常见的分区类型之一,...

    ORACLE分区表的概念及操作

    然而,分区也有其缺点,如已存在的非分区表无法直接转换为分区表,需要使用Oracle提供的在线重定义功能。此外,分区会增加数据库的复杂性,可能需要更多的存储空间,并可能对某些类型的DML操作(如INSERT、UPDATE、...

    unix中oracle分区表导出

    5. **管理分区**:Oracle提供`ALTER TABLE`命令来管理分区,例如添加、删除或重定义分区。 了解这些基本概念后,你可以更有效地在Unix环境下管理和操作Oracle分区表,同时利用SQL的强大功能进行数据操作。通过实践...

    (Oracle管理)多做知识的积累 详解ORACLE数据库的分区表.docx

    然而,分区表也有其局限性,例如已有的非分区表无法直接转换为分区表,需要使用Oracle的在线重定义功能。此外,创建和维护分区表会增加数据库的复杂性,需要更多的存储空间。 Oracle数据库支持多种类型的分区方式,...

    oracle删除哪些内容可以减少USERS01.DBF数据文件的大小

    11. **使用DBMS_REDEFINITION**:此包允许在线重定义表,可以在不中断服务的情况下完成空间优化。 12. **数据库维护计划**:定期执行数据库维护任务,如分析、统计收集和空间审计,可以帮助识别和解决空间问题。 ...

    Toad-for-Oracle-DBA培训教程

    优化模块提供了对象统计信息收集、图形化联机重定义、表和索引的大小估计、查询计划展现、对象Pin到内存中、DBMS_Profiler结果的图形化展现、SQL语句自动优化器、批量重组对象、创建新表重组单个表、修复行链接、...

    oracle表分区.[归类].pdf

    1. 现有表转换:已有的非分区表不能直接转为分区表,需要使用Oracle提供的在线重定义功能。 三、Oracle分区方法 1. 范围分区:基于特定列的数值范围进行分区,如按序号或日期创建分区。 2. Hash分区:通过哈希函数...

    DBMS_REDEFINITION

    DBMS_REDEFINITION DBMS_REDEFINITION 是 Oracle 10g 中的一个包,主要用于...DBMS_REDEFINITION 包是 Oracle 10g 中的一个非常重要的包,提供了在线重定义表的功能,可以在线修改表的属性,提高系统的性能和可用性。

    ORACLE表自动按月分区步骤

    Oracle提供在线重分区功能,可以在不中断服务的情况下改变分区结构。 - **合并分区**:当某些分区不再需要频繁访问时,可以考虑合并分区以减少存储需求。 - **删除分区**:对于过期的数据,可以定期删除分区,...

    ORACLE TABLE PARTITION

    ##### 方法三:使用Oracle 9i以上版本的在线重定义功能 这是最现代化的方法之一,特别适用于Oracle 9i及更高版本的数据库。 - **步骤**: 1. 创建非分区表`T`。 2. 插入数据。 3. 使用`DBMS_REDEFINITION.START...

    oracle表分区详解

    1. **转换限制**:现有的表无法直接转换为分区表,但Oracle提供了在线重定义表的功能来解决这一问题。 2. **管理复杂度增加**:虽然分区简化了一些管理任务,但也引入了额外的管理复杂度,特别是在分区策略设计上。 ...

Global site tag (gtag.js) - Google Analytics