In this article, let’s discuss the steps to calculate the table size in Oracle in detail.
In Oracle, data is stored in blocks. Contagious number of blocks is called Extent. One or more extent makes up a segment which corresponds to a table, a table partition or an index. The dba_segments is the dictionary where the details about the segments are stored. When using this dba_segments, we get the size of the physical data itself.
2. Calculate table size
To calculate the size of a table in ‘MB’ from the dba_segments dictionary, the following query can be used. DBA rights is required to query the dba_segments dictionary.
select segment_name,segment_type,bytes/1024/1024 MB from dba_segments where segment_type='TABLE' and segment_name='<yourtablename>';
If you do not have dba rights, then you can query user_segments view.
SELECT bytes / 1024 / 1024 MB FROM user_segments WHERE segment_name = 'Table_name'
3. Calculate all tables size
To calculate the size of all tables in ‘MB’ from the dba_segments dictionary, the following query can be used.
SELECT DS.TABLESPACE_NAME, SEGMENT_NAME, ROUND(SUM(DS.BYTES) / (1024 * 1024)) AS MB FROM DBA_SEGMENTS DS WHERE SEGMENT_NAME IN (SELECT TABLE_NAME FROM DBA_TABLES) GROUP BY DS.TABLESPACE_NAME, SEGMENT_NAME;
To conclude, we’ve discussed how to calculate table size in Oracle using the dba_segments view. There are also other ways to calculate the table size using dba_tables, all_tables or user_tables. However these views return only estimates and not the exact data size. To calculate the exact statistics from these view, the statistics needs to be gathered first.
what is difference between Dba_segments and Dba_tables?
The dba_segments describes the “physical” data segment, like a data file while dba_tables view describes a “logical” structure of the table.
What are Oracle Segments?
A segment is a set of extents that contains all the data for a specific logical storage structure within a tablespace.
How to check dba_segments in oracle?
select segment_name,segment_type,bytes/1024/1024 MB