oracle日志收缩,Oracle物化视图日志表收缩

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;