VIEW: SYS.DBA_SNAPSHOTS

Source

View Text - Preformatted

select s.sowner, s.vname, tname, mview, t.mowner, t.master, mlink,
       decode(bitand(s.flag,1),  0, 'NO', 'YES'),
       decode(bitand(s.flag,2),  0, 'NO', 'YES'),
       decode(bitand(s.flag,16),             16, 'ROWID',
	      (decode(bitand(s.flag,32),     32, 'PRIMARY KEY',
	      (decode(bitand(s.flag,8192), 8192, 'JOIN VIEW',
	      (decode(bitand(s.flag,4096), 4096, 'AGGREGATE',
              (decode(bitand(s.flag,256),   256, 'COMPLEX',
              (decode(bitand(s.flag,536870912),   536870912, 'OBJECT ID',
                                                 'UNKNOWN'))))))))))),
       t.snaptime, s.error#,
       decode(bitand(s.status,1), 0, 'REGENERATE', 'VALID'),
       decode(bitand(s.status,2), 0, 'REGENERATE', 'VALID'),
       decode(s.auto_fast,
              'C',  'COMPLETE',
              'F',  'FAST',
              '?',  'FORCE',
              'N',  'NEVER',
              NULL, 'FORCE', 'ERROR'),
       s.auto_fun, s.auto_date, r.refgroup, s.ustrg, s.uslog,
       s.query_txt, s.mas_roll_seg,
       decode(bitand(s.status,4),         4, 'INVALID',
	      (decode(bitand(s.status,8), 8, 'UNKNOWN',
                                             'VALID'))),
       decode(NVL(s.auto_fun, 'null'),
              'null', decode(s.auto_fast,                  'N', 'NEVER',
                             (decode(bitand(s.flag, 32768),  0, 'DEMAND',
                                                                'COMMIT'))),
	      'PERIODIC'),
       decode(bitand(s.flag,131072), 0, 'NO', 'YES')
from sys.snap$ s, sys.rgchild$ r, sys.snap_reftime$ t
where t.sowner = s.sowner
and t.vname = s.vname
and t.instsite = 0
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 */
and t.tablenum = 0
and t.sowner  = r.owner (+)
and t.vname = r.name (+)
and nvl(r.instsite,0) = 0
and r.type# (+) = 'SNAPSHOT'

View Text - HTML Formatted

SELECT S.SOWNER
, S.VNAME
, TNAME
, MVIEW
, T.MOWNER
, T.MASTER
, MLINK
, DECODE(BITAND(S.FLAG
, 1)
, 0
, 'NO'
, 'YES')
, DECODE(BITAND(S.FLAG
, 2)
, 0
, 'NO'
, 'YES')
, DECODE(BITAND(S.FLAG
, 16)
, 16
, 'ROWID'
, (DECODE(BITAND(S.FLAG
, 32)
, 32
, 'PRIMARY KEY'
, (DECODE(BITAND(S.FLAG
, 8192)
, 8192
, 'JOIN VIEW'
, (DECODE(BITAND(S.FLAG
, 4096)
, 4096
, 'AGGREGATE'
, (DECODE(BITAND(S.FLAG
, 256)
, 256
, 'COMPLEX'
, (DECODE(BITAND(S.FLAG
, 536870912)
, 536870912
, 'OBJECT ID'
, 'UNKNOWN')))))))))))
, T.SNAPTIME
, S.ERROR#
, DECODE(BITAND(S.STATUS
, 1)
, 0
, 'REGENERATE'
, 'VALID')
, DECODE(BITAND(S.STATUS
, 2)
, 0
, 'REGENERATE'
, 'VALID')
, DECODE(S.AUTO_FAST
, 'C'
, 'COMPLETE'
, 'F'
, 'FAST'
, '?'
, 'FORCE'
, 'N'
, 'NEVER'
, NULL
, 'FORCE'
, 'ERROR')
, S.AUTO_FUN
, S.AUTO_DATE
, R.REFGROUP
, S.USTRG
, S.USLOG
, S.QUERY_TXT
, S.MAS_ROLL_SEG
, DECODE(BITAND(S.STATUS
, 4)
, 4
, 'INVALID'
, (DECODE(BITAND(S.STATUS
, 8)
, 8
, 'UNKNOWN'
, 'VALID')))
, DECODE(NVL(S.AUTO_FUN
, 'NULL')
, 'NULL'
, DECODE(S.AUTO_FAST
, 'N'
, 'NEVER'
, (DECODE(BITAND(S.FLAG
, 32768)
, 0
, 'DEMAND'
, 'COMMIT')))
, 'PERIODIC')
, DECODE(BITAND(S.FLAG
, 131072)
, 0
, 'NO'
, 'YES') FROM SYS.SNAP$ S
, SYS.RGCHILD$ R
, SYS.SNAP_REFTIME$ T WHERE T.SOWNER = S.SOWNER AND T.VNAME = S.VNAME AND T.INSTSITE = 0 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 */ AND T.TABLENUM = 0 AND T.SOWNER = R.OWNER (+) AND T.VNAME = R.NAME (+) AND NVL(R.INSTSITE
, 0) = 0 AND R.TYPE# (+) = 'SNAPSHOT'