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
Oracle Create table Syntax,Tip and Examples
How to alter table add column oracle