VIEW: SYS.ALL_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
  and (o.owner# = userenv('SCHEMAID')
        or
        tsp.bo# in ( select obj#
                    from sys.objauth$
                    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
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
  and (o.owner# = userenv('SCHEMAID')
        or
        tsp.bo# in ( select obj#
                    from sys.objauth$
                    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
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
  and (o.owner# = userenv('SCHEMAID')
        or
        tsp.bo# in ( select obj#
                    from sys.objauth$
                    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
, 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
AND (O.OWNER# = USERENV('SCHEMAID') OR TSP.BO# IN ( SELECT OBJ#
FROM SYS.OBJAUTH$
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 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
AND (O.OWNER# = USERENV('SCHEMAID') OR TSP.BO# IN ( SELECT OBJ#
FROM SYS.OBJAUTH$
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 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
AND (O.OWNER# = USERENV('SCHEMAID') OR TSP.BO# IN ( SELECT OBJ#
FROM SYS.OBJAUTH$
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 */) ) )