Table space vs index space
                                                 Last update (2014-02-07 15:32:15)
                                                                                                                   Date added (2010-11-03 09:13:23)

Summary
It is very common as the time pass 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)|| '%' 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, 3);

SEG    SIZE_MB PERC
--- ---------- ----------
TAB    7062620 61,26%
LOB     170091 1,48%
IND    4295793 37,26%

As you can see in this 11.3TB database the 4.2TB are occupied by index kind of segments(index, partitions and subpartitions) and 7TB by tables data(tables, partitions and subpartitions). Usually in most cases after moving tables and rebuilding indexes (reseting the HWM of the tables) you get a 70-30% (tables vs indexes). So this database is under investigation if moving the tables will save some space and improve a little bit the performance.
Reviews
Categories
Filters
Search