Get Oracle table size

Oracle Tips by Burleson Consulting

First, we must remember that table size is constantly changing as DML adds and removes 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)

Click here for the script

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, here is one.

No comments :

Post a Comment