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

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

Oracle materizlized view Study (3)

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

本文链接地址: 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 操作针对物化视图日志,都可以参考上面这个方法,保证里面没有数据,
不然很可能会丢失数据的。

    分享到:
18180207355
加Q咨询