Oracle物化视图日志表收缩

原创刘广 2020-06-19

3717

1、说明

某些物化视图日志由于客户端物化视图刷新地不是很及时，导致物化视图日志表膨胀地非常厉害，但实际日志表里的数据又不是很多。一般物化视图日志表上没有索引，刷新物化视图需要对物化视图日志进行全表扫描，所以考虑到客户端的刷新速度和空间容量回收等问题，对于这种膨胀比较厉害的物化视图日志，需要进行收缩物化视图日志表其实是一张普通表，所以对它进行收缩的方法也比较多，但这些方法有些会影响物化视图日志的基表（也就是业务表）上的业务，有些可以在不影响基表业务的情况下收缩，可以根据各自的情况选择不同的方法

2、前期准备工作

【线下操作物化视图日志表收缩】
远程数据库建立堆组织表
CREATE TABLE T_600_RESULTS (
COL1 NUMBER ,
COL2 VARCHAR2(10 CHAR),
COL3 VARCHAR2(10CHAR)
)
SEGMENT CREATION IMMEDIATE 
TABLESPACE ETL_NEW 
NOLOGGING ;

--添加主键约束
ALTER TABLE T_600_RESULTS ADD CONSTRAINTS PK_T_600_RESULTS PRIMARY KEY(COL1) USING INDEX TABLESPACE ETL_INDEX; 

--插入测试数据
INSERT INTO T_600_RESULTS NOLOGGING 
SELECT ROWNUM"COL1",
       DBMS_RANDOM.STRING('X', 8) "COL2",
       DBMS_RANDOM.STRING('Q', 8) "COL3"
  FROM DUAL
CONNECT BY ROWNUM <= 1000;

--远程端创建物化视图日志
CREATE MATERIALIZED VIEW LOG ON T_600_RESULTS WITH PRIMARY KEY; 

--本地创建物化视图
CREATE MATERIALIZED VIEW MV_600_RESULTS
NOLOGGING 
NOPARALLEL 
TABLESPACE BI 
REFRESH FAST ON DEMAND 
WITH PRIMARY KEY 
START WITH SYSDATE NEXT SYSDATE +INTERVAL'1'MINUTE 
ENABLE QUERY REWRITE 
AS 
SELECT * FROM T_600_RESULTS@LINK_ETL;

核实数据字典内容
--查询物化视图相关信息
SELECT T.MVIEW_NAME,
       T.CONTAINER_NAME,
       T.MASTER_LINK,
       T.REWRITE_ENABLED,
       T.REFRESH_METHOD,
       T.BUILD_MODE,
       T.FAST_REFRESHABLE,
       T.LAST_REFRESH_DATE
  FROM USER_MVIEWS T
 WHERE T.MVIEW_NAME = Q'{MV_600_RESULTS}'
--远程数据库执行
SELECT * FROM USER_TABLES 
WHERE INSTR(TABLE_NAME,'MLOG$_T_600_RESULTS',1)>0
--本地查询
SELECT NAME,MASTER,LAST_REFRESH FROM USER_MVIEW_REFRESH_TIMES 
 WHERE LAST_REFRESH >=DATE'2017-05-08'

--对堆组织表做插入操作
DECLARE
  TYPE T_TAB IS TABLE OF T_600_RESULTS%ROWTYPE;

  L_TAB         T_TAB := T_TAB();
  L_ERROR_COUNT NUMBER;
  EX_DML_ERRORS EXCEPTION;
  PRAGMA EXCEPTION_INIT(EX_DML_ERRORS, -24381);
BEGIN
  FOR I IN 1001 .. 1500 LOOP
    L_TAB.EXTEND;
    L_TAB(L_TAB.LAST).COL1 := I;
    L_TAB(L_TAB.LAST).COL2 := DBMS_RANDOM.string('W',8);
    L_TAB(L_TAB.LAST).COL3 := DBMS_RANDOM.string('A',8);
  END LOOP;
  -- Perform a bulk operation.
  BEGIN
    FORALL I IN L_TAB.FIRST .. L_TAB.LAST SAVE EXCEPTIONS
      INSERT INTO T_600_RESULTS VALUES L_TAB (I);
  EXCEPTION
    WHEN EX_DML_ERRORS THEN
      L_ERROR_COUNT := SQL%BULK_EXCEPTIONS.COUNT;
      DBMS_OUTPUT.PUT_LINE('Number of failures: ' || L_ERROR_COUNT);
      FOR I IN 1 .. L_ERROR_COUNT LOOP
        DBMS_OUTPUT.PUT_LINE('Error: ' || I || ' Array Index: ' || SQL%BULK_EXCEPTIONS(I)
                             .ERROR_INDEX || ' Message: ' ||
                             SQLERRM(-SQL%BULK_EXCEPTIONS(I).ERROR_CODE));
      END LOOP;
  END;
END;
--对物化视图刷新基表强制独占锁
LOCK TABLE T_600_RESULTS IN EXCLUSIVE MODE; 
--创建物化视图临时日志表
CREATE TABLE MV_TEMP_600_RESULTS NOLOGGING AS SELECT * FROM MLOG$_T_600_RESULTS 
--清理物化视图日志表
TRUNCATE TABLE MLOG$_T_600_RESULTS
--将临时日志表内容重新同步到物化视图日志表
INSERT INTO MLOG$_T_600_RESULTS SELECT * FROM MV_TEMP_600_RESULTS
--释放锁资源，执行回滚操作
ROLLBACK; 

复制

3、线上操作物化视图日志表收缩

BEGIN
  DBMS_REDEFINITION.CAN_REDEF_TABLE(UNAME        => 'ETL',
                                    TNAME        => 'MLOG$_T_600_RESULTS',
                                    OPTIONS_FLAG => DBMS_REDEFINITION.cons_use_rowid);
END;
--创建在线重定义临时表
DROP TABLE MV_TEMP_600_RESULTS CASCADE CONSTRAINTS; 
create table MV_TEMP_600_RESULTS
(
  col1            NUMBER,
  snaptime$$      DATE,
  dmltype$$       VARCHAR2(1),
  old_new$$       VARCHAR2(1),
  change_vector$$ RAW(255),
  xid$$           NUMBER
)
SEGMENT CREATION IMMEDIATE
tablespace ETL;
--开始在线重定义操作
BEGIN
  DBMS_REDEFINITION.START_REDEF_TABLE(UNAME        => 'ETL',
                                      ORIG_TABLE   => 'MLOG$_T_600_RESULTS',
                                      INT_TABLE    => 'MV_TEMP_600_RESULTS',
                                      OPTIONS_FLAG => DBMS_REDEFINITION.CONS_USE_ROWID);
END;
--复制依赖对象 即：主键、索引、约束、check等
DECLARE
  NUM_ERRORS PLS_INTEGER;
BEGIN
  DBMS_REDEFINITION.COPY_TABLE_DEPENDENTS(UNAME            => 'ETL',
                                          ORIG_TABLE       => 'MLOG$_T_600_RESULTS',
                                          INT_TABLE        => 'MV_TEMP_600_RESULTS',
                                          COPY_INDEXES     => DBMS_REDEFINITION.CONS_ORIG_PARAMS,
                                          COPY_TRIGGERS    => TRUE,
                                          COPY_CONSTRAINTS => FALSE,
                                          COPY_PRIVILEGES  => TRUE,
                                          IGNORE_ERRORS    => FALSE,
                                          NUM_ERRORS       => NUM_ERRORS,
                                          COPY_STATISTICS  => TRUE);
END;
--开始在线重定义同步操作
BEGIN
  DBMS_REDEFINITION.SYNC_INTERIM_TABLE(UNAME      => 'ETL',
                                       ORIG_TABLE => 'MLOG$_T_600_RESULTS',
                                       INT_TABLE  => 'MV_TEMP_600_RESULTS');
END;
--完成在线重定义操作
BEGIN
  DBMS_REDEFINITION.FINISH_REDEF_TABLE(UNAME      => 'ETL',
                                       ORIG_TABLE => 'MLOG$_T_600_RESULTS',
                                       INT_TABLE  => 'MV_TEMP_600_RESULTS');
END;

这里使用rowid方式，重定义完的表上会多出一个隐藏字段，从10.2开始M_ROW$$的隐藏列会被命名为SYS_%DATE%的形式，且默认即为unused状态：
SELECT * FROM DBA_UNUSED_COL_TABS WHERE TABLE_NAME = Q'{MLOG$_T_600_RESULTS}'
--
ALTER TABLE MLOG$_T_600_RESULTS DROP UNUSED COLUMNS; 

复制

oracle