VIEW: SYS.DBA_TAB_HISTGRM_PENDING_STATS

Source

View Text - Preformatted

select u.name, o.name, null, null, 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
  from   sys.user$ u, sys.obj$ o, sys.col$ c,
         sys.wri$_optstat_histgrm_history h
  where  h.obj# = c.obj#
    and  h.intcol# = c.intcol#
    and  h.obj# = o.obj#
    and  o.owner# = u.user#
    and  o.type# = 2
    and  h.savtime > systimestamp
  union all
  -- partitions
  select u.name, o.name, o.subname, null, 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
  from   sys.user$ u, sys.obj$ o, sys.col$ c, sys.tabpart$ t,
         sys.wri$_optstat_histgrm_history h
  where  t.bo# = c.obj#
    and  t.obj# = o.obj#
    and  h.intcol# = c.intcol#
    and  h.obj# = o.obj#
    and  o.type# = 19
    and  o.owner# = u.user#
    and  h.savtime > systimestamp
  union all
  select u.name, o.name, o.subname, null, 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
  from   sys.user$ u, sys.obj$ o, sys.col$ c, sys.tabcompart$ t,
         sys.wri$_optstat_histgrm_history h
  where  t.bo# = c.obj#
    and  t.obj# = o.obj#
    and  h.intcol# = c.intcol#
    and  h.obj# = o.obj#
    and  o.type# = 19
    and  o.owner# = u.user#
    and  h.savtime > systimestamp
  union all
  -- sub partitions
  select u.name, op.name, op.subname, os.subname, c.name,
         h.bucket,
         case when SYS_OP_DV_CHECK(os.name, os.owner#) = 1
              then h.endpoint
              else null
         end,
         case when SYS_OP_DV_CHECK(os.name, os.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(os.name, os.owner#) = 1
              then h.epvalue_raw
              else null
         end,
         case when SYS_OP_DV_CHECK(os.name, os.owner#) = 1
            then h.ep_repeat_count
            else null
         end
  from  sys.obj$ os, sys.tabsubpart$ tsp, sys.tabcompart$ tcp,
        sys.user$ u, sys.col$ c, sys.obj$ op,
        sys.wri$_optstat_histgrm_history h
  where os.obj# = tsp.obj#
    and os.owner# = u.user#
    and h.obj#  = tsp.obj#
    and h.intcol#= c.intcol#
    and tsp.pobj#= tcp.obj#
    and tcp.bo#  = c.obj#
    and tcp.obj# = op.obj#
    and os.type# = 34
    and h.savtime > systimestamp

View Text - HTML Formatted

SELECT U.NAME
, O.NAME
, NULL
, NULL
, 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
FROM SYS.USER$ U
, SYS.OBJ$ O
, SYS.COL$ C
, SYS.WRI$_OPTSTAT_HISTGRM_HISTORY H
WHERE H.OBJ# = C.OBJ#
AND H.INTCOL# = C.INTCOL#
AND H.OBJ# = O.OBJ#
AND O.OWNER# = U.USER#
AND O.TYPE# = 2
AND H.SAVTIME > SYSTIMESTAMP UNION ALL -- PARTITIONS SELECT U.NAME
, O.NAME
, O.SUBNAME
, NULL
, 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
FROM SYS.USER$ U
, SYS.OBJ$ O
, SYS.COL$ C
, SYS.TABPART$ T
, SYS.WRI$_OPTSTAT_HISTGRM_HISTORY H
WHERE T.BO# = C.OBJ#
AND T.OBJ# = O.OBJ#
AND H.INTCOL# = C.INTCOL#
AND H.OBJ# = O.OBJ#
AND O.TYPE# = 19
AND O.OWNER# = U.USER#
AND H.SAVTIME > SYSTIMESTAMP UNION ALL SELECT U.NAME
, O.NAME
, O.SUBNAME
, NULL
, 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
FROM SYS.USER$ U
, SYS.OBJ$ O
, SYS.COL$ C
, SYS.TABCOMPART$ T
, SYS.WRI$_OPTSTAT_HISTGRM_HISTORY H
WHERE T.BO# = C.OBJ#
AND T.OBJ# = O.OBJ#
AND H.INTCOL# = C.INTCOL#
AND H.OBJ# = O.OBJ#
AND O.TYPE# = 19
AND O.OWNER# = U.USER#
AND H.SAVTIME > SYSTIMESTAMP UNION ALL -- SUB PARTITIONS SELECT U.NAME
, OP.NAME
, OP.SUBNAME
, OS.SUBNAME
, C.NAME
, H.BUCKET
, CASE WHEN SYS_OP_DV_CHECK(OS.NAME
, OS.OWNER#) = 1 THEN H.ENDPOINT ELSE NULL END
, CASE WHEN SYS_OP_DV_CHECK(OS.NAME
, OS.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(OS.NAME
, OS.OWNER#) = 1 THEN H.EPVALUE_RAW ELSE NULL END
, CASE WHEN SYS_OP_DV_CHECK(OS.NAME
, OS.OWNER#) = 1 THEN H.EP_REPEAT_COUNT ELSE NULL END
FROM SYS.OBJ$ OS
, SYS.TABSUBPART$ TSP
, SYS.TABCOMPART$ TCP
, SYS.USER$ U
, SYS.COL$ C
, SYS.OBJ$ OP
, SYS.WRI$_OPTSTAT_HISTGRM_HISTORY H
WHERE OS.OBJ# = TSP.OBJ#
AND OS.OWNER# = U.USER#
AND H.OBJ# = TSP.OBJ#
AND H.INTCOL#= C.INTCOL#
AND TSP.POBJ#= TCP.OBJ#
AND TCP.BO# = C.OBJ#
AND TCP.OBJ# = OP.OBJ#
AND OS.TYPE# = 34
AND H.SAVTIME > SYSTIMESTAMP