ITPub博客

首页 > 数据库 > Oracle > 联机重定义表在10g的改进

联机重定义表在10g的改进

原创 Oracle 作者:space6212 时间:2019-07-09 09:48:05 0 删除 编辑
在10g以前,虽然支持联机重定义表,但是联机重定义后的后续工作还有很多,比如创建触发器、索引、约束等等,当一个表包含很多依赖对象时,这个后续工作就非常耗费精力时间,并且很容易漏掉一些东西。
值得庆幸的是,10g后这个都不会成为我们的负担,10g的dbms_redefinition新提供一个过程COPY_TABLE_DEPENDENTS,可以帮我们做这件事情。
[@more@]下面以联机把一个非分区表转换为分区表为例说明步骤:

--1. create table
--这一个创建一个与源表结构一样的临时表
drop table PROD_USER.PO_ITEMS_TEMP purge;
create table PROD_USER.PO_ITEMS_TEMP tablespace ALLOC_MED
partition by range(creation_date)
(
PARTITION PBI_20090110 VALUES LESS THAN(TO_DATE('2009-01-11','yyyy-mm-dd'))
)
as select * from PO_ITEMS where rownum<1;


--2. re-definition
--开始联机重定义表

--首先检查源表是否支持联机重定义
exec dbms_redefinition.can_redef_table('PROD_USER','PO_ITEMS');

--如果支持,开始联机重定义
exec DBMS_REDEFINITION.start_redef_table('PROD_USER','PO_ITEMS','PO_ITEMS_TEMP')
--这一步完成后,PO_ITEMS的大部分数据都复制到PO_ITEMS_TEMP了


--因为拷贝依赖对象时可能需要创建索引,所以把相关参数改大
alter session set workarea_size_policy=manual;
alter session set sort_area_size=1000000000;
alter session set sort_area_retained_size=1000000000;
var n number
exec DBMS_REDEFINITION.COPY_TABLE_DEPENDENTS('PROD_USER','PO_ITEMS','PO_ITEMS_TEMP',num_errors=>:n,ignore_errors=>true)
--注意ignore_errors应该设成true,因为在COPY_TABLE_DEPENDENTS时,如果中间表的某些字段已经非空,则在应用这些非空约束时会出错,会导致余下的依赖对象不能复制过去
--可以用print n 看有多少个错误,如果n=非空字段数,则没有问题
--这一步,oracle会自动把触发器、索引、约束等相关的依赖对象复制到临时表中,如:
SYS.ALLOC1CN>select owner,index_name,table_name from dba_indexes where table_name like 'PO_ITEMS%';

OWNER INDEX_NAME TABLE_NAME
------------- ---------------------- ------------------------------
PROD_USER IX_REQUEST_ID PO_ITEMS
PROD_USER IX_SOURCE_ID PO_ITEMS
PROD_USER IX_ITEM_STATUS_ID PO_ITEMS
PROD_USER PK_PBI_ITEM_ID PO_ITEMS
PROD_USER I_PO_ITEMS_VENDOR_DATE PO_ITEMS
PROD_USER TMP$$_IX_REQUEST_ID0 PO_ITEMS_TEMP
PROD_USER TMP$$_IX_SOURCE_ID0 PO_ITEMS_TEMP
PROD_USER TMP$$_IX_ITEM_STATUS_ID0 PO_ITEMS_TEMP
PROD_USER TMP$$_PK_PBI_ITEM_ID0 PO_ITEMS_TEMP


--同步一次源表和中间表的数据
exec DBMS_REDEFINITION.SYNC_INTERIM_TABLE('PROD_USER','PO_ITEMS','PO_ITEMS_TEMP')

--完成联机重定义转换
exec dbms_redefinition.finish_redef_table('PROD_USER','PO_ITEMS','PO_ITEMS_TEMP')
--这一步完成后,所有依赖对象的名称也与中间表的名称交换,变为原来的名称,如:
SYS.ALLOC1CN>/

OWNER INDEX_NAME TABLE_NAME
------------- ------------------------------ ------------------------------
PROD_USER IX_REQUEST_ID PO_ITEMS
PROD_USER IX_SOURCE_ID PO_ITEMS
PROD_USER IX_ITEM_STATUS_ID PO_ITEMS
PROD_USER PK_PBI_ITEM_ID PO_ITEMS
PROD_USER I_PO_ITEMS_VENDOR_DATE PO_ITEMS
PROD_USER TMP$$_IX_REQUEST_ID0 PO_ITEMS_TEMP
PROD_USER TMP$$_IX_SOURCE_ID0 PO_ITEMS_TEMP
PROD_USER TMP$$_IX_ITEM_STATUS_ID0 PO_ITEMS_TEMP
PROD_USER TMP$$_PK_PBI_ITEM_ID0 PO_ITEMS_TEMP
PROD_USER TMP$$_I_PO_ITEMS_VE0 PO_ITEMS_TEMP


--6. gather statistics
exec DBMS_STATS.GATHER_TABLE_STATS('PROD_USER','PO_ITEMS',cascade=>true,no_invalidate=>false,granularity=>'ALL')

--7. drop inter table
删除中间表:
drop table PO_ITEMS_TEMP purge;

来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/231499/viewspace-1044240/,如需转载,请注明出处,否则将追究法律责任。

请登录后发表评论 登录
全部评论

注册时间:2005-01-25

  • 博文量
    245
  • 访问量
    166041