[Home] [Help]
select s.sowner as OWNER, s.vname as MVIEW_NAME, s.tname as CONTAINER_NAME,
s.query_txt as QUERY, s.query_len as QUERY_LEN,
decode(bitand(s.flag,2), 0, 'N', 'Y') as UPDATABLE, /* updatable */
s.uslog as UPDATE_LOG, s.mas_roll_seg as MASTER_ROLLBACK_SEG, s.mlink as MASTER_LINK,
decode(w.mflags,
'', '', /* missing summary */
decode(bitand(w.mflags, 4), 4, 'N', 'Y')) as REWRITE_ENABLED,
/* rewrite capability
* KKQS_NOGR_PFLAGS:
* QSMG_SUM_PART_EXT_NAME + QSMG_SUM_CONNECT_BY +
* QSMG_SUM_RAW_OUTPUT + QSMG_SUM_SUBQUERY_HAVING +
* QSMG_SUM_SUBQUERY_WHERE + QSMG_SUM_SET_OPERATOR +
* QSMG_SUM_NESTED_CURSOR + QSMG_SUM_OUT_MISSING_GRPCOL +
* QSMG_SUM_AGGREGATE_NOT_TOP
*
* KKQS_NOGR_XPFLAGS:
* QSMG_SUM_WCLS
*
* QSMG_SUM_DATA_IGNORE - 2nd-class summary
*/
decode(w.pflags,
'', '', /* missing summary */
decode(bitand(w.pflags, 1073741824), /* 2nd-class summary */
1073741824, 'NONE',
/* 2152929292 = 2147483648 + 2048 + 4096 + 65536 + 131072 +
* 1048576 + 4194304 + 8 + 4
*/
decode(bitand(w.pflags, 2152929292),
0, decode(bitand(w.xpflags, 8192),
8192, 'TEXTMATCH',
'GENERAL'),
'TEXTMATCH'))) as REWRITE_CAPABILITY,
decode(s.auto_fast,
'N', 'NEVER',
decode(bitand(s.flag, 32768), 0, 'DEMAND', 'COMMIT')) as REFRESH_MODE,
decode(s.auto_fast, /* refresh method */
'C', 'COMPLETE',
'F', 'FAST',
'?', 'FORCE',
'N', 'NEVER',
NULL, 'FORCE', 'ERROR') as REFRESH_METHOD,
decode(bitand(s.flag, 131072), /* build mode */
131072, 'PREBUILT',
decode(bitand(s.flag, 524288), 0, 'IMMEDIATE', 'DEFERRED')) as BUILD_MODE,
/* fast refreshable
* rowid+primary key+object id+subquery+complex+MAV+MJV+MAV1
* 536900016 = 16+32+536870912+128+256+4096+8192+16384
*/
decode(bitand(s.flag2, 67108864),
67108864,
/* if primary CUBE MV, use its secondary MV's flag value to
* determine its FAST REFRESHABILITY. */
(decode(bitand((select s2.flag from sys.snap$ s2
where s2.parent_sowner=s.sowner and s2.parent_vname=s.vname), 536900016),
16, 'DIRLOAD_DML', /* rowid */
32, 'DIRLOAD_DML', /* primary key */
536870912, 'DIRLOAD_DML', /* object id */
160, 'DIRLOAD_DML', /* subquery - has both the primary key */
/* bit and the subquery bit (32+128) */
536871040, 'DIRLOAD_DML', /* subquery - has both the object id bit */
/* and the subquery bit (536870912+128) */
256, 'NO', /* complex */
4096, decode(bitand(s.flag2,23), /* KKZFAGG_INSO */
0,
'DIRLOAD_DML', /* regular MAV */
'DIRLOAD_LIMITEDDML'), /* insert only MAV */
8192, 'DIRLOAD_DML', /* MJV */
16384, 'DIRLOAD_DML', /* MAV1 */
decode(bitand(s.flag2, 16384),
16384, 'DIRLOAD_DML', /* UNION_ALL MV */
'ERROR'))),
decode(
bitand(s.flag, 536900016),
16, 'DIRLOAD_DML', /* rowid */
32, 'DIRLOAD_DML', /* primary key */
536870912, 'DIRLOAD_DML', /* object id */
160, 'DIRLOAD_DML', /* subquery - has both the primary key */
/* bit and the subquery bit (32+128) */
536871040, 'DIRLOAD_DML', /* subquery - has both the object id bit */
/* and the subquery bit (536870912+128) */
256, 'NO', /* complex */
4096, decode(bitand(s.flag2,23), /* KKZFAGG_INSO */
0,
'DIRLOAD_DML', /* regular MAV */
'DIRLOAD_LIMITEDDML'), /* insert only MAV */
8192, 'DIRLOAD_DML', /* MJV */
16384, 'DIRLOAD_DML', /* MAV1 */
decode(bitand(s.flag2, 16384),
16384, 'DIRLOAD_DML', /* UNION_ALL MV */
'ERROR'))) as FAST_REFRESHABLE,
/* fixing bug 923186 */
decode(w.mflags, /*last refresh type */
'','', /*missing summary */
decode(bitand(w.mflags,16384+32768+4194304+1073741824),
0, 'NA',
16384, 'COMPLETE',
32768, 'FAST',
4194304, 'FAST_PCT',
1073741824, 'FAST_CS',
'ERROR')) as LAST_REFRESH_TYPE,
/* end fixing bug 923186 */
/* the last refresh date should be of date type and not varchar,
** SO BE CAREFUL WITH CHANGES IN THE FOLLOWING DECODE
*/
decode(w.lastrefreshdate, /* last refresh date */
NULL, to_date(NULL, 'DD-MON-YYYY'), /* missing summary */
decode(to_char(w.lastrefreshdate,'DD-MM-YYYY'),'01-01-1950',
to_date(NULL, 'DD-MON-YYYY'), w.lastrefreshdate)) as LAST_REFRESH_DATE,
/* fixing bug 14116743 */
decode(w.mflags, /*last refresh end time */
NULL, to_date(NULL, 'DD-MON-YYYY'),
decode(bitand(w.mflags,16384+32768+4194304+1073741824),
0, to_date(NULL, 'DD-MON-YYYY'),
/* complete refresh */
16384, w.lastrefreshdate+w.fullrefreshtim/(24*60*60),
/* fast refresh */
32768, w.lastrefreshdate+w.increfreshtim/(24*60*60),
/* PCT refresh */
4194304, w.lastrefreshdate+w.increfreshtim/(24*60*60),
/* cube fastsolve refresh (treated as complete refresh) */
1073741824, w.lastrefreshdate+w.fullrefreshtim/(24*60*60),
to_date(NULL, 'DD-MON-YYYY'))) as LAST_REFRESH_END_TIME,
/* staleness */
decode(NVL(s.mlink,'null'), /* not null implies remote */
'null', decode(bitand(s.status, 4), /* snapshot-invalid */
4, 'UNUSABLE',
decode(o.status,
1, decode(w.mflags,
'', '', /* missing summary */
decode(bitand(w.mflags, 8388608),
8388608, 'IMPORT', /* mv imported */
decode(bitand(w.mflags, 64), /* wh-unusable */
64, 'UNUSABLE', /* unusable */
decode(bitand(w.mflags, 32),
0, /* unknown */
/* known stale */ decode(bitand(w.mflags, 1),
0, 'FRESH',
'STALE'), 'UNKNOWN')))),
2, 'AUTHORIZATION_ERROR',
3, 'COMPILATION_ERROR',
5, 'NEEDS_COMPILE',
'ERROR')),
'UNDEFINED') as STALENESS, /* remote MV */
/* after fast refresh */
/* in the decode for after fast refresh, we only have to check
* whether w.mflags is null once. all of the other occurences
* fall under the first check. if the summary information is not
* null, we need to check for the warehouse unusable condition
* before we check to see if the MV is complex. if the summary
* information is null, we still need to check whether the MV
* is complex.
*/
decode(NVL(s.mlink,'null'), /* remote */
'null', decode(s.auto_fast, /* never refresh */
'N', 'NA',
decode(bitand(s.flag, 32768), /* on commit */
32768, 'NA',
decode(bitand(s.status, 4), /* snap-invalid */
4, 'NA',
decode(w.mflags, /* missing summary */
'', decode(bitand(s.flag, 256), /* complex */
256, 'NA',
''),
decode(o.status,
1, decode(bitand(w.mflags, 8388608),
8388608, 'UNKNOWN', /* imported */
/* warehouse unusable */ decode(bitand(w.mflags, 64),
64, 'NA',
decode(bitand(s.flag, 256), /*complex*/
256, 'NA',
/* unknown */ decode(bitand(w.mflags,32),
32, 'UNKNOWN',
/* known stale */ decode(bitand(w.mflags, 1),
0, 'FRESH',
/* stale states (on-demand only)
* (This decode is the default clause for the known-stale
* decode statement. It should be indented there, but there
* isn't enough room.)
*/
decode(bitand(s.flag, 176), /* ri+pk+sq */
/* 16+32+128 */
0, decode(bitand(s.flag, 28672), /* mjv+mav1+mav */
/* 8192+16384+4096 */
0, 'ERROR', /* no mv type */
/* mjv/mav/mav1 MV */ decode(bitand(w.mflags, 1576832),
/* 1576832 = 128+256+512+1024+2048+524288+1048576*/
/*si + su + lsi + lsu + sf + sp + spu */
128, 'FRESH', /* si */
256, 'UNKNOWN', /* su */
512, 'STALE', /* sf */
1024, 'FRESH', /* lsi */
2048, 'UNKNOWN', /* lsu */
524288, 'FRESH', /* sp */
1048576, 'UNKNOWN', /* spu */
/* 128+1024 */ 1152, 'FRESH', /* si+lsi*/
/* 256+2048 */ 2304, 'UNKNOWN', /* su+lsu*/
'ERROR')),
/* ri or pk or sq MV */ decode(bitand(w.mflags, 1576832),
/* 1576832 = 128+256+512+1024+2048+524288+1048576 */
128, 'STALE', /* si */
256, 'STALE', /* su */
512, 'STALE', /* sf */
1024, 'FRESH', /* lsi */
2048, 'UNKNOWN', /* lsu */
524288, 'FRESH', /* sp */
1048576, 'UNKNOWN', /* spu */
/* 128+1024 */ 1152, 'STALE', /* si+lsi*/
/* 256+2048 */ 2304, 'STALE', /* su+lsu*/
'ERROR'))))))),
2, 'AUTHORIZATION_ERROR',
3, 'COMPILATION_ERROR',
5, 'NEEDS_COMPILE',
'ERROR'))))),
'UNDEFINED') as AFTER_FAST_REFRESH, /* remote mv */
/* UNKNOWN_PREBUILT */
decode(w.pflags,
'','', /* missing summary */
decode(bitand(s.flag, 131072),
131072, 'Y', 'N')) as UNKNOWN_PREBUILT,
/* UNKNOWN_PLSQL_FUNC */
decode(w.pflags,
'','', /* missing summary */
decode(bitand(w.pflags, 268435456),
268435456, 'Y', 'N')) as UNKNOWN_PLSQL_FUNC,
/* UNKNOWN_EXTERNAL_TABLE */
decode(w.xpflags,
'','', /* missing summary */
decode(bitand(w.xpflags, 32768),
32768, 'Y', 'N')) as UNKNOWN_EXTERNAL_TABLE,
/* UNKNOWN_CONSIDER_FRESH */
decode(w.mflags,
'','', /* missing summary */
decode(bitand(w.mflags, 8192),
8192, 'Y', 'N')) as UNKNOWN_CONSIDER_FRESH,
/* UNKNOWN_IMPORT */
decode(w.mflags,
'','', /* missing summary */
decode(bitand(w.mflags, 8388608),
8388608, 'Y', 'N')) as UNKNOWN_IMPORT,
/* UNKNOWN_TRUSTED_FD */
decode(w.mflags,
'','', /* missing summary */
decode(bitand(w.mflags, 33554432),
33554432, 'Y', 'N')) as UNKNOWN_TRUSTED_FD,
decode(o.status,
1, 'VALID',
2, 'AUTHORIZATION_ERROR',
3, 'COMPILATION_ERROR',
5, 'NEEDS_COMPILE', 'ERROR') as COMPILE_STATE, /* compile st*/
decode(bitand(s.flag2,1024), 0, 'N', 'Y') as USE_NO_INDEX, /* USE NO INDEX ? */
(select min(TIME_DP) from sys.SMON_SCN_TIME
where (SCN_WRP*4294967295+ SCN_BAS) >
(select min(t.spare3)
from tab$ t, dependency$ d
where t.obj#= d.p_obj# and w.obj#=d.d_obj# and
t.spare3 > w.lastrefreshscn)) as STALE_SINCE,
/* whether this is a PCT refresh enabled primary CUBE MV */
(decode(bitand(w.xpflags, 8589934592), 0,
(select count(*) as num_pct_tables from
(select wd.sumobj#, wd.detailobj# from sys.sumdetail$ wd
where wd.detaileut > 0) wdd where wdd.sumobj# = w.obj#),
(select count(*) as num_pct_tables from
(select wd.sumobj#, wd.detailobj# from sys.sumdetail$ wd
where wd.detaileut > 2147483648 ) /* special secondary cube row */
wdd where wdd.sumobj# = w.obj#) )) as NUM_PCT_TABLES,
(select num_fresh_partns from
(select sumobj#, sum(num_fresh_partitions) as num_fresh_partns,
sum(num_stale_partitions) as num_stale_partns
from
(select sumobj#,
decode(partn_state, 'FRESH', partn_count, 0)
as num_fresh_partitions,
decode(partn_state, 'STALE', partn_count, 0)
as num_stale_partitions
from
(select sumobj#, partn_state, count(*) as partn_count from
(select sumobj#,
(case when partn_scn is NULL then 'FRESH'
when partn_scn < mv_scn
then 'FRESH' else 'STALE' end) partn_state
from
(select sumobj#, mv_scn, sub_pobj#, max(partn_scn) partn_scn
from
/* from tabpart$ */
(select s.obj# as sumobj#, s.lastrefreshscn as mv_scn,
t.obj# pobj#, t.obj# as sub_pobj#,
t.spare1 as partn_scn
from sys.sum$ s, sys.sumdetail$ sd, sys.tabpart$ t
where s.obj# = sd.sumobj# and sd.detailobj# = t.bo#
and bitand(sd.detaileut, 2147483648) = 0
/* NO secondary CUBE MV rows */
union /* from sumdelta$ */
select s.obj# as sumobj#, s.lastrefreshscn as mv_scn,
t.tableobj# pobj#, t.spare2 as sub_pobj#,
t.scn as partn_scn
from sys.sum$ s, sys.sumdetail$ sd, sys.sumdelta$ t
where s.obj# = sd.sumobj# and sd.detailobj# = t.tableobj#
and bitand(sd.detaileut, 2147483648) = 0
/* NO secondary CUBE MV rows */
union /* from tabsubpart$ */
select s.sumobj#, s.mv_scn, s.pobj# pobj#,
t.obj# as sub_pobj#,t.spare1 as partn_scn
from tabsubpart$ t,
(select s.obj# as sumobj#, s.lastrefreshscn as mv_scn,
t.obj# pobj#, t.spare1 as partn_scn
from sys.sum$ s, sys.sumdetail$ sd, sys.tabcompart$ t
where s.obj# = sd.sumobj# and sd.detailobj# = t.bo#
and bitand(sd.detaileut, 2147483648) = 0
/* NO secondary CUBE MV rows */) s
where t.pobj# = s.pobj#)
group by sumobj#,mv_scn, sub_pobj#)
)
group by sumobj#, partn_state)) group by sumobj#) nfsp
where nfsp.sumobj# = w.obj#) as NUM_FRESH_PCT_REGIONS,
(select num_stale_partns from
(select sumobj#, sum(num_fresh_partitions) as num_fresh_partns,
sum(num_stale_partitions) as num_stale_partns
from
(select sumobj#,
decode(partn_state, 'FRESH', partn_count, 0)
as num_fresh_partitions,
decode(partn_state, 'STALE', partn_count, 0)
as num_stale_partitions
from
(select sumobj#, partn_state, count(*) as partn_count from
(select sumobj#,
(case when partn_scn is NULL then 'FRESH'
when partn_scn < mv_scn
then 'FRESH' else 'STALE' end) partn_state
from
(select sumobj#, mv_scn, sub_pobj#, max(partn_scn) partn_scn
from
/* from tabpart$ */
(select s.obj# as sumobj#, s.lastrefreshscn as mv_scn,
t.obj# pobj#, t.obj# as sub_pobj#,
t.spare1 as partn_scn
from sys.sum$ s, sys.sumdetail$ sd, sys.tabpart$ t
where s.obj# = sd.sumobj# and sd.detailobj# = t.bo#
and bitand(sd.detaileut, 2147483648) = 0
/* NO secondary CUBE MV rows */
union /* from sumdelta$ */
select s.obj# as sumobj#, s.lastrefreshscn as mv_scn,
t.tableobj# pobj#, t.spare2 as sub_pobj#,
t.scn as partn_scn
from sys.sum$ s, sys.sumdetail$ sd, sys.sumdelta$ t
where s.obj# = sd.sumobj# and sd.detailobj# = t.tableobj#
and bitand(sd.detaileut, 2147483648) = 0
/* NO secondary CUBE MV rows */
union /* from tabsubpart$ */
select s.sumobj#, s.mv_scn, s.pobj# pobj#,
t.obj# as sub_pobj#,t.spare1 as partn_scn
from tabsubpart$ t,
(select s.obj# as sumobj#, s.lastrefreshscn as mv_scn,
t.obj# pobj#, t.spare1 as partn_scn
from sys.sum$ s, sys.sumdetail$ sd, sys.tabcompart$ t
where s.obj# = sd.sumobj# and sd.detailobj# = t.bo#
and bitand(sd.detaileut, 2147483648) = 0
/* NO secondary CUBE MV rows */) s
where t.pobj# = s.pobj#)
group by sumobj#,mv_scn, sub_pobj#)
)
group by sumobj#, partn_state)) group by sumobj#) nfsp
where nfsp.sumobj# = w.obj#) as NUM_STALE_PCT_REGIONS,
decode(bitand(t.property, 17179869184), 17179869184, 'NO',
decode(bitand(t.property, 32), 32, 'N/A', 'YES')),
s.eval_edition,
case when w.unusablebefore# is null then null
else (select name from obj$ where obj# = w.unusablebefore#) end,
case when w.unusablebeginning# is null then null
else (select name from obj$ where obj# = w.unusablebeginning#) end
from sys.user$ u, sys.sum$ w, sys.obj$ o, sys.snap$ s, sys.tab$ t, sys.obj$ co
where w.containernam(+) = s.vname
and w.containerobj# = co.obj#(+)
and co.obj# = t.obj#(+)
and o.obj#(+) = w.obj#
and o.owner# = u.user#(+)
and ((u.name = s.sowner) or (u.name IS NULL))
and s.instsite = 0
and not (bitand(s.flag, 268435456) > 0 /* MV with user-defined types */
and bitand(s.objflag, 32) > 0) /* secondary MV */
and not (bitand(s.flag2, 33554432) > 0) /* secondary CUBE MV */
and not (bitand(s.flag3, 512) > 0) /* zonemap */
SELECT S.SOWNER AS OWNER
, S.VNAME AS MVIEW_NAME
, S.TNAME AS CONTAINER_NAME
,
S.QUERY_TXT AS QUERY
, S.QUERY_LEN AS QUERY_LEN
,
DECODE(BITAND(S.FLAG
, 2)
, 0
, 'N'
, 'Y') AS UPDATABLE
, /* UPDATABLE */
S.USLOG AS UPDATE_LOG
, S.MAS_ROLL_SEG AS MASTER_ROLLBACK_SEG
, S.MLINK AS MASTER_LINK
,
DECODE(W.MFLAGS
,
''
, ''
, /* MISSING SUMMARY */
DECODE(BITAND(W.MFLAGS
, 4)
, 4
, 'N'
, 'Y')) AS REWRITE_ENABLED
,
/* REWRITE CAPABILITY
* KKQS_NOGR_PFLAGS:
* QSMG_SUM_PART_EXT_NAME + QSMG_SUM_CONNECT_BY +
* QSMG_SUM_RAW_OUTPUT + QSMG_SUM_SUBQUERY_HAVING +
* QSMG_SUM_SUBQUERY_WHERE + QSMG_SUM_SET_OPERATOR +
* QSMG_SUM_NESTED_CURSOR + QSMG_SUM_OUT_MISSING_GRPCOL +
* QSMG_SUM_AGGREGATE_NOT_TOP
*
* KKQS_NOGR_XPFLAGS:
* QSMG_SUM_WCLS
*
* QSMG_SUM_DATA_IGNORE - 2ND-CLASS SUMMARY
*/
DECODE(W.PFLAGS
,
''
, ''
, /* MISSING SUMMARY */
DECODE(BITAND(W.PFLAGS
, 1073741824)
, /* 2ND-CLASS SUMMARY */
1073741824
, 'NONE'
,
/* 2152929292 = 2147483648 + 2048 + 4096 + 65536 + 131072 +
* 1048576 + 4194304 + 8 + 4
*/
DECODE(BITAND(W.PFLAGS
, 2152929292)
,
0
, DECODE(BITAND(W.XPFLAGS
, 8192)
,
8192
, 'TEXTMATCH'
,
'GENERAL')
,
'TEXTMATCH'))) AS REWRITE_CAPABILITY
,
DECODE(S.AUTO_FAST
,
'N'
, 'NEVER'
,
DECODE(BITAND(S.FLAG
, 32768)
, 0
, 'DEMAND'
, 'COMMIT')) AS REFRESH_MODE
,
DECODE(S.AUTO_FAST
, /* REFRESH METHOD */
'C'
, 'COMPLETE'
,
'F'
, 'FAST'
,
'?'
, 'FORCE'
,
'N'
, 'NEVER'
,
NULL
, 'FORCE'
, 'ERROR') AS REFRESH_METHOD
,
DECODE(BITAND(S.FLAG
, 131072)
, /* BUILD MODE */
131072
, 'PREBUILT'
,
DECODE(BITAND(S.FLAG
, 524288)
, 0
, 'IMMEDIATE'
, 'DEFERRED')) AS BUILD_MODE
,
/* FAST REFRESHABLE
* ROWID+PRIMARY KEY+OBJECT ID+SUBQUERY+COMPLEX+MAV+MJV+MAV1
* 536900016 = 16+32+536870912+128+256+4096+8192+16384
*/
DECODE(BITAND(S.FLAG2
, 67108864)
,
67108864
,
/* IF PRIMARY CUBE MV
, USE ITS SECONDARY MV'S FLAG VALUE TO
* DETERMINE ITS FAST REFRESHABILITY. */
(DECODE(BITAND((SELECT S2.FLAG
FROM SYS.SNAP$ S2
WHERE S2.PARENT_SOWNER=S.SOWNER
AND S2.PARENT_VNAME=S.VNAME)
, 536900016)
,
16
, 'DIRLOAD_DML'
, /* ROWID */
32
, 'DIRLOAD_DML'
, /* PRIMARY KEY */
536870912
, 'DIRLOAD_DML'
, /* OBJECT ID */
160
, 'DIRLOAD_DML'
, /* SUBQUERY - HAS BOTH THE PRIMARY KEY */
/* BIT
AND THE SUBQUERY BIT (32+128) */
536871040
, 'DIRLOAD_DML'
, /* SUBQUERY - HAS BOTH THE OBJECT ID BIT */
/*
AND THE SUBQUERY BIT (536870912+128) */
256
, 'NO'
, /* COMPLEX */
4096
, DECODE(BITAND(S.FLAG2
, 23)
, /* KKZFAGG_INSO */
0
,
'DIRLOAD_DML'
, /* REGULAR MAV */
'DIRLOAD_LIMITEDDML')
, /* INSERT ONLY MAV */
8192
, 'DIRLOAD_DML'
, /* MJV */
16384
, 'DIRLOAD_DML'
, /* MAV1 */
DECODE(BITAND(S.FLAG2
, 16384)
,
16384
, 'DIRLOAD_DML'
, /* UNION_ALL MV */
'ERROR')))
,
DECODE(
BITAND(S.FLAG
, 536900016)
,
16
, 'DIRLOAD_DML'
, /* ROWID */
32
, 'DIRLOAD_DML'
, /* PRIMARY KEY */
536870912
, 'DIRLOAD_DML'
, /* OBJECT ID */
160
, 'DIRLOAD_DML'
, /* SUBQUERY - HAS BOTH THE PRIMARY KEY */
/* BIT
AND THE SUBQUERY BIT (32+128) */
536871040
, 'DIRLOAD_DML'
, /* SUBQUERY - HAS BOTH THE OBJECT ID BIT */
/*
AND THE SUBQUERY BIT (536870912+128) */
256
, 'NO'
, /* COMPLEX */
4096
, DECODE(BITAND(S.FLAG2
, 23)
, /* KKZFAGG_INSO */
0
,
'DIRLOAD_DML'
, /* REGULAR MAV */
'DIRLOAD_LIMITEDDML')
, /* INSERT ONLY MAV */
8192
, 'DIRLOAD_DML'
, /* MJV */
16384
, 'DIRLOAD_DML'
, /* MAV1 */
DECODE(BITAND(S.FLAG2
, 16384)
,
16384
, 'DIRLOAD_DML'
, /* UNION_ALL MV */
'ERROR'))) AS FAST_REFRESHABLE
,
/* FIXING BUG 923186 */
DECODE(W.MFLAGS
, /*LAST REFRESH TYPE */
''
, ''
, /*MISSING SUMMARY */
DECODE(BITAND(W.MFLAGS
, 16384+32768+4194304+1073741824)
,
0
, 'NA'
,
16384
, 'COMPLETE'
,
32768
, 'FAST'
,
4194304
, 'FAST_PCT'
,
1073741824
, 'FAST_CS'
,
'ERROR')) AS LAST_REFRESH_TYPE
,
/* END FIXING BUG 923186 */
/* THE LAST REFRESH DATE SHOULD BE OF DATE TYPE
AND NOT VARCHAR
,
** SO BE CAREFUL WITH CHANGES IN THE FOLLOWING DECODE
*/
DECODE(W.LASTREFRESHDATE
, /* LAST REFRESH DATE */
NULL
, TO_DATE(NULL
, 'DD-MON-YYYY')
, /* MISSING SUMMARY */
DECODE(TO_CHAR(W.LASTREFRESHDATE
, 'DD-MM-YYYY')
, '01-01-1950'
,
TO_DATE(NULL
, 'DD-MON-YYYY')
, W.LASTREFRESHDATE)) AS LAST_REFRESH_DATE
,
/* FIXING BUG 14116743 */
DECODE(W.MFLAGS
, /*LAST REFRESH END TIME */
NULL
, TO_DATE(NULL
, 'DD-MON-YYYY')
,
DECODE(BITAND(W.MFLAGS
, 16384+32768+4194304+1073741824)
,
0
, TO_DATE(NULL
, 'DD-MON-YYYY')
,
/* COMPLETE REFRESH */
16384
, W.LASTREFRESHDATE+W.FULLREFRESHTIM/(24*60*60)
,
/* FAST REFRESH */
32768
, W.LASTREFRESHDATE+W.INCREFRESHTIM/(24*60*60)
,
/* PCT REFRESH */
4194304
, W.LASTREFRESHDATE+W.INCREFRESHTIM/(24*60*60)
,
/* CUBE FASTSOLVE REFRESH (TREATED AS COMPLETE REFRESH) */
1073741824
, W.LASTREFRESHDATE+W.FULLREFRESHTIM/(24*60*60)
,
TO_DATE(NULL
, 'DD-MON-YYYY'))) AS LAST_REFRESH_END_TIME
,
/* STALENESS */
DECODE(NVL(S.MLINK
, 'NULL')
, /* NOT NULL IMPLIES REMOTE */
'NULL'
, DECODE(BITAND(S.STATUS
, 4)
, /* SNAPSHOT-INVALID */
4
, 'UNUSABLE'
,
DECODE(O.STATUS
,
1
, DECODE(W.MFLAGS
,
''
, ''
, /* MISSING SUMMARY */
DECODE(BITAND(W.MFLAGS
, 8388608)
,
8388608
, 'IMPORT'
, /* MV IMPORTED */
DECODE(BITAND(W.MFLAGS
, 64)
, /* WH-UNUSABLE */
64
, 'UNUSABLE'
, /* UNUSABLE */
DECODE(BITAND(W.MFLAGS
, 32)
,
0
, /* UNKNOWN */
/* KNOWN STALE */ DECODE(BITAND(W.MFLAGS
, 1)
,
0
, 'FRESH'
,
'STALE')
, 'UNKNOWN'))))
,
2
, 'AUTHORIZATION_ERROR'
,
3
, 'COMPILATION_ERROR'
,
5
, 'NEEDS_COMPILE'
,
'ERROR'))
,
'UNDEFINED') AS STALENESS
, /* REMOTE MV */
/* AFTER FAST REFRESH */
/* IN THE DECODE FOR AFTER FAST REFRESH
, WE ONLY HAVE TO CHECK
* WHETHER W.MFLAGS IS NULL ONCE. ALL OF THE OTHER OCCURENCES
* FALL UNDER THE FIRST CHECK. IF THE SUMMARY INFORMATION IS NOT
* NULL
, WE NEED TO CHECK FOR THE WAREHOUSE UNUSABLE CONDITION
* BEFORE WE CHECK TO SEE IF THE MV IS COMPLEX. IF THE SUMMARY
* INFORMATION IS NULL
, WE STILL NEED TO CHECK WHETHER THE MV
* IS COMPLEX.
*/
DECODE(NVL(S.MLINK
, 'NULL')
, /* REMOTE */
'NULL'
, DECODE(S.AUTO_FAST
, /* NEVER REFRESH */
'N'
, 'NA'
,
DECODE(BITAND(S.FLAG
, 32768)
, /* ON COMMIT */
32768
, 'NA'
,
DECODE(BITAND(S.STATUS
, 4)
, /* SNAP-INVALID */
4
, 'NA'
,
DECODE(W.MFLAGS
, /* MISSING SUMMARY */
''
, DECODE(BITAND(S.FLAG
, 256)
, /* COMPLEX */
256
, 'NA'
,
'')
,
DECODE(O.STATUS
,
1
, DECODE(BITAND(W.MFLAGS
, 8388608)
,
8388608
, 'UNKNOWN'
, /* IMPORTED */
/* WAREHOUSE UNUSABLE */ DECODE(BITAND(W.MFLAGS
, 64)
,
64
, 'NA'
,
DECODE(BITAND(S.FLAG
, 256)
, /*COMPLEX*/
256
, 'NA'
,
/* UNKNOWN */ DECODE(BITAND(W.MFLAGS
, 32)
,
32
, 'UNKNOWN'
,
/* KNOWN STALE */ DECODE(BITAND(W.MFLAGS
, 1)
,
0
, 'FRESH'
,
/* STALE STATES (ON-DEMAND ONLY)
* (THIS DECODE IS THE DEFAULT CLAUSE FOR THE KNOWN-STALE
* DECODE STATEMENT. IT SHOULD BE INDENTED THERE
, BUT THERE
* ISN'T ENOUGH ROOM.)
*/
DECODE(BITAND(S.FLAG
, 176)
, /* RI+PK+SQ */
/* 16+32+128 */
0
, DECODE(BITAND(S.FLAG
, 28672)
, /* MJV+MAV1+MAV */
/* 8192+16384+4096 */
0
, 'ERROR'
, /* NO MV TYPE */
/* MJV/MAV/MAV1 MV */ DECODE(BITAND(W.MFLAGS
, 1576832)
,
/* 1576832 = 128+256+512+1024+2048+524288+1048576*/
/*SI + SU + LSI + LSU + SF + SP + SPU */
128
, 'FRESH'
, /* SI */
256
, 'UNKNOWN'
, /* SU */
512
, 'STALE'
, /* SF */
1024
, 'FRESH'
, /* LSI */
2048
, 'UNKNOWN'
, /* LSU */
524288
, 'FRESH'
, /* SP */
1048576
, 'UNKNOWN'
, /* SPU */
/* 128+1024 */ 1152
, 'FRESH'
, /* SI+LSI*/
/* 256+2048 */ 2304
, 'UNKNOWN'
, /* SU+LSU*/
'ERROR'))
,
/* RI OR PK OR SQ MV */ DECODE(BITAND(W.MFLAGS
, 1576832)
,
/* 1576832 = 128+256+512+1024+2048+524288+1048576 */
128
, 'STALE'
, /* SI */
256
, 'STALE'
, /* SU */
512
, 'STALE'
, /* SF */
1024
, 'FRESH'
, /* LSI */
2048
, 'UNKNOWN'
, /* LSU */
524288
, 'FRESH'
, /* SP */
1048576
, 'UNKNOWN'
, /* SPU */
/* 128+1024 */ 1152
, 'STALE'
, /* SI+LSI*/
/* 256+2048 */ 2304
, 'STALE'
, /* SU+LSU*/
'ERROR')))))))
,
2
, 'AUTHORIZATION_ERROR'
,
3
, 'COMPILATION_ERROR'
,
5
, 'NEEDS_COMPILE'
,
'ERROR')))))
,
'UNDEFINED') AS AFTER_FAST_REFRESH
, /* REMOTE MV */
/* UNKNOWN_PREBUILT */
DECODE(W.PFLAGS
,
''
, ''
, /* MISSING SUMMARY */
DECODE(BITAND(S.FLAG
, 131072)
,
131072
, 'Y'
, 'N')) AS UNKNOWN_PREBUILT
,
/* UNKNOWN_PLSQL_FUNC */
DECODE(W.PFLAGS
,
''
, ''
, /* MISSING SUMMARY */
DECODE(BITAND(W.PFLAGS
, 268435456)
,
268435456
, 'Y'
, 'N')) AS UNKNOWN_PLSQL_FUNC
,
/* UNKNOWN_EXTERNAL_TABLE */
DECODE(W.XPFLAGS
,
''
, ''
, /* MISSING SUMMARY */
DECODE(BITAND(W.XPFLAGS
, 32768)
,
32768
, 'Y'
, 'N')) AS UNKNOWN_EXTERNAL_TABLE
,
/* UNKNOWN_CONSIDER_FRESH */
DECODE(W.MFLAGS
,
''
, ''
, /* MISSING SUMMARY */
DECODE(BITAND(W.MFLAGS
, 8192)
,
8192
, 'Y'
, 'N')) AS UNKNOWN_CONSIDER_FRESH
,
/* UNKNOWN_IMPORT */
DECODE(W.MFLAGS
,
''
, ''
, /* MISSING SUMMARY */
DECODE(BITAND(W.MFLAGS
, 8388608)
,
8388608
, 'Y'
, 'N')) AS UNKNOWN_IMPORT
,
/* UNKNOWN_TRUSTED_FD */
DECODE(W.MFLAGS
,
''
, ''
, /* MISSING SUMMARY */
DECODE(BITAND(W.MFLAGS
, 33554432)
,
33554432
, 'Y'
, 'N')) AS UNKNOWN_TRUSTED_FD
,
DECODE(O.STATUS
,
1
, 'VALID'
,
2
, 'AUTHORIZATION_ERROR'
,
3
, 'COMPILATION_ERROR'
,
5
, 'NEEDS_COMPILE'
, 'ERROR') AS COMPILE_STATE
, /* COMPILE ST*/
DECODE(BITAND(S.FLAG2
, 1024)
, 0
, 'N'
, 'Y') AS USE_NO_INDEX
, /* USE NO INDEX ? */
(SELECT MIN(TIME_DP)
FROM SYS.SMON_SCN_TIME
WHERE (SCN_WRP*4294967295+ SCN_BAS) >
(SELECT MIN(T.SPARE3)
FROM TAB$ T
, DEPENDENCY$ D
WHERE T.OBJ#= D.P_OBJ#
AND W.OBJ#=D.D_OBJ# AND
T.SPARE3 > W.LASTREFRESHSCN)) AS STALE_SINCE
,
/* WHETHER THIS IS A PCT REFRESH ENABLED PRIMARY CUBE MV */
(DECODE(BITAND(W.XPFLAGS
, 8589934592)
, 0
,
(SELECT COUNT(*) AS NUM_PCT_TABLES FROM
(SELECT WD.SUMOBJ#
, WD.DETAILOBJ#
FROM SYS.SUMDETAIL$ WD
WHERE WD.DETAILEUT > 0) WDD
WHERE WDD.SUMOBJ# = W.OBJ#)
,
(SELECT COUNT(*) AS NUM_PCT_TABLES FROM
(SELECT WD.SUMOBJ#
, WD.DETAILOBJ#
FROM SYS.SUMDETAIL$ WD
WHERE WD.DETAILEUT > 2147483648 ) /* SPECIAL SECONDARY CUBE ROW */
WDD
WHERE WDD.SUMOBJ# = W.OBJ#) )) AS NUM_PCT_TABLES
,
(SELECT NUM_FRESH_PARTNS FROM
(SELECT SUMOBJ#
, SUM(NUM_FRESH_PARTITIONS) AS NUM_FRESH_PARTNS
,
SUM(NUM_STALE_PARTITIONS) AS NUM_STALE_PARTNS
FROM
(SELECT SUMOBJ#
,
DECODE(PARTN_STATE
, 'FRESH'
, PARTN_COUNT
, 0)
AS NUM_FRESH_PARTITIONS
,
DECODE(PARTN_STATE
, 'STALE'
, PARTN_COUNT
, 0)
AS NUM_STALE_PARTITIONS
FROM
(SELECT SUMOBJ#
, PARTN_STATE
, COUNT(*) AS PARTN_COUNT FROM
(SELECT SUMOBJ#
,
(CASE WHEN PARTN_SCN IS NULL THEN 'FRESH'
WHEN PARTN_SCN < MV_SCN
THEN 'FRESH' ELSE 'STALE' END) PARTN_STATE
FROM
(SELECT SUMOBJ#
, MV_SCN
, SUB_POBJ#
, MAX(PARTN_SCN) PARTN_SCN
FROM
/*
FROM TABPART$ */
(SELECT S.OBJ# AS SUMOBJ#
, S.LASTREFRESHSCN AS MV_SCN
,
T.OBJ# POBJ#
, T.OBJ# AS SUB_POBJ#
,
T.SPARE1 AS PARTN_SCN
FROM SYS.SUM$ S
, SYS.SUMDETAIL$ SD
, SYS.TABPART$ T
WHERE S.OBJ# = SD.SUMOBJ#
AND SD.DETAILOBJ# = T.BO#
AND BITAND(SD.DETAILEUT
, 2147483648) = 0
/* NO SECONDARY CUBE MV ROWS */
UNION /*
FROM SUMDELTA$ */
SELECT S.OBJ# AS SUMOBJ#
, S.LASTREFRESHSCN AS MV_SCN
,
T.TABLEOBJ# POBJ#
, T.SPARE2 AS SUB_POBJ#
,
T.SCN AS PARTN_SCN
FROM SYS.SUM$ S
, SYS.SUMDETAIL$ SD
, SYS.SUMDELTA$ T
WHERE S.OBJ# = SD.SUMOBJ#
AND SD.DETAILOBJ# = T.TABLEOBJ#
AND BITAND(SD.DETAILEUT
, 2147483648) = 0
/* NO SECONDARY CUBE MV ROWS */
UNION /*
FROM TABSUBPART$ */
SELECT S.SUMOBJ#
, S.MV_SCN
, S.POBJ# POBJ#
,
T.OBJ# AS SUB_POBJ#
, T.SPARE1 AS PARTN_SCN
FROM TABSUBPART$ T
,
(SELECT S.OBJ# AS SUMOBJ#
, S.LASTREFRESHSCN AS MV_SCN
,
T.OBJ# POBJ#
, T.SPARE1 AS PARTN_SCN
FROM SYS.SUM$ S
, SYS.SUMDETAIL$ SD
, SYS.TABCOMPART$ T
WHERE S.OBJ# = SD.SUMOBJ#
AND SD.DETAILOBJ# = T.BO#
AND BITAND(SD.DETAILEUT
, 2147483648) = 0
/* NO SECONDARY CUBE MV ROWS */) S
WHERE T.POBJ# = S.POBJ#)
GROUP BY SUMOBJ#
, MV_SCN
, SUB_POBJ#)
)
GROUP BY SUMOBJ#
, PARTN_STATE)) GROUP BY SUMOBJ#) NFSP
WHERE NFSP.SUMOBJ# = W.OBJ#) AS NUM_FRESH_PCT_REGIONS
,
(SELECT NUM_STALE_PARTNS FROM
(SELECT SUMOBJ#
, SUM(NUM_FRESH_PARTITIONS) AS NUM_FRESH_PARTNS
,
SUM(NUM_STALE_PARTITIONS) AS NUM_STALE_PARTNS
FROM
(SELECT SUMOBJ#
,
DECODE(PARTN_STATE
, 'FRESH'
, PARTN_COUNT
, 0)
AS NUM_FRESH_PARTITIONS
,
DECODE(PARTN_STATE
, 'STALE'
, PARTN_COUNT
, 0)
AS NUM_STALE_PARTITIONS
FROM
(SELECT SUMOBJ#
, PARTN_STATE
, COUNT(*) AS PARTN_COUNT FROM
(SELECT SUMOBJ#
,
(CASE WHEN PARTN_SCN IS NULL THEN 'FRESH'
WHEN PARTN_SCN < MV_SCN
THEN 'FRESH' ELSE 'STALE' END) PARTN_STATE
FROM
(SELECT SUMOBJ#
, MV_SCN
, SUB_POBJ#
, MAX(PARTN_SCN) PARTN_SCN
FROM
/*
FROM TABPART$ */
(SELECT S.OBJ# AS SUMOBJ#
, S.LASTREFRESHSCN AS MV_SCN
,
T.OBJ# POBJ#
, T.OBJ# AS SUB_POBJ#
,
T.SPARE1 AS PARTN_SCN
FROM SYS.SUM$ S
, SYS.SUMDETAIL$ SD
, SYS.TABPART$ T
WHERE S.OBJ# = SD.SUMOBJ#
AND SD.DETAILOBJ# = T.BO#
AND BITAND(SD.DETAILEUT
, 2147483648) = 0
/* NO SECONDARY CUBE MV ROWS */
UNION /*
FROM SUMDELTA$ */
SELECT S.OBJ# AS SUMOBJ#
, S.LASTREFRESHSCN AS MV_SCN
,
T.TABLEOBJ# POBJ#
, T.SPARE2 AS SUB_POBJ#
,
T.SCN AS PARTN_SCN
FROM SYS.SUM$ S
, SYS.SUMDETAIL$ SD
, SYS.SUMDELTA$ T
WHERE S.OBJ# = SD.SUMOBJ#
AND SD.DETAILOBJ# = T.TABLEOBJ#
AND BITAND(SD.DETAILEUT
, 2147483648) = 0
/* NO SECONDARY CUBE MV ROWS */
UNION /*
FROM TABSUBPART$ */
SELECT S.SUMOBJ#
, S.MV_SCN
, S.POBJ# POBJ#
,
T.OBJ# AS SUB_POBJ#
, T.SPARE1 AS PARTN_SCN
FROM TABSUBPART$ T
,
(SELECT S.OBJ# AS SUMOBJ#
, S.LASTREFRESHSCN AS MV_SCN
,
T.OBJ# POBJ#
, T.SPARE1 AS PARTN_SCN
FROM SYS.SUM$ S
, SYS.SUMDETAIL$ SD
, SYS.TABCOMPART$ T
WHERE S.OBJ# = SD.SUMOBJ#
AND SD.DETAILOBJ# = T.BO#
AND BITAND(SD.DETAILEUT
, 2147483648) = 0
/* NO SECONDARY CUBE MV ROWS */) S
WHERE T.POBJ# = S.POBJ#)
GROUP BY SUMOBJ#
, MV_SCN
, SUB_POBJ#)
)
GROUP BY SUMOBJ#
, PARTN_STATE)) GROUP BY SUMOBJ#) NFSP
WHERE NFSP.SUMOBJ# = W.OBJ#) AS NUM_STALE_PCT_REGIONS
,
DECODE(BITAND(T.PROPERTY
, 17179869184)
, 17179869184
, 'NO'
,
DECODE(BITAND(T.PROPERTY
, 32)
, 32
, 'N/A'
, 'YES'))
,
S.EVAL_EDITION
,
CASE WHEN W.UNUSABLEBEFORE# IS NULL THEN NULL
ELSE (SELECT NAME
FROM OBJ$
WHERE OBJ# = W.UNUSABLEBEFORE#) END
,
CASE WHEN W.UNUSABLEBEGINNING# IS NULL THEN NULL
ELSE (SELECT NAME
FROM OBJ$
WHERE OBJ# = W.UNUSABLEBEGINNING#) END
FROM SYS.USER$ U
, SYS.SUM$ W
, SYS.OBJ$ O
, SYS.SNAP$ S
, SYS.TAB$ T
, SYS.OBJ$ CO
WHERE W.CONTAINERNAM(+) = S.VNAME
AND W.CONTAINEROBJ# = CO.OBJ#(+)
AND CO.OBJ# = T.OBJ#(+)
AND O.OBJ#(+) = W.OBJ#
AND O.OWNER# = U.USER#(+)
AND ((U.NAME = S.SOWNER) OR (U.NAME IS NULL))
AND S.INSTSITE = 0
AND NOT (BITAND(S.FLAG
, 268435456) > 0 /* MV WITH USER-DEFINED TYPES */
AND BITAND(S.OBJFLAG
, 32) > 0) /* SECONDARY MV */
AND NOT (BITAND(S.FLAG2
, 33554432) > 0) /* SECONDARY CUBE MV */
AND NOT (BITAND(S.FLAG3
, 512) > 0) /* ZONEMAP */
|
|
|
|