 |
|
Get Oracle table size
Oracle Tips by Burleson Consulting
|
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
Oracle
growth monitoring?
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.
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.
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:
col
c1 format a15 heading 'snapshot|date'
col c2 format a25 heading 'table|name'
col c3 format 999,999,999 heading 'space|used|total'
select
to_char(begin_interval_time,'yy/mm/dd
hh24:mm') c1,
object_name
c2,
space_used_total c3
from
dba_hist_seg_stat
s,
dba_hist_seg_stat_obj
o,
dba_hist_snapshot
sn
where
o.owner = 'SCHEMA_07'
and
s.obj# = o.obj#
and
sn.snap_id = s.snap_id
and
object_name like 'XIF2%'
order by
begin_interval_time;
As I note in my book
Oracle Tuning: The Definitive Reference, we can
quickly plot this information for trending and predictions:
snapshot table
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
