VIEW: SYS.DBMS_PARALLEL_EXECUTE_EXTENTS

Source

View Text - Preformatted

SELECT owner,
       segment_name,
       partition_name,
       segment_type,
       data_object_id,
       relative_fno,
       block_id,
       blocks
from (select ds.owner,
             ds.segment_name,
             ds.partition_name,
             ds.segment_type,
             e.block#           BLOCK_ID,
             e.length           BLOCKS,
             e.file#            RELATIVE_FNO,
             ds.data_object_id
      from sys.uet$ e,
           (select u.name           OWNER,
                   o.name           SEGMENT_NAME,
                   o.subname        PARTITION_NAME,
                   so.object_type   SEGMENT_TYPE,
                   ts.ts#           TABLESPACE_ID,
                   s.block#         HEADER_BLOCK,
                   s.file#          RELATIVE_FNO,
                   NVL(s.spare1,0)  SEGMENT_FLAGS,
                   o.dataobj#       DATA_OBJECT_ID
            from sys.user$ u,
                 sys.obj$  o,
                 sys.ts$   ts,
                 sys.seg$  s,
                 sys.file$ f,
                 (select 'TABLE'  OBJECT_TYPE,
                         2        OBJECT_TYPE_ID,
                         5        SEGMENT_TYPE_ID,
                         t.obj#   OBJECT_ID,
                         t.file#  HEADER_FILE,
                         t.block# HEADER_BLOCK,
                         t.ts#    TS_NUMBER
                  from sys.tab$ t
                  where bitand(t.property, 1024) = 0
                    and bitand(t.property, 8192) != 8192
                 ) so
            where s.file#  = so.header_file
              and s.block# = so.header_block
              and s.ts#    = so.ts_number
              and s.ts#    = ts.ts#
              and o.obj#   = so.object_id
              and o.owner# = u.user#
              and s.type#  = so.segment_type_id
              and o.type#  = so.object_type_id
              and s.ts#    = f.ts#
              and s.file#  = f.relfile#
            UNION ALL
            select /*+ USE_NL(U O SO) */
                   u.name           OWNER,
                   o.name           SEGMENT_NAME,
                   o.subname        PARTITION_NAME,
                   so.object_type   SEGMENT_TYPE,
                   ts.ts#           TABLESPACE_ID,
                   s.block#         HEADER_BLOCK,
                   s.file#          RELATIVE_FNO,
                   NVL(s.spare1,0)  SEGMENT_FLAGS,
                   o.dataobj#       DATA_OBJECT_ID
            from sys.user$ u,
                 sys.obj$  o,
                 sys.ts$   ts,
                 sys.seg$  s,
                 sys.file$ f,
                 (select /*+ INDEX(TP) */
                         'TABLE PARTITION' OBJECT_TYPE,
                         19                OBJECT_TYPE_ID,
                         5                 SEGMENT_TYPE_ID,
                         tp.obj#           OBJECT_ID,
                         tp.file#          HEADER_FILE,
                         tp.block#         HEADER_BLOCK,
                         tp.ts#            TS_NUMBER
                  from sys.tabpart$ tp
                 ) so
            where s.file#  = so.header_file
              and s.block# = so.header_block
              and s.ts#    = so.ts_number
              and s.ts#    = ts.ts#
              and o.obj#   = so.object_id
              and o.owner# = u.user#
              and s.type#  = so.segment_type_id
              and o.type#  = so.object_type_id
              and s.ts#    = f.ts#
              and s.file#  = f.relfile#
            UNION ALL
            select u.name           OWNER,
                   o.name           SEGMENT_NAME,
                   o.subname        PARTITION_NAME,
                   so.object_type   SEGMENT_TYPE,
                   ts.ts#           TABLESPACE_ID,
                   s.block#         HEADER_BLOCK,
                   s.file#          RELATIVE_FNO,
                   NVL(s.spare1,0)  SEGMENT_FLAGS,
                   o.dataobj#       DATA_OBJECT_ID
            from sys.user$ u,
                 sys.obj$  o,
                 sys.ts$   ts,
                 sys.seg$  s,
                 sys.file$ f,
                 (select /*+ INDEX(TSP) */
                         'TABLE SUBPARTITION' OBJECT_TYPE,
                         34                   OBJECT_TYPE_ID,
                         5                    SEGMENT_TYPE_ID,
                         tsp.obj#             OBJECT_ID,
                         tsp.file#            HEADER_FILE,
                         tsp.block#           HEADER_BLOCK,
                         tsp.ts#              TS_NUMBER
                  from sys.tabsubpart$ tsp
                 ) so
            where s.file#  = so.header_file
              and s.block# = so.header_block
              and s.ts#    = so.ts_number
              and s.ts#    = ts.ts#
              and o.obj#   = so.object_id
              and o.owner# = u.user#
              and s.type#  = so.segment_type_id
              and o.type#  = so.object_type_id
              and s.ts#    = f.ts#
              and s.file#  = f.relfile#
           ) ds,
           sys.file$ f
      where e.segfile#  = ds.relative_fno
        and e.segblock# = ds.header_block
        and e.ts#       = ds.tablespace_id
        and e.ts#       = f.ts#
        and e.file#     = f.relfile#
        and bitand(NVL(ds.segment_flags,0), 1) = 0
      union all
      select /*+ ordered use_nl(e) use_nl(f) */
             ds.owner,
             ds.segment_name,
             ds.partition_name,
             ds.segment_type,
             e.ktfbuebno       BLOCK_ID,
             e.ktfbueblks      BLOCKS,
             e.ktfbuefno       RELATIVE_FNO,
             ds.data_object_id
      from (select u.name           OWNER,
                   o.name           SEGMENT_NAME,
                   o.subname        PARTITION_NAME,
                   so.object_type   SEGMENT_TYPE,
                   ts.ts#           TABLESPACE_ID,
                   s.block#         HEADER_BLOCK,
                   s.file#          RELATIVE_FNO,
                   NVL(s.spare1,0)  SEGMENT_FLAGS,
                   o.dataobj#       DATA_OBJECT_ID
            from sys.user$ u,
                 sys.obj$  o,
                 sys.ts$   ts,
                 sys.seg$  s,
                 sys.file$ f,
                 (select 'TABLE'  OBJECT_TYPE,
                         2        OBJECT_TYPE_ID,
                         5        SEGMENT_TYPE_ID,
                         t.obj#   OBJECT_ID,
                         t.file#  HEADER_FILE,
                         t.block# HEADER_BLOCK,
                         t.ts#    TS_NUMBER
                  from sys.tab$ t
                  where bitand(t.property, 1024) = 0
                    and bitand(t.property, 8192) != 8192
                 ) so
            where s.file#  = so.header_file
              and s.block# = so.header_block
              and s.ts#    = so.ts_number
              and s.ts#    = ts.ts#
              and o.obj#   = so.object_id
              and o.owner# = u.user#
              and s.type#  = so.segment_type_id
              and o.type#  = so.object_type_id
              and s.ts#    = f.ts#
              and s.file#  = f.relfile#
            UNION ALL
            select /*+ USE_NL(U O SO) */
                   u.name           OWNER,
                   o.name           SEGMENT_NAME,
                   o.subname        PARTITION_NAME,
                   so.object_type   SEGMENT_TYPE,
                   ts.ts#           TABLESPACE_ID,
                   s.block#         HEADER_BLOCK,
                   s.file#          RELATIVE_FNO,
                   NVL(s.spare1,0)  SEGMENT_FLAGS,
                   o.dataobj#       DATA_OBJECT_ID
            from sys.user$ u,
                 sys.obj$  o,
                 sys.ts$   ts,
                 sys.seg$  s,
                 sys.file$ f,
                 (select /*+ INDEX(TP) */
                         'TABLE PARTITION' OBJECT_TYPE,
                         19                OBJECT_TYPE_ID,
                         5                 SEGMENT_TYPE_ID,
                         tp.obj#           OBJECT_ID,
                         tp.file#          HEADER_FILE,
                         tp.block#         HEADER_BLOCK,
                         tp.ts#            TS_NUMBER
                  from sys.tabpart$ tp
                 ) so
            where s.file#  = so.header_file
              and s.block# = so.header_block
              and s.ts#    = so.ts_number
              and s.ts#    = ts.ts#
              and o.obj#   = so.object_id
              and o.owner# = u.user#
              and s.type#  = so.segment_type_id
              and o.type#  = so.object_type_id
              and s.ts#    = f.ts#
              and s.file#  = f.relfile#
            UNION ALL
            select u.name           OWNER,
                   o.name           SEGMENT_NAME,
                   o.subname        PARTITION_NAME,
                   so.object_type   SEGMENT_TYPE,
                   ts.ts#           TABLESPACE_ID,
                   s.block#         HEADER_BLOCK,
                   s.file#          RELATIVE_FNO,
                   NVL(s.spare1,0)  SEGMENT_FLAGS,
                   o.dataobj#       DATA_OBJECT_ID
            from sys.user$ u,
                 sys.obj$  o,
                 sys.ts$   ts,
                 sys.seg$  s,
                 sys.file$ f,
                 (select /*+ INDEX(TSP) */
                         'TABLE SUBPARTITION' OBJECT_TYPE,
                         34                   OBJECT_TYPE_ID,
                         5                    SEGMENT_TYPE_ID,
                         tsp.obj#             OBJECT_ID,
                         tsp.file#            HEADER_FILE,
                         tsp.block#           HEADER_BLOCK,
                         tsp.ts#              TS_NUMBER
                  from sys.tabsubpart$ tsp
                 ) so
            where s.file#  = so.header_file
              and s.block# = so.header_block
              and s.ts#    = so.ts_number
              and s.ts#    = ts.ts#
              and o.obj#   = so.object_id
              and o.owner# = u.user#
              and s.type#  = so.segment_type_id
              and o.type#  = so.object_type_id
              and s.ts#    = f.ts#
              and s.file#  = f.relfile#
           ) ds,
           sys.x$ktfbue e,
           sys.file$ f
      where e.ktfbuesegfno = ds.relative_fno
        and e.ktfbuesegbno = ds.header_block
        and e.ktfbuesegtsn = ds.tablespace_id
        and e.ktfbuesegtsn = f.ts#
        and e.ktfbuefno    = f.relfile#
        and bitand(NVL(ds.segment_flags, 0), 1) = 1
     )

View Text - HTML Formatted

SELECT OWNER
, SEGMENT_NAME
, PARTITION_NAME
, SEGMENT_TYPE
, DATA_OBJECT_ID
, RELATIVE_FNO
, BLOCK_ID
, BLOCKS FROM (SELECT DS.OWNER
, DS.SEGMENT_NAME
, DS.PARTITION_NAME
, DS.SEGMENT_TYPE
, E.BLOCK# BLOCK_ID
, E.LENGTH BLOCKS
, E.FILE# RELATIVE_FNO
, DS.DATA_OBJECT_ID
FROM SYS.UET$ E
, (SELECT U.NAME OWNER
, O.NAME SEGMENT_NAME
, O.SUBNAME PARTITION_NAME
, SO.OBJECT_TYPE SEGMENT_TYPE
, TS.TS# TABLESPACE_ID
, S.BLOCK# HEADER_BLOCK
, S.FILE# RELATIVE_FNO
, NVL(S.SPARE1
, 0) SEGMENT_FLAGS
, O.DATAOBJ# DATA_OBJECT_ID
FROM SYS.USER$ U
, SYS.OBJ$ O
, SYS.TS$ TS
, SYS.SEG$ S
, SYS.FILE$ F
, (SELECT 'TABLE' OBJECT_TYPE
, 2 OBJECT_TYPE_ID
, 5 SEGMENT_TYPE_ID
, T.OBJ# OBJECT_ID
, T.FILE# HEADER_FILE
, T.BLOCK# HEADER_BLOCK
, T.TS# TS_NUMBER
FROM SYS.TAB$ T
WHERE BITAND(T.PROPERTY
, 1024) = 0
AND BITAND(T.PROPERTY
, 8192) != 8192 ) SO
WHERE S.FILE# = SO.HEADER_FILE
AND S.BLOCK# = SO.HEADER_BLOCK
AND S.TS# = SO.TS_NUMBER
AND S.TS# = TS.TS#
AND O.OBJ# = SO.OBJECT_ID
AND O.OWNER# = U.USER#
AND S.TYPE# = SO.SEGMENT_TYPE_ID
AND O.TYPE# = SO.OBJECT_TYPE_ID
AND S.TS# = F.TS#
AND S.FILE# = F.RELFILE# UNION ALL SELECT /*+ USE_NL(U O SO) */ U.NAME OWNER
, O.NAME SEGMENT_NAME
, O.SUBNAME PARTITION_NAME
, SO.OBJECT_TYPE SEGMENT_TYPE
, TS.TS# TABLESPACE_ID
, S.BLOCK# HEADER_BLOCK
, S.FILE# RELATIVE_FNO
, NVL(S.SPARE1
, 0) SEGMENT_FLAGS
, O.DATAOBJ# DATA_OBJECT_ID
FROM SYS.USER$ U
, SYS.OBJ$ O
, SYS.TS$ TS
, SYS.SEG$ S
, SYS.FILE$ F
, (SELECT /*+ INDEX(TP) */ 'TABLE PARTITION' OBJECT_TYPE
, 19 OBJECT_TYPE_ID
, 5 SEGMENT_TYPE_ID
, TP.OBJ# OBJECT_ID
, TP.FILE# HEADER_FILE
, TP.BLOCK# HEADER_BLOCK
, TP.TS# TS_NUMBER
FROM SYS.TABPART$ TP ) SO
WHERE S.FILE# = SO.HEADER_FILE
AND S.BLOCK# = SO.HEADER_BLOCK
AND S.TS# = SO.TS_NUMBER
AND S.TS# = TS.TS#
AND O.OBJ# = SO.OBJECT_ID
AND O.OWNER# = U.USER#
AND S.TYPE# = SO.SEGMENT_TYPE_ID
AND O.TYPE# = SO.OBJECT_TYPE_ID
AND S.TS# = F.TS#
AND S.FILE# = F.RELFILE# UNION ALL SELECT U.NAME OWNER
, O.NAME SEGMENT_NAME
, O.SUBNAME PARTITION_NAME
, SO.OBJECT_TYPE SEGMENT_TYPE
, TS.TS# TABLESPACE_ID
, S.BLOCK# HEADER_BLOCK
, S.FILE# RELATIVE_FNO
, NVL(S.SPARE1
, 0) SEGMENT_FLAGS
, O.DATAOBJ# DATA_OBJECT_ID
FROM SYS.USER$ U
, SYS.OBJ$ O
, SYS.TS$ TS
, SYS.SEG$ S
, SYS.FILE$ F
, (SELECT /*+ INDEX(TSP) */ 'TABLE SUBPARTITION' OBJECT_TYPE
, 34 OBJECT_TYPE_ID
, 5 SEGMENT_TYPE_ID
, TSP.OBJ# OBJECT_ID
, TSP.FILE# HEADER_FILE
, TSP.BLOCK# HEADER_BLOCK
, TSP.TS# TS_NUMBER
FROM SYS.TABSUBPART$ TSP ) SO
WHERE S.FILE# = SO.HEADER_FILE
AND S.BLOCK# = SO.HEADER_BLOCK
AND S.TS# = SO.TS_NUMBER
AND S.TS# = TS.TS#
AND O.OBJ# = SO.OBJECT_ID
AND O.OWNER# = U.USER#
AND S.TYPE# = SO.SEGMENT_TYPE_ID
AND O.TYPE# = SO.OBJECT_TYPE_ID
AND S.TS# = F.TS#
AND S.FILE# = F.RELFILE# ) DS
, SYS.FILE$ F
WHERE E.SEGFILE# = DS.RELATIVE_FNO
AND E.SEGBLOCK# = DS.HEADER_BLOCK
AND E.TS# = DS.TABLESPACE_ID
AND E.TS# = F.TS#
AND E.FILE# = F.RELFILE#
AND BITAND(NVL(DS.SEGMENT_FLAGS
, 0)
, 1) = 0 UNION ALL SELECT /*+ ORDERED USE_NL(E) USE_NL(F) */ DS.OWNER
, DS.SEGMENT_NAME
, DS.PARTITION_NAME
, DS.SEGMENT_TYPE
, E.KTFBUEBNO BLOCK_ID
, E.KTFBUEBLKS BLOCKS
, E.KTFBUEFNO RELATIVE_FNO
, DS.DATA_OBJECT_ID
FROM (SELECT U.NAME OWNER
, O.NAME SEGMENT_NAME
, O.SUBNAME PARTITION_NAME
, SO.OBJECT_TYPE SEGMENT_TYPE
, TS.TS# TABLESPACE_ID
, S.BLOCK# HEADER_BLOCK
, S.FILE# RELATIVE_FNO
, NVL(S.SPARE1
, 0) SEGMENT_FLAGS
, O.DATAOBJ# DATA_OBJECT_ID
FROM SYS.USER$ U
, SYS.OBJ$ O
, SYS.TS$ TS
, SYS.SEG$ S
, SYS.FILE$ F
, (SELECT 'TABLE' OBJECT_TYPE
, 2 OBJECT_TYPE_ID
, 5 SEGMENT_TYPE_ID
, T.OBJ# OBJECT_ID
, T.FILE# HEADER_FILE
, T.BLOCK# HEADER_BLOCK
, T.TS# TS_NUMBER
FROM SYS.TAB$ T
WHERE BITAND(T.PROPERTY
, 1024) = 0
AND BITAND(T.PROPERTY
, 8192) != 8192 ) SO
WHERE S.FILE# = SO.HEADER_FILE
AND S.BLOCK# = SO.HEADER_BLOCK
AND S.TS# = SO.TS_NUMBER
AND S.TS# = TS.TS#
AND O.OBJ# = SO.OBJECT_ID
AND O.OWNER# = U.USER#
AND S.TYPE# = SO.SEGMENT_TYPE_ID
AND O.TYPE# = SO.OBJECT_TYPE_ID
AND S.TS# = F.TS#
AND S.FILE# = F.RELFILE# UNION ALL SELECT /*+ USE_NL(U O SO) */ U.NAME OWNER
, O.NAME SEGMENT_NAME
, O.SUBNAME PARTITION_NAME
, SO.OBJECT_TYPE SEGMENT_TYPE
, TS.TS# TABLESPACE_ID
, S.BLOCK# HEADER_BLOCK
, S.FILE# RELATIVE_FNO
, NVL(S.SPARE1
, 0) SEGMENT_FLAGS
, O.DATAOBJ# DATA_OBJECT_ID
FROM SYS.USER$ U
, SYS.OBJ$ O
, SYS.TS$ TS
, SYS.SEG$ S
, SYS.FILE$ F
, (SELECT /*+ INDEX(TP) */ 'TABLE PARTITION' OBJECT_TYPE
, 19 OBJECT_TYPE_ID
, 5 SEGMENT_TYPE_ID
, TP.OBJ# OBJECT_ID
, TP.FILE# HEADER_FILE
, TP.BLOCK# HEADER_BLOCK
, TP.TS# TS_NUMBER
FROM SYS.TABPART$ TP ) SO
WHERE S.FILE# = SO.HEADER_FILE
AND S.BLOCK# = SO.HEADER_BLOCK
AND S.TS# = SO.TS_NUMBER
AND S.TS# = TS.TS#
AND O.OBJ# = SO.OBJECT_ID
AND O.OWNER# = U.USER#
AND S.TYPE# = SO.SEGMENT_TYPE_ID
AND O.TYPE# = SO.OBJECT_TYPE_ID
AND S.TS# = F.TS#
AND S.FILE# = F.RELFILE# UNION ALL SELECT U.NAME OWNER
, O.NAME SEGMENT_NAME
, O.SUBNAME PARTITION_NAME
, SO.OBJECT_TYPE SEGMENT_TYPE
, TS.TS# TABLESPACE_ID
, S.BLOCK# HEADER_BLOCK
, S.FILE# RELATIVE_FNO
, NVL(S.SPARE1
, 0) SEGMENT_FLAGS
, O.DATAOBJ# DATA_OBJECT_ID
FROM SYS.USER$ U
, SYS.OBJ$ O
, SYS.TS$ TS
, SYS.SEG$ S
, SYS.FILE$ F
, (SELECT /*+ INDEX(TSP) */ 'TABLE SUBPARTITION' OBJECT_TYPE
, 34 OBJECT_TYPE_ID
, 5 SEGMENT_TYPE_ID
, TSP.OBJ# OBJECT_ID
, TSP.FILE# HEADER_FILE
, TSP.BLOCK# HEADER_BLOCK
, TSP.TS# TS_NUMBER
FROM SYS.TABSUBPART$ TSP ) SO
WHERE S.FILE# = SO.HEADER_FILE
AND S.BLOCK# = SO.HEADER_BLOCK
AND S.TS# = SO.TS_NUMBER
AND S.TS# = TS.TS#
AND O.OBJ# = SO.OBJECT_ID
AND O.OWNER# = U.USER#
AND S.TYPE# = SO.SEGMENT_TYPE_ID
AND O.TYPE# = SO.OBJECT_TYPE_ID
AND S.TS# = F.TS#
AND S.FILE# = F.RELFILE# ) DS
, SYS.X$KTFBUE E
, SYS.FILE$ F
WHERE E.KTFBUESEGFNO = DS.RELATIVE_FNO
AND E.KTFBUESEGBNO = DS.HEADER_BLOCK
AND E.KTFBUESEGTSN = DS.TABLESPACE_ID
AND E.KTFBUESEGTSN = F.TS#
AND E.KTFBUEFNO = F.RELFILE#
AND BITAND(NVL(DS.SEGMENT_FLAGS
, 0)
, 1) = 1 )