This script calculates the average row size for all tables in a schema. It generates a script (getAvgCol.sql) from USER_TABLES and then runs it. The following type of SELECT is generated for each table in USER_TABLES: SELECT round(avg(nvl(vsize(COL1),0)) + round(avg(nvl(vsize(COL2),0)) + ... + round(avg(nvl(vsize(COLn),0)) Where n=# of cols. on the table Tables with LONG and LOB columns will not report row size properly. Also tables with object types will throw the following error and will also not report row size properly: ORA-00932: inconsistent datatypes ============= Sample Output ============= ACCOUNTS 6 ACCTS 39 ACCT_ADDRS 38 BAD_DATA 116 BASE1 6 BONUS CEG1 11 CHESS_SAVE CHESS_SAVE_PLAYER CITIES 36 COMPANY_SUMMARY 60 CR_FILES 113

Script:

SET ECHO offREM NAME:   ROWSZ.SQLdrop table column_counts;create table column_counts        (        table_name,        column_count        )        as        (        select table_name, max(column_id)        from user_tab_columns        where data_type not like 'LONG%' AND table_name in        (select table_name from user_tables)        group by table_name        )        ;set pages 0set tab onset trim onset verify offset feedback offset termout offset head offset lines 100set recsep offset embedded onspool getavgcol.sqlprompt column TB format A30prompt set head off recsep offprompt set lines 80 feedback off pages 0prompt spool getavgcolREMcolumn select_line format A8column end_line format A1column from_stmt format A34 word_wrapcolumn col_nm format A100column col_val format A32column tnm1 noprintcolumn tnmprint format A37column column_id noprintbreak on tnm1 skip 2set null ''clear breaksselect UTC.table_name tnm1,        decode(column_id,1,'select ' || chr(39) || UTC.table_name || chr(39) ||                ' TB, ', '        ') ||        'round(avg(nvl(vsize('||column_name||'),0)),0)' ||        decode(column_id,column_count, ' row_size from ' || UTC.table_name             || ';'|| chr(10)||chr(10),                ' +') col_nmfrom user_tab_columns UTC, column_counts CCwhere UTC.data_type not like 'LONG%' AND UTC.table_name = CC.table_nameorder by UTC.table_name, UTC.column_id;prompt spool offprompt exitspool offdrop table column_counts;exit