在线重定义 Oracle Redefinition
在一个高可用系统中，如果需要改变一个表 的定义是一件比较棘手的问题，尤其是对于7×24系统。Oracle提供的基本语法基 本可以满足一般性修改，但是对于把普通堆表改为分区表，把索引组织表修改为堆表等操作就无法完成了。而且，对于被大量DML语句访问的表，幸运的是， Oracle从9i版本开始提供了在线重定义表功能，通过调用DBMS_REDEFINITION包，可以在修改表结构的同时允许DML操作
在线重定义表具有以下功能：
修改表的存储参数；
可以将表转移到其他表空间；
增加并行查询选项；
增加或删除分区；
重建表以减少碎片；
将堆表改为索引组织表或相反的操作；
增加或删除一个列。
调用DBMS_REDEFINITION包需要EXECUTE_CATALOG_ROLE角色，除此之外， 还需要CREATE ANY TABLE、ALTER ANY TABLE、DROP ANY TABLE、LOCK ANY TABLE和SELECT ANY TABLE的权限。
在线重定义表的步骤如下：
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()过程完成表的重定义。这个过程中，原始表会被独占模式锁定一小段时 间，具体时间和表的数据量有关。
执行完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;
下面是进行重定义操作后的结果：
原始表根据中间表的属性和特性进行重定 义；
START_REDEF_TABLE ()和FINISH_REDEF_TABLE()操作之间在中间表上建立的触发器、索引、约束和授权，现在定义在原始表上。中间表上disabled的约 束在原始表上处于enabled状态。
原始表上定义的触发器、索引、约束和授权 建立在中间表上，并会在删除中间表时删除。原始表上原来enabled状态的索引，建 立在中间表上，并处于disabled状态。
任何定义在原始表上的存储过程和游标都会 变为INVALID，当下次调用时后自动进行编译。
如果执行过程中出现错误或者人为选择退出 的话，可以执行DBMS_REDEFINITION.ABORT_REDEF_TABLE ()过程。
Example of using PK
-------------------------------------------------
-- orgin table
drop table redef_test_1 purge;
create table redef_test_1
(
id number not null ,
batch_identifier varchar2(10) not null ,
logging_time date default sysdate,
message varchar2(4000)
);
alter table redef_test_1
add constraint redef_test_1_pk primary key (id);
create index idx_redef_test_1_c1 on redef_test_1 (batch_identifier);
insert into redef_test_1
(id, batch_identifier, logging_time, message)
select rownum, 'ID_' || rownum, sysdate + rownum, 'msg'
from dual
connect by rownum <= 100;
commit;
select * from redef_test_1;
select * from redef_test_1_inter;
-----------------------------------------------
-- check if we can redefine this table
begin
dbms_redefinition.can_redef_table(user,
'redef_test_1',
dbms_redefinition.cons_use_pk);
end;
/
-----------------------------------------------
-- create inter table
drop table redef_test_1_inter purge;
create table redef_test_1_inter
(
id number,
batch_identifier varchar2(10),
logging_time date default sysdate,
message varchar2(4000)
)
partition by range (logging_time)
(
partition p200811 values less than (to_date('20081201','yyyymmdd')),
partition p200812 values less than (to_date('20090101','yyyymmdd')),
partition pmax values less than (maxvalue)
);
-----------------------------------------------
-- starting
begin
dbms_redefinition.start_redef_table (user,
'redef_test_1',
'redef_test_1_inter',
'id id,
batch_identifier batch_identifier,
to_date(to_char(logging_time,''yyyymmdd hh24:mi:ss''),''yyyymmdd hh24:mi:ss'') logging_time,
message message',
options_flag => dbms_redefinition.cons_use_pk);
end;
/
-----------------------------------------------
-- before copy we want use a partitioned index to replace orginal b-tree one
create index TMP$$_IDX_REDEF_TEST_1_C10
on redef_test_1_inter (batch_identifier) local;
begin
dbms_redefinition.register_dependent_object(user,
'redef_test_1',
'redef_test_1_inter',
dep_type => dbms_redefinition.cons_index,
dep_owner => user,
dep_orig_name => 'IDX_REDEF_TEST_1_C1',
dep_int_name => 'TMP$$_IDX_REDEF_TEST_1_C10');
end;
/
-----------------------------------------------
-- copy the constraints and indexes
declare
l_num_errors pls_integer;
begin
dbms_redefinition.copy_table_dependents(user,
'redef_test_1',
'redef_test_1_inter',
num_errors => l_num_errors);
if l_num_errors <> 0 then
Raise_application_error(-20300, 'Number of errors: ' || l_num_errors);
end if;
end;
/
-- view to check for errors.
select object_name, base_table_name, ddl_txt from dba_redefinition_errors;
-- check the copy result
select * from dba_redefinition_objects o order by o.object_type, o.object_name;
-----------------------------------------------
-- sync
begin
dbms_redefinition.sync_interim_table(user,
'redef_test_1',
'redef_test_1_inter');
end;
/
-----------------------------------------------
-- finishing
begin
dbms_redefinition.finish_redef_table(user,
'redef_test_1',
'redef_test_1_inter');
end;
/
-----------------------------------------------
-- abort
begin
dbms_redefinition.abort_redef_table(user,
'redef_test_1',
'redef_test_1_inter');
end;
/
-----------------------------------------------
-- EXTER: enable all constraints if they are disabled
declare
l_tab varchar2(30) := 'redef_test_1';
begin
for x in (select 'alter table '||l_tab||' enable constraint ' ||
uc.constraint_name as stmt
from user_constraints uc
where uc.table_name = upper(l_tab)) loop
execute immediate x.stmt;
end loop;
end;
/
Example of using ROWID
步骤基本一致，区别是
1. options_flag 参数指定为 dbms_redefinition.cons_use_rowid
2. finish之后需要把隐藏列删除
select column_name, column_id
from user_tab_cols
where table_name = upper('redef_test_1')
order by column_id;
COLUMN_NAME COLUMN_ID
------------------------------ ----------
ID 1
BATCH_IDENTIFIER 2
LOGGING_TIME 3
MESSAGE 4
SYS_C00005_08103116:42:11$
alter table redef_test_1 drop unused columns;
If you used rowids for the redefinition and your COMPATIBLE initialization parameter is set to 10.1 or lower, set to UNUSED the hidden column M_ROW$$ that is now in the redefined table.
ALTER TABLE table_name SET UNUSED (M_ROW$$);
If COMPATIBLE is 10.2 or higher, this hidden column is automatically set to UNUSED for you when redefinition completes.
posted on 2012-01-30 18:07 wait4friend 阅读(293) 评论(0) 编辑 收藏 举报
