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

Comments