How to find table size in Oracle database?

In my previous articles I have given the multiple examples and queries to find out cumulative sum or find the SQL_ID for the query. Also I have given the Query to find out the database size in previous articles. In this article I would like to give the answer of most asked question in oracle database interview- How to find table size in Oracle with real examples. I would like to explain multiple scenarios to find table size in Oracle database. There are multiple ways to find the same. Sometimes developers does not have access to the specified system tables so we require to use other way to achieve the same goal. So I would like to give various ways to find table size in Oracle database.You can check the Examsnap Microsoft AZ-104 for microsoft Azure exam dumps.

Find table Size in Oracle Database multiple scenarios :

In this section I would like to explain the ways to find table size in Oracle database with multiple ways. There are some times situations will come when user require to find the table size. Sometimes due to table size it is impacting the actual performance of the application. So it is important for developers or dbas to find out the table size for the databases.

Scenario 1 : Find out the table size using dba_segments view

User can use and find out the size of table using system view named DBA_Segements. Lets say you want to find out the table size of Employee table.

Query :

select segment_name,segment_type,round(SUM(BYTES)/power(2,20)) Table_Size_MB from dba_segments where segment_type=’TABLE’ and owner=’TABLE_OWNER’ and segment_name=’Employee’;

The above query will give you the table size for Employee table in MB.

Scenario 2 : Find out the table size for multiple tables

User can also find out the table size using multiple tables using IN clause. Lets say you want to find out the size of table in MB for Employee and customer table together.

Query :

select segment_name,segment_type,round(SUM(BYTES)/power(2,20)) Table_Size_MB from dba_segments where segment_type=’TABLE’ and owner=’TABLE_OWNER’ and segment_name IN(‘Employee’,Customer’);

The above query will give you the size of two tables. Using this way you can find out the size of tables.

Scenario 3 : Find out the table size using user_segments table

Sometimes user does not have access for dba_segments table. So user can use the user_segment user.

Query :

select round(SUM(BYTES)/power(2,20)) Table_Size_MB from user_segments where owner=’SCOTT’ and segment_name=’EMPLOYEE’;

The above query will calculate the Employee table size for SCOTT user in MB.

Scenario 4 : Partitioning table size

Sometimes user has used partitions for the tables to improve the performance. According to the partition use needs to find out the table size. You can use following query for the same .

select PARTITION_NAME,round(SUM(BYTES)/power(2,20)) Table_Size_MB from dba_segments where SEGMENT_NAME=upper(‘Employee’) and PARTITION_NAME=’P_Joining_date’ group by PARTITION_NAME;

The above query will give you the table size for partitioned employee table for date partition on joining date.

Scenario 5 : Find Top size tables 50.

This is most common scenarios where user wants to find the top size tables for the reference. The following query will give you list of top size tables.

Query :

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

The above query will give you Top 50 tables in size.

These are few queries using which you can calculate the table size in oracle database. If you like this article or if you have any issues with the same kindly comment in comments section.