VIEW: SYS.ALL_SUMMARIES

Source

View Text - Preformatted

select u.name, o.name, u.name, s.containernam,
       s.lastrefreshscn, s.lastrefreshdate,
       decode (s.refreshmode, 0, 'NONE', 1, 'ANY', 2, 'INCREMENTAL', 3,'FULL'),
       decode(bitand(s.pflags, 25165824), 25165824, 'N', 'Y'),
       s.fullrefreshtim, s.increfreshtim,
       decode(bitand(s.pflags, 48), 0, 'N', 'Y'),
       decode(bitand(s.mflags, 64), 0, 'N', 'Y'), /* QSMQSUM_UNUSABLE */
       decode(bitand(s.pflags, 1294319), 0, 'Y', 'N'),
       decode(bitand((select n.flag2 from sys.snap$ n
                      where n.vname=s.containernam and n.sowner=u.name), 67108864),
                     67108864,  /* primary CUBE mv? */
                     decode(bitand((select n2.flag from sys.snap$ n2
                            where n2.parent_sowner=u.name and n2.parent_vname=s.containernam), 256),
                            256, 'N', 'Y'), /* Its child mv's properties determin INC_REFRESHABLE */
                     /* Bug 2070729: change mask value from 236879743 to
                      * 236617599 (excludes flag QSMG_SUM_DETAIL_IS_SNAPSHOT).
                      * Same chang applies to the INC_REFRESHABLE
                      * column in other views.
                      */
                     decode(bitand(s.pflags, 236617599), 0, 'Y', 'N')),
       decode(bitand(s.mflags, 1), 0, 'N', 'Y'), /* QSMQSUM_KNOWNSTL */
       s.sumtextlen,s.sumtext
from sys.user$ u, sys.sum$ s, sys.obj$ o
where o.owner# = u.user#
  and o.obj# = s.obj#
  and bitand(s.xpflags, 8388608) = 0 /* NOT REWRITE EQUIVALENCE SUMMARY */
  and (o.owner# = userenv('SCHEMAID')
       or o.obj# in
            (select oa.obj#
             from sys.objauth$ oa
             where grantee# in ( select kzsrorol
                                 from x$kzsro
                               )
            )
       or /* user has system privileges */
         exists (select null from v$enabledprivs
                 where priv_number in (-45 /* LOCK ANY TABLE */,
                                       -47 /* SELECT ANY TABLE */,
                                       -397/* READ ANY TABLE */,
                                       -48 /* INSERT ANY TABLE */,
                                       -49 /* UPDATE ANY TABLE */,
                                       -50 /* DELETE ANY TABLE */)
                 )
      )

View Text - HTML Formatted

SELECT U.NAME
, O.NAME
, U.NAME
, S.CONTAINERNAM
, S.LASTREFRESHSCN
, S.LASTREFRESHDATE
, DECODE (S.REFRESHMODE
, 0
, 'NONE'
, 1
, 'ANY'
, 2
, 'INCREMENTAL'
, 3
, 'FULL')
, DECODE(BITAND(S.PFLAGS
, 25165824)
, 25165824
, 'N'
, 'Y')
, S.FULLREFRESHTIM
, S.INCREFRESHTIM
, DECODE(BITAND(S.PFLAGS
, 48)
, 0
, 'N'
, 'Y')
, DECODE(BITAND(S.MFLAGS
, 64)
, 0
, 'N'
, 'Y')
, /* QSMQSUM_UNUSABLE */ DECODE(BITAND(S.PFLAGS
, 1294319)
, 0
, 'Y'
, 'N')
, DECODE(BITAND((SELECT N.FLAG2
FROM SYS.SNAP$ N
WHERE N.VNAME=S.CONTAINERNAM
AND N.SOWNER=U.NAME)
, 67108864)
, 67108864
, /* PRIMARY CUBE MV? */ DECODE(BITAND((SELECT N2.FLAG
FROM SYS.SNAP$ N2
WHERE N2.PARENT_SOWNER=U.NAME
AND N2.PARENT_VNAME=S.CONTAINERNAM)
, 256)
, 256
, 'N'
, 'Y')
, /* ITS CHILD MV'S PROPERTIES DETERMIN INC_REFRESHABLE */ /* BUG 2070729: CHANGE MASK VALUE
FROM 236879743 TO * 236617599 (EXCLUDES FLAG QSMG_SUM_DETAIL_IS_SNAPSHOT). * SAME CHANG APPLIES TO THE INC_REFRESHABLE * COLUMN IN OTHER VIEWS. */ DECODE(BITAND(S.PFLAGS
, 236617599)
, 0
, 'Y'
, 'N'))
, DECODE(BITAND(S.MFLAGS
, 1)
, 0
, 'N'
, 'Y')
, /* QSMQSUM_KNOWNSTL */ S.SUMTEXTLEN
, S.SUMTEXT FROM SYS.USER$ U
, SYS.SUM$ S
, SYS.OBJ$ O WHERE O.OWNER# = U.USER#
AND O.OBJ# = S.OBJ#
AND BITAND(S.XPFLAGS
, 8388608) = 0 /* NOT REWRITE EQUIVALENCE SUMMARY */
AND (O.OWNER# = USERENV('SCHEMAID') OR O.OBJ# IN (SELECT OA.OBJ#
FROM SYS.OBJAUTH$ OA
WHERE GRANTEE# IN ( SELECT KZSROROL
FROM X$KZSRO ) ) OR /* USER HAS SYSTEM PRIVILEGES */ EXISTS (SELECT NULL
FROM V$ENABLEDPRIVS
WHERE PRIV_NUMBER IN (-45 /* LOCK ANY TABLE */
, -47 /* SELECT ANY TABLE */
, -397/* READ ANY TABLE */
, -48 /* INSERT ANY TABLE */
, -49 /* UPDATE ANY TABLE */
, -50 /* DELETE ANY TABLE */) ) )