在线重定义

在线重定义是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;