Main menu
You are here
Show Materialized View
SQL> alter SESSION SET nls_date_format = 'DD.MM.YYYY HH24:MI:SS';
Session altered.
SQL> select MVIEW_NAME,LAST_REFRESH_DATE,REFRESH_METHOD,SUMMARY,FULLREFRESHTIM,INCREFRESHTIM from ALL_MVIEW_ANALYSIS;
MVIEW_NAME LAST_REFRESH_DATE REFRESH_ S FULLREFRESHTIM INCREFRESHTIM
------------------------------ ------------------- -------- - -------------- -------------
PADRQCLIENTGROUPRELATION 23.10.2009 22:05:34 FORCE Y 79 0
VCLM_TRANSACTION 23.10.2009 15:55:16 FORCE Y 310 0
CLM_RELATIONSHIP_TYPE 17.10.2009 11:12:03 FAST Y 2 0
CLM_RELATIONSHIP_DETAILS_LOG 23.10.2009 14:42:57 FAST Y 3671 0
CLM_RELATIONSHIP_DETAILS 23.10.2009 13:28:28 FAST Y 3902 0
CLM_GROUP_LOG 23.10.2009 13:22:19 FAST N 230 0
CLM_GROUP 23.10.2009 13:16:51 FAST Y 271 0
PADRQULTIMATEPARENTGROUP 23.10.2009 22:05:16 FORCE Y 18 0
column "MVIEW BEING REFRESHED" format a30
column INSERTS format 9999999
column UPDATES format 9999999
column DELETES format 9999999
select CURRMVOWNER_KNSTMVR || '.' || CURRMVNAME_KNSTMVR "MVIEW BEING REFRESHED",
decode( REFTYPE_KNSTMVR, 1, 'FAST', 2, 'COMPLETE', 'UNKNOWN' ) REFTYPE,
decode(GROUPSTATE_KNSTMVR, 1, 'SETUP', 2, 'INSTANTIATE', 3, 'WRAPUP', 'UNKNOWN' ) STATE,
TOTAL_INSERTS_KNSTMVR INSERTS, TOTAL_UPDATES_KNSTMVR UPDATES,
TOTAL_DELETES_KNSTMVR DELETES from X$KNSTMVR X
WHERE type_knst=6 and exists (select 1 from v$session s
where s.sid=x.sid_knst and s.serial#=x.serial_knst);
MVIEW BEING REFRESHED REFTYPE STATE INSERTS UPDATES DELETES
-------------------------------------------------- -------- ----------- ---------- ---------- ----------
SCC.CLM_RELATIONSHIP_DETAILS_LOG FAST WRAPUP 0 0 0
SQL> select * from DBA_MVIEW_REFRESH_TIMES;
OWNER NAME MASTER_OWNE MASTER LAST_REFRESH
------ ------------------------------ ----------- ------------------------------ -------------------
SCC PADRQULTIMATEPARENTGROUP SCC NATIVEULTIMATEPARENTGROUP 26.10.2009 22:08:22
SCC CLM_GROUP CGSOWNER GROUP 23.10.2009 12:16:50
SCC CLM_GROUP_LOG CGSOWNER GROUP_LOG 23.10.2009 12:22:19
SCC CLM_RELATIONSHIP_DETAILS CGSOWNER RELATIONSHIP_DETAILS 23.10.2009 12:28:28
SCC CLM_RELATIONSHIP_DETAILS_LOG CGSOWNER RELATIONSHIP_DETAILS_LOG 23.10.2009 13:42:57
SCC CLM_RELATIONSHIP_TYPE CGSOWNER RELATIONSHIP_TYPE 17.10.2009 10:12:02
SCC VCLM_TRANSACTION SCC VCLM_EVENT 23.10.2009 15:55:16
SCC PADRQCLIENTGROUPRELATION SCC NATIVECLIENT 26.10.2009 22:08:37
SQL> col owner form a9
SQL> select l.snapshot_id, owner, name, substr(snapshot_site,1,30) snapshot_site,
to_char(current_snapshots, 'mm/dd/yyyy hh24:mi:ss') current_snapshots
from dba_registered_snapshots r, dba_snapshot_logs l
where r.snapshot_id = l.snapshot_id (+)
SNAPSHOT_ID OWNER NAME SNAPSHOT_SITE CURRENT_SNAPSHOTS
----------- --------- ------------------------------ ------------------------------ -------------------
SCC VCLM_TRANSACTION SC
SCC PADRQULTIMATEPARENTGROUP SC
SCC PADRQCLIENTGROUPRELATION SC
SQL> col mview format a40
SQL> col master format a40
SQL> select owner || '.' || name mview, master_owner || '.' || master master from dba_mview_refresh_times;
MVIEW MASTER
---------------------------------------- ----------------------------------------
SCC.PADRQULTIMATEPARENTGROUP SCC.NATIVEULTIMATEPARENTGROUP
SCC.CLM_GROUP CGSOWNER.GROUP
SCC.CLM_GROUP_LOG CGSOWNER.GROUP_LOG
SCC.CLM_RELATIONSHIP_DETAILS CGSOWNER.RELATIONSHIP_DETAILS
SCC.CLM_RELATIONSHIP_DETAILS_LOG CGSOWNER.RELATIONSHIP_DETAILS_LOG
SCC.CLM_RELATIONSHIP_TYPE CGSOWNER.RELATIONSHIP_TYPE
SCC.VCLM_TRANSACTION SCC.VCLM_EVENT
SCC.PADRQCLIENTGROUPRELATION SCC.NATIVECLIENT
-- was wird gerade refresht.
SQL> select * from sys.v_$mvrefresh;
SID SERIAL# CURRMVOWNER CURRMVNAME
---------- ---------- ------------------------------- -------------------------------
1298 31569 SCC CLM_RELATIONSHIP_TYPE
SQL> select sql_text from v$session ses, v$sqlarea sql where ses.sql_hash_value = sql.hash_value(+) and
ses.sql_address = sql.address(+) and ses.sid=1298;
SQL_TEXT
----------------------------------------------------------------------------------------------------
BEGIN dbms_mview.refresh (:1, :2); END;
-- beim hängen vom MView kann auch der Process hängen.
Lösung: PID auf OS finden und mit kill entfernen und mit
SQL> alter system kill session '1298,31569’;
SQL> select SID,SERIAL#,PADDR,USERNAME,MACHINE,PROGRAM,LOGON_TIME from v$session where sid
in (select sid from v$lock where id1 in (select id1 from v$lock where block=1));
SID SERIAL# PADDR USERNAME MACHINE PROGRAM LOGON_TIME
---------- ---------- ---------------- -------- ----------------------------------- -----------------
1227 32422 070000005ADBC988 SCC km78 27.10.09 06:03:47
1265 43883 070000005ADB1C48 SCC km78 27.10.09 06:03:16
1298 31569 070000005ADB72E8 SCC km78 17.10.09 13:08:07
SQL> select sql_text from v$session ses, v$sqlarea sql where ses.sql_hash_value = sql.hash_value(+) and
ses.sql_address = sql.address(+) and ses.sid=1298;
SQL_TEXT
---------------------------------------
BEGIN dbms_mview.refresh (:1, :2); END;
-- Welcher Betriebssystem-Prozess gehört zur blockierenden Session ?
SQL> select a.sid,b.spid from v$session a,v$process b where a.paddr=b.addr and sid in (select sid from v$lock where block=1) ;
SID SPID
---------- ------------
913 1552614
-- Complet Refresh von einer View auf CommandLine:
Manually start a refresh
execute dbms_mview.refresh ('<owner.mv_table>');
Force a complete refresh
execute dbms_mview.refresh ('<owner.mv_table>','C');
SQL> execute dbms_mview.refresh('SCC.CLM_GROUP_LOG','c');
PL/SQL procedure successfully completed.
-- View die Information über die MV liefern.
select * from all_refresh;
select * from all_refresh_children;
select * from sys.v_$mvrefresh;
Internet Links:
http://www.ordix.de/ORDIXNews/2_2002/db_2.html
http://www.techienuggets.com/Detail?tx=38483
http://www.orafaq.com/wiki/Oracle_Materialized_Views