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.
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 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 |