eTRM uses javascript that opens and focuses a separate window to display reports. Your browser does not appear to support javascript. This should not prevent you from using eTRM but it does mean the window focus feature will not work. Please check the contents of other windows on your desktop if you click on a link that does not appear to do anything.

VIEW: SYS.ALL_IND_STATISTICS

Source

View Text - Preformatted


SELECT
    u.name, o.name, ut.name, ot.name,
    NULL,NULL, NULL, NULL, 'INDEX',
    i.blevel, i.leafcnt, i.distkey, i.lblkkey, i.dblkkey, i.clufac, i.rowcnt,
    ins.cachedblk, ins.cachehit, i.samplesize, i.analyzetime,
    decode(bitand(i.flags, 2048), 0, 'NO', 'YES'),
    decode(bitand(i.flags, 64), 0, 'NO', 'YES'),
    decode(bitand(t.trigflag, 67108864) + bitand(t.trigflag, 134217728),
           0, NULL, 67108864, 'DATA', 134217728, 'CACHE', 'ALL'),
    case when
           (i.analyzetime is null or
            t.analyzetime is null) then null
         when (i.analyzetime < t.analyzetime or
               (((m.inserts + m.deletes + m.updates) >
                  t.rowcnt *
                  to_number(DBMS_STATS.GET_PREFS('STALE_PERCENT',
                                                 DBMS_STATS_INTERNAL.DQ(ut.name),
                                                 DBMS_STATS_INTERNAL.DQ(ot.name)))/100 or
                bitand(m.flags,1) = 1))) then 'YES'
         else  'NO'
    end,
    'SHARED'
  FROM
    sys.user$ u, sys.ind$ i, sys.obj$ o, sys.ind_stats$ ins,
    sys.obj$ ot, sys.user$ ut, sys.tab$ t, sys.mon_mods_all$ m
  WHERE
      u.user# = o.owner#
  and o.obj# = i.obj#
  and bitand(i.flags, 4096) = 0
  and i.type# in (1, 2, 4, 6, 7, 8)
  and i.obj# = ins.obj# (+)
  and i.bo# = ot.obj#
  and ot.type# = 2
  and ot.owner# = ut.user#
  and ot.obj# = t.obj#
  and t.obj# = m.obj# (+)
  and o.subname IS NULL
  and o.namespace = 4 and o.remoteowner IS NULL and o.linkname IS NULL
  and bitand(o.flags, 128) = 0 -- not in recycle bin
  and (o.owner# = userenv('SCHEMAID')
        or
       o.obj# in ( select obj#
                    FROM sys.objauth$
                    where grantee# in ( select kzsrorol
                                        FROM x$kzsro
                                      )
                   )
        or
         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 */)
                 )
       )
  UNION ALL
  /* Cluster indexes */
  SELECT
    u.name, o.name, ut.name, ot.name,
    NULL,NULL, NULL, NULL, 'INDEX',
    i.blevel, i.leafcnt, i.distkey, i.lblkkey, i.dblkkey, i.clufac, i.rowcnt,
    ins.cachedblk, ins.cachehit, i.samplesize, i.analyzetime,
    decode(bitand(i.flags, 2048), 0, 'NO', 'YES'),
    decode(bitand(i.flags, 64), 0, 'NO', 'YES'),
    -- a cluster index is considered locked if any of the table in
    -- the cluster is locked.
    decode((select
           decode(nvl(sum(decode(bitand(t.trigflag, 67108864), 0, 0, 1)),0),
                  0, 0, 67108864) +
           decode(nvl(sum(decode(bitand(nvl(t.trigflag, 0), 134217728),
                                 0, 0, 1)), 0),
                  0, 0, 134217728)
           from  sys.tab$ t where i.bo# = t.bobj#),
           0, NULL, 67108864, 'DATA', 134217728, 'CACHE', 'ALL'),
    case
         when i.analyzetime is null then null
         when
           (select                                 -- STALE
              sum(case when
                      i.analyzetime < tab.analyzetime or
                      bitand(m.flags,1) = 1 or
                      m.inserts + m.updates + m.deletes >
                        tab.rowcnt *
                        to_number(DBMS_STATS.GET_PREFS('STALE_PERCENT',
                                                       DBMS_STATS_INTERNAL.DQ(ut.name),
                                                       DBMS_STATS_INTERNAL.DQ(ot.name)))/100
                  then 1 else 0 end)
            from sys.tab$ tab, mon_mods_all$ m
            where
              m.obj#(+) = tab.obj# and tab.bobj# = i.bo#) > 0 then 'YES'
         else 'NO' end,
    'SHARED'
  FROM
    sys.user$ u, sys.ind$ i, sys.obj$ o, sys.ind_stats$ ins,
    sys.obj$ ot, sys.user$ ut
  WHERE
      u.user# = o.owner#
  and o.obj# = i.obj#
  and bitand(i.flags, 4096) = 0
  and i.type# = 3 /* Cluster index */
  and i.obj# = ins.obj# (+)
  and i.bo# = ot.obj#
  and ot.owner# = ut.user#
  and o.subname IS NULL
  and o.namespace = 4 and o.remoteowner IS NULL and o.linkname IS NULL
  and bitand(o.flags, 128) = 0 -- not in recycle bin
  and (o.owner# = userenv('SCHEMAID')
        or
       o.obj# in ( select obj#
                    FROM sys.objauth$
                    where grantee# in ( select kzsrorol
                                        FROM x$kzsro
                                      )
                   )
        or
         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 */)
                 )
       )
  UNION ALL
  /* Partitions */
  SELECT
    u.name, io.name, ut.name, ot.name,
    io.subname, ip.part#, NULL, NULL, 'PARTITION',
    ip.blevel, ip.leafcnt, ip.distkey, ip.lblkkey, ip.dblkkey,
    ip.clufac, ip.rowcnt, ins.cachedblk, ins.cachehit,
    ip.samplesize, ip.analyzetime,
    decode(bitand(ip.flags, 16), 0, 'NO', 'YES'),
    decode(bitand(ip.flags, 8), 0, 'NO', 'YES'),
    /* stattype_locked */
    (select
       -- not a local index, just look at the lock at table level
       decode(bitand(t.trigflag, 67108864) + bitand(t.trigflag, 134217728),
              0, NULL, 67108864, 'DATA', 134217728, 'CACHE', 'ALL')
       FROM sys.tab$ t
       where t.obj# = i.bo# and
             bitand(po.flags, 1) = 0   -- not local index
     union all
     select
       -- local index, we need to see if the corresponding partn is locked
       decode(
       /*
        * Following decode returns 1 if DATA stats locked for partition
        * or at table level
        */
       decode(bitand(t.trigflag, 67108864) + bitand(tp.flags, 32), 0, 0, 1) +
       /*
        * Following decode returns 2 if CACHE stats locked for partition
        * or at table level
        */
       decode(bitand(t.trigflag, 134217728) + bitand(tp.flags, 64), 0, 0, 2),
       /* if 0 => not locked, 3 => data and cache stats locked */
       0, NULL, 1, 'DATA', 2, 'CACHE', 'ALL')
       FROM sys.tabpartv$ tp, sys.tab$ t
       where tp.bo# = i.bo# and tp.phypart# = ip.phypart# and
             tp.bo# = t.obj# and
             bitand(po.flags, 1) = 1),  -- local index
    /* stale_stats */
    (select
       case     when (i.analyzetime is null or
                      tab.analyzetime is null) then null
                when (i.analyzetime < tab.analyzetime  or
                      ((m.inserts + m.deletes + m.updates) >
                        tab.rowcnt *
                        to_number(DBMS_STATS.GET_PREFS('STALE_PERCENT',
                                                       DBMS_STATS_INTERNAL.DQ(ut.name),
                                                       DBMS_STATS_INTERNAL.DQ(ot.name)))/100 or
                       bitand(m.flags,1) = 1)) then 'YES'
                else 'NO'
       end
       FROM sys.tab$ tab, sys.mon_mods_all$ m
       where tab.obj# = i.bo# and tab.obj# = m.obj# (+) and
             bitand(po.flags, 1) = 0   -- not local index
     union all
     select
       case     when (ip.analyzetime is null or
                      tp.analyzetime is null) then null
                when (ip.analyzetime < tp.analyzetime  or
                      ((m.inserts + m.deletes + m.updates) >
                        tp.rowcnt *
                        to_number(DBMS_STATS.GET_PREFS('STALE_PERCENT',
                                                       DBMS_STATS_INTERNAL.DQ(ut.name),
                                                       DBMS_STATS_INTERNAL.DQ(ot.name)))/100 or
                       bitand(m.flags,1) = 1)) then 'YES'
                else 'NO'
       end
       FROM sys.tabpartv$ tp, sys.mon_mods_all$ m
       where tp.bo# = i.bo# and tp.phypart# = ip.phypart# and
             tp.obj# = m.obj# (+) and
             bitand(po.flags, 1) = 1),  -- local index
    'SHARED'
  FROM
    sys.obj$ io, sys.indpartv$ ip,
    sys.user$ u, sys.ind_stats$ ins,
    sys.ind$ i, sys.obj$ ot, sys.user$ ut, sys.partobj$ po
  WHERE
      io.obj# = ip.obj#
  and ip.bo# = i.obj#
  and io.owner# = u.user#
  and ip.obj# = ins.obj# (+)
  and ip.bo# = i.obj#
  and i.type# != 9  --  no domain indexes
  and i.bo# = ot.obj#
  and ot.type# = 2
  and ot.owner# = ut.user#
  and i.obj# = po.obj#
  and io.namespace = 4 and io.remoteowner IS NULL and io.linkname IS NULL
  and bitand(io.flags, 128) = 0 -- not in recycle bin
  and (io.owner# = userenv('SCHEMAID')
        or
        i.bo# in (select obj#
                    FROM sys.objauth$
                    where grantee# in ( select kzsrorol
                                        FROM x$kzsro
                                      )
                   )
        or
         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 */)
                 )
       )
  UNION ALL
  /* Composite partitions */
  SELECT
    u.name, io.name, ut.name, ot.name,
    io.subname, icp.part#, NULL, NULL, 'PARTITION',
    icp.blevel, icp.leafcnt, icp.distkey, icp.lblkkey, icp.dblkkey,
    icp.clufac, icp.rowcnt, ins.cachedblk, ins.cachehit,
    icp.samplesize, icp.analyzetime,
    decode(bitand(icp.flags, 16), 0, 'NO', 'YES'),
    decode(bitand(icp.flags, 8), 0, 'NO', 'YES'),
    /* stattype_locked */
    (select
       -- not a local index, just look at the lock at table level
       decode(bitand(t.trigflag, 67108864) + bitand(t.trigflag, 134217728),
              0, NULL, 67108864, 'DATA', 134217728, 'CACHE', 'ALL')
       FROM sys.tab$ t
       where t.obj# = i.bo# and
             bitand(po.flags, 1) = 0   -- not local index
     union all
     select
       -- local index, we need to see if the corresponding partn is locked
       decode(
       /*
        * Following decode returns 1 if DATA stats locked for partition
        * or at table level
        */
       decode(bitand(t.trigflag, 67108864) + bitand(tcp.flags, 32), 0, 0, 1) +
       /*
        * Following decode returns 2 if CACHE stats locked for partition
        * or at table level
        */
       decode(bitand(t.trigflag, 134217728) + bitand(tcp.flags, 64), 0, 0, 2),
       /* if 0 => not locked, 3 => data and cache stats locked */
       0, NULL, 1, 'DATA', 2, 'CACHE', 'ALL')
       FROM sys.tabcompartv$ tcp, sys.tab$ t
       where tcp.bo# = i.bo# and tcp.phypart# = icp.phypart# and
             tcp.bo# = t.obj# and
             bitand(po.flags, 1) = 1),  -- local index
    /* stale_stats */
    (select
       case     when (i.analyzetime is null or
                      tab.analyzetime is null) then null
                when (i.analyzetime < tab.analyzetime  or
                      ((m.inserts + m.deletes + m.updates) >
                        tab.rowcnt *
                        to_number(DBMS_STATS.GET_PREFS('STALE_PERCENT',
                                                       DBMS_STATS_INTERNAL.DQ(ut.name),
                                                       DBMS_STATS_INTERNAL.DQ(ot.name)))/100 or
                       bitand(m.flags,1) = 1)) then 'YES'
                else 'NO'
       end
       FROM sys.tab$ tab, sys.mon_mods_all$ m
       where tab.obj# = i.bo# and tab.obj# = m.obj# (+) and
             bitand(po.flags, 1) = 0   -- not local index
     union all
     select
       case     when (icp.analyzetime is null or
                      tcp.analyzetime is null) then null
                when (icp.analyzetime < tcp.analyzetime  or
                      ((m.inserts + m.deletes + m.updates) >
                        tcp.rowcnt *
                        to_number(DBMS_STATS.GET_PREFS('STALE_PERCENT',
                                                       DBMS_STATS_INTERNAL.DQ(ut.name),
                                                       DBMS_STATS_INTERNAL.DQ(ot.name)))/100 or
                       bitand(m.flags,1) = 1)) then 'YES'
                else 'NO'
       end
       FROM sys.tabcompartv$ tcp, sys.mon_mods_all$ m
       where tcp.bo# = i.bo# and tcp.phypart# = icp.phypart# and
             tcp.obj# = m.obj# (+) and
             bitand(po.flags, 1) = 1),  -- local index
    'SHARED'
  FROM
    sys.obj$ io, sys.indcompartv$ icp, sys.user$ u, sys.ind_stats$ ins,
    sys.ind$ i, sys.obj$ ot, sys.user$ ut, sys.partobj$ po
  WHERE
      io.obj# = icp.obj#
  and io.owner# = u.user#
  and icp.obj# = ins.obj# (+)
  and i.obj# = icp.bo#
  and icp.bo# = i.obj#
  and i.type# != 9  --  no domain indexes
  and i.bo# = ot.obj#
  and ot.type# = 2
  and ot.owner# = ut.user#
  and i.obj# = po.obj#
  and io.namespace = 4 and io.remoteowner IS NULL and io.linkname IS NULL
  and bitand(io.flags, 128) = 0 -- not in recycle bin
  and (io.owner# = userenv('SCHEMAID')
        or
        i.bo# 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 */)
                 )
      )
  UNION ALL
  /* Subpartitions */
  SELECT
    u.name, op.name, ut.name, ot.name,
    op.subname, icp.part#, os.subname, isp.subpart#,
    'SUBPARTITION',
    isp.blevel, isp.leafcnt, isp.distkey, isp.lblkkey, isp.dblkkey,
    isp.clufac, isp.rowcnt, ins.cachedblk, ins.cachehit,
    isp.samplesize, isp.analyzetime,
    decode(bitand(isp.flags, 16), 0, 'NO', 'YES'),
    decode(bitand(isp.flags, 8), 0, 'NO', 'YES'),
    /* stattype_locked */
    (select
       -- not a local index, just look at the lock at table level
       decode(bitand(t.trigflag, 67108864) + bitand(t.trigflag, 134217728),
              0, NULL, 67108864, 'DATA', 134217728, 'CACHE', 'ALL')
       FROM sys.tab$ t
       where t.obj# = i.bo# and
             bitand(po.flags, 1) = 0   -- not local index
     union all
     select
       -- local index, we need to see if the corresponding composite partn
       -- is locked
       decode(
       /*
        * Following decode returns 1 if DATA stats locked for partition
        * or at table level
        */
       decode(bitand(t.trigflag, 67108864) + bitand(tcp.flags, 32), 0, 0, 1) +
       /*
        * Following decode returns 2 if CACHE stats locked for partition
        * or at table level
        */
       decode(bitand(t.trigflag, 134217728) + bitand(tcp.flags, 64), 0, 0, 2),
       /* if 0 => not locked, 3 => data and cache stats locked */
       0, NULL, 1, 'DATA', 2, 'CACHE', 'ALL')
       FROM  sys.tabcompartv$ tcp, sys.tabsubpartv$ tsp, sys.tab$ t
       where tcp.bo# = i.bo# and tcp.phypart# = icp.phypart# and
             tsp.pobj# = tcp.obj# and
             isp.physubpart# = tsp.physubpart# and
             tcp.bo# = t.obj# and
             bitand(po.flags, 1) = 1),  -- local index
    /* stale_stats */
    (select
       case     when (i.analyzetime is null or
                      tab.analyzetime is null) then null
                when (i.analyzetime < tab.analyzetime  or
                      ((m.inserts + m.deletes + m.updates) >
                        tab.rowcnt *
                        to_number(DBMS_STATS.GET_PREFS('STALE_PERCENT',
                                                       DBMS_STATS_INTERNAL.DQ(ut.name),
                                                       DBMS_STATS_INTERNAL.DQ(ot.name)))/100 or
                       bitand(m.flags,1) = 1)) then 'YES'
                else 'NO'
       end
       FROM sys.tab$ tab, sys.mon_mods_all$ m
       where tab.obj# = i.bo# and tab.obj# = m.obj# (+) and
             bitand(po.flags, 1) = 0   -- not local index
     union all
     select
       case     when (isp.analyzetime is null or
                      tsp.analyzetime is null) then null
                when (isp.analyzetime < tsp.analyzetime  or
                      ((m.inserts + m.deletes + m.updates) >
                        tsp.rowcnt *
                        to_number(DBMS_STATS.GET_PREFS('STALE_PERCENT',
                                                       DBMS_STATS_INTERNAL.DQ(ut.name),
                                                       DBMS_STATS_INTERNAL.DQ(ot.name)))/100 or
                       bitand(m.flags,1) = 1)) then 'YES'
                else 'NO'
       end
       FROM  sys.tabcompartv$ tcp, sys.tabsubpartv$ tsp, sys.mon_mods_all$ m
       where tcp.bo# = i.bo# and tcp.phypart# = icp.phypart# and
             tsp.pobj# = tcp.obj# and
             isp.physubpart# = tsp.physubpart# and
             tsp.obj# = m.obj# (+) and
             bitand(po.flags, 1) = 1),  -- local index
    'SHARED'
  FROM
    sys.obj$ os, sys.obj$ op, sys.indcompartv$ icp, sys.indsubpartv$ isp,
    sys.user$ u,  sys.ind_stats$ ins,
    sys.ind$ i, sys.obj$ ot, sys.user$ ut, sys.partobj$ po
  WHERE
      os.obj# = isp.obj#
  and op.obj# = icp.obj#
  and icp.obj# = isp.pobj#
  and icp.bo# = i.obj#
  and i.type# != 9  --  no domain indexes
  and u.user# = op.owner#
  and isp.obj# = ins.obj# (+)
  and icp.bo# = i.obj#
  and i.bo# = ot.obj#
  and ot.type# = 2
  and ot.owner# = ut.user#
  and i.obj# = po.obj#
  and op.namespace = 4 and op.remoteowner IS NULL and op.linkname IS NULL
  and bitand(op.flags, 128) = 0 -- not in recycle bin
  and (op.owner# = userenv('SCHEMAID')
        or i.bo# 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 */)
                 )
      )
 UNION ALL
 SELECT
    u.name, o.name, ut.name, ot.name,
    NULL,NULL, NULL, NULL, 'INDEX',
    sesi.blevel_kxttst_is, sesi.leafcnt_kxttst_is, sesi.distkey_kxttst_is,
    sesi.lblkkey_kxttst_is, sesi.dblkkey_kxttst_is, sesi.clufac_kxttst_is,
    sesi.rowcnt_kxttst_is,
    sesi.cachedblk_kxttst_is, sesi.cachehit_kxttst_is,
    sesi.samplesize_kxttst_is, sesi.analyzetime_kxttst_is,
    decode(bitand(sesi.flags_kxttst_is, 2048), 0, 'NO', 'YES'),
    decode(bitand(sesi.flags_kxttst_is, 64), 0, 'NO', 'YES'),
    null,  /* no lock on session private stats */
    null, /* session based dml monitoring not available */
    'SESSION'
  FROM
    sys.x$kxttsteis sesi,
    sys.user$ u, sys.ind$ i, sys.obj$ o,
    sys.obj$ ot, sys.user$ ut
  WHERE
      u.user# = o.owner#
  and o.obj# = i.obj#
  and i.obj# = sesi.obj#_kxttst_is
  and bitand(i.flags, 4096) = 0
  and i.type# in (1, 2, 4, 6, 7, 8)
  and i.bo# = ot.obj#
  and ot.type# = 2
  and ot.owner# = ut.user#
  and o.subname IS NULL
  and o.namespace = 4 and o.remoteowner IS NULL and o.linkname IS NULL
  and bitand(o.flags, 128) = 0 -- not in recycle bin
  and (o.owner# = userenv('SCHEMAID')
        or
       o.obj# in ( select obj#
                    FROM sys.objauth$
                    where grantee# in ( select kzsrorol
                                        FROM x$kzsro
                                      )
                   )
        or
         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
, UT.NAME
, OT.NAME
, NULL
, NULL
, NULL
, NULL
, 'INDEX'
, I.BLEVEL
, I.LEAFCNT
, I.DISTKEY
, I.LBLKKEY
, I.DBLKKEY
, I.CLUFAC
, I.ROWCNT
, INS.CACHEDBLK
, INS.CACHEHIT
, I.SAMPLESIZE
, I.ANALYZETIME
, DECODE(BITAND(I.FLAGS
, 2048)
, 0
, 'NO'
, 'YES')
, DECODE(BITAND(I.FLAGS
, 64)
, 0
, 'NO'
, 'YES')
, DECODE(BITAND(T.TRIGFLAG
, 67108864) + BITAND(T.TRIGFLAG
, 134217728)
, 0
, NULL
, 67108864
, 'DATA'
, 134217728
, 'CACHE'
, 'ALL')
, CASE WHEN (I.ANALYZETIME IS NULL OR T.ANALYZETIME IS NULL) THEN NULL WHEN (I.ANALYZETIME < T.ANALYZETIME OR (((M.INSERTS + M.DELETES + M.UPDATES) > T.ROWCNT * TO_NUMBER(DBMS_STATS.GET_PREFS('STALE_PERCENT'
, DBMS_STATS_INTERNAL.DQ(UT.NAME)
, DBMS_STATS_INTERNAL.DQ(OT.NAME)))/100 OR BITAND(M.FLAGS
, 1) = 1))) THEN 'YES' ELSE 'NO' END
, 'SHARED' FROM SYS.USER$ U
, SYS.IND$ I
, SYS.OBJ$ O
, SYS.IND_STATS$ INS
, SYS.OBJ$ OT
, SYS.USER$ UT
, SYS.TAB$ T
, SYS.MON_MODS_ALL$ M WHERE U.USER# = O.OWNER#
AND O.OBJ# = I.OBJ#
AND BITAND(I.FLAGS
, 4096) = 0
AND I.TYPE# IN (1
, 2
, 4
, 6
, 7
, 8)
AND I.OBJ# = INS.OBJ# (+)
AND I.BO# = OT.OBJ#
AND OT.TYPE# = 2
AND OT.OWNER# = UT.USER#
AND OT.OBJ# = T.OBJ#
AND T.OBJ# = M.OBJ# (+)
AND O.SUBNAME IS NULL
AND O.NAMESPACE = 4
AND O.REMOTEOWNER IS NULL
AND O.LINKNAME IS NULL
AND BITAND(O.FLAGS
, 128) = 0 -- NOT IN RECYCLE BIN
AND (O.OWNER# = USERENV('SCHEMAID') OR O.OBJ# IN ( SELECT OBJ#
FROM SYS.OBJAUTH$
WHERE GRANTEE# IN ( SELECT KZSROROL
FROM X$KZSRO ) ) OR 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 */) ) ) UNION ALL /* CLUSTER INDEXES */ SELECT U.NAME
, O.NAME
, UT.NAME
, OT.NAME
, NULL
, NULL
, NULL
, NULL
, 'INDEX'
, I.BLEVEL
, I.LEAFCNT
, I.DISTKEY
, I.LBLKKEY
, I.DBLKKEY
, I.CLUFAC
, I.ROWCNT
, INS.CACHEDBLK
, INS.CACHEHIT
, I.SAMPLESIZE
, I.ANALYZETIME
, DECODE(BITAND(I.FLAGS
, 2048)
, 0
, 'NO'
, 'YES')
, DECODE(BITAND(I.FLAGS
, 64)
, 0
, 'NO'
, 'YES')
, -- A CLUSTER INDEX IS CONSIDERED LOCKED IF ANY OF THE TABLE IN -- THE CLUSTER IS LOCKED. DECODE((SELECT DECODE(NVL(SUM(DECODE(BITAND(T.TRIGFLAG
, 67108864)
, 0
, 0
, 1))
, 0)
, 0
, 0
, 67108864) + DECODE(NVL(SUM(DECODE(BITAND(NVL(T.TRIGFLAG
, 0)
, 134217728)
, 0
, 0
, 1))
, 0)
, 0
, 0
, 134217728)
FROM SYS.TAB$ T
WHERE I.BO# = T.BOBJ#)
, 0
, NULL
, 67108864
, 'DATA'
, 134217728
, 'CACHE'
, 'ALL')
, CASE WHEN I.ANALYZETIME IS NULL THEN NULL WHEN (SELECT -- STALE SUM(CASE WHEN I.ANALYZETIME < TAB.ANALYZETIME OR BITAND(M.FLAGS
, 1) = 1 OR M.INSERTS + M.UPDATES + M.DELETES > TAB.ROWCNT * TO_NUMBER(DBMS_STATS.GET_PREFS('STALE_PERCENT'
, DBMS_STATS_INTERNAL.DQ(UT.NAME)
, DBMS_STATS_INTERNAL.DQ(OT.NAME)))/100 THEN 1 ELSE 0 END)
FROM SYS.TAB$ TAB
, MON_MODS_ALL$ M WHERE M.OBJ#(+) = TAB.OBJ#
AND TAB.BOBJ# = I.BO#) > 0 THEN 'YES' ELSE 'NO' END
, 'SHARED' FROM SYS.USER$ U
, SYS.IND$ I
, SYS.OBJ$ O
, SYS.IND_STATS$ INS
, SYS.OBJ$ OT
, SYS.USER$ UT WHERE U.USER# = O.OWNER#
AND O.OBJ# = I.OBJ#
AND BITAND(I.FLAGS
, 4096) = 0
AND I.TYPE# = 3 /* CLUSTER INDEX */
AND I.OBJ# = INS.OBJ# (+)
AND I.BO# = OT.OBJ#
AND OT.OWNER# = UT.USER#
AND O.SUBNAME IS NULL
AND O.NAMESPACE = 4
AND O.REMOTEOWNER IS NULL
AND O.LINKNAME IS NULL
AND BITAND(O.FLAGS
, 128) = 0 -- NOT IN RECYCLE BIN
AND (O.OWNER# = USERENV('SCHEMAID') OR O.OBJ# IN ( SELECT OBJ#
FROM SYS.OBJAUTH$
WHERE GRANTEE# IN ( SELECT KZSROROL
FROM X$KZSRO ) ) OR 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 */) ) ) UNION ALL /* PARTITIONS */ SELECT U.NAME
, IO.NAME
, UT.NAME
, OT.NAME
, IO.SUBNAME
, IP.PART#
, NULL
, NULL
, 'PARTITION'
, IP.BLEVEL
, IP.LEAFCNT
, IP.DISTKEY
, IP.LBLKKEY
, IP.DBLKKEY
, IP.CLUFAC
, IP.ROWCNT
, INS.CACHEDBLK
, INS.CACHEHIT
, IP.SAMPLESIZE
, IP.ANALYZETIME
, DECODE(BITAND(IP.FLAGS
, 16)
, 0
, 'NO'
, 'YES')
, DECODE(BITAND(IP.FLAGS
, 8)
, 0
, 'NO'
, 'YES')
, /* STATTYPE_LOCKED */ (SELECT -- NOT A LOCAL INDEX
, JUST LOOK AT THE LOCK AT TABLE LEVEL DECODE(BITAND(T.TRIGFLAG
, 67108864) + BITAND(T.TRIGFLAG
, 134217728)
, 0
, NULL
, 67108864
, 'DATA'
, 134217728
, 'CACHE'
, 'ALL')
FROM SYS.TAB$ T
WHERE T.OBJ# = I.BO# AND BITAND(PO.FLAGS
, 1) = 0 -- NOT LOCAL INDEX UNION ALL SELECT -- LOCAL INDEX
, WE NEED TO SEE IF THE CORRESPONDING PARTN IS LOCKED DECODE( /* * FOLLOWING DECODE RETURNS 1 IF DATA STATS LOCKED FOR PARTITION * OR AT TABLE LEVEL */ DECODE(BITAND(T.TRIGFLAG
, 67108864) + BITAND(TP.FLAGS
, 32)
, 0
, 0
, 1) + /* * FOLLOWING DECODE RETURNS 2 IF CACHE STATS LOCKED FOR PARTITION * OR AT TABLE LEVEL */ DECODE(BITAND(T.TRIGFLAG
, 134217728) + BITAND(TP.FLAGS
, 64)
, 0
, 0
, 2)
, /* IF 0 => NOT LOCKED
, 3 => DATA
AND CACHE STATS LOCKED */ 0
, NULL
, 1
, 'DATA'
, 2
, 'CACHE'
, 'ALL')
FROM SYS.TABPARTV$ TP
, SYS.TAB$ T
WHERE TP.BO# = I.BO#
AND TP.PHYPART# = IP.PHYPART# AND TP.BO# = T.OBJ# AND BITAND(PO.FLAGS
, 1) = 1)
, -- LOCAL INDEX /* STALE_STATS */ (SELECT CASE WHEN (I.ANALYZETIME IS NULL OR TAB.ANALYZETIME IS NULL) THEN NULL WHEN (I.ANALYZETIME < TAB.ANALYZETIME OR ((M.INSERTS + M.DELETES + M.UPDATES) > TAB.ROWCNT * TO_NUMBER(DBMS_STATS.GET_PREFS('STALE_PERCENT'
, DBMS_STATS_INTERNAL.DQ(UT.NAME)
, DBMS_STATS_INTERNAL.DQ(OT.NAME)))/100 OR BITAND(M.FLAGS
, 1) = 1)) THEN 'YES' ELSE 'NO' END
FROM SYS.TAB$ TAB
, SYS.MON_MODS_ALL$ M
WHERE TAB.OBJ# = I.BO#
AND TAB.OBJ# = M.OBJ# (+) AND BITAND(PO.FLAGS
, 1) = 0 -- NOT LOCAL INDEX UNION ALL SELECT CASE WHEN (IP.ANALYZETIME IS NULL OR TP.ANALYZETIME IS NULL) THEN NULL WHEN (IP.ANALYZETIME < TP.ANALYZETIME OR ((M.INSERTS + M.DELETES + M.UPDATES) > TP.ROWCNT * TO_NUMBER(DBMS_STATS.GET_PREFS('STALE_PERCENT'
, DBMS_STATS_INTERNAL.DQ(UT.NAME)
, DBMS_STATS_INTERNAL.DQ(OT.NAME)))/100 OR BITAND(M.FLAGS
, 1) = 1)) THEN 'YES' ELSE 'NO' END
FROM SYS.TABPARTV$ TP
, SYS.MON_MODS_ALL$ M
WHERE TP.BO# = I.BO#
AND TP.PHYPART# = IP.PHYPART# AND TP.OBJ# = M.OBJ# (+) AND BITAND(PO.FLAGS
, 1) = 1)
, -- LOCAL INDEX 'SHARED' FROM SYS.OBJ$ IO
, SYS.INDPARTV$ IP
, SYS.USER$ U
, SYS.IND_STATS$ INS
, SYS.IND$ I
, SYS.OBJ$ OT
, SYS.USER$ UT
, SYS.PARTOBJ$ PO WHERE IO.OBJ# = IP.OBJ#
AND IP.BO# = I.OBJ#
AND IO.OWNER# = U.USER#
AND IP.OBJ# = INS.OBJ# (+)
AND IP.BO# = I.OBJ#
AND I.TYPE# != 9 -- NO DOMAIN INDEXES
AND I.BO# = OT.OBJ#
AND OT.TYPE# = 2
AND OT.OWNER# = UT.USER#
AND I.OBJ# = PO.OBJ#
AND IO.NAMESPACE = 4
AND IO.REMOTEOWNER IS NULL
AND IO.LINKNAME IS NULL
AND BITAND(IO.FLAGS
, 128) = 0 -- NOT IN RECYCLE BIN
AND (IO.OWNER# = USERENV('SCHEMAID') OR I.BO# IN (SELECT OBJ#
FROM SYS.OBJAUTH$
WHERE GRANTEE# IN ( SELECT KZSROROL
FROM X$KZSRO ) ) OR 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 */) ) ) UNION ALL /* COMPOSITE PARTITIONS */ SELECT U.NAME
, IO.NAME
, UT.NAME
, OT.NAME
, IO.SUBNAME
, ICP.PART#
, NULL
, NULL
, 'PARTITION'
, ICP.BLEVEL
, ICP.LEAFCNT
, ICP.DISTKEY
, ICP.LBLKKEY
, ICP.DBLKKEY
, ICP.CLUFAC
, ICP.ROWCNT
, INS.CACHEDBLK
, INS.CACHEHIT
, ICP.SAMPLESIZE
, ICP.ANALYZETIME
, DECODE(BITAND(ICP.FLAGS
, 16)
, 0
, 'NO'
, 'YES')
, DECODE(BITAND(ICP.FLAGS
, 8)
, 0
, 'NO'
, 'YES')
, /* STATTYPE_LOCKED */ (SELECT -- NOT A LOCAL INDEX
, JUST LOOK AT THE LOCK AT TABLE LEVEL DECODE(BITAND(T.TRIGFLAG
, 67108864) + BITAND(T.TRIGFLAG
, 134217728)
, 0
, NULL
, 67108864
, 'DATA'
, 134217728
, 'CACHE'
, 'ALL')
FROM SYS.TAB$ T
WHERE T.OBJ# = I.BO# AND BITAND(PO.FLAGS
, 1) = 0 -- NOT LOCAL INDEX UNION ALL SELECT -- LOCAL INDEX
, WE NEED TO SEE IF THE CORRESPONDING PARTN IS LOCKED DECODE( /* * FOLLOWING DECODE RETURNS 1 IF DATA STATS LOCKED FOR PARTITION * OR AT TABLE LEVEL */ DECODE(BITAND(T.TRIGFLAG
, 67108864) + BITAND(TCP.FLAGS
, 32)
, 0
, 0
, 1) + /* * FOLLOWING DECODE RETURNS 2 IF CACHE STATS LOCKED FOR PARTITION * OR AT TABLE LEVEL */ DECODE(BITAND(T.TRIGFLAG
, 134217728) + BITAND(TCP.FLAGS
, 64)
, 0
, 0
, 2)
, /* IF 0 => NOT LOCKED
, 3 => DATA
AND CACHE STATS LOCKED */ 0
, NULL
, 1
, 'DATA'
, 2
, 'CACHE'
, 'ALL')
FROM SYS.TABCOMPARTV$ TCP
, SYS.TAB$ T
WHERE TCP.BO# = I.BO#
AND TCP.PHYPART# = ICP.PHYPART# AND TCP.BO# = T.OBJ# AND BITAND(PO.FLAGS
, 1) = 1)
, -- LOCAL INDEX /* STALE_STATS */ (SELECT CASE WHEN (I.ANALYZETIME IS NULL OR TAB.ANALYZETIME IS NULL) THEN NULL WHEN (I.ANALYZETIME < TAB.ANALYZETIME OR ((M.INSERTS + M.DELETES + M.UPDATES) > TAB.ROWCNT * TO_NUMBER(DBMS_STATS.GET_PREFS('STALE_PERCENT'
, DBMS_STATS_INTERNAL.DQ(UT.NAME)
, DBMS_STATS_INTERNAL.DQ(OT.NAME)))/100 OR BITAND(M.FLAGS
, 1) = 1)) THEN 'YES' ELSE 'NO' END
FROM SYS.TAB$ TAB
, SYS.MON_MODS_ALL$ M
WHERE TAB.OBJ# = I.BO#
AND TAB.OBJ# = M.OBJ# (+) AND BITAND(PO.FLAGS
, 1) = 0 -- NOT LOCAL INDEX UNION ALL SELECT CASE WHEN (ICP.ANALYZETIME IS NULL OR TCP.ANALYZETIME IS NULL) THEN NULL WHEN (ICP.ANALYZETIME < TCP.ANALYZETIME OR ((M.INSERTS + M.DELETES + M.UPDATES) > TCP.ROWCNT * TO_NUMBER(DBMS_STATS.GET_PREFS('STALE_PERCENT'
, DBMS_STATS_INTERNAL.DQ(UT.NAME)
, DBMS_STATS_INTERNAL.DQ(OT.NAME)))/100 OR BITAND(M.FLAGS
, 1) = 1)) THEN 'YES' ELSE 'NO' END
FROM SYS.TABCOMPARTV$ TCP
, SYS.MON_MODS_ALL$ M
WHERE TCP.BO# = I.BO#
AND TCP.PHYPART# = ICP.PHYPART# AND TCP.OBJ# = M.OBJ# (+) AND BITAND(PO.FLAGS
, 1) = 1)
, -- LOCAL INDEX 'SHARED' FROM SYS.OBJ$ IO
, SYS.INDCOMPARTV$ ICP
, SYS.USER$ U
, SYS.IND_STATS$ INS
, SYS.IND$ I
, SYS.OBJ$ OT
, SYS.USER$ UT
, SYS.PARTOBJ$ PO WHERE IO.OBJ# = ICP.OBJ#
AND IO.OWNER# = U.USER#
AND ICP.OBJ# = INS.OBJ# (+)
AND I.OBJ# = ICP.BO#
AND ICP.BO# = I.OBJ#
AND I.TYPE# != 9 -- NO DOMAIN INDEXES
AND I.BO# = OT.OBJ#
AND OT.TYPE# = 2
AND OT.OWNER# = UT.USER#
AND I.OBJ# = PO.OBJ#
AND IO.NAMESPACE = 4
AND IO.REMOTEOWNER IS NULL
AND IO.LINKNAME IS NULL
AND BITAND(IO.FLAGS
, 128) = 0 -- NOT IN RECYCLE BIN
AND (IO.OWNER# = USERENV('SCHEMAID') OR I.BO# 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 */) ) ) UNION ALL /* SUBPARTITIONS */ SELECT U.NAME
, OP.NAME
, UT.NAME
, OT.NAME
, OP.SUBNAME
, ICP.PART#
, OS.SUBNAME
, ISP.SUBPART#
, 'SUBPARTITION'
, ISP.BLEVEL
, ISP.LEAFCNT
, ISP.DISTKEY
, ISP.LBLKKEY
, ISP.DBLKKEY
, ISP.CLUFAC
, ISP.ROWCNT
, INS.CACHEDBLK
, INS.CACHEHIT
, ISP.SAMPLESIZE
, ISP.ANALYZETIME
, DECODE(BITAND(ISP.FLAGS
, 16)
, 0
, 'NO'
, 'YES')
, DECODE(BITAND(ISP.FLAGS
, 8)
, 0
, 'NO'
, 'YES')
, /* STATTYPE_LOCKED */ (SELECT -- NOT A LOCAL INDEX
, JUST LOOK AT THE LOCK AT TABLE LEVEL DECODE(BITAND(T.TRIGFLAG
, 67108864) + BITAND(T.TRIGFLAG
, 134217728)
, 0
, NULL
, 67108864
, 'DATA'
, 134217728
, 'CACHE'
, 'ALL')
FROM SYS.TAB$ T
WHERE T.OBJ# = I.BO# AND BITAND(PO.FLAGS
, 1) = 0 -- NOT LOCAL INDEX UNION ALL SELECT -- LOCAL INDEX
, WE NEED TO SEE IF THE CORRESPONDING COMPOSITE PARTN -- IS LOCKED DECODE( /* * FOLLOWING DECODE RETURNS 1 IF DATA STATS LOCKED FOR PARTITION * OR AT TABLE LEVEL */ DECODE(BITAND(T.TRIGFLAG
, 67108864) + BITAND(TCP.FLAGS
, 32)
, 0
, 0
, 1) + /* * FOLLOWING DECODE RETURNS 2 IF CACHE STATS LOCKED FOR PARTITION * OR AT TABLE LEVEL */ DECODE(BITAND(T.TRIGFLAG
, 134217728) + BITAND(TCP.FLAGS
, 64)
, 0
, 0
, 2)
, /* IF 0 => NOT LOCKED
, 3 => DATA
AND CACHE STATS LOCKED */ 0
, NULL
, 1
, 'DATA'
, 2
, 'CACHE'
, 'ALL')
FROM SYS.TABCOMPARTV$ TCP
, SYS.TABSUBPARTV$ TSP
, SYS.TAB$ T
WHERE TCP.BO# = I.BO#
AND TCP.PHYPART# = ICP.PHYPART# AND TSP.POBJ# = TCP.OBJ# AND ISP.PHYSUBPART# = TSP.PHYSUBPART# AND TCP.BO# = T.OBJ# AND BITAND(PO.FLAGS
, 1) = 1)
, -- LOCAL INDEX /* STALE_STATS */ (SELECT CASE WHEN (I.ANALYZETIME IS NULL OR TAB.ANALYZETIME IS NULL) THEN NULL WHEN (I.ANALYZETIME < TAB.ANALYZETIME OR ((M.INSERTS + M.DELETES + M.UPDATES) > TAB.ROWCNT * TO_NUMBER(DBMS_STATS.GET_PREFS('STALE_PERCENT'
, DBMS_STATS_INTERNAL.DQ(UT.NAME)
, DBMS_STATS_INTERNAL.DQ(OT.NAME)))/100 OR BITAND(M.FLAGS
, 1) = 1)) THEN 'YES' ELSE 'NO' END
FROM SYS.TAB$ TAB
, SYS.MON_MODS_ALL$ M
WHERE TAB.OBJ# = I.BO#
AND TAB.OBJ# = M.OBJ# (+) AND BITAND(PO.FLAGS
, 1) = 0 -- NOT LOCAL INDEX UNION ALL SELECT CASE WHEN (ISP.ANALYZETIME IS NULL OR TSP.ANALYZETIME IS NULL) THEN NULL WHEN (ISP.ANALYZETIME < TSP.ANALYZETIME OR ((M.INSERTS + M.DELETES + M.UPDATES) > TSP.ROWCNT * TO_NUMBER(DBMS_STATS.GET_PREFS('STALE_PERCENT'
, DBMS_STATS_INTERNAL.DQ(UT.NAME)
, DBMS_STATS_INTERNAL.DQ(OT.NAME)))/100 OR BITAND(M.FLAGS
, 1) = 1)) THEN 'YES' ELSE 'NO' END
FROM SYS.TABCOMPARTV$ TCP
, SYS.TABSUBPARTV$ TSP
, SYS.MON_MODS_ALL$ M
WHERE TCP.BO# = I.BO#
AND TCP.PHYPART# = ICP.PHYPART# AND TSP.POBJ# = TCP.OBJ# AND ISP.PHYSUBPART# = TSP.PHYSUBPART# AND TSP.OBJ# = M.OBJ# (+) AND BITAND(PO.FLAGS
, 1) = 1)
, -- LOCAL INDEX 'SHARED' FROM SYS.OBJ$ OS
, SYS.OBJ$ OP
, SYS.INDCOMPARTV$ ICP
, SYS.INDSUBPARTV$ ISP
, SYS.USER$ U
, SYS.IND_STATS$ INS
, SYS.IND$ I
, SYS.OBJ$ OT
, SYS.USER$ UT
, SYS.PARTOBJ$ PO WHERE OS.OBJ# = ISP.OBJ#
AND OP.OBJ# = ICP.OBJ#
AND ICP.OBJ# = ISP.POBJ#
AND ICP.BO# = I.OBJ#
AND I.TYPE# != 9 -- NO DOMAIN INDEXES
AND U.USER# = OP.OWNER#
AND ISP.OBJ# = INS.OBJ# (+)
AND ICP.BO# = I.OBJ#
AND I.BO# = OT.OBJ#
AND OT.TYPE# = 2
AND OT.OWNER# = UT.USER#
AND I.OBJ# = PO.OBJ#
AND OP.NAMESPACE = 4
AND OP.REMOTEOWNER IS NULL
AND OP.LINKNAME IS NULL
AND BITAND(OP.FLAGS
, 128) = 0 -- NOT IN RECYCLE BIN
AND (OP.OWNER# = USERENV('SCHEMAID') OR I.BO# 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 */) ) ) UNION ALL SELECT U.NAME
, O.NAME
, UT.NAME
, OT.NAME
, NULL
, NULL
, NULL
, NULL
, 'INDEX'
, SESI.BLEVEL_KXTTST_IS
, SESI.LEAFCNT_KXTTST_IS
, SESI.DISTKEY_KXTTST_IS
, SESI.LBLKKEY_KXTTST_IS
, SESI.DBLKKEY_KXTTST_IS
, SESI.CLUFAC_KXTTST_IS
, SESI.ROWCNT_KXTTST_IS
, SESI.CACHEDBLK_KXTTST_IS
, SESI.CACHEHIT_KXTTST_IS
, SESI.SAMPLESIZE_KXTTST_IS
, SESI.ANALYZETIME_KXTTST_IS
, DECODE(BITAND(SESI.FLAGS_KXTTST_IS
, 2048)
, 0
, 'NO'
, 'YES')
, DECODE(BITAND(SESI.FLAGS_KXTTST_IS
, 64)
, 0
, 'NO'
, 'YES')
, NULL
, /* NO LOCK ON SESSION PRIVATE STATS */ NULL
, /* SESSION BASED DML MONITORING NOT AVAILABLE */ 'SESSION' FROM SYS.X$KXTTSTEIS SESI
, SYS.USER$ U
, SYS.IND$ I
, SYS.OBJ$ O
, SYS.OBJ$ OT
, SYS.USER$ UT WHERE U.USER# = O.OWNER#
AND O.OBJ# = I.OBJ#
AND I.OBJ# = SESI.OBJ#_KXTTST_IS
AND BITAND(I.FLAGS
, 4096) = 0
AND I.TYPE# IN (1
, 2
, 4
, 6
, 7
, 8)
AND I.BO# = OT.OBJ#
AND OT.TYPE# = 2
AND OT.OWNER# = UT.USER#
AND O.SUBNAME IS NULL
AND O.NAMESPACE = 4
AND O.REMOTEOWNER IS NULL
AND O.LINKNAME IS NULL
AND BITAND(O.FLAGS
, 128) = 0 -- NOT IN RECYCLE BIN
AND (O.OWNER# = USERENV('SCHEMAID') OR O.OBJ# IN ( SELECT OBJ#
FROM SYS.OBJAUTH$
WHERE GRANTEE# IN ( SELECT KZSROROL
FROM X$KZSRO ) ) OR 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 */) ) )