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?
   segment_name           table_name,    
   sum(bytes)/(1024*1024) table_size_meg 
   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'
   to_char(begin_interval_time,'yy/mm/dd hh24:mm')     c1,
   object_name      c2,
   space_used_total c3
   dba_hist_seg_stat       s,
   dba_hist_seg_stat_obj   o,
   dba_hist_snapshot       sn
   o.owner = 'SCHEMA_07'
   s.obj# = o.obj#
   sn.snap_id = s.snap_id
   object_name like 'XIF2%'
order by
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


Popular posts from this blog

PostgreSQL pgBadger

PostgreSQL Pgbadger Installation On Linux

How To Schedule PostgreSQL Jobs using pgAgent on Linux plateform

How to configure Replication Manager (repmgr) ?

Script to kill ALL IDLE Connection In postgreSQL