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.

[Home] [Help]

VIEW: SYS.DBA_IND_PENDING_STATS

Source

View Text - Preformatted


select u.name, o.name, ut.name, ot.name, o.subname, null,
         h.blevel, h.leafcnt, h.distkey, h.lblkkey, h.dblkkey,
         h.clufac, h.rowcnt, h.samplesize, h.analyzetime
  from   sys.user$ u,  sys.obj$ o,  sys.ind$ i,
         sys.user$ ut, sys.obj$ ot, sys.wri$_optstat_ind_history h
  where  u.user# = o.owner#   -- user(i) X obj(i)
    and  o.obj#  = i.obj#     -- obj(i)  X ind
    and  h.obj#  = i.obj#     -- stat    X ind
    and  i.bo#   = ot.obj#    -- ind     X obj(t)
    and  ut.user# = ot.owner# -- user(t) X obj(t)
    and  o.namespace = 4 and o.remoteowner IS NULL and o.linkname IS NULL
    and  i.type# in (1, 2, 3, 4, 6, 7, 8)
    and  bitand(i.flags, 4096) = 0  -- not a fake index
    and  h.savtime > systimestamp
  union all
  -- partitions
  select u.name, o.name, ut.name, ot.name, o.subname, null,
         h.blevel, h.leafcnt, h.distkey, h.lblkkey, h.dblkkey,
         h.clufac, h.rowcnt, h.samplesize, h.analyzetime
  from   sys.user$ u,  sys.obj$ o,  sys.ind$ i, indpart$ ip,
         sys.user$ ut, sys.obj$ ot, sys.wri$_optstat_ind_history h
  where  u.user# = o.owner#   -- user(i) X obj(i)
    and  ip.bo# = i.obj#
    and  h.obj# = ip.obj#
    and  i.bo#  = ot.obj#
    and  o.obj# = ip.obj#
    and  ut.user# = ot.owner#
    and  o.namespace = 4 and o.remoteowner IS NULL and o.linkname IS NULL
    and  i.type# in (1, 2, 3, 4, 6, 7, 8)
    and  bitand(i.flags, 4096) = 0  -- not a fake index
    and  h.savtime > systimestamp
  union all
  select u.name, o.name, ut.name, ot.name, o.subname, null,
         h.blevel, h.leafcnt, h.distkey, h.lblkkey, h.dblkkey,
         h.clufac, h.rowcnt, h.samplesize, h.analyzetime
  from   sys.user$ u,  sys.obj$ o,  sys.ind$ i, indcompart$ ip,
         sys.user$ ut, sys.obj$ ot, sys.wri$_optstat_ind_history h
  where  u.user# = o.owner#   -- user(i) X obj(i)
    and  ip.bo# = i.obj#
    and  h.obj# = ip.obj#
    and  i.bo#  = ot.obj#
    and  o.obj# = ip.obj#
    and  ut.user# = ot.owner#
    and  o.namespace = 4 and o.remoteowner IS NULL and o.linkname IS NULL
    and  i.type# in (1, 2, 3, 4, 6, 7, 8)
    and  bitand(i.flags, 4096) = 0  -- not a fake index
    and  h.savtime > systimestamp
  union all
  -- sub partitions
  select ui.name, oi.name, ut.name, ot.name, os.name, os.subname,
         h.blevel, h.leafcnt, h.distkey, h.lblkkey, h.dblkkey,
         h.clufac, h.rowcnt, h.samplesize, h.analyzetime
  from   sys.obj$ os, sys.indsubpart$ isp, sys.indcompart$ icp,
         sys.user$ ut, sys.obj$ ot,
         sys.obj$ oi,  sys.ind$ i, sys.user$ ui,
         sys.wri$_optstat_ind_history h
  where  ui.user# = oi.owner#
    and  os.obj#  = isp.obj#
    and  h.obj#   = isp.obj#
    and  isp.pobj#= icp.obj#
    and  icp.bo#  = i.obj#
    and  oi.obj#  = i.obj#
    and  i.bo#    = ot.obj#
    and  ut.user# = ot.owner#
    and  oi.type# = 1
    and  os.type# = 35
    and  ot.type# = 2
    and  os.namespace = 4 and os.remoteowner IS NULL and os.linkname IS NULL
    and  i.type# in (1, 2, 3, 4, 6, 7, 8)
    and  bitand(i.flags, 4096) = 0  -- not a fake index
    and  h.savtime > systimestamp

View Text - HTML Formatted

SELECT U.NAME
, O.NAME
, UT.NAME
, OT.NAME
, O.SUBNAME
, NULL
, H.BLEVEL
, H.LEAFCNT
, H.DISTKEY
, H.LBLKKEY
, H.DBLKKEY
, H.CLUFAC
, H.ROWCNT
, H.SAMPLESIZE
, H.ANALYZETIME
FROM SYS.USER$ U
, SYS.OBJ$ O
, SYS.IND$ I
, SYS.USER$ UT
, SYS.OBJ$ OT
, SYS.WRI$_OPTSTAT_IND_HISTORY H
WHERE U.USER# = O.OWNER# -- USER(I) X OBJ(I)
AND O.OBJ# = I.OBJ# -- OBJ(I) X IND
AND H.OBJ# = I.OBJ# -- STAT X IND
AND I.BO# = OT.OBJ# -- IND X OBJ(T)
AND UT.USER# = OT.OWNER# -- USER(T) X OBJ(T)
AND O.NAMESPACE = 4
AND O.REMOTEOWNER IS NULL
AND O.LINKNAME IS NULL
AND I.TYPE# IN (1
, 2
, 3
, 4
, 6
, 7
, 8)
AND BITAND(I.FLAGS
, 4096) = 0 -- NOT A FAKE INDEX
AND H.SAVTIME > SYSTIMESTAMP UNION ALL -- PARTITIONS SELECT U.NAME
, O.NAME
, UT.NAME
, OT.NAME
, O.SUBNAME
, NULL
, H.BLEVEL
, H.LEAFCNT
, H.DISTKEY
, H.LBLKKEY
, H.DBLKKEY
, H.CLUFAC
, H.ROWCNT
, H.SAMPLESIZE
, H.ANALYZETIME
FROM SYS.USER$ U
, SYS.OBJ$ O
, SYS.IND$ I
, INDPART$ IP
, SYS.USER$ UT
, SYS.OBJ$ OT
, SYS.WRI$_OPTSTAT_IND_HISTORY H
WHERE U.USER# = O.OWNER# -- USER(I) X OBJ(I)
AND IP.BO# = I.OBJ#
AND H.OBJ# = IP.OBJ#
AND I.BO# = OT.OBJ#
AND O.OBJ# = IP.OBJ#
AND UT.USER# = OT.OWNER#
AND O.NAMESPACE = 4
AND O.REMOTEOWNER IS NULL
AND O.LINKNAME IS NULL
AND I.TYPE# IN (1
, 2
, 3
, 4
, 6
, 7
, 8)
AND BITAND(I.FLAGS
, 4096) = 0 -- NOT A FAKE INDEX
AND H.SAVTIME > SYSTIMESTAMP UNION ALL SELECT U.NAME
, O.NAME
, UT.NAME
, OT.NAME
, O.SUBNAME
, NULL
, H.BLEVEL
, H.LEAFCNT
, H.DISTKEY
, H.LBLKKEY
, H.DBLKKEY
, H.CLUFAC
, H.ROWCNT
, H.SAMPLESIZE
, H.ANALYZETIME
FROM SYS.USER$ U
, SYS.OBJ$ O
, SYS.IND$ I
, INDCOMPART$ IP
, SYS.USER$ UT
, SYS.OBJ$ OT
, SYS.WRI$_OPTSTAT_IND_HISTORY H
WHERE U.USER# = O.OWNER# -- USER(I) X OBJ(I)
AND IP.BO# = I.OBJ#
AND H.OBJ# = IP.OBJ#
AND I.BO# = OT.OBJ#
AND O.OBJ# = IP.OBJ#
AND UT.USER# = OT.OWNER#
AND O.NAMESPACE = 4
AND O.REMOTEOWNER IS NULL
AND O.LINKNAME IS NULL
AND I.TYPE# IN (1
, 2
, 3
, 4
, 6
, 7
, 8)
AND BITAND(I.FLAGS
, 4096) = 0 -- NOT A FAKE INDEX
AND H.SAVTIME > SYSTIMESTAMP UNION ALL -- SUB PARTITIONS SELECT UI.NAME
, OI.NAME
, UT.NAME
, OT.NAME
, OS.NAME
, OS.SUBNAME
, H.BLEVEL
, H.LEAFCNT
, H.DISTKEY
, H.LBLKKEY
, H.DBLKKEY
, H.CLUFAC
, H.ROWCNT
, H.SAMPLESIZE
, H.ANALYZETIME
FROM SYS.OBJ$ OS
, SYS.INDSUBPART$ ISP
, SYS.INDCOMPART$ ICP
, SYS.USER$ UT
, SYS.OBJ$ OT
, SYS.OBJ$ OI
, SYS.IND$ I
, SYS.USER$ UI
, SYS.WRI$_OPTSTAT_IND_HISTORY H
WHERE UI.USER# = OI.OWNER#
AND OS.OBJ# = ISP.OBJ#
AND H.OBJ# = ISP.OBJ#
AND ISP.POBJ#= ICP.OBJ#
AND ICP.BO# = I.OBJ#
AND OI.OBJ# = I.OBJ#
AND I.BO# = OT.OBJ#
AND UT.USER# = OT.OWNER#
AND OI.TYPE# = 1
AND OS.TYPE# = 35
AND OT.TYPE# = 2
AND OS.NAMESPACE = 4
AND OS.REMOTEOWNER IS NULL
AND OS.LINKNAME IS NULL
AND I.TYPE# IN (1
, 2
, 3
, 4
, 6
, 7
, 8)
AND BITAND(I.FLAGS
, 4096) = 0 -- NOT A FAKE INDEX
AND H.SAVTIME > SYSTIMESTAMP