Query to check table size in Oracle

We often need to find out how big is that particular table. Suppose you did huge data load and want to know the size of the table. We can use below Query to check table size in Oracle

For size in MB

select
owner as “Schema”
, segment_name as “Object Name”
, segment_type as “Object Type”
, round(bytes/1024/1024,2) as “Object Size (Mb)”
, tablespace_name as “Tablespace”
from dba_segments
where segment_name=’<table_name>’;

For size in GB

select
owner as “Schema”
, segment_name as “Object Name”
, segment_type as “Object Type”
, round(bytes/1024/1024/1024,2) as “Object Size (Mb)”
, tablespace_name as “Tablespace”
from dba_segments
where segment_name=’<table_name>’;

If you dont have access to dba_segments ,then you can use user_segments also

For size in MB

select segment_name as “Object Name”
, segment_type as “Object Type”
, round(bytes/1024/1024,2) as “Object Size (Mb)”
, tablespace_name as “Tablespace”
from user_segments
where segment_name=’<table_name>’;

For size in GB

select
segment_name as “Object Name”
, segment_type as “Object Type”
, round(bytes/1024/1024/1024,2) as “Object Size (Mb)”
, tablespace_name as “Tablespace”
from user_segments
where segment_name=’<table_name>’;

we can also use this query to find the top ten biggest table in particular table or particular schema

Top 10 big tables in Particular schema

select * from (select
owner as “Schema”
, segment_name as “Object Name”
, segment_type as “Object Type”
, round(bytes/1024/1024/1024,2) as “Object Size (Mb)”
, tablespace_name as “Tablespace”
from dba_segments
where owner=’<schema>’  and segment_type=’TABLE’ order by 4 desc)  where rownum < 11;

Top 10 big tables in Particular tablespace

select * from (select
owner as “Schema”
, segment_name as “Object Name”
, segment_type as “Object Type”
, round(bytes/1024/1024/1024,2) as “Object Size (Mb)”
, tablespace_name as “Tablespace”
from dba_segments
where tablespace_name =’<tablespace name>’ and segment_name=’TABLE’ order by 4 desc ) where rownum <11;

Hope you like this article on Query to check table size in Oracle

Related Articles

Tablespace Name ,Size ,Free space,datafiles ,growth in Oracle Database

How to Alter Table in Oracle

Oracle Create table Syntax,Tip and Examples

How to Alter Table in Oracle

How to alter table add column oracle

Leave a Reply