VIEW: SYS.AD_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 )