How to Find the Size of Number Of Rows Of A Table

You can use the AVG_ROW_LEN column in the DBA_TABLES view which will give you the average row size of a table in bytes. Before using this column you will need to compute statistics by either running the ANALYZE COMPUTE STATISTICS command or the DBMS_STATS package.

For the size of a specific row you would have to retrieve it and use the VSIZE(col) function to add up the length of the data.

Example:

I want to see total size of the rows from consumer table for US market.

Step 1:

SQL> set serveroutput on

SQL> DECLARE

PUT_SYN varchar2(4000);

FULL_SYN VARCHAR2(4000) := 'Sum(0';

CURSOR CALC_SIZE IS

select 'nvl(vsize('||column_name||'),0)'

from dba_tab_cols

where table_name='CONSUMER' and owner='IPX';

BEGIN

OPEN CALC_SIZE;

LOOP

FETCH CALC_SIZE INTO PUT_SYN;

EXIT WHEN CALC_SIZE%NOTFOUND;

FULL_SYN:=FULL_SYN || '+' || PUT_SYN;

END LOOP;

FULL_SYN:=FULL_SYN || ')';

dbms_output.put_line(FULL_SYN);

END;

/

The above will generate output in the form of:

Sum(0+nvl(vsize(CONSUMERID),0)+nvl(vsize(ALIAS),0)+nvl(vsize(MSISDN),0)+nvl(vsiz

e(STATUS),0)+nvl(vsize(OPERATORID),0)+nvl(vsize(CREATED),0)+nvl(vsize(DBTIMESTAMP),0)+nvl(vsize(FRAUDSTATUS),0)+nvl(vsize(OPERATORTIMESTAMP),0)+nvl(vsize(EXPORTSTATUS),0)+nvl(vsize(OPERATORALIAS),0)+nvl(vsize(ALIASOPERATORID),0)+nvl(vsize(COMMENTS),0)+nvl(vsize(SYS_C00014_10051102:30:23$),0)+nvl(vsize(ATTRIBUTE),0)+nvl(vsize(OPERATORSOURCETYPE),0)+nvl(vsize(BLOCKEDUNTIL),0))

PL/SQL procedure successfully completed.

Step 2:

select Sum(0+nvl(vsize(CONSUMERID),0)+nvl(vsize(ALIAS),0)+nvl(vsize(MSISDN),0)+

nvl(vsize(STATUS),0)+nvl(vsize(OPERATORID),0)+nvl(vsize(CREATED),0)

+nvl(vsize(DBTIMESTAMP),0)+nvl(vsize(FRAUDSTATUS),0)

+nvl(vsize(OPERATORTIMESTAMP),0)+nvl(vsize(EXPORTSTATUS),0)

+nvl(vsize(OPERATORALIAS),0)+nvl(vsize(ALIASOPERATORID),0)

+nvl(vsize(COMMENTS),0)+nvl(vsize(ATTRIBUTE),0)+nvl

(vsize(OPERATORSOURCETYPE),0)+nvl(vsize(BLOCKEDUNTIL),0)) "Total Size"

from consumer c

where C.OPERATORID in (select O.OPERATORID from operator o where O.MARKETID='US');

Total Size

-------------------------------

9677914526