Question: I to know the size of my Oracle
database. Is there a table that shows the total size of an
Oracle database.
Answer: There are several ways to
measure the size of an Oracle database:
1 - The size of the Oracle database files can be computed
several ways:
-- get database size from v$datafile:
select round((sum(bytes)/1048576/1024),2)
from v$datafile;
-- get Oracle database size from
dba_data_files:
select
"Reserved_Space(MB)", "Reserved_Space(MB)"
- "Free_Space(MB)" "Used_Space(MB)","Free_Space(MB)"
from(
select
(select sum(bytes/(1014*1024)) from
dba_data_files) "Reserved_Space(MB)",
(select
sum(bytes/(1024*1024)) from dba_free_space) "Free_Space(MB)"
from dual
);
2 - The size of the sum of table extents
select
segment_name table_name,
sum(bytes)/(1024*1024) table_size_meg
from
user_extents
where
segment_type='TABLE'
and
segment_name = 'MYTAB'
group
See code depot for full scripts.
3 - The sum of the size of the data within the tables
You can also compute the size of an Oracle database over
time. In
Oracle 10g and beyond we have the dba_hist_seg_stat
table with a wealth of information about all active segments
within the database, including the space usage in the
space_allocated_total and space_used_total
columns.
This
script will show "spaced used total" (total size) for a
specific Oracle table, essentially computing the Oracle
table size over time:
