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.USER_TAB_STATISTICS

Source

View Text - Preformatted


SELECT /* TABLES */
    o.name, NULL, NULL, NULL, NULL, 'TABLE', t.rowcnt,
    decode(bitand(t.property, 64), 0, t.blkcnt, TO_NUMBER(NULL)),
    decode(bitand(t.property, 64), 0, t.empcnt, TO_NUMBER(NULL)),
    decode(bitand(t.property, 64), 0, t.avgspc, TO_NUMBER(NULL)),
    t.chncnt, t.avgrln, t.avgspc_flb,
    decode(bitand(t.property, 64), 0, t.flbcnt, TO_NUMBER(NULL)),
    ts.cachedblk, ts.cachehit, t.samplesize, t.analyzetime,
    decode(bitand(t.flags, 512), 0, 'NO', 'YES'),
    decode(bitand(t.flags, 256), 0, 'NO', 'YES'),
    decode(bitand(t.trigflag, 67108864) + bitand(t.trigflag, 134217728),
           0, NULL, 67108864, 'DATA', 134217728, 'CACHE', 'ALL'),
    case
      when t.analyzetime is null then null
      when ((m.inserts + m.deletes + m.updates) >
             t.rowcnt *
             to_number(DBMS_STATS.GET_PREFS('STALE_PERCENT',
                                            SYS_CONTEXT('USERENV', 'SESSION_USER'),
                                            DBMS_STATS_INTERNAL.DQ(o.name)))/100 or
            bitand(m.flags,1) = 1) then 'YES'
      else  'NO'
    end,
    'SHARED'
  FROM
    sys.obj$ o, sys.tab$ t, sys.tab_stats$ ts, sys.mon_mods_all$ m
  WHERE
        o.obj# = t.obj#
    and bitand(t.property, 1) = 0 /* not a typed table */
    and o.obj# = ts.obj# (+)
    and t.obj# = m.obj# (+)
    and o.owner# = userenv('SCHEMAID') and o.subname IS NULL
    and o.namespace = 1 and o.remoteowner IS NULL and o.linkname IS NULL
    and bitand(o.flags, 128) = 0 -- not in recycle bin
  UNION ALL
  SELECT /* PARTITIONS,  NOT IOT */
    o.name, o.subname, tp.part#, NULL, NULL, 'PARTITION',
    tp.rowcnt, tp.blkcnt, tp.empcnt, tp.avgspc,
    tp.chncnt, tp.avgrln, TO_NUMBER(NULL), TO_NUMBER(NULL),
    ts.cachedblk, ts.cachehit, tp.samplesize, tp.analyzetime,
    decode(bitand(tp.flags, 16), 0, 'NO', 'YES'),
    decode(bitand(tp.flags, 8), 0, 'NO', 'YES'),
    decode(
      /*
       * Following decode returns 1 if DATA stats locked for partition
       * or at table level
       */
      decode(bitand(tab.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(tab.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'),
    case
      when tp.analyzetime is null then null
      when ((m.inserts + m.deletes + m.updates) >
             tp.rowcnt *
             to_number(DBMS_STATS.GET_PREFS('STALE_PERCENT',
                                            SYS_CONTEXT('USERENV', 'SESSION_USER'),
                                            DBMS_STATS_INTERNAL.DQ(o.name)))/100 or
            bitand(m.flags,1) = 1) then 'YES'
      else  'NO'
    end,
    'SHARED'
  FROM
    sys.obj$ o, sys.tabpartv$ tp, sys.tab_stats$ ts, sys.tab$ tab,
    sys.mon_mods_all$ m
  WHERE
        o.obj# = tp.obj#
    and tp.bo# = tab.obj#
    and bitand(tab.property, 64) = 0
    and o.obj# = ts.obj# (+)
    and tp.obj# = m.obj# (+)
    and o.owner# = userenv('SCHEMAID')
    and o.namespace = 1 and o.remoteowner IS NULL and o.linkname IS NULL
    and bitand(o.flags, 128) = 0 -- not in recycle bin
  UNION ALL
  SELECT /* IOT Partitions */
    o.name, o.subname, tp.part#, NULL, NULL, 'PARTITION',
    tp.rowcnt, TO_NUMBER(NULL), TO_NUMBER(NULL), TO_NUMBER(NULL),
    tp.chncnt, tp.avgrln, TO_NUMBER(NULL), TO_NUMBER(NULL),
    TO_NUMBER(NULL), TO_NUMBER(NULL), tp.samplesize, tp.analyzetime,
    decode(bitand(tp.flags, 16), 0, 'NO', 'YES'),
    decode(bitand(tp.flags, 8), 0, 'NO', 'YES'),
    decode(
      /*
       * Following decode returns 1 if DATA stats locked for partition
       * or at table level
       */
      decode(bitand(tab.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(tab.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'),
    case
      when tp.analyzetime is null then null
      when ((m.inserts + m.deletes + m.updates) >
             tp.rowcnt *
             to_number(DBMS_STATS.GET_PREFS('STALE_PERCENT',
                                            SYS_CONTEXT('USERENV', 'SESSION_USER'),
                                            DBMS_STATS_INTERNAL.DQ(o.name)))/100 or
            bitand(m.flags,1) = 1) then 'YES'
      else 'NO'
    end,
    'SHARED'
  FROM
    sys.obj$ o, sys.tabpartv$ tp, sys.tab$ tab, sys.mon_mods_all$ m
  WHERE
        o.obj# = tp.obj#
    and tp.bo# = tab.obj#
    and bitand(tab.property, 64) = 64
    and tp.obj# = m.obj# (+)
    and o.owner# = userenv('SCHEMAID')
    and o.namespace = 1 and o.remoteowner IS NULL and o.linkname IS NULL
    and bitand(o.flags, 128) = 0 -- not in recycle bin
  UNION ALL
  SELECT /* COMPOSITE PARTITIONS */
    o.name, o.subname, tcp.part#, NULL, NULL, 'PARTITION',
    tcp.rowcnt, tcp.blkcnt, tcp.empcnt, tcp.avgspc,
    tcp.chncnt, tcp.avgrln, NULL, NULL, ts.cachedblk, ts.cachehit,
    tcp.samplesize, tcp.analyzetime,
    decode(bitand(tcp.flags, 16), 0, 'NO', 'YES'),
    decode(bitand(tcp.flags, 8), 0, 'NO', 'YES'),
    decode(
      /*
       * Following decode returns 1 if DATA stats locked for partition
       * or at table level
       */
      decode(bitand(tab.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(tab.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'),
    case
      when tcp.analyzetime is null then null
      when ((m.inserts + m.deletes + m.updates) >
             tcp.rowcnt *
             to_number(DBMS_STATS.GET_PREFS('STALE_PERCENT',
                                            SYS_CONTEXT('USERENV', 'SESSION_USER'),
                                            DBMS_STATS_INTERNAL.DQ(o.name)))/100 or
            bitand(m.flags,1) = 1) then 'YES'
      else 'NO'
    end,
    'SHARED'
  FROM
    sys.obj$ o, sys.tabcompartv$ tcp, sys.tab_stats$ ts, sys.tab$ tab,
    sys.mon_mods_all$ m
  WHERE
        o.obj# = tcp.obj#
    and tcp.bo# = tab.obj#
    and o.obj# = ts.obj# (+)
    and tcp.obj# = m.obj# (+)
    and o.owner# = userenv('SCHEMAID')
    and o.namespace = 1 and o.remoteowner IS NULL and o.linkname IS NULL
    and bitand(o.flags, 128) = 0 -- not in recycle bin
  UNION ALL
  SELECT /* SUBPARTITIONS */
    po.name, po.subname, tcp.part#,  so.subname, tsp.subpart#,
   'SUBPARTITION', tsp.rowcnt,
    tsp.blkcnt, tsp.empcnt, tsp.avgspc,
    tsp.chncnt, tsp.avgrln, NULL, NULL,
    ts.cachedblk, ts.cachehit, tsp.samplesize, tsp.analyzetime,
    decode(bitand(tsp.flags, 16), 0, 'NO', 'YES'),
    decode(bitand(tsp.flags, 8), 0, 'NO', 'YES'),
    decode(
      /*
       * Following decode returns 1 if DATA stats locked for partition
       * or at table level.
       * Note that dbms_stats does n't allow locking subpartition stats.
       * If the composite partition is locked, all subpartitions are
       * considered locked. Hence decode checks for tcp entry.
       */
      decode(bitand(tab.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(tab.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'),
    case
      when tsp.analyzetime is null then null
      when ((m.inserts + m.deletes + m.updates) >
             tsp.rowcnt *
             to_number(DBMS_STATS.GET_PREFS('STALE_PERCENT',
                                            SYS_CONTEXT('USERENV', 'SESSION_USER'),
                                            DBMS_STATS_INTERNAL.DQ(po.name)))/100 or
            bitand(m.flags,1) = 1) then 'YES'
      else  'NO'
    end,
    'SHARED'
  FROM
    sys.obj$ po, sys.obj$ so, sys.tabcompartv$ tcp, sys.tabsubpartv$ tsp,
    sys.tab_stats$ ts, sys.tab$ tab, sys.mon_mods_all$ m
  WHERE
        so.obj# = tsp.obj#
    and po.obj# = tcp.obj#
    and tcp.obj# = tsp.pobj#
    and tcp.bo# = tab.obj#
    and bitand(tab.property, 64) = 0
    and so.obj# = ts.obj# (+)
    and tsp.obj# = m.obj# (+)
    and po.owner# = userenv('SCHEMAID')
    and po.namespace = 1 and po.remoteowner IS NULL and po.linkname IS NULL
    and bitand(po.flags, 128) = 0 -- not in recycle bin
  UNION ALL
  SELECT /* FIXED TABLES */
    t.kqftanam, NULL, NULL, NULL, NULL, 'FIXED TABLE',
    decode(nvl(fobj.obj#, 0), 0, TO_NUMBER(NULL), st.rowcnt),
    TO_NUMBER(NULL), TO_NUMBER(NULL), TO_NUMBER(NULL), TO_NUMBER(NULL),
    decode(nvl(fobj.obj#, 0), 0, TO_NUMBER(NULL), st.avgrln),
    TO_NUMBER(NULL), TO_NUMBER(NULL), TO_NUMBER(NULL), TO_NUMBER(NULL),
    decode(nvl(fobj.obj#, 0), 0, TO_NUMBER(NULL), st.samplesize),
    decode(nvl(fobj.obj#, 0), 0, TO_DATE(NULL), st.analyzetime),
    decode(nvl(fobj.obj#, 0), 0, NULL,
           decode(nvl(st.obj#, 0), 0, NULL, 'YES')),
    decode(nvl(fobj.obj#, 0), 0, NULL,
           decode(nvl(st.obj#, 0), 0, NULL,
                  decode(bitand(st.flags, 1), 0, 'NO', 'YES'))),
    decode(nvl(fobj.obj#, 0), 0, NULL,
           decode (bitand(fobj.flags, 67108864) +
                     bitand(fobj.flags, 134217728),
                   0, NULL, 67108864, 'DATA', 134217728, 'CACHE', 'ALL')),
    NULL,
    'SHARED'
    FROM sys.x$kqfta t, sys.fixed_obj$ fobj, sys.tab_stats$ st
    where
    t.kqftaobj = fobj.obj#(+)
    /*
     * if fobj and st are not in sync (happens when db open read only
     * after upgrade), do not display stats.
     */
    and t.kqftaver = fobj.timestamp (+) - to_date('01-01-1991', 'DD-MM-YYYY')
    and t.kqftaobj = st.obj#(+)
    and userenv('SCHEMAID') = 0  /* SYS */
  UNION ALL
  SELECT /* session private stats for GTT */
    o.name, NULL, NULL, NULL, NULL, 'TABLE', ses.rowcnt_kxttst_ts,
    decode(bitand(t.property, 64), 0, ses.blkcnt_kxttst_ts, TO_NUMBER(NULL)),
    decode(bitand(t.property, 64), 0, ses.empcnt_kxttst_ts, TO_NUMBER(NULL)),
    decode(bitand(t.property, 64), 0, ses.avgspc_kxttst_ts, TO_NUMBER(NULL)),
    ses.chncnt_kxttst_ts, ses.avgrln_kxttst_ts, ses.avgspc_flb_kxttst_ts,
    decode(bitand(t.property, 64), 0, ses.flbcnt_kxttst_ts, TO_NUMBER(NULL)),
    ses.cachedblk_kxttst_ts, ses.cachehit_kxttst_ts,
    ses.samplesize_kxttst_ts, ses.analyzetime_kxttst_ts,
    /* kketsflg = 8 (KQLDTVCF_GLS) */
    decode(bitand(ses.flags_kxttst_ts, 8), 0, 'NO', 'YES'),
    /* kketsflg = 4 (KQLDTVCF_USS) */
    decode(bitand(ses.flags_kxttst_ts, 4), 0, 'NO', 'YES'),
    null,  /* no lock on session private stats */
    null,  /* session based dml monitoring not available */
    'SESSION'
  FROM
    sys.x$kxttstets ses,
    sys.obj$ o, sys.tab$ t
  WHERE
        o.obj# = t.obj#
    and t.obj# = ses.obj#_kxttst_ts
    and bitand(t.property, 1) = 0 /* not a typed table */
    and o.owner# = userenv('SCHEMAID') and o.subname IS NULL
    and o.namespace = 1 and o.remoteowner IS NULL and o.linkname IS NULL
    and bitand(o.flags, 128) = 0 -- not in recycle bin

View Text - HTML Formatted

SELECT /* TABLES */ O.NAME
, NULL
, NULL
, NULL
, NULL
, 'TABLE'
, T.ROWCNT
, DECODE(BITAND(T.PROPERTY
, 64)
, 0
, T.BLKCNT
, TO_NUMBER(NULL))
, DECODE(BITAND(T.PROPERTY
, 64)
, 0
, T.EMPCNT
, TO_NUMBER(NULL))
, DECODE(BITAND(T.PROPERTY
, 64)
, 0
, T.AVGSPC
, TO_NUMBER(NULL))
, T.CHNCNT
, T.AVGRLN
, T.AVGSPC_FLB
, DECODE(BITAND(T.PROPERTY
, 64)
, 0
, T.FLBCNT
, TO_NUMBER(NULL))
, TS.CACHEDBLK
, TS.CACHEHIT
, T.SAMPLESIZE
, T.ANALYZETIME
, DECODE(BITAND(T.FLAGS
, 512)
, 0
, 'NO'
, 'YES')
, DECODE(BITAND(T.FLAGS
, 256)
, 0
, 'NO'
, 'YES')
, DECODE(BITAND(T.TRIGFLAG
, 67108864) + BITAND(T.TRIGFLAG
, 134217728)
, 0
, NULL
, 67108864
, 'DATA'
, 134217728
, 'CACHE'
, 'ALL')
, CASE WHEN T.ANALYZETIME IS NULL THEN NULL WHEN ((M.INSERTS + M.DELETES + M.UPDATES) > T.ROWCNT * TO_NUMBER(DBMS_STATS.GET_PREFS('STALE_PERCENT'
, SYS_CONTEXT('USERENV'
, 'SESSION_USER')
, DBMS_STATS_INTERNAL.DQ(O.NAME)))/100 OR BITAND(M.FLAGS
, 1) = 1) THEN 'YES' ELSE 'NO' END
, 'SHARED' FROM SYS.OBJ$ O
, SYS.TAB$ T
, SYS.TAB_STATS$ TS
, SYS.MON_MODS_ALL$ M WHERE O.OBJ# = T.OBJ#
AND BITAND(T.PROPERTY
, 1) = 0 /* NOT A TYPED TABLE */
AND O.OBJ# = TS.OBJ# (+)
AND T.OBJ# = M.OBJ# (+)
AND O.OWNER# = USERENV('SCHEMAID')
AND O.SUBNAME IS NULL
AND O.NAMESPACE = 1
AND O.REMOTEOWNER IS NULL
AND O.LINKNAME IS NULL
AND BITAND(O.FLAGS
, 128) = 0 -- NOT IN RECYCLE BIN UNION ALL SELECT /* PARTITIONS
, NOT IOT */ O.NAME
, O.SUBNAME
, TP.PART#
, NULL
, NULL
, 'PARTITION'
, TP.ROWCNT
, TP.BLKCNT
, TP.EMPCNT
, TP.AVGSPC
, TP.CHNCNT
, TP.AVGRLN
, TO_NUMBER(NULL)
, TO_NUMBER(NULL)
, TS.CACHEDBLK
, TS.CACHEHIT
, TP.SAMPLESIZE
, TP.ANALYZETIME
, DECODE(BITAND(TP.FLAGS
, 16)
, 0
, 'NO'
, 'YES')
, DECODE(BITAND(TP.FLAGS
, 8)
, 0
, 'NO'
, 'YES')
, DECODE( /* * FOLLOWING DECODE RETURNS 1 IF DATA STATS LOCKED FOR PARTITION * OR AT TABLE LEVEL */ DECODE(BITAND(TAB.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(TAB.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')
, CASE WHEN TP.ANALYZETIME IS NULL THEN NULL WHEN ((M.INSERTS + M.DELETES + M.UPDATES) > TP.ROWCNT * TO_NUMBER(DBMS_STATS.GET_PREFS('STALE_PERCENT'
, SYS_CONTEXT('USERENV'
, 'SESSION_USER')
, DBMS_STATS_INTERNAL.DQ(O.NAME)))/100 OR BITAND(M.FLAGS
, 1) = 1) THEN 'YES' ELSE 'NO' END
, 'SHARED' FROM SYS.OBJ$ O
, SYS.TABPARTV$ TP
, SYS.TAB_STATS$ TS
, SYS.TAB$ TAB
, SYS.MON_MODS_ALL$ M WHERE O.OBJ# = TP.OBJ#
AND TP.BO# = TAB.OBJ#
AND BITAND(TAB.PROPERTY
, 64) = 0
AND O.OBJ# = TS.OBJ# (+)
AND TP.OBJ# = M.OBJ# (+)
AND O.OWNER# = USERENV('SCHEMAID')
AND O.NAMESPACE = 1
AND O.REMOTEOWNER IS NULL
AND O.LINKNAME IS NULL
AND BITAND(O.FLAGS
, 128) = 0 -- NOT IN RECYCLE BIN UNION ALL SELECT /* IOT PARTITIONS */ O.NAME
, O.SUBNAME
, TP.PART#
, NULL
, NULL
, 'PARTITION'
, TP.ROWCNT
, TO_NUMBER(NULL)
, TO_NUMBER(NULL)
, TO_NUMBER(NULL)
, TP.CHNCNT
, TP.AVGRLN
, TO_NUMBER(NULL)
, TO_NUMBER(NULL)
, TO_NUMBER(NULL)
, TO_NUMBER(NULL)
, TP.SAMPLESIZE
, TP.ANALYZETIME
, DECODE(BITAND(TP.FLAGS
, 16)
, 0
, 'NO'
, 'YES')
, DECODE(BITAND(TP.FLAGS
, 8)
, 0
, 'NO'
, 'YES')
, DECODE( /* * FOLLOWING DECODE RETURNS 1 IF DATA STATS LOCKED FOR PARTITION * OR AT TABLE LEVEL */ DECODE(BITAND(TAB.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(TAB.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')
, CASE WHEN TP.ANALYZETIME IS NULL THEN NULL WHEN ((M.INSERTS + M.DELETES + M.UPDATES) > TP.ROWCNT * TO_NUMBER(DBMS_STATS.GET_PREFS('STALE_PERCENT'
, SYS_CONTEXT('USERENV'
, 'SESSION_USER')
, DBMS_STATS_INTERNAL.DQ(O.NAME)))/100 OR BITAND(M.FLAGS
, 1) = 1) THEN 'YES' ELSE 'NO' END
, 'SHARED' FROM SYS.OBJ$ O
, SYS.TABPARTV$ TP
, SYS.TAB$ TAB
, SYS.MON_MODS_ALL$ M WHERE O.OBJ# = TP.OBJ#
AND TP.BO# = TAB.OBJ#
AND BITAND(TAB.PROPERTY
, 64) = 64
AND TP.OBJ# = M.OBJ# (+)
AND O.OWNER# = USERENV('SCHEMAID')
AND O.NAMESPACE = 1
AND O.REMOTEOWNER IS NULL
AND O.LINKNAME IS NULL
AND BITAND(O.FLAGS
, 128) = 0 -- NOT IN RECYCLE BIN UNION ALL SELECT /* COMPOSITE PARTITIONS */ O.NAME
, O.SUBNAME
, TCP.PART#
, NULL
, NULL
, 'PARTITION'
, TCP.ROWCNT
, TCP.BLKCNT
, TCP.EMPCNT
, TCP.AVGSPC
, TCP.CHNCNT
, TCP.AVGRLN
, NULL
, NULL
, TS.CACHEDBLK
, TS.CACHEHIT
, TCP.SAMPLESIZE
, TCP.ANALYZETIME
, DECODE(BITAND(TCP.FLAGS
, 16)
, 0
, 'NO'
, 'YES')
, DECODE(BITAND(TCP.FLAGS
, 8)
, 0
, 'NO'
, 'YES')
, DECODE( /* * FOLLOWING DECODE RETURNS 1 IF DATA STATS LOCKED FOR PARTITION * OR AT TABLE LEVEL */ DECODE(BITAND(TAB.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(TAB.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')
, CASE WHEN TCP.ANALYZETIME IS NULL THEN NULL WHEN ((M.INSERTS + M.DELETES + M.UPDATES) > TCP.ROWCNT * TO_NUMBER(DBMS_STATS.GET_PREFS('STALE_PERCENT'
, SYS_CONTEXT('USERENV'
, 'SESSION_USER')
, DBMS_STATS_INTERNAL.DQ(O.NAME)))/100 OR BITAND(M.FLAGS
, 1) = 1) THEN 'YES' ELSE 'NO' END
, 'SHARED' FROM SYS.OBJ$ O
, SYS.TABCOMPARTV$ TCP
, SYS.TAB_STATS$ TS
, SYS.TAB$ TAB
, SYS.MON_MODS_ALL$ M WHERE O.OBJ# = TCP.OBJ#
AND TCP.BO# = TAB.OBJ#
AND O.OBJ# = TS.OBJ# (+)
AND TCP.OBJ# = M.OBJ# (+)
AND O.OWNER# = USERENV('SCHEMAID')
AND O.NAMESPACE = 1
AND O.REMOTEOWNER IS NULL
AND O.LINKNAME IS NULL
AND BITAND(O.FLAGS
, 128) = 0 -- NOT IN RECYCLE BIN UNION ALL SELECT /* SUBPARTITIONS */ PO.NAME
, PO.SUBNAME
, TCP.PART#
, SO.SUBNAME
, TSP.SUBPART#
, 'SUBPARTITION'
, TSP.ROWCNT
, TSP.BLKCNT
, TSP.EMPCNT
, TSP.AVGSPC
, TSP.CHNCNT
, TSP.AVGRLN
, NULL
, NULL
, TS.CACHEDBLK
, TS.CACHEHIT
, TSP.SAMPLESIZE
, TSP.ANALYZETIME
, DECODE(BITAND(TSP.FLAGS
, 16)
, 0
, 'NO'
, 'YES')
, DECODE(BITAND(TSP.FLAGS
, 8)
, 0
, 'NO'
, 'YES')
, DECODE( /* * FOLLOWING DECODE RETURNS 1 IF DATA STATS LOCKED FOR PARTITION * OR AT TABLE LEVEL. * NOTE THAT DBMS_STATS DOES N'T ALLOW LOCKING SUBPARTITION STATS. * IF THE COMPOSITE PARTITION IS LOCKED
, ALL SUBPARTITIONS ARE * CONSIDERED LOCKED. HENCE DECODE CHECKS FOR TCP ENTRY. */ DECODE(BITAND(TAB.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(TAB.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')
, CASE WHEN TSP.ANALYZETIME IS NULL THEN NULL WHEN ((M.INSERTS + M.DELETES + M.UPDATES) > TSP.ROWCNT * TO_NUMBER(DBMS_STATS.GET_PREFS('STALE_PERCENT'
, SYS_CONTEXT('USERENV'
, 'SESSION_USER')
, DBMS_STATS_INTERNAL.DQ(PO.NAME)))/100 OR BITAND(M.FLAGS
, 1) = 1) THEN 'YES' ELSE 'NO' END
, 'SHARED' FROM SYS.OBJ$ PO
, SYS.OBJ$ SO
, SYS.TABCOMPARTV$ TCP
, SYS.TABSUBPARTV$ TSP
, SYS.TAB_STATS$ TS
, SYS.TAB$ TAB
, SYS.MON_MODS_ALL$ M WHERE SO.OBJ# = TSP.OBJ#
AND PO.OBJ# = TCP.OBJ#
AND TCP.OBJ# = TSP.POBJ#
AND TCP.BO# = TAB.OBJ#
AND BITAND(TAB.PROPERTY
, 64) = 0
AND SO.OBJ# = TS.OBJ# (+)
AND TSP.OBJ# = M.OBJ# (+)
AND PO.OWNER# = USERENV('SCHEMAID')
AND PO.NAMESPACE = 1
AND PO.REMOTEOWNER IS NULL
AND PO.LINKNAME IS NULL
AND BITAND(PO.FLAGS
, 128) = 0 -- NOT IN RECYCLE BIN UNION ALL SELECT /* FIXED TABLES */ T.KQFTANAM
, NULL
, NULL
, NULL
, NULL
, 'FIXED TABLE'
, DECODE(NVL(FOBJ.OBJ#
, 0)
, 0
, TO_NUMBER(NULL)
, ST.ROWCNT)
, TO_NUMBER(NULL)
, TO_NUMBER(NULL)
, TO_NUMBER(NULL)
, TO_NUMBER(NULL)
, DECODE(NVL(FOBJ.OBJ#
, 0)
, 0
, TO_NUMBER(NULL)
, ST.AVGRLN)
, TO_NUMBER(NULL)
, TO_NUMBER(NULL)
, TO_NUMBER(NULL)
, TO_NUMBER(NULL)
, DECODE(NVL(FOBJ.OBJ#
, 0)
, 0
, TO_NUMBER(NULL)
, ST.SAMPLESIZE)
, DECODE(NVL(FOBJ.OBJ#
, 0)
, 0
, TO_DATE(NULL)
, ST.ANALYZETIME)
, DECODE(NVL(FOBJ.OBJ#
, 0)
, 0
, NULL
, DECODE(NVL(ST.OBJ#
, 0)
, 0
, NULL
, 'YES'))
, DECODE(NVL(FOBJ.OBJ#
, 0)
, 0
, NULL
, DECODE(NVL(ST.OBJ#
, 0)
, 0
, NULL
, DECODE(BITAND(ST.FLAGS
, 1)
, 0
, 'NO'
, 'YES')))
, DECODE(NVL(FOBJ.OBJ#
, 0)
, 0
, NULL
, DECODE (BITAND(FOBJ.FLAGS
, 67108864) + BITAND(FOBJ.FLAGS
, 134217728)
, 0
, NULL
, 67108864
, 'DATA'
, 134217728
, 'CACHE'
, 'ALL'))
, NULL
, 'SHARED'
FROM SYS.X$KQFTA T
, SYS.FIXED_OBJ$ FOBJ
, SYS.TAB_STATS$ ST WHERE T.KQFTAOBJ = FOBJ.OBJ#(+) /* * IF FOBJ
AND ST ARE NOT IN SYNC (HAPPENS WHEN DB OPEN READ ONLY * AFTER UPGRADE)
, DO NOT DISPLAY STATS. */
AND T.KQFTAVER = FOBJ.TIMESTAMP (+) - TO_DATE('01-01-1991'
, 'DD-MM-YYYY')
AND T.KQFTAOBJ = ST.OBJ#(+)
AND USERENV('SCHEMAID') = 0 /* SYS */ UNION ALL SELECT /* SESSION PRIVATE STATS FOR GTT */ O.NAME
, NULL
, NULL
, NULL
, NULL
, 'TABLE'
, SES.ROWCNT_KXTTST_TS
, DECODE(BITAND(T.PROPERTY
, 64)
, 0
, SES.BLKCNT_KXTTST_TS
, TO_NUMBER(NULL))
, DECODE(BITAND(T.PROPERTY
, 64)
, 0
, SES.EMPCNT_KXTTST_TS
, TO_NUMBER(NULL))
, DECODE(BITAND(T.PROPERTY
, 64)
, 0
, SES.AVGSPC_KXTTST_TS
, TO_NUMBER(NULL))
, SES.CHNCNT_KXTTST_TS
, SES.AVGRLN_KXTTST_TS
, SES.AVGSPC_FLB_KXTTST_TS
, DECODE(BITAND(T.PROPERTY
, 64)
, 0
, SES.FLBCNT_KXTTST_TS
, TO_NUMBER(NULL))
, SES.CACHEDBLK_KXTTST_TS
, SES.CACHEHIT_KXTTST_TS
, SES.SAMPLESIZE_KXTTST_TS
, SES.ANALYZETIME_KXTTST_TS
, /* KKETSFLG = 8 (KQLDTVCF_GLS) */ DECODE(BITAND(SES.FLAGS_KXTTST_TS
, 8)
, 0
, 'NO'
, 'YES')
, /* KKETSFLG = 4 (KQLDTVCF_USS) */ DECODE(BITAND(SES.FLAGS_KXTTST_TS
, 4)
, 0
, 'NO'
, 'YES')
, NULL
, /* NO LOCK ON SESSION PRIVATE STATS */ NULL
, /* SESSION BASED DML MONITORING NOT AVAILABLE */ 'SESSION' FROM SYS.X$KXTTSTETS SES
, SYS.OBJ$ O
, SYS.TAB$ T WHERE O.OBJ# = T.OBJ#
AND T.OBJ# = SES.OBJ#_KXTTST_TS
AND BITAND(T.PROPERTY
, 1) = 0 /* NOT A TYPED TABLE */
AND O.OWNER# = USERENV('SCHEMAID')
AND O.SUBNAME IS NULL
AND O.NAMESPACE = 1
AND O.REMOTEOWNER IS NULL
AND O.LINKNAME IS NULL
AND BITAND(O.FLAGS
, 128) = 0 -- NOT IN RECYCLE BIN