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_TAB_HISTOGRAMS

Source

View Text - Preformatted


select /*+ ordered */ o.name,
       decode(bitand(c.property, 1), 1, a.name, c.name),
       h.bucket,
       case when SYS_OP_DV_CHECK(o.name, o.owner#) = 1
            then h.endpoint
            else null
       end,
       case when SYS_OP_DV_CHECK(o.name, o.owner#) = 1
            then case when h.epvalue is not null then epvalue
                 else dbms_stats.conv_raw(h.epvalue_raw, c.type#) end
            else null
       end,
       case when SYS_OP_DV_CHECK(o.name, o.owner#) = 1
            then h.epvalue_raw
            else null
       end,
       case when SYS_OP_DV_CHECK(o.name, o.owner#) = 1
            then h.ep_repeat_count
            else null
       end,
       'SHARED'
from sys.obj$ o, sys.col$ c, sys.histgrm$ h, sys.attrcol$ a
where o.obj# = c.obj#
  and o.owner# = userenv('SCHEMAID')
  and c.obj# = h.obj# and c.intcol# = h.intcol#
  and (o.type# in (3, 4)                                     /* cluster, view */
       or
       (o.type# = 2     /* tables, excluding iot - overflow and nested tables */
        and
        not exists (select null
                      from sys.tab$ t
                     where t.obj# = o.obj#
                       and (bitand(t.property, 512) = 512 or
                            bitand(t.property, 8192) = 8192))))
  and c.obj# = a.obj#(+)
  and c.intcol# = a.intcol#(+)
union all
select /*+ ordered */ o.name,
       decode(bitand(c.property, 1), 1, a.name, c.name),
       0,
       case when SYS_OP_DV_CHECK(o.name, o.owner#) = 1
            then h.minimum
            else null
       end,
       null,
       null,
       0,
       'SHARED'
from sys.obj$ o, sys.col$ c, sys.hist_head$ h, sys.attrcol$ a
where o.obj# = c.obj#
  and o.owner# = userenv('SCHEMAID')
  and c.obj# = h.obj# and c.intcol# = h.intcol#
  and (o.type# in (3, 4)                                     /* cluster, view */
       or
       (o.type# = 2     /* tables, excluding iot - overflow and nested tables */
        and
        not exists (select null
                      from sys.tab$ t
                     where t.obj# = o.obj#
                       and (bitand(t.property, 512) = 512 or
                            bitand(t.property, 8192) = 8192))))
  and h.row_cnt = 0 and h.distcnt > 0
  and c.obj# = a.obj#(+)
  and c.intcol# = a.intcol#(+)
union all
select /*+ ordered */ o.name,
       decode(bitand(c.property, 1), 1, a.name, c.name),
       1,
       case when SYS_OP_DV_CHECK(o.name, o.owner#) = 1
            then h.maximum
            else null
       end,
       null,
       null,
       0,
       'SHARED'
from sys.obj$ o, sys.col$ c, sys.hist_head$ h, sys.attrcol$ a
where o.obj# = c.obj#
  and o.owner# = userenv('SCHEMAID')
  and c.obj# = h.obj# and c.intcol# = h.intcol#
  and (o.type# in (3, 4)                                     /* cluster, view */
       or
       (o.type# = 2     /* tables, excluding iot - overflow and nested tables */
        and
        not exists (select null
                      from sys.tab$ t
                     where t.obj# = o.obj#
                       and (bitand(t.property, 512) = 512 or
                            bitand(t.property, 8192) = 8192))))
  and h.row_cnt = 0 and h.distcnt > 0
  and c.obj# = a.obj#(+)
  and c.intcol# = a.intcol#(+)
union all
select /*+ ordered */
       ft.kqftanam,
       c.kqfconam,
       h.bucket,
       h.endpoint,
       case when h.epvalue is not null then epvalue
            else dbms_stats.conv_raw(h.epvalue_raw, c.KQFCODTY) end,
       h.epvalue_raw,
       h.ep_repeat_count,
       'SHARED'
from   sys.x$kqfta ft, sys.fixed_obj$ fobj, sys.x$kqfco c, sys.histgrm$ h
where  ft.kqftaobj = fobj. obj#
  and c.kqfcotob = ft.kqftaobj
  and h.obj# = ft.kqftaobj
  and h.intcol# = c.kqfcocno
  /*
   * if fobj and st are not in sync (happens when db open read only
   * after upgrade), do not display stats.
   */
  and ft.kqftaver =
         fobj.timestamp - to_date('01-01-1991', 'DD-MM-YYYY')
  and userenv('SCHEMAID') = 0  /* SYS */
union all
select /*+ ordered */ o.name,
       decode(bitand(c.property, 1), 1, a.name, c.name),
       h.bucket_kxttst_hs,
       case when SYS_OP_DV_CHECK(o.name, o.owner#) = 1
            then h.endpoint_kxttst_hs
            else null
       end,
       case when SYS_OP_DV_CHECK(o.name, o.owner#) = 1
            then dbms_stats.conv_raw(h.epvalue_raw_kxttst_hs, c.type#)
            else null
       end,
       case when SYS_OP_DV_CHECK(o.name, o.owner#) = 1
            then h.epvalue_raw_kxttst_hs
            else null
       end,
       case when SYS_OP_DV_CHECK(o.name, o.owner#) = 1
            then h.ep_repeat_count_kxttst_hs
            else null
       end,
       'SESSION'
from sys.obj$ o, sys.col$ c, sys.x$kxttstehs h, sys.attrcol$ a
where o.obj# = c.obj#
  and o.owner# = userenv('SCHEMAID')
  and c.obj# = h.obj#_kxttst_hs and c.intcol# = h.intcol#_kxttst_hs
  and (o.type# in (3, 4)                                     /* cluster, view */
       or
       (o.type# = 2     /* tables, excluding iot - overflow and nested tables */
        and
        not exists (select null
                      from sys.tab$ t
                     where t.obj# = o.obj#
                       and (bitand(t.property, 512) = 512 or
                            bitand(t.property, 8192) = 8192))))
  and c.obj# = a.obj#(+)
  and c.intcol# = a.intcol#(+)

View Text - HTML Formatted

SELECT /*+ ORDERED */ O.NAME
, DECODE(BITAND(C.PROPERTY
, 1)
, 1
, A.NAME
, C.NAME)
, H.BUCKET
, CASE WHEN SYS_OP_DV_CHECK(O.NAME
, O.OWNER#) = 1 THEN H.ENDPOINT ELSE NULL END
, CASE WHEN SYS_OP_DV_CHECK(O.NAME
, O.OWNER#) = 1 THEN CASE WHEN H.EPVALUE IS NOT NULL THEN EPVALUE ELSE DBMS_STATS.CONV_RAW(H.EPVALUE_RAW
, C.TYPE#) END ELSE NULL END
, CASE WHEN SYS_OP_DV_CHECK(O.NAME
, O.OWNER#) = 1 THEN H.EPVALUE_RAW ELSE NULL END
, CASE WHEN SYS_OP_DV_CHECK(O.NAME
, O.OWNER#) = 1 THEN H.EP_REPEAT_COUNT ELSE NULL END
, 'SHARED' FROM SYS.OBJ$ O
, SYS.COL$ C
, SYS.HISTGRM$ H
, SYS.ATTRCOL$ A WHERE O.OBJ# = C.OBJ#
AND O.OWNER# = USERENV('SCHEMAID')
AND C.OBJ# = H.OBJ#
AND C.INTCOL# = H.INTCOL#
AND (O.TYPE# IN (3
, 4) /* CLUSTER
, VIEW */ OR (O.TYPE# = 2 /* TABLES
, EXCLUDING IOT - OVERFLOW
AND NESTED TABLES */ AND NOT EXISTS (SELECT NULL
FROM SYS.TAB$ T
WHERE T.OBJ# = O.OBJ#
AND (BITAND(T.PROPERTY
, 512) = 512 OR BITAND(T.PROPERTY
, 8192) = 8192))))
AND C.OBJ# = A.OBJ#(+)
AND C.INTCOL# = A.INTCOL#(+) UNION ALL SELECT /*+ ORDERED */ O.NAME
, DECODE(BITAND(C.PROPERTY
, 1)
, 1
, A.NAME
, C.NAME)
, 0
, CASE WHEN SYS_OP_DV_CHECK(O.NAME
, O.OWNER#) = 1 THEN H.MINIMUM ELSE NULL END
, NULL
, NULL
, 0
, 'SHARED' FROM SYS.OBJ$ O
, SYS.COL$ C
, SYS.HIST_HEAD$ H
, SYS.ATTRCOL$ A WHERE O.OBJ# = C.OBJ#
AND O.OWNER# = USERENV('SCHEMAID')
AND C.OBJ# = H.OBJ#
AND C.INTCOL# = H.INTCOL#
AND (O.TYPE# IN (3
, 4) /* CLUSTER
, VIEW */ OR (O.TYPE# = 2 /* TABLES
, EXCLUDING IOT - OVERFLOW
AND NESTED TABLES */ AND NOT EXISTS (SELECT NULL
FROM SYS.TAB$ T
WHERE T.OBJ# = O.OBJ#
AND (BITAND(T.PROPERTY
, 512) = 512 OR BITAND(T.PROPERTY
, 8192) = 8192))))
AND H.ROW_CNT = 0
AND H.DISTCNT > 0
AND C.OBJ# = A.OBJ#(+)
AND C.INTCOL# = A.INTCOL#(+) UNION ALL SELECT /*+ ORDERED */ O.NAME
, DECODE(BITAND(C.PROPERTY
, 1)
, 1
, A.NAME
, C.NAME)
, 1
, CASE WHEN SYS_OP_DV_CHECK(O.NAME
, O.OWNER#) = 1 THEN H.MAXIMUM ELSE NULL END
, NULL
, NULL
, 0
, 'SHARED' FROM SYS.OBJ$ O
, SYS.COL$ C
, SYS.HIST_HEAD$ H
, SYS.ATTRCOL$ A WHERE O.OBJ# = C.OBJ#
AND O.OWNER# = USERENV('SCHEMAID')
AND C.OBJ# = H.OBJ#
AND C.INTCOL# = H.INTCOL#
AND (O.TYPE# IN (3
, 4) /* CLUSTER
, VIEW */ OR (O.TYPE# = 2 /* TABLES
, EXCLUDING IOT - OVERFLOW
AND NESTED TABLES */ AND NOT EXISTS (SELECT NULL
FROM SYS.TAB$ T
WHERE T.OBJ# = O.OBJ#
AND (BITAND(T.PROPERTY
, 512) = 512 OR BITAND(T.PROPERTY
, 8192) = 8192))))
AND H.ROW_CNT = 0
AND H.DISTCNT > 0
AND C.OBJ# = A.OBJ#(+)
AND C.INTCOL# = A.INTCOL#(+) UNION ALL SELECT /*+ ORDERED */ FT.KQFTANAM
, C.KQFCONAM
, H.BUCKET
, H.ENDPOINT
, CASE WHEN H.EPVALUE IS NOT NULL THEN EPVALUE ELSE DBMS_STATS.CONV_RAW(H.EPVALUE_RAW
, C.KQFCODTY) END
, H.EPVALUE_RAW
, H.EP_REPEAT_COUNT
, 'SHARED' FROM SYS.X$KQFTA FT
, SYS.FIXED_OBJ$ FOBJ
, SYS.X$KQFCO C
, SYS.HISTGRM$ H WHERE FT.KQFTAOBJ = FOBJ. OBJ#
AND C.KQFCOTOB = FT.KQFTAOBJ
AND H.OBJ# = FT.KQFTAOBJ
AND H.INTCOL# = C.KQFCOCNO /* * IF FOBJ
AND ST ARE NOT IN SYNC (HAPPENS WHEN DB OPEN READ ONLY * AFTER UPGRADE)
, DO NOT DISPLAY STATS. */
AND FT.KQFTAVER = FOBJ.TIMESTAMP - TO_DATE('01-01-1991'
, 'DD-MM-YYYY')
AND USERENV('SCHEMAID') = 0 /* SYS */ UNION ALL SELECT /*+ ORDERED */ O.NAME
, DECODE(BITAND(C.PROPERTY
, 1)
, 1
, A.NAME
, C.NAME)
, H.BUCKET_KXTTST_HS
, CASE WHEN SYS_OP_DV_CHECK(O.NAME
, O.OWNER#) = 1 THEN H.ENDPOINT_KXTTST_HS ELSE NULL END
, CASE WHEN SYS_OP_DV_CHECK(O.NAME
, O.OWNER#) = 1 THEN DBMS_STATS.CONV_RAW(H.EPVALUE_RAW_KXTTST_HS
, C.TYPE#) ELSE NULL END
, CASE WHEN SYS_OP_DV_CHECK(O.NAME
, O.OWNER#) = 1 THEN H.EPVALUE_RAW_KXTTST_HS ELSE NULL END
, CASE WHEN SYS_OP_DV_CHECK(O.NAME
, O.OWNER#) = 1 THEN H.EP_REPEAT_COUNT_KXTTST_HS ELSE NULL END
, 'SESSION' FROM SYS.OBJ$ O
, SYS.COL$ C
, SYS.X$KXTTSTEHS H
, SYS.ATTRCOL$ A WHERE O.OBJ# = C.OBJ#
AND O.OWNER# = USERENV('SCHEMAID')
AND C.OBJ# = H.OBJ#_KXTTST_HS
AND C.INTCOL# = H.INTCOL#_KXTTST_HS
AND (O.TYPE# IN (3
, 4) /* CLUSTER
, VIEW */ OR (O.TYPE# = 2 /* TABLES
, EXCLUDING IOT - OVERFLOW
AND NESTED TABLES */ AND NOT EXISTS (SELECT NULL
FROM SYS.TAB$ T
WHERE T.OBJ# = O.OBJ#
AND (BITAND(T.PROPERTY
, 512) = 512 OR BITAND(T.PROPERTY
, 8192) = 8192))))
AND C.OBJ# = A.OBJ#(+)
AND C.INTCOL# = A.INTCOL#(+)