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

 22,319 views last month,  1 views today

About Mehmet Salih Deveci

I am Founder of SysDBASoft IT and IT Tutorial and Certified Expert about Oracle & SQL Server database, Goldengate, Exadata Machine, Oracle Database Appliance administrator with 10+years experience.I have OCA, OCP, OCE RAC Expert Certificates I have worked 100+ Banking, Insurance, Finance, Telco and etc. clients as a Consultant, Insource or Outsource.I have done 200+ Operations in this clients such as Exadata Installation & PoC & Migration & Upgrade, Oracle & SQL Server Database Upgrade, Oracle RAC Installation, SQL Server AlwaysOn Installation, Database Migration, Disaster Recovery, Backup Restore, Performance Tuning, Periodic Healthchecks.I have done 2000+ Table replication with Goldengate or SQL Server Replication tool for DWH Databases in many clients.If you need Oracle DBA, SQL Server DBA, APPS DBA,  Exadata, Goldengate, EBS Consultancy and Training you can send my email adress [email protected].-                                                                                                                                                                                                                                                 -Oracle DBA, SQL Server DBA, APPS DBA,  Exadata, Goldengate, EBS ve linux Danışmanlık ve Eğitim için  [email protected] a mail atabilirsiniz.

Leave a Reply