love wife & love life —Roger的Oracle&MySQL技术博客

Phone:18180207355 提供专业Oracle&MySQL数据恢复、性能优化、迁移升级、紧急救援等服务

Oracle materizlized view Study (3)

本站文章除注明转载外,均为本站原创: 转载自love wife & love life —Roger的Oracle&MySQL技术博客

本文链接地址: Oracle materizlized view Study (3)

+++++++ 详解物化视图日志

前面讲了物化视图日志创建的几种方式,这里详细讲解关于物化视图日志相关的一些东西,例如如何维护。
物化视图日志,简单一点讲,就是其记录了基表所有的dml变化,然后在刷新时也是根据物化视图日志来
进行扫描的,在刷新完成以后,物化视图日志里面的信息会被清空。但是高水位线不会降低。

SQL> SELECT owner,MVIEW_NAME,REWRITE_ENABLED,REFRESH_MODE,REFRESH_METHOD,BUILD_MODE
  2  FROM sys.dba_mviews WHERE owner='KILLDB';
 
OWNER           MVIEW_NAME           R REFRES REFRESH_ BUILD_MOD
--------------- -------------------- - ------ -------- ---------
KILLDB          MV_TAB1              N DEMAND FAST     IMMEDIATE
KILLDB          MV_TAB4              N DEMAND FORCE    IMMEDIATE
 
SQL> SELECT COUNT(*) FROM mv_tab1;
 
  COUNT(*)
----------
     14891
SQL> SELECT object_name FROM dba_objects WHERE object_name LIKE 'MLOG$_%';
 
OBJECT_NAME
-----------------------------------------------------
MLOG$_T1
MLOG$_T2
MLOG$_T3
MLOG$_T4
SQL> SELECT object_type FROM sys.dba_objects WHERE object_name=UPPER('mv_tab1');
 
OBJECT_TYPE
-------------------
TABLE
MATERIALIZED VIEW
 
SQL> SELECT dbms_metadata.get_ddl('TABLE','MV_TAB1') FROM DUAL; 
 
DBMS_METADATA.GET_DDL('TABLE','MV_TAB1')
--------------------------------------------------------------------------------
 
  CREATE TABLE "KILLDB"."MV_TAB1"
   (    "OWNER" VARCHAR2(30),
        "OBJECT_NAME" VARCHAR2(128),
        "SUBOBJECT_NAME" VARCHAR2(30),
        "OBJECT_ID" NUMBER,
        "DATA_OBJECT_ID" NUMBER,
        "OBJECT_TYPE" VARCHAR2(19),
        "CREATED" DATE,
        "LAST_DDL_TIME" DATE,
        "TIMESTAMP" VARCHAR2(19),
        "STATUS" VARCHAR2(7),
        "TEMPORARY" VARCHAR2(1),
        "GENERATED" VARCHAR2(1),
        "SECONDARY" VARCHAR2(1),
         CONSTRAINT "PK_T11" PRIMARY KEY ("OBJECT_ID")
  USING INDEX PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS
  STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
  TABLESPACE "USERS"  ENABLE
   ) PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING
  STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
  TABLESPACE "USERS"
 
SQL>

我们这里的基表是T1,在你创建物化视图日志的时候,oracle会自动将其命名为mlog$_基表名的形式。
下面我们来看看这个物化视图日志的结构是怎么样的?

SQL> DESC mlog$_t1
 Name                         NULL?    TYPE
 ---------------------------- -------- ------------------------------
 OBJECT_ID                             NUMBER
 SNAPTIME$$                            DATE
 DMLTYPE$$                             VARCHAR2(1)
 OLD_NEW$$                             VARCHAR2(1)
 CHANGE_VECTOR$$                       RAW(255)
 
我们前面创建的物化视图mv_tab1的语句如下:
SQL> ALTER TABLE t1 ADD CONSTRAINT pk_t1 PRIMARY KEY(object_id) ;
 
TABLE altered.
 
SQL> CREATE materialized VIEW log ON t1 WITH PRIMARY KEY;
 
Materialized VIEW log created.
 
SQL>  CREATE materialized VIEW mv_tab1 REFRESH FAST FOR UPDATE AS
  2  SELECT * FROM t1 WHERE object_id > 10000 AND object_id < 30001;
 
Materialized VIEW created.

—— 下面我们来操作一条数据来进行观察

SQL> SELECT * FROM mlog$_t1 WHERE rownum < 5;                                                                                                         
 
no ROWS selected                                                                                                                                      
 
SQL> DELETE FROM t1 WHERE object_id=11111;                                                                                                            
 
1 ROW deleted.                                                                                                                                        
 
SQL> commit;                                                                                                                                          
 
Commit complete.                                                                                                                                      
 
SQL> SELECT COUNT(*) FROM mv_tab1;                                                                                                                    
 
  COUNT(*)                                                                                                                                            
----------                                                                                                                                            
     14891                                                                                                                                            
 
SQL> ALTER SESSION SET nls_date_format='yyyy-mm-dd hh24:mi:ss';
 
SESSION altered.
 
SQL> SELECT * FROM mlog$_t1 WHERE rownum < 5;
 
 OBJECT_ID SNAPTIME$$          D O CHANGE_VECTOR$$
---------- ------------------- - - ----------------------------------------
     11111 4000-01-01 00:00:00 D O 0000
 
SQL> EXEC dbms_mview.refresh('MV_TAB1');
 
PL/SQL PROCEDURE successfully completed.
 
SQL>  SELECT * FROM mlog$_t1 WHERE rownum < 5;
 
no ROWS selected
 
SQL>  
SQL> SELECT COUNT(*) FROM mv_tab1;
 
  COUNT(*)
----------
     14890

下面来分别描述这几个列的具体含义:
object_id: 这一列是10g以后新增加的,是你的前面创建物化视图指定的主键列。如果你的主键是id,那么这里列名也是id。
SNAPTIME$$:记录刷新操作的时间
DMLTYPE$$:记录DML的类型,主要有几种,分别是i(insert)、d(delete)、u(update)
OLD_NEW$$:记录更新的列的值是之前old值(O),还是更新后新的new值(n).当然,如果是update的话,则是U。
CHANGE_VECTOR$$: 记录dml操作发生的字段

例如:

SQL> UPDATE t1 SET owner='killdb.com' WHERE object_id=12222;
 
1 ROW updated.
 
SQL> commit;
 
Commit complete.
 
SQL> SELECT * FROM mlog$_t1 WHERE rownum < 5;
 
 OBJECT_ID SNAPTIME$$          D O CHANGE_VECTOR$$
---------- ------------------- - - ----------------------------------------
     12222 4000-01-01 00:00:00 U U 0200

Oracle采用的方式就是用每个BIT位去映射一个列。

比如:第一列被更新设置为02,即00000010。第二列设置为04,即00000100,第三列设置为08,即00001000。
当第一列和第二列同时被更新,则设置为06,00000110。如果三列都被更新,设置为0E,00001110。

所以上面这里这里查询结果02就表示第1列。

从上面可以看出,物化视图日志的的包含的列可能有:主键列。rowid,sequence,object或指明的某列。实际上就是
创建物化视图日志的几种方式。

既然物化视图和物化视图日志都是实实在在存在的对象,那么我们来看看其结构跟普通的数据表是否完全一样。

SQL> SELECT dbms_rowid.rowid_relative_fno(rowid) file#,
  2         dbms_rowid.rowid_block_number(rowid) blk#
  3    FROM mv_tab1 WHERE object_id=12222;
 
     FILE#       BLK#
---------- ----------
         4       6749
 
SQL> SELECT dbms_rowid.rowid_relative_fno(rowid) file#,
  2         dbms_rowid.rowid_block_number(rowid) blk#
  3    FROM mlog$_t1;
 
     FILE#       BLK#
---------- ----------
         4       6678
 
SQL> 
SQL> DELETE FROM t1 WHERE object_id=13333;
 
1 ROW deleted.
 
SQL>  SELECT xidusn,xidslot,xidsqn,ubablk,ubafil,ubarec FROM v$transaction;
 
    XIDUSN    XIDSLOT     XIDSQN     UBABLK     UBAFIL     UBAREC
---------- ---------- ---------- ---------- ---------- ----------
         4          5        774       1985          2         16
 
SQL> SELECT usn,name FROM v$rollname WHERE usn=4;
 
       USN NAME
---------- ------------------------------
         4 _SYSSMU4$
 
SQL>  SELECT * FROM mlog$_t1 ;
 
 OBJECT_ID SNAPTIME$$          D O CHANGE_VECTOR$$
---------- ------------------- - - ----------------------------------------
     12222 4000-01-01 00:00:00 U U 0200
     13333 4000-01-01 00:00:00 D O 0000
 
SQL> 
SQL> SELECT dbms_rowid.rowid_relative_fno(rowid) file#,
  2         dbms_rowid.rowid_block_number(rowid) blk#
  3    FROM mv_tab1 WHERE object_id=13333;
 
     FILE#       BLK#
---------- ----------
         4       6783
 
SQL> SELECT dbms_rowid.rowid_relative_fno(rowid) file#,
  2         dbms_rowid.rowid_block_number(rowid) blk#
  3    FROM mlog$_t1;
 
     FILE#       BLK#
---------- ----------
         4       6678
         4       6678
 
SQL> 
 
 
---For bbed
BBED> SET file 4 block 6783
        FILE#           4
        BLOCK#          6783
 
BBED> map /v
 File: /home/ora10g/oradata/roger/users01.dbf (4)
 Block: 6783                                  Dba:0x01001a7f
------------------------------------------------------------
 KTB DATA Block (TABLE/Cluster)
 
 struct kcbh, 20 bytes                      @0       
    ub1 type_kcbh                           @0       
    ub1 frmt_kcbh                           @1       
    ub1 spare1_kcbh                         @2       
    ub1 spare2_kcbh                         @3       
    ub4 rdba_kcbh                           @4       
    ub4 bas_kcbh                            @8       
    ub2 wrp_kcbh                            @12      
    ub1 seq_kcbh                            @14      
    ub1 flg_kcbh                            @15      
    ub2 chkval_kcbh                         @16      
    ub2 spare3_kcbh                         @18      
 
 struct ktbbh, 96 bytes                     @20      
    ub1 ktbbhtyp                            @20      
    UNION ktbbhsid, 4 bytes                 @24      
    struct ktbbhcsc, 8 bytes                @28      
    b2 ktbbhict                             @36      
    ub1 ktbbhflg                            @38      
    ub1 ktbbhfsl                            @39      
    ub4 ktbbhfnx                            @40      
    struct ktbbhitl[3], 72 bytes            @44      
 
 struct kdbh, 14 bytes                      @124     
    ub1 kdbhflag                            @124     
    b1 kdbhntab                             @125     
    b2 kdbhnrow                             @126     
    sb2 kdbhfrre                            @128     
    sb2 kdbhfsbo                            @130     
    sb2 kdbhfseo                            @132     
    b2 kdbhavsp                             @134     
    b2 kdbhtosp                             @136     
 
 struct kdbt[1], 4 bytes                    @138     
    b2 kdbtoffs                             @138     
    b2 kdbtnrow                             @140     
 
 sb2 kdbr[70]                               @142     
 
 ub1 freespace[873]                         @282     
 
 ub1 rowdata[7033]                          @1155    
 
 ub4 tailchk                                @8188    
 
 
BBED> SET file 4 block 6678
        FILE#           4
        BLOCK#          6678
 
BBED> map /v
 File: /home/ora10g/oradata/roger/users01.dbf (4)
 Block: 6678                                  Dba:0x01001a16
------------------------------------------------------------
 KTB DATA Block (TABLE/Cluster)
 
 struct kcbh, 20 bytes                      @0       
    ub1 type_kcbh                           @0       
    ub1 frmt_kcbh                           @1       
    ub1 spare1_kcbh                         @2       
    ub1 spare2_kcbh                         @3       
    ub4 rdba_kcbh                           @4       
    ub4 bas_kcbh                            @8       
    ub2 wrp_kcbh                            @12      
    ub1 seq_kcbh                            @14      
    ub1 flg_kcbh                            @15      
    ub2 chkval_kcbh                         @16      
    ub2 spare3_kcbh                         @18      
 
 struct ktbbh, 72 bytes                     @20      
    ub1 ktbbhtyp                            @20      
    UNION ktbbhsid, 4 bytes                 @24      
    struct ktbbhcsc, 8 bytes                @28      
    b2 ktbbhict                             @36      
    ub1 ktbbhflg                            @38      
    ub1 ktbbhfsl                            @39      
    ub4 ktbbhfnx                            @40      
    struct ktbbhitl[2], 48 bytes            @44      
 
 struct kdbh, 14 bytes                      @100     
    ub1 kdbhflag                            @100     
    b1 kdbhntab                             @101     
    b2 kdbhnrow                             @102     
    sb2 kdbhfrre                            @104     
    sb2 kdbhfsbo                            @106     
    sb2 kdbhfseo                            @108     
    b2 kdbhavsp                             @110     
    b2 kdbhtosp                             @112     
 
 struct kdbt[1], 4 bytes                    @114     
    b2 kdbtoffs                             @114     
    b2 kdbtnrow                             @116     
 
 sb2 kdbr[129]                              @118     
 
 ub1 freespace[4801]                        @376     
 
 ub1 rowdata[3011]                          @5177    
 
 ub4 tailchk                                @8188    
 
------------block dump
Block header dump:  0x01001a16
 Object id ON Block? Y
 seg/obj: 0xe074  csc: 0x00.4927ae  itc: 2  flg: E  typ: 1 - DATA
     brn: 0  bdba: 0x1001a11 ver: 0x01 opc: 0
     inc: 0  exflg: 0
 
 Itl           Xid                  Uba         Flag  Lck        Scn/Fsc
0x01   0x0008.017.00000383  0x00800388.0331.06  C---    0  scn 0x0000.00492635
0x02   0x0004.005.00000306  0x008007c1.03e3.10  --U-    1  fsc 0x0000.00492921
 
data_block_dump,DATA header at 0xd0d9864
===============
tsiz: 0x1f98
hsiz: 0x114
pbl: 0x0d0d9864
bdba: 0x01001a16
     76543210
flag=--------
ntab=1
nrow=129
frre=0
fsbo=0x114
fseo=0x13be
avsp=0x1e56
tosp=0x1e56
0xe:pti[0]      nrow=129        offs=0
0x12:pri[0]     sfll=3
0x14:pri[1]     offs=0x13d5
0x16:pri[2]     offs=0x13be
0x18:pri[3]     sfll=4
......省略部分内容
0x112:pri[128]  sfll=-1
block_row_dump:
tab 0, ROW 1, @0x13d5
tl: 23 fb: --H-FL-- lb: 0x0  cc: 5
col  0: [ 4]  c3 02 17 17
col  1: [ 7]  8c 64 01 01 01 01 01
col  2: [ 1]  55
col  3: [ 1]  55
col  4: [ 2]  02 00
tab 0, ROW 2, @0x13be
tl: 23 fb: --H-FL-- lb: 0x2  cc: 5
col  0: [ 4]  c3 02 22 22
col  1: [ 7]  8c 64 01 01 01 01 01
col  2: [ 1]  44
col  3: [ 1]  4f
col  4: [ 2]  00 00
end_of_block_dump
 
 
SQL> SELECT hextostr('8c 64 01 01 01 01 01') colname FROM dual;
 
COLNAME
--------------------------------------------------------------------------------
 d
 
SQL> SELECT hextostr('44 4f') colname FROM dual;
 
COLNAME
--------------------------------------------------------------------------------
DO
 
SQL>

可以看到物化视图block和物化视图日志的block结构跟普通的数据块完全一样,没有任何不同。
我们知道物化视图在完成刷新以后,日志是会被清理掉的。这里有个疑问了?如果日志很大那么
必须回影响性能,如果你长时间的刷新,那么也比如会导致物化视图日志的高水位线很高。

下面我们来看一个实现复制功能的物化视图日志的情况:

SQL> CREATE TABLE t_hw AS SELECT * FROM sys.dba_objects;
 
TABLE created.
 
SQL>  CREATE materialized VIEW log ON t_hw WITH rowid;
 
Materialized VIEW log created.
 
SQL>  CREATE materialized VIEW mv_t_hw AS SELECT * FROM t_hw;
 
Materialized VIEW created.
 
SQL> 
SQL> DELETE FROM t_hw WHERE object_id < 10001;
 
9562 ROWS deleted.
 
SQL> commit;
 
Commit complete.
 
SQL> analyze TABLE MLOG$_T_HW compute statistics;
 
TABLE analyzed.
 
SQL> DELETE FROM t_hw WHERE object_id < 10001;
 
0 ROWS deleted.
 
SQL> DELETE FROM t_hw WHERE object_id < 30001;
 
19890 ROWS deleted.
 
SQL> commit;
 
Commit complete.
 
SQL> INSERT INTO t_hw SELECT * FROM sys.dba_objects WHERE object_id <30000;
 
29451 ROWS created.
 
SQL> commit;
 
Commit complete.
 
SQL> DELETE FROM t_hw;
 
50927 ROWS deleted.
 
SQL> commit;
 
Commit complete.
 
SQL>  INSERT INTO t_hw SELECT * FROM sys.dba_objects WHERE object_id IS NOT NULL;
 
50935 ROWS created.
 
SQL> commit;
 
Commit complete.
 
SQL> analyze TABLE MLOG$_T_HW compute statistics;
 
TABLE analyzed.
SQL> SELECT bytes/1024/1024 FROM sys.dba_segments WHERE segment_name='MLOG$_T_HW';
 
BYTES/1024/1024
---------------
             10
SQL> SET timing ON
SQL> EXEC dbms_mview.refresh('MV_T_HW');
 
PL/SQL PROCEDURE successfully completed.
 
Elapsed: 00:00:21.88
SQL> ---可以看到此时来一次完全刷新非常慢。  
 
中间有想一系列的操作,省略.......
SQL> INSERT INTO t_hw SELECT * FROM sys.dba_objects WHERE object_id IS NOT NULL;
 
50935 ROWS created.
 
Elapsed: 00:00:09.40
SQL> commit;
 
Commit complete.
 
Elapsed: 00:00:00.00
SQL> DELETE FROM t_hw;
 
50935 ROWS deleted.
 
Elapsed: 00:00:09.61
SQL> commit;
 
Commit complete.
 
Elapsed: 00:00:00.02
SQL> INSERT INTO t_hw SELECT * FROM sys.dba_objects WHERE object_id IS NOT NULL;
 
50935 ROWS created.
 
Elapsed: 00:00:09.55
SQL> commit;
 
Commit complete.
 
Elapsed: 00:00:00.01
SQL> analyze TABLE MLOG$_T_HW compute statistics;
 
TABLE analyzed.
 
Elapsed: 00:00:01.11
SQL> analyze TABLE mv_t_hw compute statistics;
 
TABLE analyzed.
 
Elapsed: 00:00:00.69
SQL> SELECT COUNT(*) FROM mlog$_t_hw;
 
  COUNT(*)
----------
    203740
 
Elapsed: 00:00:00.02
SQL> EXEC dbms_mview.refresh('MV_T_HW');
 
PL/SQL PROCEDURE successfully completed.
 
Elapsed: 00:00:24.78
SQL> SELECT COUNT(*) FROM mlog$_t_hw;
 
  COUNT(*)
----------
         0
 
Elapsed: 00:00:00.23
SQL> 
SQL> l
  1* SELECT BLOCKS,EMPTY_BLOCKS FROM sys.dba_tables WHERE TABLE_NAME='MLOG$_T_HW'
SQL> /
 
    BLOCKS EMPTY_BLOCKS
---------- ------------
      1252           28
 
SQL> 
SQL> l
  1* SELECT BLOCKS,EMPTY_BLOCKS FROM sys.dba_tables WHERE TABLE_NAME='MLOG$_T_HW'
SQL> /
 
    BLOCKS EMPTY_BLOCKS
---------- ------------
      1630           34
SQL> SELECT BLOCKS,EMPTY_BLOCKS FROM dba_tables WHERE TABLE_NAME='MV_T_HW';
 
    BLOCKS EMPTY_BLOCKS
---------- ------------
       768            0

我们可以看到物化视图日志高水位并不会降低,只会增大,甚至可能比物化视图本身还大。

降低物化视图日志高水位的稳妥方式还建议使用move,如下:

SQL> conn killdb/killdb
Connected.
SQL> ALTER TABLE mlog$_t_hw move;
 
TABLE altered.
 
SQL> SELECT BLOCKS,EMPTY_BLOCKS FROM sys.dba_tables WHERE TABLE_NAME='MLOG$_T_HW'
  2  ;
 
    BLOCKS EMPTY_BLOCKS
---------- ------------
      1630           34
 
SQL> ALTER TABLE mlog$_t_hw move;
 
TABLE altered.
 
SQL> analyze TABLE MLOG$_T_HW compute statistics;
 
TABLE analyzed.
 
SQL> SELECT BLOCKS,EMPTY_BLOCKS FROM sys.dba_tables WHERE TABLE_NAME='MLOG$_T_HW';
 
    BLOCKS EMPTY_BLOCKS
---------- ------------
         0            8
 
当然,你也可以使用shrink,例如;
SQL> ALTER TABLE MLOG$_T_HW enable ROW MOVEMENT;
 
TABLE altered.
 
SQL> ALTER TABLE MLOG$_T_HW shrink SPACE;
 
TABLE altered.
 
SQL> SELECT BLOCKS,EMPTY_BLOCKS FROM sys.dba_tables WHERE TABLE_NAME='MLOG$_T_HW';
 
    BLOCKS EMPTY_BLOCKS
---------- ------------
       874           22
 
SQL> analyze TABLE MLOG$_T_HW compute statistics;
 
TABLE analyzed.
 
SQL>  SELECT BLOCKS,EMPTY_BLOCKS FROM sys.dba_tables WHERE TABLE_NAME='MLOG$_T_HW';
 
    BLOCKS EMPTY_BLOCKS
---------- ------------
         1            7
 
有点奇怪的是通过包进行清理,发现不好使,如下:
SQL> analyze TABLE MLOG$_T_HW compute statistics;
 
TABLE analyzed.
 
SQL> SELECT BLOCKS,EMPTY_BLOCKS FROM sys.dba_tables WHERE TABLE_NAME='MLOG$_T_HW';
 
    BLOCKS EMPTY_BLOCKS
---------- ------------
       370           14
 
SQL> SELECT OWNER,NAME,MVIEW_SITE,MVIEW_ID FROM DBA_REGISTERED_MVIEWS;
 
OWNER           NAME           MVIEW_SITE                       MVIEW_ID
--------------- -------------- ------------------------------ ----------
KILLDB          MV_TAB4        ROGER                                  26
KILLDB          MV_TAB1        ROGER                                  20
KILLDB          MV_T_HW        ROGER                                  42
 
Elapsed: 00:00:00.00
SQL> 
SQL> EXEC dbms_mview.PURGE_MVIEW_FROM_LOG('42');
 
PL/SQL PROCEDURE successfully completed.
 
Elapsed: 00:00:00.05
SQL> analyze TABLE MLOG$_T_HW compute statistics;
 
TABLE analyzed.
 
SQL> SELECT BLOCKS,EMPTY_BLOCKS FROM sys.dba_tables WHERE TABLE_NAME='MLOG$_T_HW';
 
    BLOCKS EMPTY_BLOCKS
---------- ------------
       370           14
 
SQL>

说明:其实在源端,还有一个比较重要的表也记录mv相关的信息,不过这个对象是在sys下面。

SQL> l
  1*  SELECT * FROM mlog$ WHERE master='T_HW'
SQL> /
 
MOWNER  MASTER  OLDEST    OLDEST_PK OLDEST_SE  OSCN YOUNGEST     YSCN LOG        TRIG  FLAG MTIME     TEMP_LOG   OLDEST_OI OLDEST_NE
------- ------- --------- --------- --------- ----- --------- ------- ---------- ----- ---- --------- ---------- --------- ---------
KILLDB  T_HW    22-AUG-12 22-AUG-12 01-JAN-00       22-AUG-12 4849452 MLOG$_T_HW         98 22-AUG-12 RUPD$_T_HW 01-JAN-00 01-JAN-00
 
SQL> 
 
在10g中,物化视图相关的一些视图如下:
SQL> SELECT TABLE_NAME FROM dict WHERE TABLE_NAME LIKE 'DBA_MVIEW%';
 
TABLE_NAME
------------------------------
DBA_MVIEW_ANALYSIS
DBA_MVIEW_AGGREGATES
DBA_MVIEW_DETAIL_RELATIONS
DBA_MVIEW_KEYS
DBA_MVIEW_JOINS
DBA_MVIEW_COMMENTS
DBA_MVIEWS
DBA_MVIEW_REFRESH_TIMES
DBA_MVIEW_LOGS
DBA_MVIEW_LOG_FILTER_COLS
 
10 ROWS selected.

最后还要一个简单的问题,突然想到的,那就是关于mview log的清理,我如何知道什么时候才能清理呢?如果清理时间点
不对,那么岂不是会影响刷新吗? 判断的依据就是mlog$_NAME.SNAPTIME$$和sys.slog$.SNAPTIME;

当mlog$_NAME.SNAPTIME$$ <= MIN (SLOG$.SNAPTIME)时,才可以进行清理。
 
SQL> CREATE TABLE T (ID NUMBER PRIMARY KEY, NAME VARCHAR2(30));
 
TABLE created.
 
SQL> INSERT INTO T SELECT ROWNUM, OBJECT_NAME FROM DBA_OBJECTS;
 
50938 ROWS created.
 
SQL> CREATE MATERIALIZED VIEW LOG ON T;
 
Materialized VIEW log created.
 
SQL> CREATE MATERIALIZED VIEW MV_T REFRESH FAST AS SELECT * FROM T;
 
Materialized VIEW created.
 
SQL> SELECT COUNT(*) FROM mlog$_t;
 
  COUNT(*)
----------
         0
 
SQL> commit;
 
Commit complete.
 
SQL> SELECT COUNT(*) FROM mlog$_t;
  COUNT(*)
----------
         0
 
SQL> DELETE FROM t WHERE rownum < 10001;
 
10000 ROWS deleted.
 
SQL> SELECT COUNT(*) FROM mlog$_t;
 
  COUNT(*)
----------
     10000
SQL> ALTER SESSION SET nls_date_format='yyyy-mm-dd hh24:mi:ss';
 
SESSION altered.
 
SQL> SELECT SNAPTIME$$ FROM  mlog$_t WHERE rownum < 5;
 
SNAPTIME$$
-------------------
4000-01-01 00:00:00
4000-01-01 00:00:00
4000-01-01 00:00:00
4000-01-01 00:00:00
 
SQL>  SELECT SNAPTIME FROM  sys.slog$ WHERE MASTER='T';
 
SNAPTIME
-------------------
2012-08-22 07:48:30
 
可以看到此时MLOG$_T的 SNAPTIME$$是设置为无穷大的。此时说明物化视图日志是不能清理的。
SQL> COLUMN snapshot_id HEADING 'SnapshotID' FORMAT b9999999999
SQL>  COLUMN owner HEADING 'Owner' FORMAT A6
SQL>  COLUMN name HEADING 'Mview Name' FORMAT A30
SQL>  COLUMN snapshot_site HEADING 'Mview Site' format a30
SQL>  COLUMN current_snapshots HEADING 'Last Time Refresh' format a21 
SQL> 
SQL> SELECT l.snapshot_id, owner, name, substr(snapshot_site,1,30) snapshot_site, 
  2  to_char(current_snapshots, 'mm/dd/yyyy hh24:mi:ss') current_snapshots
  3   FROM dba_registered_snapshots r, dba_snapshot_logs l
  4   WHERE r.snapshot_id = l.snapshot_id (+)
  5   AND l.master='&table_name';
Enter VALUE FOR TABLE_NAME: T
OLD   5:  AND l.master='&table_name'
NEW   5:  AND l.master='T'
 
 SnapshotID Owner  Mview Name                     Mview Site                     LAST TIME Refresh
----------- ------ ------------------------------ ------------------------------ ---------------------
         61 KILLDB MV_T                           ROGER                          08/22/2012 07:48:30
 
SQL> EXEC dbms_mview.refresh('mv_t');
 
PL/SQL PROCEDURE successfully completed.
 
SQL> SELECT l.snapshot_id, owner, name, substr(snapshot_site,1,30) snapshot_site, 
  2  to_char(current_snapshots, 'mm/dd/yyyy hh24:mi:ss') current_snapshots
  3   FROM dba_registered_snapshots r, dba_snapshot_logs l
  4   WHERE r.snapshot_id = l.snapshot_id (+)
  5   AND l.master='&table_name';
Enter VALUE FOR TABLE_NAME: T
OLD   5:  AND l.master='&table_name'
NEW   5:  AND l.master='T'
 
 SnapshotID Owner  Mview Name                     Mview Site                     LAST TIME Refresh
----------- ------ ------------------------------ ------------------------------ ---------------------
         61 KILLDB MV_T                           ROGER                          08/22/2012 08:00:06
 
SQL>

关于前面使用move或truncate 物化视图日志表,我在一篇mos文档上看到了如下一个 标准的步骤:

1) LOCK TABLE scott.emp IN EXCLUSIVE MODE; –基表
2) CREATE TABLE scott.templog AS SELECT * FROM scott.mlog$_emp; –另外一个session
3) TRUNCATE TABLE scott.mlog$_emp;
4) INSERT INTO scott.mlog$_emp SELECT * FROM scott.templog;
drop table scott.mlog$_emp;
5) ROLLBACK;

注意:Any changes made to the master table between the time you copy the rows
to a new location and when you truncate the log do not appear until after you
perform a complete refresh. Then it is better to truncate the MView log when it
is empty. Only the owner of a MView log or a user with the DELETE ANY TABLE
system privilege can truncate a mview log.

note: How To Truncate Materialized View Log [ID 457070.1]

最后补充一句,不管是move还是shrink 操作针对物化视图日志,都可以参考上面这个方法,保证里面没有数据,
不然很可能会丢失数据的。

2 Responses to “Oracle materizlized view Study (3)”

  1. tony Says:

    能不能不用     bbed啊 ,唉

  2. A Journey from Oracle to MS SQL Server – Brendan Codes Says:

    […] see why in a moment). It has to have the same fields as the real SYS.MLOG$ table, which I found on this page but have listed here for […]

Leave a Reply

You must be logged in to post a comment.