Blog

In our operational daily basis DBA activities, it is important to know the schema size, table size, DB size, object size etc. So here are the proper steps/commands where you can find how to check object size in Oracle database:

Check Object size in Oracle :

Schema size :

Total schemas size :
select owner, sum(bytes)/1024/1024/1024 Size_GB from dba_segments
group by owner;

Index Size/Object size :

select sum(bytes)/1024/1024 as "Index Size (MB)" from dba_segments where segment_name='&INDEX_NAME';

Table size :

col SEGMENT_NAME for a15
col SEGMENT_TYPE for a15
select segment_name,segment_type, sum(bytes/1024/1024/1024) GB from dba_segments where segment_name='&Your_Table_Name' group by segment_name,segment_type;

DB size :

select sum(bytes)/1024/1024 size_in_mb from dba_data_files;

DB size Including all(Datafiles, redolog,tempfile,controlfile) :

select
( select sum(bytes)/1024/1024/1024 data_size from dba_data_files ) +
( select nvl(sum(bytes),0)/1024/1024/1024 temp_size from dba_temp_files ) +
( select sum(bytes)/1024/1024/1024 redo_size from sys.v_$log ) +
( select sum(BLOCK_SIZE*FILE_SIZE_BLKS)/1024/1024/1024 controlfile_size from v$controlfile) "Total Size "
from
dual;

Tablespace size :

set colsep|
set pages 500 lines 500;
select seg_stats.tablespace_name,seg_stats.TotalGB,
seg_stats.TotalMB,DECODE(SIGN(free_stats.FreeGB-seg_stats.FreeGB), 1, seg_stats.FreeGB,
free_stats.FreeGB) FreeGB,DECODE(SIGN(free_stats.FreeMB-seg_stats.FreeMB), 1, seg_stats.FreeMB, free_stats.FreeMB) FreeMB,
DECODE(SIGN(free_stats.Percent_Free-seg_stats.Percent_Free), 1, seg_stats.Percent_Free, free_stats.Percent_Free) Percent_Free
from
(select used.tablespace_name, total.TotalGB, total.TotalMB, (total.TotalGB - used.UsedGB) FreeGB ,
(total.TotalMB - used.UsedMB) FreeMB ,round((total.TotalMB - used.UsedMB)100/total.TotalMB) Percent_Free from (select b.tablespace_name, round(sum(nvl(a.bytes,0))/(10241024)) UsedMB,round(sum(nvl(a.bytes,0))/(102410241024)) UsedGB
from dba_segments a, dba_tablespaces b
where a.tablespace_name (+) = b.tablespace_name
group by b.tablespace_name) used,
(select tablespace_name, sum(bytes)/(10241024) TotalMB, round(sum(bytes)/(102410241024)) TotalGB from dba_data_files group by TABLESPACE_NAME) total where used.tablespace_name = total.tablespace_name) seg_stats, (select free.tablespace_name, total.TotalGB, total.TotalMB, free.FreeGB ,free.FreeMB ,round(free.FreeMB100/total.TotalMB) Percent_Free
from (select b.tablespace_name, round(sum(nvl(a.bytes,0))/(10241024)) FreeMB,round(sum(nvl(a.bytes,0))/(102410241024)) FreeGB from dba_free_space a, dba_tablespaces b where a.tablespace_name (+) = b.tablespace_name group by b.tablespace_name) free, (select tablespace_name, sum(bytes)/(10241024) TotalMB, round(sum(bytes)/(102410241024)) TotalGB
from dba_data_files group by TABLESPACE_NAME) total
where free.tablespace_name = total.tablespace_name) free_stats
where seg_stats.tablespace_name=free_stats.tablespace_name;

So here are some useful commands to check object size in Oracle. Hope this helps!!

Related Article :

https://dbapostmortem.com/how-to-find-ddl-of-a-table-in-oracle-database/