SQL> l
  1  select column_name, data_type, data_length, data_precision, data_scale
  2  from user_Tab_columns
  3* where table_name = 'EMP'
SQL> /

COLUMN_NAM DATA_TYPE  DATA_LENGTH DATA_PRECISION DATA_SCALE
---------- ---------- ----------- -------------- ----------
EMPNO      NUMBER              22              4          0
ENAME      VARCHAR2            10
JOB        VARCHAR2             9
MGR        NUMBER              22              4          0
HIREDATE   DATE                 7
SAL        NUMBER              22              7          2
COMM       NUMBER              22              7          2
DEPTNO     NUMBER              22              2          0

8 rows selected.

SQL>

SQL> select empno, mgr, sal, comm, deptno from emp;

     EMPNO        MGR        SAL       COMM     DEPTNO
---------- ---------- ---------- ---------- ----------
      7369       7902        800                    20
      7499       7698       1600        300         30
      7521       7698       1250        500         30
      7566       7839       2975                    20
      7654       7698       1250       1400         30
      7698       7839       2850                    30
      7782       7839       2450                    10
      7788       7566       3000                    20
      7839                  5000                    10
      7844       7698       1500          0         30
      7876       7788       1100                    20
      7900       7698        950                    30
      7902       7566       3000                    20
      7934       7782       1300                    10

14 rows selected.

SQL>

select 'SELECT SUM(VSIZE('||column_name||')) FROM '||owner||'.'||table_name||';'
from dba_tab_columns
/

SQL> declare
  2    cursor c1 is
  3      select column_name, table_name
  4        from user_tab_columns
  5        where table_name = 'EMP'
  6          and data_type in ('NUMBER', 'VARCHAR2');
  7    str       varchar2(200);
  8    l_sum_len number;
  9  begin
 10    for cur_r in c1 loop
 11      str := 'select sum(length(' ||
 12              cur_r.column_name   ||
 13             ')) from '           ||
 14              cur_r.table_name;
 15
 16      execute immediate (str) into l_sum_len;
 17      dbms_output.put_line(cur_r.table_name  ||'.' ||
 18                           cur_r.column_name ||': '||
 19                           l_sum_len
 20                          );
 21    end loop;
 22  end;
 23  /
EMP.EMPNO: 56
EMP.ENAME: 70
EMP.JOB: 96
EMP.MGR: 52
EMP.SAL: 54
EMP.COMM: 11
EMP.DEPTNO: 28

PL/SQL procedure successfully completed.

SQL>

SQL> declare
  2    cursor c1 is
  3      select owner, column_name, table_name
  4        from all_tab_columns
  5        where data_type in ('NUMBER', 'VARCHAR2')
  6         and owner in ('SCOTT', 'FRANJO')
  7       order by owner, table_name;
  8    str       varchar2(200);
  9    l_sum_len number;
 10  begin
 11    for cur_r in c1 loop
 12      str := 'select sum(length(' ||
 13              cur_r.column_name   ||
 14             ')) from '           ||
 15              cur_r.owner         ||'.'||
 16              cur_r.table_name;
 17
 18      execute immediate (str) into l_sum_len;
 19      dbms_output.put_line(cur_r.owner       ||'.' ||
 20                           cur_r.table_name  ||'.' ||
 21                           cur_r.column_name ||': '||
 22                           l_sum_len
 23                          );
 24    end loop;
 25  end;
 26  /
FRANJO.BONUS.COMM:
FRANJO.BONUS.SAL:
FRANJO.BONUS.JOB:
FRANJO.BONUS.ENAME:
FRANJO.DEPT.DNAME: 33
FRANJO.DEPT.LOC: 27
FRANJO.DEPT.DEPTNO: 8
FRANJO.EMP.ENAME: 70
FRANJO.EMP.EMPNO: 56
FRANJO.EMP.DEPTNO: 28
FRANJO.EMP.COMM: 11
FRANJO.EMP.SAL: 54
FRANJO.EMP.MGR: 52
FRANJO.EMP.JOB: 96
FRANJO.PROBA.COL1: 3
FRANJO.SALGRADE.HISAL: 20
FRANJO.SALGRADE.LOSAL: 19
FRANJO.SALGRADE.GRADE: 5
FRANJO.STAGE.TABLE_NAME: 22
FRANJO.STAGE.RECORDCOUNT: 10
SCOTT.BONUS.JOB:
SCOTT.BONUS.SAL:
SCOTT.BONUS.COMM:
SCOTT.BONUS.ENAME:
SCOTT.DEPT.DNAME: 33
SCOTT.DEPT.DEPTNO: 8
SCOTT.DEPT.LOC: 27
SCOTT.EMP.DEPTNO: 28
SCOTT.EMP.COMM: 11
SCOTT.EMP.SAL: 54
SCOTT.EMP.MGR: 52
SCOTT.EMP.JOB: 96
SCOTT.EMP.EMPNO: 56
SCOTT.EMP.ENAME: 70
SCOTT.SALGRADE.LOSAL: 19
SCOTT.SALGRADE.GRADE: 5
SCOTT.SALGRADE.HISAL: 20
SCOTT.STAGE.TABLE_NAME: 22
SCOTT.STAGE.RECORDCOUNT: 10

PL/SQL procedure successfully completed.

SQL>

and owner in ('SCOTT', 'FRANJO')

and owner NOT in (...)

SQL> declare cursor xyz is select owner,column_name,table_name from             
  2  all_tab_columns where data_type in ('number','varchar2') and
  3  owner in('SCOTT','HR') order by owner,table_name;
  4  str varchar2(300);
  5  sum_len number;
  6  begin
  7  for cur_r in xyz loop 
  8  str:= 'select sum(length('|| cur_r.column_name  || '))  from  ' ||
  9  cur_r.owner   ||'.'|| cur_r.table_name;
 10  
 11  execute immediate (str) into sum_len;
 12  
 13  dbms_output.put_line(cur_r.owner  ||'.'|| cur_r.table_name|| '.'||
 14  cur_r.column_name ||':'|| sum_len );
 15  end loop;
 16  end;
 17  /
     

    declare
     fhandle utl_file.file_type;  
      cursor c1 is
        select owner, column_name, table_name
          from all_tab_columns
          where data_type in ('NUMBER', 'VARCHAR2')
           and owner in ('SCOTT', 'FRANJO')
         order by owner, table_name;
      str       varchar2(200);
      l_sum_len number;
   begin
    fhandle := UTL_FILE.FOPEN('temp_test','Sfg2.txt','w');
     utl_file.new_line (fhandle);
     for cur_r in c1 loop
       str := 'select sum(length(' ||
               cur_r.column_name   ||
              ')) from '           ||
               cur_r.owner         ||'.'||
               cur_r.table_name;
 
       execute immediate (str) into l_sum_len;
       utl_file.put_line(cur_r.owner       ||
                            cur_r.table_name  ||
                            cur_r.column_name ||
                            l_sum_len
                           );
     end loop;
     
     UTL_FILE.FCLOSE(fhandle);
  
   end;
   /
   

select * from all_directories