Actual Objects Size in DB

Description	Actual size occupied by objects
Download Script	db_obj_size.sql
Date	20-Sep-2013

In this example we can find the actual size of the objects i.e., the space occupied by all the physical objects not the space occupied by datafiles.

The objects include the below list

• LOBINDEX
• INDEX PARTITION
• TABLE SUBPARTITION
• ROLLBACK
• TABLE PARTITION
• NESTED TABLE
• LOB PARTITION
• INDEX SUBPARTITION
• CACHE
• LOBSEGMENT
• INDEX
• TABLE
• TYPE2 UNDO
• CLUSTER
• LOB SUBPARTITION

SELECT sum(bytes)/1024/1024 "Used in MB" FROM dba_segments
/

Size occupied by each type of object

select unique segment_type,sum(bytes)/1024/1024 from dba_segments group by segment_type
/

select unique segment_type,sum(bytes)/1024/1024 "Size in MB"from dba_segments group by segment_type /

Sample Execution and Output

SQL> SELECT sum(bytes)/1024/1024 "Size in MB" FROM dba_segments
/
Size in MB
----------
435925.375

SQL> select unique segment_type,sum(bytes)/1024/1024 "Size in MB"from dba_segments group by segment_type
/

SEGMENT_TYPE                 Size in MB
------------------ --------------------
LOBINDEX                      1513.6875
INDEX PARTITION              24582.5625
TABLE SUBPARTITION                  136
ROLLBACK                           .375
TABLE PARTITION              24811.8125
NESTED TABLE                      4.375
LOB PARTITION                   12.5625
INDEX SUBPARTITION                   83
CACHE                             .0625
LOBSEGMENT                   56456.3125
INDEX                        136582.688
TABLE                            186685
TYPE2 UNDO                    4482.5625
CLUSTER                         310.375
LOB SUBPARTITION                    269

15 rows selected.