CODEWITHSUNDEEP
oracle-database
Roniu
Roniu

Reputation: 79

Calculate how much space take 100 rows in a Table

I have a Table with 5000 rows. Is there any way to find out how much space is used by the first 100 rows?

EDIT I found this script on the internet:

WITH table_size AS
 (SELECT   owner, segment_name, SUM (BYTES) total_size
      FROM dba_extents
     WHERE segment_type = 'TABLE'
  GROUP BY owner, segment_name)
SELECT table_name, avg_row_len, num_rows * avg_row_len actual_size_of_data,
   b.total_size
FROM dba_tables a, table_size b
WHERE a.owner = UPPER ('&&ENTER_OWNER_NAME')
 AND a.table_name = UPPER ('&&ENTER_TABLE_NAME')
 AND a.owner = b.owner
 AND a.table_name = b.segment_name;

I don't know if it gives the desired result. It calculates the Average Row Length and I multiply that with 100.

Upvotes: 0

Views: 1841

Answers (1)

Mark Wagoner
Mark Wagoner

Reputation: 1769

Oracle separates the logical storage from the physical by packing rows into blocks. So, while it's not straight forward and it is also not exact, it is possible.

You have to determine the sum of the bytes used by a row (this will be an educated guess if you have one or more varchar2 columns) then, based on block size, determine how many rows will fit in a block. Oracle always allocates a full block even if it only has two store on byte in it so the total storage will be a factor of block size.

Upvotes: 2

Related Questions