Get Oracle table size
Oracle Database Tips by Donald Burleson
Question: What script
do I run to get the size of a table?
Answer: First, we
must remember that Oracle table size is constantly changing as DML adds
and re moves rows. Also, we have many other factors that
determine the "actual" size of a table:
The high water mark for the Oracle table
The number of allocated extents
The amount of spaced reserved on each block
for row expansion (pctfree)
You need to ask yourself exactly
which Oracle table size, you wish to query:
Do you want only the row space consumed? (
select avg_row_len*num_rows from dba_tables)
Do you want to include allocated file space
for the table? (select . . . from dba_segments)
Do you want to include un-used extent space? (select
. . . from dba_data_files, dba_extents . . )
Do you want to include un-used space up to the
high water mark? This may over-estimate the real Oracle table size.
Do you want table sizes for
There are many Oracle scripts for
computing the size of a table, depending on these factors, but
the best is the Oracle
script collection which has many scripts to compute table
sizes, depending on your needs.
segment_name = 'MYTAB'
See code depot for full
In Oracle 10g 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" for a specific Oracle table, essentiall computing the Oracle table
size over time:
c1 format a15 heading 'snapshot|date'
col c2 format a25 heading 'table|name'
col c3 format 999,999,999 heading 'space|used|total'
o.owner = 'SCHEMA_07'
s.obj# = o.obj#
sn.snap_id = s.snap_id
object_name like 'XIF2%'
As I note in my book
Oracle Tuning: The Definitive Reference, we can
quickly plot this information for trending and predictions:
date name SPACE_USED_TOTAL
--------------- ------------------------- ----------
06/03/24 07:03 XIF2IMM_FLIGHT_OCCURRENCE 24,340
06/03/26 07:03 XIF2IMM_FLIGHT_OCCURRENCE 34,468