建立测试表

createtableT_tablenologgingasselect*fromdba_objects;createtablet_table_1nologgingasselect*fromdba_tables;

--建立日志记录

creatematerializedviewlogonT_tablewithrowid,sequence(object_name,object_type,owner)includingnewvalues;creatematerializedviewlogont_table_1withrowid,sequence(table_name)includingnewvalues;

--建立物化视图

creatematerializedviewmv_t_tablenologgingrefreshfastondemandwithrowidSTARTWITHTO_DATE('21-08-201710:09:08','DD-MM-YYYYHH24:MI:SS')NEXTSYSDATE+1/(24*60)asselectcount(*),a.object_type,a.ownerfromT_tablea,t_table_1bwherea.object_name=b.table_namegroupbya.object_type,a.owner


--测试

select*frommv_t_tablewhereowner='SYSTEM';deletefromt_tablewhereowner='SYSTEM';insertintot_tableselect*fromdba_objectswhereowner='SYSTEM';


--查看日志生成量

selecta.name,b.valuefromv$statnamea,v$mystatbwherea.statistic#=b.statistic#anda.name='redosize';


--查看刷新日志

SELECTowner,mview_name,last_refresh_scn,last_refresh_date,query,REVISIONFROMdba_mview_analysisWHEREowner='UTF32';

--查看日志记录表,MLOG$为日志记录表，一旦更新完成，此表的内容会被清掉；

select*fromMLOG$_T_TABLEselect*fromMLOG$_T_TABLE_1;

官方说明参见：http://docs.oracle.com/cd/E11882_01/server.112/e10706/repmview.htm#REPLN265