set linesize 2048;
set colsize 30;
SELECT   a.user_name
        ,NVL(d.name,'SYS_TBS_MEMORY')  AS 'TABLESPACE_NAME'
        , b.table_name
        , round((c.fixed_alloc_mem + c.var_alloc_mem)/(1024*1024),2) 'ALLOC(M)'
        , round((c.fixed_used_mem + c.var_used_mem)/(1024*1024),2) 'USED(M)'
        , round((c.fixed_used_mem + c.var_used_mem)/(c.fixed_alloc_mem + c.var_alloc_mem)*100,2) 'EFFICIENCY(%)'
FROM   system_.sys_users_ a
     , system_.sys_tables_ b
     , v$memtbl_info c left outer join v$tablespaces d  on c.tablespace_id = d.id
WHERE  b.table_type = 'T'
  and a.user_id = b.user_id
  and b.table_oid = c.table_oid
order by 1,2,3, 4 desc ;

set linesize 2048;
set colsize 30;

 SELECT
        c.user_name
        , decode(f.table_type, 'Q', 'QUEUE', 'T', 'TABLE') object_type
        , table_name object_name
        , e.index_name
        , rpad(case2(e.index_type=1, 'b-tree', 'r-tree'),10,' ') index_type
        , '16 bytes * rowcount' 'ALLOC'
 FROM      v$memtbl_info a
        ,  v$index b
        , system_.sys_users_ c
        , system_.sys_indices_ e
        , system_.sys_tables_ f
 WHERE
       a.table_oid = f.table_oid
   and b.index_id = e.index_id
   and e.user_id = c.user_id
   and f.user_id = e.user_id
   and f.tbs_id = a.tablespace_id
   and f.table_oid = b.table_oid
   and c.user_name <> 'SYSTEM_' ;

1. Create a function that can count the number of records in the table.
CREATE FUNCTION GETCOUNT(u_name varchar(40), t_name varchar(40))
 RETURN INTEGER
 AS
    RECORDCOUNT integer;
BEGIN
       EXECUTE IMMEDIATE 'SELECT count(*)  FROM ' || u_name||'.'||t_name INTO RECORDCOUNT ;
RETURN RECORDCOUNT;

END;

/


2. Use function to query the usage per index.
set linesize 2048;
set colsize 30;
SELECT
        c.user_name
        , decode(f.table_type, 'Q', 'QUEUE', 'T', 'TABLE') object_type
        , table_name object_name
        , e.index_name
        , rpad(case2(e.index_type=1, 'b-tree', 'r-tree'),10,' ') index_type
        , ROUND( 16 * GETCOUNT(c.user_name, f.table_name) / 1024/1024, 2)  'ALLOC(M)'
 FROM      v$memtbl_info a
        ,  v$index b
        , system_.sys_users_ c
        , system_.sys_indices_ e
        , system_.sys_tables_ f
 WHERE
       a.table_oid = f.table_oid
   and b.index_id = e.index_id
   and e.user_id = c.user_id
   and f.user_id = e.user_id
   and f.tbs_id = a.tablespace_id
   and f.table_oid = b.table_oid
   and c.user_name <> 'SYSTEM_' ; 

1. Create a function that can count the number of records in the table.
CREATE FUNCTION GETCOUNT(u_name varchar(40), t_name varchar(40))
 RETURN INTEGER
 AS
    RECORDCOUNT integer;
BEGIN
       EXECUTE IMMEDIATE 'SELECT count(*)  FROM ' || u_name||'.'||t_name INTO RECORDCOUNT ;
RETURN RECORDCOUNT;

END;

/


2. Use function to look up the total index usage size per table.
select
          user_name
        , table_name
        , count(index_name) AS 'INDEX_COUNT'
        , round( SUM(alloc) /1024/1024, 2 ) as 'Alloc(M)'
from (
         SELECT
                  c.user_name
                , f.table_name
                , e.index_name
                , 16 * GETCOUNT(c.user_name, f.table_name) AS alloc
         FROM      v$memtbl_info a
                ,  v$index b
                , system_.sys_users_ c
                , system_.sys_indices_ e
                , system_.sys_tables_ f
         WHERE
               a.table_oid = f.table_oid
           and b.index_id = e.index_id
           and e.user_id = c.user_id
           and f.user_id = e.user_id
           and f.tbs_id = a.tablespace_id
           and f.table_oid = b.table_oid
           and c.user_name <> 'SYSTEM_'
     )
 group by user_name, table_name;  

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(MB)'
  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 B.INDEX_TBS_ID <> 0
   AND U.USER_ID = T.USER_ID
 ORDER BY TABLE_NAME, B.INDEX_ID
;