VIEW: SYS.DBA_USTATS

Source

View Text - Preformatted

select u.name, o.name, o.subname,
         decode (bitand(s.property, 3), 1, 'INDEX', 2, 'COLUMN'),
         decode (bitand(s.property, 12), 8, 'DIRECT', 4, 'IMPLICIT'),
         c.name, u1.name, o1.name, s.statistics
  from   sys.user$ u, sys.obj$ o, sys.col$ c, sys.ustats$ s,
         sys.user$ u1, sys.obj$ o1
  where  bitand(s.property, 3)=2 and s.obj#=o.obj# and o.owner#=u.user#
  and    s.intcol#=c.intcol# and s.statstype#=o1.obj#
  and    o1.owner#=u1.user# and c.obj#=s.obj#
union all    -- partition case
  select u.name, o.name, o.subname,
         decode (bitand(s.property, 3), 1, 'INDEX', 2, 'COLUMN'),
         decode (bitand(s.property, 12), 8, 'DIRECT', 4, 'IMPLICIT'),
         c.name, u1.name, o1.name, s.statistics
  from   sys.user$ u, sys.user$ u1, sys.obj$ o, sys.obj$ o1, sys.col$ c,
         sys.ustats$ s, sys.tabpart$ t, sys.obj$ o2
  where  bitand(s.property, 3)=2 and s.obj# = o.obj#
  and    s.obj# = t.obj# and t.bo# = o2.obj# and o2.owner# = u.user#
  and    s.intcol# = c.intcol# and s.statstype#=o1.obj# and o1.owner#=u1.user#
  and    t.bo#=c.obj#
union all
  select u.name, o.name, o.subname,
         decode (bitand(s.property, 3), 1, 'INDEX', 2, 'COLUMN'),
         decode (bitand(s.property, 12), 8, 'DIRECT', 4, 'IMPLICIT'),
          NULL, u1.name, o1.name, s.statistics
  from   sys.user$ u, sys.obj$ o, sys.ustats$ s,
         sys.user$ u1, sys.obj$ o1
  where  bitand(s.property, 3)=1 and s.obj#=o.obj# and o.owner#=u.user#
  and    s.statstype#=o1.obj# and o1.owner#=u1.user# and o.type#=1
union all -- index partition
  select u.name, o.name, o.subname,
         decode (bitand(s.property, 3), 1, 'INDEX', 2, 'COLUMN'),
         decode (bitand(s.property, 12), 8, 'DIRECT', 4, 'IMPLICIT'),
         NULL, u1.name, o1.name, s.statistics
  from   sys.user$ u, sys.user$ u1, sys.obj$ o, sys.obj$ o1,
         sys.ustats$ s, sys.indpart$ i, sys.obj$ o2
  where  bitand(s.property, 3)=1 and s.obj# = o.obj#
  and    s.obj# = i.obj# and i.bo# = o2.obj# and o2.owner# = u.user#
  and    s.statstype#=o1.obj# and o1.owner#=u1.user#

View Text - HTML Formatted

SELECT U.NAME
, O.NAME
, O.SUBNAME
, DECODE (BITAND(S.PROPERTY
, 3)
, 1
, 'INDEX'
, 2
, 'COLUMN')
, DECODE (BITAND(S.PROPERTY
, 12)
, 8
, 'DIRECT'
, 4
, 'IMPLICIT')
, C.NAME
, U1.NAME
, O1.NAME
, S.STATISTICS
FROM SYS.USER$ U
, SYS.OBJ$ O
, SYS.COL$ C
, SYS.USTATS$ S
, SYS.USER$ U1
, SYS.OBJ$ O1
WHERE BITAND(S.PROPERTY
, 3)=2
AND S.OBJ#=O.OBJ#
AND O.OWNER#=U.USER#
AND S.INTCOL#=C.INTCOL#
AND S.STATSTYPE#=O1.OBJ#
AND O1.OWNER#=U1.USER#
AND C.OBJ#=S.OBJ# UNION ALL -- PARTITION CASE SELECT U.NAME
, O.NAME
, O.SUBNAME
, DECODE (BITAND(S.PROPERTY
, 3)
, 1
, 'INDEX'
, 2
, 'COLUMN')
, DECODE (BITAND(S.PROPERTY
, 12)
, 8
, 'DIRECT'
, 4
, 'IMPLICIT')
, C.NAME
, U1.NAME
, O1.NAME
, S.STATISTICS
FROM SYS.USER$ U
, SYS.USER$ U1
, SYS.OBJ$ O
, SYS.OBJ$ O1
, SYS.COL$ C
, SYS.USTATS$ S
, SYS.TABPART$ T
, SYS.OBJ$ O2
WHERE BITAND(S.PROPERTY
, 3)=2
AND S.OBJ# = O.OBJ#
AND S.OBJ# = T.OBJ#
AND T.BO# = O2.OBJ#
AND O2.OWNER# = U.USER#
AND S.INTCOL# = C.INTCOL#
AND S.STATSTYPE#=O1.OBJ#
AND O1.OWNER#=U1.USER#
AND T.BO#=C.OBJ# UNION ALL SELECT U.NAME
, O.NAME
, O.SUBNAME
, DECODE (BITAND(S.PROPERTY
, 3)
, 1
, 'INDEX'
, 2
, 'COLUMN')
, DECODE (BITAND(S.PROPERTY
, 12)
, 8
, 'DIRECT'
, 4
, 'IMPLICIT')
, NULL
, U1.NAME
, O1.NAME
, S.STATISTICS
FROM SYS.USER$ U
, SYS.OBJ$ O
, SYS.USTATS$ S
, SYS.USER$ U1
, SYS.OBJ$ O1
WHERE BITAND(S.PROPERTY
, 3)=1
AND S.OBJ#=O.OBJ#
AND O.OWNER#=U.USER#
AND S.STATSTYPE#=O1.OBJ#
AND O1.OWNER#=U1.USER#
AND O.TYPE#=1 UNION ALL -- INDEX PARTITION SELECT U.NAME
, O.NAME
, O.SUBNAME
, DECODE (BITAND(S.PROPERTY
, 3)
, 1
, 'INDEX'
, 2
, 'COLUMN')
, DECODE (BITAND(S.PROPERTY
, 12)
, 8
, 'DIRECT'
, 4
, 'IMPLICIT')
, NULL
, U1.NAME
, O1.NAME
, S.STATISTICS
FROM SYS.USER$ U
, SYS.USER$ U1
, SYS.OBJ$ O
, SYS.OBJ$ O1
, SYS.USTATS$ S
, SYS.INDPART$ I
, SYS.OBJ$ O2
WHERE BITAND(S.PROPERTY
, 3)=1
AND S.OBJ# = O.OBJ#
AND S.OBJ# = I.OBJ#
AND I.BO# = O2.OBJ#
AND O2.OWNER# = U.USER#
AND S.STATSTYPE#=O1.OBJ#
AND O1.OWNER#=U1.USER#