Skip to content

Instantly share code, notes, and snippets.

@dincosman

dincosman/dba_mviews.sql

Last active October 7, 2023 06:21
Show Gist options

  • Select an option

    Learn more about clone URLs
  • Save dincosman/2f07852f84bdbb8996e0803dbfa0c120 to your computer and use it in GitHub Desktop.

Select an option

Learn more about clone URLs
Save dincosman/2f07852f84bdbb8996e0803dbfa0c120 to your computer and use it in GitHub Desktop.
Download ZIP
Dba_mviews select query
Raw
dba_mviews.sql
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, decode(bitand(s.flag3, 67108864), 0, 'DEMAND', 'STATEMENT'), '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 * 4294967296 + 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,
nls_collation_name(nvl(o.dflcollid, 16382)),
/* ON_QUERY_COMPUTATION */
decode(bitand(s.flag3, 2097152), 0, 'N', 'Y') AS
on_query_computation
FROM
sys.user$ u,
sys.sum$ w,
sys."_CURRENT_EDITION_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 */;
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment