VIEW: SYS._utl$_lc_ind_subs

Source

View Text - Preformatted

SELECT 'C', Obj_IdxSubPart.name, Obj_IdxSubPart.subname,
       Obj_Tab.obj#, Obj_Tab.owner#, Obj_Idx.obj#, Obj_Idx.owner#,
       Usr.name, Obj_IdxSubPart.obj#, TabSubPart.obj#,
       IndComPart.obj#, TabComPart.obj#,
       DECODE(bitand(IndSubPart.flags, 1), 1, 'U', 'V'),
       'L', Ind.degree,
       CASE WHEN TabSubPart.rowcnt IS NOT NULL THEN TabSubPart.rowcnt
            WHEN Seg.file# IS NULL THEN
              -- for deferred segment creation, set rowcnt to 0
              (CASE WHEN bitand(TabSubPart.flags, 65536) = 65536 THEN 0
               ELSE 1000000000
               END)
            ELSE CEIL((dbms_space_admin.segment_number_blocks(TSpc.ts#,
              Seg.file#, Seg.block#, Seg.type#, Seg.cachehint,
              NVL(Seg.spare1, 0),
              decode(bitand(Seg.spare1, 1), 1,
              Seg.hwmincr, TabSubPart.dataobj#),
              Seg.blocks) *
              TSpc.blocksize) / 100) END,
       DECODE(bitand(Ind.property, 1024), 1024, 'Y', 'N')
FROM
     sys.obj$ Obj_IdxSubPart,
     sys.indsubpart$ IndSubPart,
     sys.indcompart$ IndComPart,
     sys.user$ Usr,
     sys.obj$ Obj_Idx,
     sys.Ind$ Ind,
     sys.obj$ Obj_Tab,
     sys.partobj$ PartObj,
     sys.tabcompart$ TabComPart,
     sys.tab$ Tab,
     sys.ts$ TSpc,
     sys.tabsubpart$ TabSubPart
LEFT OUTER JOIN
     sys.seg$ Seg
ON   Seg.file# = TabSubPart.file#
  AND Seg.block# = TabSubPart.block#
  AND Seg.ts# = TabSubPart.ts#
  AND Seg.type# = 5
WHERE IndComPart.obj# = IndSubPart.pobj#
  AND IndComPart.bo# = Ind.obj#
  AND Ind.type# <> 4             -- not IOT - TOP
  AND Ind.bo# = Obj_Tab.obj#
  AND Obj_IdxSubPart.obj# = IndSubPart.obj#
  AND Obj_Idx.owner# = Usr.user#
  AND Ind.obj# = Obj_Idx.obj#
  AND PartObj.obj# = Ind.obj#
  AND TabComPart.bo# = Obj_Tab.obj#
  AND TabComPart.part# = IndComPart.part#
  AND TabComPart.obj# = TabSubPart.pobj#
  AND TabSubPart.subpart# = IndSubPart.subpart#
  AND Tab.obj# = Obj_Tab.obj#
  AND TabSubPart.ts# = TSpc.ts#

View Text - HTML Formatted

SELECT 'C'
, OBJ_IDXSUBPART.NAME
, OBJ_IDXSUBPART.SUBNAME
, OBJ_TAB.OBJ#
, OBJ_TAB.OWNER#
, OBJ_IDX.OBJ#
, OBJ_IDX.OWNER#
, USR.NAME
, OBJ_IDXSUBPART.OBJ#
, TABSUBPART.OBJ#
, INDCOMPART.OBJ#
, TABCOMPART.OBJ#
, DECODE(BITAND(INDSUBPART.FLAGS
, 1)
, 1
, 'U'
, 'V')
, 'L'
, IND.DEGREE
, CASE WHEN TABSUBPART.ROWCNT IS NOT NULL THEN TABSUBPART.ROWCNT WHEN SEG.FILE# IS NULL THEN -- FOR DEFERRED SEGMENT CREATION
, SET ROWCNT TO 0 (CASE WHEN BITAND(TABSUBPART.FLAGS
, 65536) = 65536 THEN 0 ELSE 1000000000 END) ELSE CEIL((DBMS_SPACE_ADMIN.SEGMENT_NUMBER_BLOCKS(TSPC.TS#
, SEG.FILE#
, SEG.BLOCK#
, SEG.TYPE#
, SEG.CACHEHINT
, NVL(SEG.SPARE1
, 0)
, DECODE(BITAND(SEG.SPARE1
, 1)
, 1
, SEG.HWMINCR
, TABSUBPART.DATAOBJ#)
, SEG.BLOCKS) * TSPC.BLOCKSIZE) / 100) END
, DECODE(BITAND(IND.PROPERTY
, 1024)
, 1024
, 'Y'
, 'N') FROM SYS.OBJ$ OBJ_IDXSUBPART
, SYS.INDSUBPART$ INDSUBPART
, SYS.INDCOMPART$ INDCOMPART
, SYS.USER$ USR
, SYS.OBJ$ OBJ_IDX
, SYS.IND$ IND
, SYS.OBJ$ OBJ_TAB
, SYS.PARTOBJ$ PARTOBJ
, SYS.TABCOMPART$ TABCOMPART
, SYS.TAB$ TAB
, SYS.TS$ TSPC
, SYS.TABSUBPART$ TABSUBPART LEFT OUTER JOIN SYS.SEG$ SEG ON SEG.FILE# = TABSUBPART.FILE#
AND SEG.BLOCK# = TABSUBPART.BLOCK#
AND SEG.TS# = TABSUBPART.TS#
AND SEG.TYPE# = 5 WHERE INDCOMPART.OBJ# = INDSUBPART.POBJ#
AND INDCOMPART.BO# = IND.OBJ#
AND IND.TYPE# <> 4 -- NOT IOT - TOP
AND IND.BO# = OBJ_TAB.OBJ#
AND OBJ_IDXSUBPART.OBJ# = INDSUBPART.OBJ#
AND OBJ_IDX.OWNER# = USR.USER#
AND IND.OBJ# = OBJ_IDX.OBJ#
AND PARTOBJ.OBJ# = IND.OBJ#
AND TABCOMPART.BO# = OBJ_TAB.OBJ#
AND TABCOMPART.PART# = INDCOMPART.PART#
AND TABCOMPART.OBJ# = TABSUBPART.POBJ#
AND TABSUBPART.SUBPART# = INDSUBPART.SUBPART#
AND TAB.OBJ# = OBJ_TAB.OBJ#
AND TABSUBPART.TS# = TSPC.TS#