在线重定义表的测试和问题 <转>
在一个高可用系统中，如果需要改变一个表的定义是一件比较棘手的问题，尤其是对于7×24 系统。Oracle提供的基本语法基本可以满足一般性修改，但是对于把普通堆表改为分区表，把索引组织表修改为堆表等操作就无法完成了。而且，对于被大量 DML语句访问的表，幸运的是，Oracle从9i版本开始提供了在线重定义表功能，通过调用DBMS_REDEFINITION包，可以在修改表结构的 同时允许DML操作。
1、在线重定义表具有以下功能：
（1）修改表的存储参数；
（2）可以将表转移到其他表空间；
（3）增加并行查询选项；
（4）增加或删除分区；
（5）重建表以减少碎片；
（6）将堆表改为索引组织表或相反的操作；
（7）增加或删除一个列。
2、权限问题：
调用DBMS_REDEFINITION 包需要EXECUTE_CATALOG_ROLE角色，
除此之外，还需要
CREATE ANY TABLE、
ALTER ANY TABLE、
DROP ANY TABLE、
LOCK ANY TABLE和
SELECT ANY TABLE的权限。
3、在线重定义表的步骤如下：
（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()过程完成表的重定义。这个过程中，原始表会被独占模式锁定一小段时间，具体时间和表的数据量有关。执行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;
4、下面是进行重定义操作后的结果：
（1）原始表根据中间表的属性和特性进行重定义；
（2）START_REDEF_TABLE()和FINISH_REDEF_TABLE()操作之间在中间表上建立的触发器、索引、约束和授权，现在定义在原始表上。中间表上disabled的约束在原始表上处于enabled状态。
（3）原始表上定义的触发器、索引、约束和授权建立在中间表上，并会在删除中间表时删除。原始表上原来enabled状态的索引，建立在中间表上，并处于disabled状态。
（4）任何定义在原始表上的存储过程和游标都会变为INVALID，当下次调用时后自动进行编译。
（5）如果执行过程中出现错误或者人为选择退出的话，可以执行DBMS_REDEFINITION.ABORT_REDEF_TABLE()过程。
#######################################################################################
例子1：
做一个最简单的在线重定义表的测试：
alter session force parallel dml parallel 4;
alter session force parallel query parallel 4;
1、测试可否对表做在线重定义
BEGIN
DBMS_REDEFINITION.CAN_REDEF_TABLE(’testuser’,’MESSAGE_BAK’,
DBMS_REDEFINITION.CONS_USE_PK);
END;
/
2、创建中间表
CREATE TABLE TEMP_MESSAGE_BAK
( MESSAGE_ID NUMBER ,
MESSAGE_TITLE VARCHAR2(300) ,
MESSAGE_LINE NUMBER ,
MESSAGE_BODY VARCHAR2(4000),
CREATE_TIME DATE,
MESSAGE_TYPE_CODE NUMBER,
FORMAT_TYPE_CODE NUMBER(5,0),
MESSAGE_FILE_NAME VARCHAR2(500),
DIRECTION_TYPE_CODE NUMBER,
MESSAGE_GROUP_ID NUMBER,
PART_INDEX VARCHAR2(20) );
3、开始
begin
dbms_redefinition.start_redef_table(
uname => ‘testuser’,
orig_table => ‘MESSAGE_BAK’,
int_table => ‘TEMP_MESSAGE_BAK’,
OPTIONS_FLAG => DBMS_REDEFINITION.cons_use_pk);
end;
/
这里没写columns的mapping关系。
4、创建索引限制以及触发器等.
注:在10g 中,如果这些定义变化了.可以通过 DBMS_REDEFINITION.REGISTER_DEPENDENT_OBJECT() 来创建.
如果这些对象定义不变化,则调用 DBMS_REDEFINITION.COPY_TABLE_DEPENDENTS() 即可.
DECLARE
num_errors PLS_INTEGER;
BEGIN
DBMS_REDEFINITION.COPY_TABLE_DEPENDENTS(
‘testuser’,’MESSAGE_BAK’,’TEMP_MESSAGE_BAK’,DBMS_REDEFINITION.CONS_ORIG_PARAMS,
TRUE, TRUE, TRUE, FALSE, num_errors, TRUE);
END;
/
5、同步。如果需要同步的数据特别大,则可能考虑通过 prebuilt table 的方法先建立物化视图.
EXEC DBMS_REDEFINITION.sync_interim_table (’testuser’, ‘MESSAGE_BAK’, ‘TEMP_MESSAGE_BAK’);
6、结束操作
BEGIN
DBMS_REDEFINITION.FINISH_REDEF_TABLE(’testuser’, ‘MESSAGE_BAK’, ‘TEMP_MESSAGE_BAK’);
END;
/
7、如果想中断操作，可以执行：
EXEC dbms_redefinition.abort_redef_table(’testuser’, ‘MESSAGE_BAK’,’TEMP_MESSAGE_BAK’);
8、查询 DBA_REDEFINITION_ERRORS view to check for errors.
SQL> select object_name, base_table_name, ddl_txt from
DBA_REDEFINITION_ERRORS;
OBJECT_NAME BASE_TABLE_NAME DDL_TXT
————- —————- ——————————
SYS_C005836 ADMIN_EMP CREATE UNIQUE INDEX “HR”.”TMP$
$_SYS_C0058360″ ON “HR”.”INT_A
DMIN_EMP” (”EMPNO”)
SYS_C005836 ADMIN_EMP ALTER TABLE “HR”.”INT_ADMIN_EM
P” ADD CONSTRAINT “TMP$$_SYS_C
0058360″ PRIMARY KEY
例子2：
#############################################################################
做一个最简单的在线重定义表的测试：
alter session force parallel dml parallel 4;
alter session force parallel query parallel 4;
1、测试可否对表做在线重定义
BEGIN
DBMS_REDEFINITION.CAN_REDEF_TABLE(’HELIOS’,’MESSAGE_BAK’,
DBMS_REDEFINITION.CONS_USE_PK);
END;
/
2、创建中间表
CREATE TABLE TEMP_MESSAGE_BAK
( MESSAGE_ID NUMBER ,
MESSAGE_TITLE VARCHAR2(300) ,
MESSAGE_LINE NUMBER ,
MESSAGE_BODY VARCHAR2(4000),
CREATE_TIME DATE,
MESSAGE_TYPE_CODE NUMBER,
FORMAT_TYPE_CODE NUMBER(5,0),
MESSAGE_FILE_NAME VARCHAR2(500),
DIRECTION_TYPE_CODE NUMBER,
MESSAGE_GROUP_ID NUMBER,
PART_INDEX VARCHAR2(20) );
3、开始
begin
dbms_redefinition.start_redef_table(
uname => ‘HELIOS’,
orig_table => ‘MESSAGE_BAK’,
int_table => ‘TEMP_MESSAGE_BAK’,
OPTIONS_FLAG => DBMS_REDEFINITION.cons_use_pk);
end;
/
这里没写columns的mapping关系。
4、创建索引限制以及触发器等.
注:在10g 中,如果这些定义变化了.可以通过 DBMS_REDEFINITION.REGISTER_DEPENDENT_OBJECT() 来创建.
如果这些对象定义不变化,则调用 DBMS_REDEFINITION.COPY_TABLE_DEPENDENTS() 即可.
DECLARE
num_errors PLS_INTEGER;
BEGIN
DBMS_REDEFINITION.COPY_TABLE_DEPENDENTS(
‘HELIOS’,’MESSAGE_BAK’,’TEMP_MESSAGE_BAK’,DBMS_REDEFINITION.CONS_ORIG_PARAMS,
TRUE, TRUE, TRUE, FALSE, num_errors, TRUE);
END;
/
5、同步。如果需要同步的数据特别大,则可能考虑通过 prebuilt table 的方法先建立物化视图.
EXEC DBMS_REDEFINITION.sync_interim_table (’HELIOS’, ‘MESSAGE_BAK’, ‘TEMP_MESSAGE_BAK’);
6、结束操作
BEGIN
DBMS_REDEFINITION.FINISH_REDEF_TABLE(’HELIOS’, ‘MESSAGE_BAK’, ‘TEMP_MESSAGE_BAK’);
END;
/
7、如果想中断操作，可以执行：
EXEC dbms_redefinition.abort_redef_table(’HELIOS’, ‘MESSAGE_BAK’,’TEMP_MESSAGE_BAK’);
8、查询 DBA_REDEFINITION_ERRORS view to check for errors.
SQL> select object_name, base_table_name, ddl_txt from
DBA_REDEFINITION_ERRORS;
OBJECT_NAME BASE_TABLE_NAME DDL_TXT
————- —————- ——————————
SYS_C005836 ADMIN_EMP CREATE UNIQUE INDEX “HR”.”TMP$
$_SYS_C0058360″ ON “HR”.”INT_A
DMIN_EMP” (”EMPNO”)
SYS_C005836 ADMIN_EMP ALTER TABLE “HR”.”INT_ADMIN_EM
P” ADD CONSTRAINT “TMP$$_SYS_C
0058360″ PRIMARY KEY
########################################################################################################
将表重定义为分区表：
1、创建中间表为分区表
CREATE TABLE TEMP_MESSAGE_BAK_part
( MESSAGE_ID NUMBER ,
MESSAGE_TITLE VARCHAR2(300) ,
MESSAGE_LINE NUMBER ,
MESSAGE_BODY VARCHAR2(4000),
CREATE_TIME DATE,
MESSAGE_TYPE_CODE NUMBER,
FORMAT_TYPE_CODE NUMBER(5,0),
MESSAGE_FILE_NAME VARCHAR2(500),
DIRECTION_TYPE_CODE NUMBER,
MESSAGE_GROUP_ID NUMBER,
PART_INDEX VARCHAR2(20) )
PARTITION BY RANGE(CREATE_TIME)
(PARTITION ctime_y05 VALUES LESS THAN (TO_DATE(’2006-01-01′, ‘YYYY-MM-DD’)),
PARTITION ctime_y06 VALUES LESS THAN (TO_DATE(’2007-01-01′, ‘YYYY-MM-DD’)),
PARTITION ctime_y07 VALUES LESS THAN (TO_DATE(’2008-01-01′, ‘YYYY-MM-DD’)),
PARTITION ctime_y08 VALUES LESS THAN (TO_DATE(’2009-01-01′, ‘YYYY-MM-DD’)),
PARTITION ctime_y09 VALUES LESS THAN (TO_DATE(’2010-01-01′, ‘YYYY-MM-DD’)));
2、开始
begin
dbms_redefinition.start_redef_table(
uname => ‘HELIOS’,
orig_table => ‘MESSAGE_BAK’,
int_table => ‘TEMP_MESSAGE_BAK_PART’,
OPTIONS_FLAG => DBMS_REDEFINITION.cons_use_pk);
end;
/
3、处理约束、index等
DECLARE
num_errors PLS_INTEGER;
BEGIN
DBMS_REDEFINITION.COPY_TABLE_DEPENDENTS(
‘HELIOS’,’MESSAGE_BAK’,’TEMP_MESSAGE_BAK_PART’,DBMS_REDEFINITION.CONS_ORIG_PARAMS,
TRUE, TRUE, TRUE, FALSE, num_errors, TRUE);
END;
/
4、同步
EXEC DBMS_REDEFINITION.sync_interim_table (’HELIOS’, ‘MESSAGE_BAK’, ‘TEMP_MESSAGE_BAK_PART’);
顺便执行
SQL> select * from dba_redefinition_objects;
OBJECT_TYPE OBJECT_OWNER OBJECT_NAME BASE_TABLE_OWNER
———— —————————— —————————— ——————————
BASE_TABLE_NAME INTERIM_OBJECT_OWNER INTERIM_OBJECT_NAME
—————————— —————————— ——————————
CONSTRAINT HELIOS SYS_C006309 HELIOS
MESSAGE_BAK HELIOS TMP$$_SYS_C0063091
CONSTRAINT HELIOS SYS_C006311 HELIOS
MESSAGE_BAK HELIOS TMP$$_SYS_C0063111
CONSTRAINT HELIOS SYS_C006310 HELIOS
MESSAGE_BAK HELIOS TMP$$_SYS_C0063101
CONSTRAINT HELIOS PK_MESSAGE_BAK HELIOS
MESSAGE_BAK HELIOS TMP$$_PK_MESSAGE_BAK1
TABLE HELIOS MESSAGE_BAK HELIOS
MESSAGE_BAK HELIOS TEMP_MESSAGE_BAK_PART
INDEX HELIOS PK_MESSAGE_BAK HELIOS
MESSAGE_BAK HELIOS TMP$$_PK_MESSAGE_BAK1
6 rows selected.
5、结束
BEGIN
DBMS_REDEFINITION.FINISH_REDEF_TABLE(’HELIOS’, ‘MESSAGE_BAK’, ‘TEMP_MESSAGE_BAK_PART’);
END;
/
6、查看MESSAGE_BAK，已经变为分区表了。
ref: http://download.oracle.com/docs/cd/B19306_01/appdev.102/b14258/d_redefi.htm#CBBFDJBC
##############################################################################
如何处理表的在线重定义导常情况?
在线重定义一张表.将普通表做成分区表.
通过测试表S_CUST_VALUE是可以做成分区表的
dbms_redefinition.can_redef_table(’XXXX’,’S_CUST_VALUE’,DBMS_REDEFINITION.CONS_USE_PK);
重定义时:
dbms_redefinition.start_redef_table(’XXXX’,’S_CUST_VALUE’,’S_CUST_VALUE_BAK’);
报错:数据不能插入指定分区.
查检发现原来S_CUST_VALUE表中有几个数据不对,导入不能插入新的分区,在线重定义失败.调整数据之后,现次重定义联
dbms_redefinition.start_redef_table(’XXXX’,’S_CUST_VALUE’,’S_CUST_VALUE_BAK’);
报错:ora-12091:不能联机重新定义具有实体化视图的表.
[解决方法]
dorp materialized view log on TB_LOG;
另一个比较重要的问题：
ref http://yumianfeilong.com/html/2007/12/27/155.html
11g 新功能文档上有一段:
1.2.9.6 Minimize Dependent PL/SQL Recompilation After Online Table Redefinition
This feature minimizes the need to recompile dependent PL/SQL packages after an online table redefinition.
If the redefinition does not logically affect the PL/SQL packages, recompilation is not needed.
This optimization is on by default.This feature reduces the time and effort to manually recompile dependent PL/SQL after an online table redefinition.
This also includes views, synonyms, and other table dependent objects (with the exception of triggers)
that are not logically affected by the redefinition.
意思是说在finish_redef_table后，不会invalidate视图，同义词等其他依赖的对象。弱化了DDL的耦合性。
这确实是个改进。因为在10g中invalidated public syonym可能会导致finish_redef_table失败。
看在10g中的测试:
[coolcode lang=”sql” linenum=”no”]
alter session set nls_date_format=’yyyy-mm-dd hh24:mi:ss’;
set pagesize 100 linesize 150
col object_name format a30
SQL> select object_name,status,LAST_DDL_TIME,object_type from dba_objects where object_name=’BINZHANG’;
OBJECT_NAME STATUS LAST_DDL_TIME OBJECT_TYPE
—————————— ——- ——————- ——————-
BINZHANG VALID 2007-12-27 00:19:35 SYNONYM
BINZHANG VALID 2007-12-27 00:18:35 TABLE
SQL> exec dbms_redefinition.can_redef_table ( USER, ‘BINZHANG’, DBMS_REDEFINITION.CONS_USE_PK) ;
PL/SQL procedure successfully completed.
SQL> CREATE TABLE BINZHANG_TMP (
OWNER VARCHAR2(30),
OBJECT_NAME VARCHAR2(128),
SUBOBJECT_NAME VARCHAR2(30),
OBJECT_ID NUMBER,
DATA_OBJECT_ID NUMBER,
OBJECT_TYPE VARCHAR2(19),
CREATED DATE,
LAST_DDL_TIME DATE,
TIMESTAMP VARCHAR2(19),
STATUS VARCHAR2(7),
TEMPORARY VARCHAR2(1),
GENERATED VARCHAR2(1),
SECONDARY VARCHAR2(1));
Table created.
SQL> CREATE UNIQUE INDEX BINZHANGUNIDX_TMP ON BINZHANG_TMP (OBJECT_ID ) ;
Index created.
SQL> alter table BINZHANG_TMP add primary key(object_id);
Table altered.
SQL> exec dbms_redefinition.START_REDEF_TABLE ( USER, ‘BINZHANG’, ‘BINZHANG_TMP’, NULL, DBMS_REDEFINITION.CONS_USE_PK);
PL/SQL procedure successfully completed.
SQL> select count(*) from BINZHANG_TMP;
COUNT(*)
———-
13427
SQL> exec dbms_redefinition.finish_redef_table( USER, ‘BINZHANG’, ‘BINZHANG_TMP’);
PL/SQL procedure successfully completed.
SQL> select object_name,status,LAST_DDL_TIME,object_type from dba_objects where object_name=’BINZHANG’;
OBJECT_NAME STATUS LAST_DDL_TIME OBJECT_TYPE
—————————— ——- ——————- ——————-
BINZHANG INVALID 2007-12-27 00:19:35 SYNONYM
BINZHANG VALID 2007-12-27 00:27:01 TABLE
SQL> conn / as sysdba
Connected.
SQL> select count(*) from binzhang;
COUNT(*)
———-
13427
SQL> alter session set nls_date_format=’yyyy-mm-dd hh24:mi:ss’;
Session altered.
SQL> select object_name,status,LAST_DDL_TIME,object_type from dba_objects where object_name=’BINZHANG’;
OBJECT_NAME STATUS LAST_DDL_TIME OBJECT_TYPE
—————————— ——- ——————- ——————-
BINZHANG VALID 2007-12-27 00:28:16 SYNONYM
BINZHANG VALID 2007-12-27 00:27:01 TABLE
[/coolcode]
注意状态变为INVALID 的同义词。当查询很多的时候，finish_redef_table很可能会失败，遇见如下错误。
[coolcode lang=”sql” linenum=”no”]
SQL> exec dbms_redefinition.finish_redef_table( USER, ‘BINZHANG’, ‘BINZHANG_TMP’);
BEGIN dbms_redefinition.finish_redef_table( USER, ‘BINZHANG’, ‘BINZHANG_TMP’); END;
*
ERROR at line 1:
ORA-42012: error occurred while completing the redefinition
ORA-04020: deadlock detected while trying to lock object PUBLIC.BINZHANG
ORA-06512: at “SYS.DBMS_REDEFINITION”, line 76
ORA-06512: at “SYS.DBMS_REDEFINITION”, line 1376
ORA-06512: at line 1
[/coolcode]
在11g中，有了这个不invalidate views, synonyms, and other table dependent objects (with the exception of triggers)的改进。就能够保证finish_redef_table不会因为ORA-04020而失败。
[coolcode lang=”sql” linenum=”no”]
Connected to:
Oracle Database 11g Enterprise Edition Release 11.1.0.6.0 – Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SQL> create table binzhang as select * from dba_objects where object_id >0;
Table created.
SQL> alter table binzhang add primary key(object_id);
Table altered.
SQL> exec dbms_redefinition.can_redef_table ( USER, ‘BINZHANG’, DBMS_REDEFINITION.CONS_USE_PK) ;
PL/SQL procedure successfully completed.
SQL> create public synonym binzhang for binzhang.binzhang;
Synonym created.
alter session set nls_date_format=’yyyy-mm-dd hh24:mi:ss’;
set pagesize 100 linesize 150
col object_name format a30
Session altered.
SQL> select object_name,status,LAST_DDL_TIME,object_type from dba_objects where object_name=’BINZHANG’;
OBJECT_NAME STATUS LAST_DDL_TIME OBJECT_TYPE
—————————— ——————— ——————- ———————————————————
BINZHANG VALID 2007-12-26 23:42:34 SYNONYM
BINZHANG VALID 2007-12-26 23:42:04 TABLE
SQL> create table binzhang_tmp as select * from binzhang where rownum=0;
Table created.
SQL> alter table BINZHANG_TMP add primary key(object_id);
Table altered.
SQL> exec dbms_redefinition.START_REDEF_TABLE ( USER, ‘BINZHANG’, ‘BINZHANG_TMP’, NULL, DBMS_REDEFINITION.CONS_USE_PK);
PL/SQL procedure successfully completed.
SQL> select count(*) from BINZHANG_TMP;
COUNT(*)
———-
68967
SQL> select count(*) from BINZHANG;
COUNT(*)
———-
68967
SQL> exec dbms_redefinition.finish_redef_table( USER, ‘BINZHANG’, ‘BINZHANG_TMP’);
PL/SQL procedure successfully completed.
SQL> select object_name,status,LAST_DDL_TIME,object_type from dba_objects where object_name=’BINZHANG’;
OBJECT_NAME STATUS LAST_DDL_TIME OBJECT_TYPE
—————————— ——————— ——————- ———————————————————
BINZHANG VALID 2007-12-26 23:42:34 SYNONYM
BINZHANG VALID 2007-12-26 23:41:54 TABLE
测试中的public synonym的last ddl time并没有改变。
11g中添加的这些功能增强了DBA维护操作的并发性。
