VIEW: SYS.DBA_SUBPART_HISTOGRAMS

Source

View Text - Preformatted

select u.name,
       o.name, o.subname,
       tsp.cname,
       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, tsp.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
from sys.obj$ o, sys.histgrm$ h, sys.user$ u, tsp$ tsp
where o.obj# = tsp.obj# and tsp.obj# = h.obj#
  and tsp.intcol# = h.intcol#
  and o.type# = 34 /* TABLE SUBPARTITION */
  and o.owner# = u.user#
  and o.namespace = 1 and o.remoteowner IS NULL and o.linkname IS NULL
union
select u.name,
       o.name, o.subname,
       tsp.cname,
       0,
       case when SYS_OP_DV_CHECK(o.name, o.owner#) = 1
            then h.minimum
            else null
       end,
       null,
       null,
       0
from sys.obj$ o, sys.hist_head$ h, sys.user$ u, tsp$ tsp
where o.obj# = tsp.obj# and tsp.obj# = h.obj#
  and tsp.intcol# = h.intcol#
  and o.type# = 34 /* TABLE SUBPARTITION */
  and h.row_cnt = 0 and h.distcnt > 0
  and o.owner# = u.user#
  and o.namespace = 1 and o.remoteowner IS NULL and o.linkname IS NULL
union
select u.name,
       o.name, o.subname,
       tsp.cname,
       1,
       case when SYS_OP_DV_CHECK(o.name, o.owner#) = 1
            then h.maximum
            else null
       end,
       null,
       null,
       0
from sys.obj$ o, sys.hist_head$ h, sys.user$ u, tsp$ tsp
where o.obj# = tsp.obj# and tsp.obj# = h.obj#
  and tsp.intcol# = h.intcol#
  and o.type# = 34 /* TABLE SUBPARTITION */
  and h.row_cnt = 0 and h.distcnt > 0
  and o.owner# = u.user#
  and o.namespace = 1 and o.remoteowner IS NULL and o.linkname IS NULL

View Text - HTML Formatted

SELECT U.NAME
, O.NAME
, O.SUBNAME
, TSP.CNAME
, 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
, TSP.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 FROM SYS.OBJ$ O
, SYS.HISTGRM$ H
, SYS.USER$ U
, TSP$ TSP WHERE O.OBJ# = TSP.OBJ#
AND TSP.OBJ# = H.OBJ#
AND TSP.INTCOL# = H.INTCOL#
AND O.TYPE# = 34 /* TABLE SUBPARTITION */
AND O.OWNER# = U.USER#
AND O.NAMESPACE = 1
AND O.REMOTEOWNER IS NULL
AND O.LINKNAME IS NULL UNION SELECT U.NAME
, O.NAME
, O.SUBNAME
, TSP.CNAME
, 0
, CASE WHEN SYS_OP_DV_CHECK(O.NAME
, O.OWNER#) = 1 THEN H.MINIMUM ELSE NULL END
, NULL
, NULL
, 0 FROM SYS.OBJ$ O
, SYS.HIST_HEAD$ H
, SYS.USER$ U
, TSP$ TSP WHERE O.OBJ# = TSP.OBJ#
AND TSP.OBJ# = H.OBJ#
AND TSP.INTCOL# = H.INTCOL#
AND O.TYPE# = 34 /* TABLE SUBPARTITION */
AND H.ROW_CNT = 0
AND H.DISTCNT > 0
AND O.OWNER# = U.USER#
AND O.NAMESPACE = 1
AND O.REMOTEOWNER IS NULL
AND O.LINKNAME IS NULL UNION SELECT U.NAME
, O.NAME
, O.SUBNAME
, TSP.CNAME
, 1
, CASE WHEN SYS_OP_DV_CHECK(O.NAME
, O.OWNER#) = 1 THEN H.MAXIMUM ELSE NULL END
, NULL
, NULL
, 0 FROM SYS.OBJ$ O
, SYS.HIST_HEAD$ H
, SYS.USER$ U
, TSP$ TSP WHERE O.OBJ# = TSP.OBJ#
AND TSP.OBJ# = H.OBJ#
AND TSP.INTCOL# = H.INTCOL#
AND O.TYPE# = 34 /* TABLE SUBPARTITION */
AND H.ROW_CNT = 0
AND H.DISTCNT > 0
AND O.OWNER# = U.USER#
AND O.NAMESPACE = 1
AND O.REMOTEOWNER IS NULL
AND O.LINKNAME IS NULL