VIEW: SYS.ALL_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
    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, 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
    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, 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
    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 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
    and (os.owner# = userenv('SCHEMAID')
        or os.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
, 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
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
, 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
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
, 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
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 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
AND (OS.OWNER# = USERENV('SCHEMAID') OR OS.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 */) ) )