+++++++ 详解物化视图日志
前面讲了物化视图日志创建的几种方式，这里详细讲解关于物化视图日志相关的一些东西，例如如何维护。
物化视图日志，简单一点讲，就是其记录了基表所有的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 操作针对物化视图日志，都可以参考上面这个方法，保证里面没有数据，
不然很可能会丢失数据的。
发表回复