在线重定义表的测试和问题

来源：互联网发布：手机怎么注册淘宝达人编辑：程序博客网时间：2024/06/05 07:36

选自：http://www.orablogger.com/2009/04/%E5%9C%A8%E7%BA%BF%E9%87%8D%E5%AE%9A%E4%B9%89%E8%A1%A8%E7%9A%84%E6%B5%8B%E8%AF%95%E5%92%8C%E9%97%AE%E9%A2%98/#more-379

在一个高可用系统中，如果需要改变一个表的定义是一件比较棘手的问题，尤其是对于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、创建中间表

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;

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> 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维护操作的并发性。