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

在一个高可用系统中,如果需要改变一个表的定义是一件比较棘手的问题,尤其是对于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维护操作的并发性。

 

文章来源

posted on 2012-09-28 13:13  要么牛逼,要么滚蛋  阅读(336)  评论(0编辑  收藏  举报

导航