Page History

SELECT U.USER_NAME, T.TABLE_NAME TABLE_NAME
     , B.INDEX_NAME
     , LPAD(I.IS_PARTITIONED, 14) INDEX_PARTITIONED
     , ROUND(((USED_NODE_COUNT+ PREPARE_NODE_COUNT) / 15 * 32768)/1024/1024, 1) AS 'SIZE(MMB)'
  FROM         V$MEM_BTREE_HEADER B
     , SYSTEM_.SYS_INDICES_ I
     , SYSTEM_.SYS_TABLES_ T
     , SYSTEM_.SYS_USERS_ U
 WHERE 1=1
   AND B.INDEX_ID = I.INDEX_ID
   AND I.TABLE_ID = T.TABLE_ID
   AND IB.INDEX_TBS_ID <> 0
 ORDER BY PREPARE_NODE_COUNT + USED_NODE_COUNT DESCAND U.USER_ID = T.USER_ID
 ORDER BY TABLE_NAME, B.INDEX_ID
;

USER_NAME             TABLE_NAME            INDEX_NAME            INDEX_PARTITIONED     SIZE(MMB)    

----------------------------------------------------------------------------------------------------------
SYS                   EMPLOYEES             __SYS_IDX_ID_143                   F        0           
SYS                   EMPLOYEES             EMP_IDX1                           F        0           
SYS                   FOO                   __SYS_IDX_ID_171                   F        0           
SYS                   GOODS                 __SYS_IDX_ID_145                   F        0           
SYS                   GOODS                 __SYS_IDX_ID_146                   F        0           
SYS                   MEM_T                 M_IDX01               20.3             F        36.5        
SYS                   M_PART_SALES          M_IDX_PREFIX                       T        0           
SYS                   M_PART_SALES          IDX_PART_1            0.8             T        8.7         
2 rows selected.
iSQL>SYS                   M_PART_SALES          IDX_PART_2                         T        0.7         
SYS                   M_PART_SALES          IDX_PART_3                         T        0.7