MVIEW LOG 
===========

col log_owner for a30
col master for a40
col log_table for a40
col last_purge_date for a30
col last_purge_status for 9999999
select log_owner,master,log_table,last_purge_date,last_purge_status from dba_mview_logs;

MVIEW LOG SIZE
===============

col owner for a20
col segment_name for a40
SELECT S.OWNER “Owner”,NVL(S.SEGMENT_NAME, ‘TABLE TOTAL SIZE’) “Segment_name”,ROUND(SUM(S.BYTES)/1024/1024/1024,1) “Segment_size_GB”
FROM DBA_SEGMENTS S
WHERE S.SEGMENT_NAME IN (‘&Log_table_name‘)
AND S.OWNER IN (‘&Owner‘)
OR S.SEGMENT_NAME IN (
(
SELECT L.SEGMENT_NAME FROM DBA_LOBS L WHERE L.TABLE_NAME = ‘&Log_table_name‘ AND L.OWNER IN (‘&Owner‘)
)
)
GROUP BY S.OWNER,ROLLUP(S.SEGMENT_NAME)
ORDER BY 1,2,3;

.
LAST_REFRESH_DATE  

col owner for a30
col mview_name for a30
col last_refresh_date for a30
select owner,mview_name,LAST_REFRESH_DATE from dba_mviews where mview_name=’&Mview_name’;

REFRESH MVIEW

exec DBMS_MVIEW.REFRESH(‘<MView Name>’, ‘F’);  — FAST REFRESH

exec DBMS_MVIEW.REFRESH(‘<MView Name>’, ‘C’);  –COMPLETE REFRESH

Share this post
Recent Posts

Leave a Comment

Start typing and press Enter to search