Find Table Size & Schema Size and Database Size in Oracle

I will explain How to Find Table Size & Schema Size and Database Size in Oracle in this post.

 

 

 

Oracle Table Size Check

You can find out the Table size using the dba_segments views as follows.

select segment_name,segment_type,round(SUM(BYTES)/power(2,20)) SEGMENT_SIZE_MB
 from dba_segments
 where segment_type='TABLE' and owner='TABLE_OWNER' and segment_name='<TABLE_NAME>';

 

 

If you don’t have DBA rights, you can query it using user_segments view as follows.

select round(SUM(BYTES)/power(2,20)) SEGMENT_SIZE_MB from user_segments where owner='TABLE_OWNER' and segment_name='<TABLE_NAME>';

 

 

If your table is partitioned, you can find out the partition size as follows.

 

select PARTITION_NAME,round(SUM(BYTES)/power(2,20)) SEGMENT_SIZE_MB from dba_segments where SEGMENT_NAME=upper('&TABLE_NAME') and PARTITION_NAME='PARTITION_NAME' group by PARTITION_NAME;

 

 

Find Top Tables by Size in Oracle

You want to learn which table is biggest in database or which schema is biggest in database.

You can learn Top segment of database and Table size with following script.

SELECT * FROM (
SELECT SEGMENT_NAME, round(SUM(BYTES)/power(2,20)) SEGMENT_SIZE
FROM DBA_SEGMENTS where segment_type='TABLE' 
GROUP BY SEGMENT_NAME
ORDER BY 2 desc)
WHERE ROWNUM < 101;

 

 

 

Find Top Schemas by Size in Oracle

You can learn Top Schema of database and Schema size with following script.

SELECT * FROM (
SELECT OWNER, SUM(BYTES)/1048576 MB 
from DBA_SEGMENTS 
GROUP BY OWNER 
ORDER BY MB DESC)
WHERE ROWNUM < 20;

 

You can check the size of schema using the following script.

SELECT OWNER, SUM(BYTES)/1048576 MB from DBA_SEGMENTS where owner='HR';

 

 

Oracle Database Size

You can check the size of oracle database from segment size and datafile size. Datafile size is total size of physical database size. But Segment size is  net size of database which is called total data size.

 

select /*+parallel(4) */ round(sum(bytes / (1024*1024*1024))) "DB Size in GB" from dba_segments;

select /*+parallel(4) */ round(sum(bytes / (1024*1024*1024))) "DB Size in GB" from dba_data_files;

 

 

Do you want to learn Oracle Database Performance Tuning detailed, then read the following articles.

Performance Tuning and SQL Tuning Tutorial in the Oracle Database