Oracle Table space vs index space

It is very common as time passes the database to constantly grow. You can calculate the tables and indexes space occupied with the following script.

SELECT SUBSTR(segment_type, 0, 18) SEGMENT, SUM(size_mb) size_mb, 
ROUND((RATIO_TO_REPORT(SUM(size_mb)) OVER ())*100, 2)|| '%' PERC
FROM (
SELECT segment_type, ROUND(SUM(size_mb)) size_mb 
FROM (SELECT owner, segment_name, segment_type, partition_name,

ROUND(bytes/(1024*1024),2)

SIZE_MB, tablespace_name 
FROM DBA_SEGMENTS 
WHERE SEGMENT_TYPE IN ('LOBINDEX', 'INDEX PARTITION',

'INDEX SUBPARTITION', 'INDEX', 
'TABLE SUBPARTITION', 'TABLE PARTITION', 'LOB PARTITION',

'LOBSEGMENT', 'TABLE', 'LOB SUBPARTITION') 

ORDER BY bytes DESC) 
GROUP BY segment_type 
ORDER BY size_mb DESC) 
GROUP BY SUBSTR(segment_type, 0, 18);

SEGMENT               SIZE_MB PERC
------------------ ---------- --------
TABLE SUBPARTITION    1980567 17,18%
INDEX PARTITION       1333269 11,56%
LOBINDEX                  298 0%
TABLE PARTITION       1806063 15,67%
LOB PARTITION           12080 ,1%
INDEX SUBPARTITION    2240245 19,43%
LOBSEGMENT             157713 1,37%
TABLE                 3275990 28,42%
INDEX                  722279 6,27%

9 rows selected.

If someone wants to have a better grouping like tables against indexes and lob space can use the following.

SELECT SUBSTR(segment_type, 0, 3) SEGMENT, SUM(size_mb) size_mb,
ROUND((RATIO_TO_REPORT(SUM(size_mb)) OVER ())*100, 2)|| '%' PERCFROM (SELECT segment_type, ROUND(SUM(size_mb)) size_mb
FROM (SELECT owner, segment_name, segment_type, partition_name, ROUND(bytes/(1024*1024),2) SIZE_MB, tablespace_name
FROM DBA_SEGMENTS
WHERE SEGMENT_TYPE IN ('LOBINDEX', 'INDEX PARTITION', 'INDEX SUBPARTITION', 'INDEX',
'TABLE SUBPARTITION', 'TABLE PARTITION', 'LOB PARTITION', 'LOBSEGMENT', 'TABLE', 'LOB SUBPARTITION')
ORDER BY bytes DESC)
GROUP BY segment_type
ORDER BY size_mb DESC)
GROUP BY SUBSTR(segment_type, 0, 3); SEG SIZE_MB PERC--- ---------- ----------TAB 7062620 61,26%LOB 170091 1,48%IND 4295793 37,26%