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

Source

View Text - Preformatted


select 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.obj$ o,  sys.ind$ i,
         sys.user$ ut, sys.obj$ ot, sys.wri$_optstat_ind_history h
  where  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
    and  o.owner# = userenv('SCHEMAID')
  union all
  -- partitions
  select 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.obj$ o,  sys.ind$ i, indpart$ ip,
         sys.user$ ut, sys.obj$ ot, sys.wri$_optstat_ind_history h
  where  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
    and  o.owner# = userenv('SCHEMAID')
  union all
  select 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.obj$ o,  sys.ind$ i, indcompart$ ip,
         sys.user$ ut, sys.obj$ ot, sys.wri$_optstat_ind_history h
  where  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
    and  o.owner# = userenv('SCHEMAID')
  union all
  -- sub partitions
  select 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.wri$_optstat_ind_history h
  where  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
    and  ot.owner# = userenv('SCHEMAID')

View Text - HTML Formatted

SELECT 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.OBJ$ O
, SYS.IND$ I
, SYS.USER$ UT
, SYS.OBJ$ OT
, SYS.WRI$_OPTSTAT_IND_HISTORY H
WHERE 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
AND O.OWNER# = USERENV('SCHEMAID') UNION ALL -- PARTITIONS SELECT 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.OBJ$ O
, SYS.IND$ I
, INDPART$ IP
, SYS.USER$ UT
, SYS.OBJ$ OT
, SYS.WRI$_OPTSTAT_IND_HISTORY H
WHERE 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
AND O.OWNER# = USERENV('SCHEMAID') UNION ALL SELECT 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.OBJ$ O
, SYS.IND$ I
, INDCOMPART$ IP
, SYS.USER$ UT
, SYS.OBJ$ OT
, SYS.WRI$_OPTSTAT_IND_HISTORY H
WHERE 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
AND O.OWNER# = USERENV('SCHEMAID') UNION ALL -- SUB PARTITIONS SELECT 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.WRI$_OPTSTAT_IND_HISTORY H
WHERE 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
AND OT.OWNER# = USERENV('SCHEMAID')