To estimate the space required for an existing table you need the

• Pctfree of that table
• Average row length
• No of rows for which the estimate is being prepared.

if a block size for a tablespace is described as 8k , then the whole 8k is not utilized for storing the data, 90-120 bytes in a block will be used for block header and based on the pctfree setting of a table , some storage in a block is left for updates.

if the pctfree setting is 10 then 10% of the block is left free. To Accurately calculate the size of an table u need to know the average length of a row. You can get this information from user_tables in the column avg_row_len. The Pct_free column from the same table specifies the free space to be left in a block.

Formula to calculate the actual storage space is :

(No.of Rows * avg_row_len)/(Actual usage of the block(eliminating the pctfree) * actual usage of the block(eliminating the block header size))

In order to simplify the above calculation i have created a small anonymous block below :


/* This Anonymous block is developed by Parveen Kumar Devatha */
/* You can freely use it or distribute it by crediting the author */declare
v_pctfree number;
v_No_of_rows number:= 1000000; -- replace with no of rows for future expansion
v_table varchar2(50) := 'COMP_SCRIP_MAP'; -- replace with the table you want to estimate
V_block_size number;
V_AVG_ROW_LEN NUMBER;
v_est_bytes Number;
begin
select block_size INTO v_block_size from user_tablespaces a,user_tables b where a.tablespace_name=b.tablespace_name
and b.table_name=v_table;
select pct_free,AVG_ROW_LEN into v_pctfree,V_AVG_ROW_LEN FROM USER_TABLES WHERE TABLE_NAME=v_table;
v_est_bytes:=ROUND((v_AVG_ROW_LEN * v_NO_OF_ROWS) /((100-V_PCTFREE) /100 ) * ((V_BLOCK_SIZE-90)/V_BLOCK_SIZE),0);
dbms_output.put_line(v_est_bytes || ' Bytes required for the expansion of '|| V_TABLE ||' table for the next ' || v_no_of_rows ||' rows') ;
dbms_output.put_line(round(v_est_bytes/1000,2) || 'KB required for the expansion of '|| V_TABLE ||' table for the next ' || v_no_of_rows|| 'rows') ;
dbms_output.put_line(round(v_est_bytes/1000000,2) || 'MB required for the expansion of '|| V_TABLE ||' table for the next ' || v_no_of_rows|| ' rows') ;
exception
when no_data_found then
Dbms_output.put_line('No table exsiting check whether case is correct or the table in the current schema');
when others then
dbms_output.put_line(sqlerrm);
end;