在线重定义

Love The Way You Lie 2022-08-05 08:58 175阅读 0赞

在线重定义是oracle改变表结构的高可用方案，即在线修改表结构，分为以主键和rowid2种定义方法

它是一个刷新物化视图，瞬间锁表rename的过程，将原表的base数据和更新数据通过物化视图同步到中间表然后rename中间表成原表

注意：1原表索引对应的中间表索引必须为全局，不然会报错

2对应归档的数据库，势必产生大量的归档，重建索引最好加上nologing

参考脚本：--------------------------------------------------------------------------------------------------------------------------------------------------------------------

1、开始在线重定义，7min  
  
BEGIN  
  DBMS\_REDEFINITION.START\_REDEF\_TABLE(uname        => 'dpapp',  
                                      orig\_table   => 't\_app\_system\_log',  
                                      int\_table    => 't\_app\_system\_log\_BAK',  
                                      options\_flag => DBMS\_REDEFINITION.CONS\_USE\_PK);  
END;  
/  
  
  
\-- COPY THE TABLE DEPENDENTS FROM THE ORIGINAL TABLE TO THE INTERIM TABLE  
2、复制原始表的依赖对象  
  
DECLARE  
  error\_count pls\_integer := 0;  
BEGIN  
  DBMS\_REDEFINITION.COPY\_TABLE\_DEPENDENTS(uname            => 'dpapp',  
                                          orig\_table       => 't\_app\_system\_log',  
                                          int\_table        => 't\_app\_system\_log\_BAK',  
                                          copy\_indexes     => 0,--不copy 索引  
                                          copy\_triggers    => TRUE,  
                                          copy\_constraints => FALSE,  
                                          copy\_privileges  => TRUE,  
                                          ignore\_errors    => FALSE,  
                                          num\_errors       => error\_count,  
                                          copy\_statistics  => FALSE,  
                                          copy\_mvlog       => TRUE);  
  DBMS\_OUTPUT.PUT\_LINE('errors := ' || TO\_CHAR(error\_count));  
END;  
/  
  
  
3、检查是否有错误  
select \* from DBA\_REDEFINITION\_ERRORS;  
  
\--  
  
\-- DO A FINAL SYNCHRONIZE BEFORE FINISHING THE REDEFINITION  
4、同步临时表数据  
  
BEGIN  
  DBMS\_REDEFINITION.SYNC\_INTERIM\_TABLE('dpapp',  
                                       't\_app\_system\_log',  
                                       't\_app\_system\_log\_BAK');  
END;  
/  
  
\-- PL/SQL procedure successfully completed.  
  
\-- FINISH THE REDEFINITION  
5、完成同步  
  
begin  
 DBMS\_REDEFINITION.FINISH\_REDEF\_TABLE('dpapp','t\_app\_system\_log','t\_app\_system\_log\_BAK');  
end;  
  
\-- PL/SQL procedure successfully completed.?  
  
6.检查  
with tab as  
 (select upper('t\_app\_system\_log') tabname from dual)  
select sysdate,  
       segment\_name,  
       segment\_type,  
       round(bytes / 1024 / 1024 / 1024, 2) GB  
  from user\_segments  
 where (segment\_name in (select \* from tab) or  
       segment\_name in  
       (select i.index\_name  
           from user\_indexes i  
          where i.table\_name in (select \* from tab)))  
 order by bytes desc;  
   
   
  
  
  
\-- Grant/Revoke object privileges  
grant select on DIPOA.WFWORKITEM\_BAK to OASEARCH;  
  
、验证新表数据  
select count(0) from t\_app\_system\_log;  
select count(0) from t\_app\_system\_log\_BAK;  
  
select \* from dba\_tab\_privs where table\_name = 't\_app\_system\_log';  
  
select \* from dba\_indexes where table\_name = 'T\_TEMP\_WFWORKITEM\_BAK';  
  
select \* from dba\_objects where object\_name = 'WFWORKITEM\_BAK';  
  
  
  
  
alter index DIPOA.IDX\_WFWORKITEM\_BAK\_005 noparallel;  
  
  
7、删除临时表alter index DIPOA.IDX\_WFWORKITEM\_BAK\_003 noparallel;  
\---删除临时表  
drop table T\_TEMP\_WFWORKITEM\_BAK purge;  
  
8、收集统计信息  
begin  
DBMS\_STATS.GATHER\_TABLE\_STATS(OWNNAME       => 'DIPOA',  
                                  TABNAME       => 'WFWORKITEM\_BAK',  
                                  DEGREE        => 8,  
                                  no\_invalidate => false,  
                                  CASCADE       => TRUE);  
end;  
/  
\--==============回退方案=================================================  
\--取消在线重定义  
BEGIN  
  DBMS\_REDEFINITION.ABORT\_REDEF\_TABLE(UNAME      => 'dpapp',  
                                      ORIG\_TABLE => 't\_app\_system\_log',  
                                      INT\_TABLE  => 't\_app\_system\_log\_bak',  
                                      PART\_NAME  => NULL);  
end;