建立测试表@H_502_1@

createtableT_tablenologgingasselect*fromdba_objects;
createtablet_table_1nologgingasselect*fromdba_tables;

--建立日志记录@H_502_1@

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

--建立物化视图@H_502_1@

creatematerializedviewmv_t_tablenologging
refreshfastondemand
withrowid
STARTWITHTO_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_name
groupbya.object_type,a.owner

@H_502_1@

--测试@H_502_1@

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

@H_502_1@

--查看日志生成量@H_502_1@

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

@H_502_1@

--查看刷新日志@H_502_1@

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

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

select*fromMLOG$_T_TABLE
select*fromMLOG$_T_TABLE_1;

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