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