Oracle 表在线重定义

Oracle 11G 表在线重定义(Redefining Tables Online)

目录:

1、在线表重新定义的特性

2、在线重定义表的步骤

3、重新定义过程的结果

4、执行中间的同步

5、终止在线重定义和清理错误

6、在线重定义的一些限制

7、在线重定义单个表分区

8、在线重定义的一般步骤

9、DBMS_REDEFINITION包所需的特权

10、实战案例

11、知识扩展

参考文献:

Oracle 11G 在线文档:http://docs.oracle.com/cd/E11882_01/server.112/e10595/tables.htm#ADMIN01514

在任何数据库系统中,偶尔需要修改一个表的逻辑或物理结构:
♦ 提高查询的性能或DML
♦ 容纳应用程序更改
♦ 管理存储

Oracle数据库提供一个机制来进行表结构修改,对可用性没有显著影响。 这个机制被称为在线表重新定义。 在线表重新定义提供了一个大幅增加可用性比传统的重新定义表方法。
当一个表在线重新定义,它既可以查询和DML,在重新定义的过程中。 通常情况下,表被锁在独占模式只在一个很小的窗口,是独立于表的大小和复杂性的重新定义,这对用户来说是完全透明的。 然而,如果在线重新定义时,有许多并发DML操作,则需要等待更长时间表锁定。 由于等待,更多的更改提交到原始表,这就增加了刷新时间和表锁的时间。
在线表重新定义需要的空闲空间量大约相当原表的空间使用量。 如果增加新列,可能需要更多的空间。
您可以执行在线表重新定义企业管理器向导或是在对象DBMS_REDEFINITION包中。

在线表重新定义的特性

在线表重新定义使您能够:

♦修改表或簇表的存储参数
♦移动表或簇表到不同的表空间
♦添加、修改、删除一个或多个表或簇表中的列
♦添加或删除分区支持(非簇表)
♦改变分区结构
♦改变一个表分区的物理性质,包括移动它到同一模式下的不同表空间。
♦改变物化视图的物理性质或Oracle Streams Advance 排队队列。
♦添加支持并行查询
♦重建一个表或簇表来减少碎片
♦改变一个堆表到索引组指标,或是相反的操作。
♦转换对象表到关系表或是对象列的表,或是相反的操作。

在线重定义表的步骤如下

1.选择一种重定义方法
存在两种重定义方法,一种是基于主键、另一种是基于ROWID。ROWID的方式不能用于索引组织表,而且重定义后会存在隐藏列M_ROW$$。默认采用主键的方式。
2.调用DBMS_REDEFINITION.CAN_REDEF_TABLE()过程来验证在线重新定义表,如果表不满足重定义的条件,将会报错并给出原因。
3.在用一个方案中建立一个空的中间表,根据重定义后你期望得到的结构建立中间表。比如:采用分区表,增加了COLUMN等。没有必要创建触发器、索引、约束和授权等,因为这些会在接下来的步骤进行复制。
在中间表上建立触发器、索引和约束,并进行相应的授权。任何包含中间表的完整性约束应将状态置为disabled。
当重定义完成时,中间表上建立的触发器、索引、约束和授权将复制重定义表上的触发器、索引、约束和授权。中间表上disabled的约束将在重定义表上enable。

4.调用DBMS_REDEFINITION.START_REDEF_TABLE()过程,并提供下列参数:被重定义的表的名称、中间表的名称、列的映射规则、重定义方法。
如果映射方法没有提供,则认为所有包括在中间表中的列用于表的重定义。如果给出了映射方法,则只考虑映射方法中给出的列。如果没有给出重定义方法,则认为使用主键方式。
如果使用rowid方法进行在线重定义分区表,需要在临时表上开启行迁移。
ALTER TABLE … ENABLE ROW MOVEMENT;
5.(可选)如果你重新定义是一个大表,通过下面两条语句,并行运行提高性能。
ALTER SESSION FORCE PARALLEL DML PARALLEL degree-of-parallelism;
ALTER SESSION FORCE PARALLEL QUERY PARALLEL degree-of-parallelism;
6、调用START_REDEF_TABLE 开始在线重定义,并提供以下参数
模式和表名
临时表名
重新定义的方法
(可选)列名
如果重新定义只有一个分区表的分区,那么列出分区的名称
DBMS_REDEFINITION.CONS_USE_PK是用来表明,重新定义应该使用主键,DBMS_REDEFINITION.CONS_USE_ROWID是用来表明,重新定义应使用rowid,如果省略该参数,默认的方法重新定义(CONS_USE_PK)如果在执行DBMS_REDEFINITION.START_REDEF_TABLE()过程中直接在重定义表上执行了大量的DML操作,那么可以选择执行一次或多次的SYNC_INTERIM_TABLE()过程,以减少最后一步执行FINISH_REDEF_TABLE()过程时的锁定时间。如果START_REDEF_TABLE由于某种原因失败,您必须调用ABORT_REDEF_TABLE,否则后续试图重新定义表将会失败。

7.复制依赖对象(比如触发器,索引,物化视图日志、授权、和约束)和统计数据从表中被重新定义的临时表,使用下列两种方法之一:
方法1:自动创建依赖对象
使用COPY_TABLE_DEPENDENTS程序自动创建依赖对象,结果是,当重新定义完成后,依赖对象的名称将是相同的原始依赖对象的名称。
执行DBMS_REDEFINITION.FINISH_REDEF_TABLE()过程完成表的重定义。这个过程中,原始表会被独占模式锁定一小段时间,具体时间和表的数据量有关。
方法2:手动创建依赖对象
执行完FINISH_REDEF_TABLE()过程后,原始表重定义后具有了中间表的属性、索引、约束、授权和触发器。中间表上disabled的约束在原始表上处于enabled状态。

8.执行FINISH_REDEF_TABLE完成表的定义过程。 在这个过程中,原始表被锁在独占模式很短的时间内,独立于原始表的数据量。 然而,FINISH_REDEF_TABLE将等待所有之前未提交DML。
9.(可选)可以重命名索引、触发器和约束。对于采用了ROWID方式重定义的表,包括了一个隐含列M_ROW$$。推荐使用下列语句经隐含列置为UNUSED状态或删除。
ALTER TABLE TABLE_NAME SET UNUSED (M_ROW$$);
ALTER TABLE TABLE_NAME DROP UNUSED COLUMNS;
10.等待任何长时间运行的查询临时表来完成,然后删除临时表。如果你删除临时表有活跃的查询运行时,您可能会遇到一个ORA-08103错误(“对象不再存在”)。

自动创建依赖对象

您使用COPY_TABLE_DEPENDENTS程序自动创建依赖对象的临时表。
你会发现如果复制依赖对象时发生的错误检查num_errors输出参数。 如果ignore_errors参数设置为TRUE,COPY_TABLE_DEPENDENTS程序继续复制依赖对象,即使遇到一个错误当创建一个对象。 您可以查看这些错误通过查询DBA_REDEFINITION_ERRORS视图。如果ignore_errors被设置为FALSE,COPY_TABLE_DEPENDENTS程序停止复制对象一旦遇到任何错误。

手动创建依赖对象

如果你手动创建依赖对象的临时表和SQL * +或企业经理,您必须使用REGISTER_DEPENDENT_OBJECT过程注册依赖对象。 注册依赖对象允许重新定义完成过程来恢复依赖对象名称在重新定义他们。
你还会使用REGISTER_DEPENDENT_OBJECT程序,如果COPY_TABLE_DEPENDENTS过程未能复制依赖对象和人工干预是必须的。
你可以查询DBA_REDEFINITION_OBJECTS为了确定哪些依赖对象注册。 这个视图显示注册的显式的依赖对象REGISTER_DEPENDENT_OBJECT过程或隐式的COPY_TABLE_DEPENDENTS过程。 只显示当前信息的视图。UNREGISTER_DEPENDENT_OBJECT过程可用于注销一个对象依赖的表被重新定义和临时表。

重新定义过程的结果

以下是重新定义过程的最终结果

原始表重新定义的列,索引、约束、授权、触发器和临时表的统计信息。
注册的依赖对象,或者显式地使用REGISTER_DEPENDENT_OBJECT或隐式地使用COPY_TABLE_DEPENDENTS对象名称,自动重命名,这样依赖重新定义之前重新定义表是一样的。
注意:如果没有注册完成或者没有完成自动复制,然后您必须手动重命名依赖对象。
临时表的引用约束涉及现在涉及到重新定义了表和启用。
任何索引、触发器、物化视图日志,赠款和约束定义在原始表(重新定义之前)转移到临时表,删除当用户删除临时表。 任何涉及前的原始表的引用约束定义现在涉及到临时表和禁用。

一些PL / SQL对象、视图、同义词和其他table-dependent对象可能会失效。 只有那些对象依赖的元素表,改变失效。 例如,如果一个PL / SQL程序重新定义表的查询只列被重新定义不变,过程仍然有效。 看到“管理对象的依赖关系”关于模式的更多信息对象的依赖关系。

执行中间的同步

后重新定义过程已经开始通过调用START_REDEF_TABLE之前,FINISH_REDEF_TABLE被称为,大量的DML语句可能是原始表上执行。 如果你知道是这种情况,建议您定期同步的临时表与原表。 这是通过调用来完成的SYNC_INTERIM_TABLE过程。 调用这个过程花费的时间减少了FINISH_REDEF_TABLE完成重新定义的过程。 是没有限制的次数,您可以调用SYNC_INTERIM_TABLE。

终止在线重定义和清理错误

如果一个错误是重新定义过程中,或者如果您选择终止重新定义的过程,调用ABORT_REDEF_TABLE,执行完之后可以删除临时表及其依赖对象。

在线重定义的一些限制

♦如果使用基于主键的方式,则原表后重定义后的表必须有相同的主键, 如果使用基于ROWID的方式,则不能是索引组织表
♦重新定义一个物化视图日志表,任何依赖的后续刷新物化视图必须是一个完整的刷新。
♦索引组织表的溢出表不能在线重定义。
♦细粒度访问控制表在线(级安全)不能被重新定义。
♦表的闪回数据档案不能启用在线重新定义。
♦拥有BFILE,LOGN列的表不能在线重定义。
♦不支持水平数据子集。
♦sys和system下的表不能在线重定义。
♦临时表不能被重新定义。
♦表中的一个子集的行不能被重新定义。
♦只有简单的确定性表达式、序列和SYSDATE时可以使用临时表中的列映射到原始表。 例如,子查询是不允许的。
♦如果在添加新列重新定义的一部分,没有为这些列列映射,然后他们不能宣布NOT NULL直到重新定义完成。
♦不能有任何引用约束之间的表被重新定义和临时表。
♦在线重定义无法采用nologging
♦物化视图容器表或者高级队列表不能在线重定义
♦您不能执行在线重新定义分区的表上如果表包含一个或多个嵌套表。
♦在线重定义不能同时运行在多个表在不同DBMS_REDEFINITION如果通过引用相关的表分区。
♦在线重定义一个表或对象XMLType表可以导致一个悬空REF在其他表如果有其他表REF重新定义表列引用。

在线重定义单个表分区

从Oracle数据库10 g版本2开始,你可以在线重新定义单个表分区。 举例来说,这是非常有用的,如果你想要一个分区移动到一个不同的表空间,并可以保持分区可用的DML操作。例如,一个非常大的表移动到一个不同的表空间,你可以一次移动一个分区,以减少所需的自由空间和撤销空间来完成。

重新定义单个分区与重新定义一个表格的不同之处在于以下方面

不需要复制依赖对象。 它不是有效的使用COPY_TABLE_DEPENDENTS程序重新定义单个分区。
您必须手动创建任何本地临时表上的索引。
列映射的字符串START_REDEF_TABLE必须NULL。
当使用by-rowid方法,重新定义放入隐藏列的最后阶段是DROP M_ROW$$ 而不是设置未使用。

在线重定义单个表分区的规则

底层机制是使用交换分区方法(ALTER TABLE…EXCHANGE PARTITION)
这里有一些一般的限制:
♦没有逻辑的变化(如添加或删除列)是允许的。
♦没有更改分区的方法(比如从范围分区到散列分区)是允许的。

定义临时表的规则

♦如果范围、散列,或列表分区被重新定义,临时表必须分区。
♦如果分区被重新定义一系列复合range-hash分区表的分区,临时表必须是一个哈希分区表。 此外,临时表的分区键必须是相同的subpartitioning range-hash分区表的关键,在临时表和分区的数量必须相同的subpartitions范围分区被重新定义。
♦如果分区被重新定义一系列复合range-list分区表的分区,临时表必须分区表列表。 此外,临时表的分区键必须是相同的subpartitioning range-list分区表的♦键和值列表的临时表的列表分区必须精确匹配的值列表的列表subpartitions范围分区被重新定义。
♦如果你临时表定义为压缩,那么您必须使用通过主键的方法重新定义,而不是by-rowid方法。

索引组指标重定义分区的额外规则

♦必须使用索引组织临时表
♦原始和临时表必须有相同主键的列,在相同的顺序。
♦如果启用了键的压缩,必须启用它的原始和临时表,用相同的前缀长度。
♦原始和临时表必须为任何LOB列有相同的存储属性。
♦原始和临时表必须有溢出段。

在线重定义的一般步骤

1、 检查是否满足条件
EXEC DBMS_REDEFINITION.CAN_REDEF_TABLE(‘EDW’, ‘CTL_LOG’, DBMS_REDEFINITION.CONS_USE_PK);
–检查原表是否满足在线重定义的条件
2、 生成中间表
CREATE TABLE CTL_LOG_TMP
–中间表的表结构要与原表一致,可以新加列
3、 开始重定义
EXEC DBMS_REDEFINITION.START_REDEF_TABLE(‘EDW’, ‘CTL_LOG’,’CTL_LOG_TMP’,’ID ID,PROC_NAME PROC_NAME,CTL_TIME CTL_TIME,CTL_DEMO CTL_DEMO’);
–首先将临时表转换成一个以原表为基础的物化视图;表结构不完全一致也没关系,只要能映射正确即可
4、 更新重定义过程中的DML
EXEC DBMS_REDEFINITION.SYNC_INTERIM_TABLE(‘EDW’,’CTL_LOG’,’CTL_LOG_TMP’);
–可以选择执行一次或多次,同步中间表与原表的数据(为防止在表的重定义过程中,对源表进行的DML操作),缩短执行DBMS_REDEFINITION.FINISH_REDEF_TABLE时的锁表时间
5、 完成重定义
EXEC DBMS_REDEFINITION.FINISH_REDEF_TABLE(‘EDW’,’CTL_LOG’,’CTL_LOG_TMP’);
–将原表变成与手工新建的中间临时表表结构相同,并将中间临时表从物化视图再转换成表(与当初的原表结构相同)
6、 删除中间表
DROP TABLE CTL_LOG_TMP;
7、 异常处理
EXEC DBMS_REDEFINITION.ABORT_REDEF_TABLE(‘EDW’,’CTL_LOG’,’CTL_LOG_TMP’);
–如果在线重定义失败,那么就必须必须调用DBMS_REDEFINITION.ABORT_REDEF_TABLE来释放快照。

DBMS_REDEFINITION包所需的特权

需授予 EXECUTE_CATALOG_ROLE 角色
CREATE TABLE
CREATE MATERIALIZED VIEW
CREATE TRIGGER
CREATE ANY TABLE
ALTER ANY TABLE
DROP ANY TABLE
LOCK ANY TABLE
SELECT ANY TABLE
CREATE ANY TRIGGER
CREATE ANY INDEX

实战案例

经常有由于业务初期规划不利,这业务和数据不断增长的同时,没有对数据库进行相应的优化,会出现单表数据量巨大,严重影响数据库性能和业务系统,这时候就需要想办法将单表处理成表分区以分散数据压力。
本文利用在线重定义,将单表重定义成分区表,新分区表使用HASH分区。
原表 T_ASINGLE 数据量5.8亿条 SGLCHECKID 和CHECKITEMID 是联合主键

列名        数据类型
ORDERNO VARCHAR2(20),
SGLCHECKID CHAR(18) not null,
CHECKITEMID CHAR(4) not null,
ITEMRESULT VARCHAR2(1000),
RESULTTYPE CHAR(1),
UNIT VARCHAR2(10),
LOWCRITICAL VARCHAR2(10),
HIGHCRITICAL VARCHAR2(10),
ADAPTERLOWCRITICAL VARCHAR2(10),
ADAPTERHIGHCRITICAL VARCHAR2(10),
CRITICALNAME VARCHAR2(20),
CHECKDOCTOR CHAR(18),
CREATEDATE DATE,
UPDATEDATE DATE default SYSDATE,
ISABANDON CHAR(1),
REGISTDEPT VARCHAR2(13),
COMPELETEDEPT VARCHAR2(20),
OPERATOR CHAR(18),
OPERATEDATE DATE default SYSDATE not null,
OPERATEDEPT VARCHAR2(13),
UPFLAG CHAR(1) default 0 not null,
CHECKUNITID CHAR(2),
RSLTFLAG VARCHAR2(5),
ISALWAYSTIP CHAR(1),
MYROWID VARCHAR2(50) default SYS_GUID() not null,
FSENDDIRECT VARCHAR2(13),
FNOTE VARCHAR2(200),
VALIDDOCTOR CHAR(18),
VALIDDATE DATE,
MACHINECODE VARCHAR2(32),
REGISTDATE DATE,
REGISTDATE2 DATE,
CHECKSTATUS2 CHAR(4)

表占空间240G
按照Oracle建议表大于2G,就应该做表分区,所以这里建立了2的幂次方 128个表空间用于存储128个表分区。

1、创建表空间
数据库采用的OMF管理数据文件,所以不用苦思每个数据文件的名称了,Oracle替你想了。每个数据文件初始100M 自动扩展10M 最大 30000M

CREATE TABLESPACE TS_basepart_dw00001 DATAFILE size 100M autoextend on next 10M maxsize 30000M EXTENT MANAGEMENT LOCAL SEGMENT SPACE MANAGEMENT AUTO;
CREATE TABLESPACE TS_basepart_dw00002 DATAFILE size 100M autoextend on next 10M maxsize 30000M EXTENT MANAGEMENT LOCAL SEGMENT SPACE MANAGEMENT AUTO;
CREATE TABLESPACE TS_basepart_dw00003 DATAFILE size 100M autoextend on next 10M maxsize 30000M EXTENT MANAGEMENT LOCAL SEGMENT SPACE MANAGEMENT AUTO;
CREATE TABLESPACE TS_basepart_dw00004 DATAFILE size 100M autoextend on next 10M maxsize 30000M EXTENT MANAGEMENT LOCAL SEGMENT SPACE MANAGEMENT AUTO;
CREATE TABLESPACE TS_basepart_dw00005 DATAFILE size 100M autoextend on next 10M maxsize 30000M EXTENT MANAGEMENT LOCAL SEGMENT SPACE MANAGEMENT AUTO;
CREATE TABLESPACE TS_basepart_dw00006 DATAFILE size 100M autoextend on next 10M maxsize 30000M EXTENT MANAGEMENT LOCAL SEGMENT SPACE MANAGEMENT AUTO;
CREATE TABLESPACE TS_basepart_dw00007 DATAFILE size 100M autoextend on next 10M maxsize 30000M EXTENT MANAGEMENT LOCAL SEGMENT SPACE MANAGEMENT AUTO;
CREATE TABLESPACE TS_basepart_dw00008 DATAFILE size 100M autoextend on next 10M maxsize 30000M EXTENT MANAGEMENT LOCAL SEGMENT SPACE MANAGEMENT AUTO;
CREATE TABLESPACE TS_basepart_dw00009 DATAFILE size 100M autoextend on next 10M maxsize 30000M EXTENT MANAGEMENT LOCAL SEGMENT SPACE MANAGEMENT AUTO;
CREATE TABLESPACE TS_basepart_dw00010 DATAFILE size 100M autoextend on next 10M maxsize 30000M EXTENT MANAGEMENT LOCAL SEGMENT SPACE MANAGEMENT AUTO;
----------------省略---------------分区是从TS_basepart_dw00001 到 TS_basepart_dw00128
CREATE TABLESPACE TS_basepart_dw00120 DATAFILE size 100M autoextend on next 10M maxsize 30000M EXTENT MANAGEMENT LOCAL SEGMENT SPACE MANAGEMENT AUTO;
CREATE TABLESPACE TS_basepart_dw00121 DATAFILE size 100M autoextend on next 10M maxsize 30000M EXTENT MANAGEMENT LOCAL SEGMENT SPACE MANAGEMENT AUTO;
CREATE TABLESPACE TS_basepart_dw00122 DATAFILE size 100M autoextend on next 10M maxsize 30000M EXTENT MANAGEMENT LOCAL SEGMENT SPACE MANAGEMENT AUTO;
CREATE TABLESPACE TS_basepart_dw00123 DATAFILE size 100M autoextend on next 10M maxsize 30000M EXTENT MANAGEMENT LOCAL SEGMENT SPACE MANAGEMENT AUTO;
CREATE TABLESPACE TS_basepart_dw00124 DATAFILE size 100M autoextend on next 10M maxsize 30000M EXTENT MANAGEMENT LOCAL SEGMENT SPACE MANAGEMENT AUTO;
CREATE TABLESPACE TS_basepart_dw00125 DATAFILE size 100M autoextend on next 10M maxsize 30000M EXTENT MANAGEMENT LOCAL SEGMENT SPACE MANAGEMENT AUTO;
CREATE TABLESPACE TS_basepart_dw00126 DATAFILE size 100M autoextend on next 10M maxsize 30000M EXTENT MANAGEMENT LOCAL SEGMENT SPACE MANAGEMENT AUTO;
CREATE TABLESPACE TS_basepart_dw00127 DATAFILE size 100M autoextend on next 10M maxsize 30000M EXTENT MANAGEMENT LOCAL SEGMENT SPACE MANAGEMENT AUTO;
CREATE TABLESPACE TS_basepart_dw00128 DATAFILE size 100M autoextend on next 10M maxsize 30000M EXTENT MANAGEMENT LOCAL SEGMENT SPACE MANAGEMENT AUTO;

2、创建中间表(临时表)

-- Create table
create table T_ASINGLE_TEMP
ORDERNO VARCHAR2(20),
SGLCHECKID CHAR(18) not null,
CHECKITEMID CHAR(4) not null,
ITEMRESULT VARCHAR2(1000),
RESULTTYPE CHAR(1),
UNIT VARCHAR2(10),
LOWCRITICAL VARCHAR2(10),
HIGHCRITICAL VARCHAR2(10),
ADAPTERLOWCRITICAL VARCHAR2(10),
ADAPTERHIGHCRITICAL VARCHAR2(10),
CRITICALNAME VARCHAR2(20),
CHECKDOCTOR CHAR(18),
CREATEDATE DATE,
UPDATEDATE DATE default SYSDATE,
ISABANDON CHAR(1),
REGISTDEPT VARCHAR2(13),
COMPELETEDEPT VARCHAR2(20),
OPERATOR CHAR(18),
OPERATEDATE DATE default SYSDATE not null,
OPERATEDEPT VARCHAR2(13),
UPFLAG CHAR(1) default 0 not null,
CHECKUNITID CHAR(2),
RSLTFLAG VARCHAR2(5),
ISALWAYSTIP CHAR(1),
MYROWID VARCHAR2(50) default SYS_GUID() not null,
FSENDDIRECT VARCHAR2(13),
FNOTE VARCHAR2(200),
VALIDDOCTOR CHAR(18),
VALIDDATE DATE,
MACHINECODE VARCHAR2(32),
REGISTDATE DATE,
REGISTDATE2 DATE,
CHECKSTATUS2 CHAR(4)
)
partition by hash (SGLCHECKID)
(
partition TRESULTPART00001
tablespace TS_BASEPART_DW00001,
partition TRESULTPART00002
tablespace TS_BASEPART_DW00002,
partition TRESULTPART00003
tablespace TS_BASEPART_DW00003,
partition TRESULTPART00004
tablespace TS_BASEPART_DW00004,
partition TRESULTPART00005
tablespace TS_BASEPART_DW00005,
partition TRESULTPART00006
tablespace TS_BASEPART_DW00006,
partition TRESULTPART00007
tablespace TS_BASEPART_DW00007,
partition TRESULTPART00008
tablespace TS_BASEPART_DW00008,
partition TRESULTPART00009
tablespace TS_BASEPART_DW00009,
partition TRESULTPART00010
tablespace TS_BASEPART_DW00010,
----省略----分区名称是从TRESULTPART00001 到 TRESULTPART00128
partition TRESULTPART00120
tablespace TS_BASEPART_DW00120,
partition TRESULTPART00121
tablespace TS_BASEPART_DW00121,
partition TRESULTPART00122
tablespace TS_BASEPART_DW00122,
partition TRESULTPART00123
tablespace TS_BASEPART_DW00123,
partition TRESULTPART00124
tablespace TS_BASEPART_DW00124,
partition TRESULTPART00125
tablespace TS_BASEPART_DW00125,
partition TRESULTPART00126
tablespace TS_BASEPART_DW00126,
partition TRESULTPART00127
tablespace TS_BASEPART_DW00127,
partition TRESULTPART00128
tablespace TS_BASEPART_DW00128
);

3、按照Oracle文档介绍的,授予dbuser相关权限。

需授予 EXECUTE_CATALOG_ROLE 角色

权限
CREATE TABLE
CREATE MATERIALIZED VIEW
CREATE TRIGGER
CREATE ANY TABLE
ALTER ANY TABLE
DROP ANY TABLE
LOCK ANY TABLE
SELECT ANY TABLE
CREATE ANY TRIGGER
CREATE ANY INDEX

4、使用CAN_REDEF_TABLE验证原表是否可以做在线重定义
登录到dbuser

SQL>  EXEC DBMS_REDEFINITION.CAN_REDEF_TABLE('dbuser','T_ASINGLE');
PL/SQL procedure successfully completed.

5、开始在线重定义表,由于数据量较大,这个过程比较漫长,用时2个半小时

SQL>   EXEC DBMS_REDEFINITION.START_REDEF_TABLE('dbuser', 'T_ASINGLE', 'T_ASINGLE_TEMP');

我们注意到Oracle新建了两张表RUPD$_DEMO和MLOG$_DEMO,其实Oracle在线重定义是通过物化视图的LOG来实现的。做完这一步后,在中间表中也有了和原表相同的数据

SQL>   select object_id,object_name,object_type,status from user_objects;
OBJECT_ID OBJECT_NAME OBJECT_TYPE STATUS
---------- --------------- ------------------- -------
77134 RUPD$_DEMO TABLE VALID
77133 MLOG$_DEMO TABLE VALID

6、使用COPY_TABLE_DEPENDENTS把原始表的权限、约束、索引、物化视图LOG在中间表上创建一份

SQL>   set serveroutput on
SQL>   var v_err number
SQL>   exec DBMS_REDEFINITION.COPY_TABLE_DEPENDENTS('dbuser', 'T_ASINGLE', 'T_ASINGLE_TEMP', NUM_ERRORS => :V_ERR);
PL/SQL procedure successfully completed.

有时有可能会报这样的错误,不过是无关紧要的

SQL>   exec DBMS_REDEFINITION.COPY_TABLE_DEPENDENTS('dbuser','T_ASINGLE','T_ASINGLE_TEMP',NUM_ERRORS => :V_ERR);

begin DBMS_REDEFINITION.COPY_TABLE_DEPENDENTS('dbuser','T_ASINGLE','T_ASINGLE_TEMP',NUM_ERRORS =>:V_ERR); end;

ORA-01442: column to be modified to NOT NULL is already NOT NULL
ORA-06512: at "SYS.DBMS_REDEFINITION", line 984
ORA-06512: at "SYS.DBMS_REDEFINITION", line 1726
ORA-06512: at line 2
v_err
---------

如果在线重定义的时间比较长,而在这个过程中有其他的DML语句操作在原始表上,Oracle通过SYNC_INTERIM_TABLE来做同步,Oracle建议如果DML操作较多,可以多次执行SYNC_INTERIM_TABLE来做分批次数据同步,以免最后FINISH时,等待时间过长。

SQL>   desc MLOG$_T_ASINGLE;
Name Type Nullable Default Comments
--------------- ----------- -------- ------- --------
SGLCHECKID CHAR(18) Y
CHECKITEMID CHAR(4) Y
SNAPTIME$$ DATE Y
DMLTYPE$$ VARCHAR2(1) Y
OLD_NEW$$ VARCHAR2(1) Y
CHANGE_VECTOR$$ RAW(255) Y

SQL>   select * from MLOG$_T_ASINGLE;

SGLCHECKID CHECKITEMID SNAPTIME$$ DMLTYPE$$ OLD_NEW$$ CHANGE_VECTOR$$
------------------ ----------- ----------- --------- --------- --------------------------------------------------------------------------------
123213213 0002 4000-1-1 I N FEFFFFFFFF

7、执行同步

SQL>  EXEC DBMS_REDEFINITION.SYNC_INTERIM_TABLE('dbuser', 'T_ASINGLE', 'T_ASINGLE_TEMP');
PL/SQL procedure successfully completed

8、完成在线重定义,在这一步中,要对原始表DEMO以独占的方式锁定。

SQL>  EXEC DBMS_REDEFINITION.FINISH_REDEF_TABLE('dbuser', 'T_ASINGLE', 'T_ASINGLE_TEMP');
PL/SQL procedure successfully completed.

9、查看重定义分区是否成功

SQL>  select object_id,object_name,object_type,status from user_objects where object_name='T_ASINGLE';

OBJECT_ID OBJECT_NAME OBJECT_TYPE STATUS
---------- -------------------------------------------------------------------------------- ------------------- -------
152458 T_ASINGLE TABLE VALID
152459 T_ASINGLE TABLE PARTITION VALID
152460 T_ASINGLE TABLE PARTITION VALID
152461 T_ASINGLE TABLE PARTITION VALID
152462 T_ASINGLE TABLE PARTITION VALID
152463 T_ASINGLE TABLE PARTITION VALID
152464 T_ASINGLE TABLE PARTITION VALID
152465 T_ASINGLE TABLE PARTITION VALID
152466 T_ASINGLE TABLE PARTITION VALID
152467 T_ASINGLE TABLE PARTITION VALID
152468 T_ASINGLE TABLE PARTITION VALID
152469 T_ASINGLE TABLE PARTITION VALID
152470 T_ASINGLE TABLE PARTITION VALID
152471 T_ASINGLE TABLE PARTITION VALID
152472 T_ASINGLE TABLE PARTITION VALID
152473 T_ASINGLE TABLE PARTITION VALID
152474 T_ASINGLE TABLE PARTITION VALID
152475 T_ASINGLE TABLE PARTITION VALID
152476 T_ASINGLE TABLE PARTITION VALID
152477 T_ASINGLE TABLE PARTITION VALID
........省略

10、查看索引状态也是正常有效

SQL>   select table_name,index_name,status from user_indexes where table_name='T_ASINGLE';
 
TABLE_NAME                     INDEX_NAME                     STATUS
------------------------------ ------------------------------ --------
T_ASINGLE                         PK_T_ASINGLE          VALID

至此完成将单表在线重定义为分区表。

知识扩展

Oracle的普通表没有办法通过修改属性的方式直接转化为分区表,必须通过重建的方式进行转变,其实有四种方法可以实现,下面着重介绍另外两种效率比较高的方法,并说明它们各自的特点。
1、EXP/IMP或EXPDP/IMPDP
2、利用原表重建分区表(CTAS方法)
3、交换分区
4、在线重定义

方法二:利用原表重建分区表

步骤:
SQL> CREATE TABLE T (ID NUMBER PRIMARY KEY, TIME DATE);
表已创建。
SQL> INSERT INTO T SELECT ROWNUM,CREATED FROM DBA_OBJECTS;
已创建6264行。
SQL> COMMIT;
提交完成。
SQL> CREATE TABLE T_NEW (ID, TIME) PARTITION BY RANGE (TIME)
2 (PARTITION P1 VALUES LESS THAN (TO_DATE(‘2004-7-1’, ‘YYYY-MM-DD’)),
3 PARTITION P2 VALUES LESS THAN (TO_DATE(‘2005-1-1’, ‘YYYY-MM-DD’)),
4 PARTITION P3 VALUES LESS THAN (TO_DATE(‘2005-7-1’, ‘YYYY-MM-DD’)),
5 PARTITION P4 VALUES LESS THAN (MAXVALUE))
6 AS SELECT ID, TIME FROM T;
表已创建。
SQL> RENAME T TO T_OLD;
表已重命名。
SQL> RENAME T_NEW TO T;
表已重命名。

SQL> SELECT COUNT(*) FROM T;
COUNT(*)
———-
6264
SQL> SELECT COUNT(*) FROM T PARTITION (P1);
COUNT(*)

———-
0
SQL> SELECT COUNT(*) FROM T PARTITION (P2);
COUNT(*)
———-
6246
SQL> SELECT COUNT(*) FROM T PARTITION (P3);

COUNT(*)

———-

18
优点:
方法简单易用,由于采用DDL语句,不会产生UNDO,且只产生少量REDO,效率相对较高,而且建表完成后数据已经在分布到各个分区中了。
不足:
对于数据的一致性方面还需要额外的考虑。由于几乎没有办法通过手工锁定T表的方式保证一致性,在执行CREATE TABLE语句和RENAME T_NEW TO T语句直接的修改可能会丢失,如果要保证一致性,需要在执行完语句后对数据进行检查,而这个代价是比较大的。另外在执行两个RENAME语句之间执行的对T的访问会失败。
适用于修改不频繁的表,在闲时进行操作,表的数据量不宜太大。

方法三:使用交换分区的方法

步骤:

SQL> CREATE TABLE T (ID NUMBER PRIMARY KEY, TIME DATE);
表已创建。
SQL> INSERT INTO T SELECT ROWNUM, CREATED FROM DBA_OBJECTS;
已创建6264行。
SQL> COMMIT;
提交完成。
SQL> CREATE TABLE T_NEW (ID NUMBER PRIMARY KEY, TIME DATE) PARTITION BY RANGE (TIME)
2 (PARTITION P1 VALUES LESS THAN (TO_DATE(‘2005-7-1’, ‘YYYY-MM-DD’)),
3 PARTITION P2 VALUES LESS THAN (MAXVALUE));
表已创建。
SQL> ALTER TABLE T_NEW EXCHANGE PARTITION P1 WITH TABLE T;
表已更改。
SQL> RENAME T TO T_OLD;
表已重命名。
SQL> RENAME T_NEW TO T;
表已重命名。
SQL> SELECT COUNT(*) FROM T;
COUNT(*)

———-

6264

优点:

只是对数据字典中分区和表的定义进行了修改,没有数据的修改或复制,效率最高。如果对数据在分区中的分布没有进一步要求的话,实现比较简单。在执行完RENAME操作后,可以检查T_OLD中是否存在数据,如果存在的话,直接将这些数据插入到T中,可以保证对T插入的操作不会丢失。
不足:
仍然存在一致性问题,交换分区之后RENAME T_NEW TO T之前,查询、更新和删除会出现错误或访问不到数据。如果要求数据分布到多个分区中,则需要进行分区的SPLIT操作,会增加操作的复杂度,效率也会降低。
适用于包含大数据量的表转到分区表中的一个分区的操作。应尽量在闲时进行操作。

发表评论