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_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, 4, 6, 7, 8)
    and  bitand(i.flags, 4096) = 0  -- not a fake index
    and  h.savtime > systimestamp
    and (o.owner# = userenv('SCHEMAID')
       or o.obj# 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
  -- 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
    and (o.owner# = userenv('SCHEMAID')
       or o.obj# 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, 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
    and (o.owner# = userenv('SCHEMAID')
       or o.obj# 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
  -- 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
    and  (ot.owner# = userenv('SCHEMAID')
        or ot.obj# 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 */)
                 )
        )

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
, 4
, 6
, 7
, 8)
AND BITAND(I.FLAGS
, 4096) = 0 -- NOT A FAKE INDEX
AND H.SAVTIME > SYSTIMESTAMP
AND (O.OWNER# = USERENV('SCHEMAID') OR O.OBJ# 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 -- 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
AND (O.OWNER# = USERENV('SCHEMAID') OR O.OBJ# 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
, 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
AND (O.OWNER# = USERENV('SCHEMAID') OR O.OBJ# 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 -- 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
AND (OT.OWNER# = USERENV('SCHEMAID') OR OT.OBJ# 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 */) ) )