Drop Down MenusCSS Drop Down MenuPure CSS Dropdown Menu

script do I run to get the size of a table

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?
select 
   segment_name           table_name,    
   sum(bytes)/(1024*1024) table_size_meg 
from   
   user_extents 
where  
   segment_type='TABLE' 
and    
   segment_name = 'MYTAB' 
group  ;

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;
 
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



Comments

Popular posts from this blog

PostgreSQL Pgbadger Installation On Linux

PostgreSQL Sequence

How to configure Replication Manager (repmgr) ?

Postgresql maximum size

PostgreSQL pgBadger