oracle重新定义表,oracle 在线重定义表

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

修改表的存储参数；

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

增加并行查询选项；

增加或删除分区；

重建表以减少碎片；

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

增加或删除一个列。

原理：增量物化视图刷新

目的：清理表中碎片，释放空间(重建表以减少碎片)

重定义方法：

存在两种重定义方法，一种是基于主键、另一种是基于ROWID。

ROWID的方式不能用于索引组织表，而且重定义后会存在隐藏列M_ROW$$。

默认采用主键的方式。

前提条件：

1)查看表中是否含有主键，在线重定义需要表中存在主键

select a.constraint_name,  a.column_name from dba_cons_columns a, dba_constraints b

where a.constraint_name = b.constraint_name  and b.constraint_type = 'P' and a.table_name in('ZWD_JDMSG_GD','ZB2C_KUCUN_LOG');

2)如果在执行在线重定义的过程中出现任何的失败操作，那么需要执行dbms_redefinition.abort_redef_table来回退；

1、检查是否能够重定义

EXEC DBMS_REDEFINITION.CAN_REDEF_TABLE('用户名', '源表', DBMS_REDEFINITION.CONS_USE_PK);   --主键方式

EXEC DBMS_REDEFINITION.CAN_REDEF_TABLE('用户名', '源表', DBMS_REDEFINITION.CONS_USE_ROWID);   --ROWID方式

2、获取源表建表语句

SET SERVEROUTPUT ON

SET LINESIZE 1000

SET FEEDBACK OFF

set long 99999

set pagesize 4000

select dbms_metadata.get_ddl('TABLE','ZWD_JDMSG_GD','SAPSR3') from dual;

3、创建中间表(根据源表创建语句)

4、查看依赖对象

SELECT * from dba_dependencies where REFERENCED_NAME in ('ZWD_JDMSG_GD','ZB2C_KUCUN_LOG');

5、调用dbms_redefinition包执行表的在线重定义。

EXEC DBMS_REDEFINITION.start_redef_table('SAPSR3','ZWD_JDMSG_GD','T_TEMP');

或者

--null代表所有的列名与原表一样

begin

dbms_redefinition.start_redef_table(‘用户’,‘原表’,‘中间表’,null,dbms_redefinition.cons_use_rowid);

end;

6、复制依赖对象到中间表，后几个参数分别表示从原表复制索引、触发器、约束、权限、某一项复制出错跳过、复制过程中的出错数目、复制统计信息、复制物化视图日志

(各选项凭需要取用，完毕后需检查num_errors的值，有错一定要查看，酌情处理)

DECLARE

num_errors PLS_INTEGER;

BEGIN

DBMS_REDEFINITION.COPY_TABLE_DEPENDENTS

(‘用户’, ‘原表’,‘临时表’, DBMS_REDEFINITION.CONS_ORIG_PARAMS, TRUE, TRUE, TRUE, TRUE, num_errors, TRUE, TRUE);

END;

7、查看报错信息

select object_name,base_table_name,ddl_txt from DBA_REDEFINITION_ERRORS;

8、 将中间表中的数据和原始表中的数据进行同步操作(这一步不是必须的)。

如果在执行dbms_redefinition.start_redef_table和dbms_redefinition.finish_redef_table之间有大量的DML操作发生，

那么执行一次dbms_redefinition.sync_interim_table来减少最后一步执行dbms_redefinition.finish_redef_table时的锁定时间。

EXEC dbms_redefinition.sync_interim_table('DBSYAN','T_TEST','T_TEMP');

9、结束在线重定义过程

EXEC dbms_redefinition.finish_redef_table('DBSYAN','T_TEST','T_TEMP');

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

10、检查原表的表空间是否改变，检查其数据、结构、索引等依赖对象是否正确

11、执行失败，进行回退

EXEC dbms_redefinition.abort_redef_table ('DBSYAN','T_TEST','T_TEMP')；

注意：

对于采用了ROWID方式重定义的表，包括了一个隐含列M_ROW$$。推荐使用下列语句经隐含列置为UNUSED状态或删除

ALTER TABLE TABLE_NAME SET UNUSED (M_ROW$$);

ALTER TABLE TABLE_NAME DROP UNUSED COLUMNS;