VIEW: SYS._utl$_gnp_ind

Source

View Text - Preformatted

SELECT 'G', Obj_Idx.name, NULL, Obj_Tab.obj#, Obj_Tab.owner#,
       Obj_Idx.obj#, Obj_Idx.owner#, Usr.name, NULL, NULL, NULL, NULL,
       DECODE(bitand(Ind.flags, 1), 1, 'U', 'V'),
       'G', Ind.degree,
       CASE WHEN Tab.rowcnt IS NOT NULL THEN Tab.rowcnt
            WHEN Seg.file# IS NULL THEN
              -- for deferred segment creation, set rowcnt to 0
              (CASE WHEN bitand(Tab.property, 17179869184) = 17179869184 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, Tab.dataobj#),
              Seg.blocks) *
              TSpc.blocksize) / 100) END,
       DECODE(bitand(Ind.property, 1024), 1024, 'Y', 'N')
FROM
     sys.obj$ Obj_Idx,
     sys.user$ Usr,
     sys.ind$ Ind,
     sys.obj$ Obj_Tab,
     sys.ts$ TSpc,
     sys.tab$ Tab
LEFT OUTER JOIN
     sys.seg$ Seg
ON Seg.file# = Tab.file#
  AND Seg.block# = Tab.block#
  AND Seg.ts# = Tab.ts#
  AND Seg.type# = 5
WHERE Obj_Idx.owner# = Usr.user#
  AND Obj_Tab.obj# = Ind.bo#
  AND Ind.type# <> 4                    -- not IOT - TOP
  AND Ind.obj# = Obj_Idx.obj#
  AND NOT (bitand(Ind.property, 2) = 2) -- 0x02 = Partitioned Index
  AND Tab.obj# = Obj_Tab.obj#
  AND Tab.ts# = TSpc.ts#

View Text - HTML Formatted

SELECT 'G'
, OBJ_IDX.NAME
, NULL
, OBJ_TAB.OBJ#
, OBJ_TAB.OWNER#
, OBJ_IDX.OBJ#
, OBJ_IDX.OWNER#
, USR.NAME
, NULL
, NULL
, NULL
, NULL
, DECODE(BITAND(IND.FLAGS
, 1)
, 1
, 'U'
, 'V')
, 'G'
, IND.DEGREE
, CASE WHEN TAB.ROWCNT IS NOT NULL THEN TAB.ROWCNT WHEN SEG.FILE# IS NULL THEN -- FOR DEFERRED SEGMENT CREATION
, SET ROWCNT TO 0 (CASE WHEN BITAND(TAB.PROPERTY
, 17179869184) = 17179869184 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
, TAB.DATAOBJ#)
, SEG.BLOCKS) * TSPC.BLOCKSIZE) / 100) END
, DECODE(BITAND(IND.PROPERTY
, 1024)
, 1024
, 'Y'
, 'N') FROM SYS.OBJ$ OBJ_IDX
, SYS.USER$ USR
, SYS.IND$ IND
, SYS.OBJ$ OBJ_TAB
, SYS.TS$ TSPC
, SYS.TAB$ TAB LEFT OUTER JOIN SYS.SEG$ SEG ON SEG.FILE# = TAB.FILE#
AND SEG.BLOCK# = TAB.BLOCK#
AND SEG.TS# = TAB.TS#
AND SEG.TYPE# = 5 WHERE OBJ_IDX.OWNER# = USR.USER#
AND OBJ_TAB.OBJ# = IND.BO#
AND IND.TYPE# <> 4 -- NOT IOT - TOP
AND IND.OBJ# = OBJ_IDX.OBJ#
AND NOT (BITAND(IND.PROPERTY
, 2) = 2) -- 0X02 = PARTITIONED INDEX
AND TAB.OBJ# = OBJ_TAB.OBJ#
AND TAB.TS# = TSPC.TS#