Compute a table's rowsize

This is a utility script that computes the average rowsize of an existing Oracle table. It can help you determine how many rows can fit into a block size, so you can properly plan the space requirements for a table. It should be run in SQL*PLUS. Anyone can use this script so long as the SELECT privilege is selected on the ALL_TAB_COLUMNS table and the table being computed. You must supply the table name to be computed for the rowsize.

  
     set serverout ON size 1000000
     set ver off
     ACCEPT tbnm PROMPT 'Enter name of table to be computed: '
     ACCEPT tbown PROMPT 'Enter table owner: '
     DEF dot = "."

     spool &tbnm.lst

     DECLARE

       V_SIZE  NUMBER;
       V_TOT_SIZE NUMBER(5,2) := 0;
       V_COLUMN VARCHAR2(255);
       V_INIT_SIZE NUMBER;
       
       CURSOR SLC_TAB_COL_CURSOR IS
         SELECT column_name
         FROM all_tab_columns
         WHERE table_name=upper('&tbnm')
         AND   owner = upper('&tbown');

         SLC_TAB_COL_REC SLC_TAB_COL_CURSOR%ROWTYPE;

       CURSOR AVG_ROW_SIZE_CURSOR IS
         SELECT avg(vsize(V_COLUMN))
         FROM &tbown&dot&tbnm;

     BEGIN

        dbms_output.put_line(chr(10));
        FOR SLC_TAB_COL_REC IN SLC_TAB_COL_CURSOR LOOP

            V_COLUMN := SLC_TAB_COL_REC.COLUMN_NAME;
            
            OPEN AVG_ROW_SIZE_CURSOR;
            FETCH AVG_ROW_SIZE_CURSOR INTO V_SIZE;
            V_TOT_SIZE := V_TOT_SIZE + V_SIZE;
            CLOSE AVG_ROW_SIZE_CURSOR;
            dbms_output.put_line('Col. Name: '||V_COLUMN||'  '||' Avg. Size: '||V_SIZE);

       END LOOP;

       dbms_output.put_line(chr(10)||'Average Table Rowsize is : '||V_TOT_SIZE||' bytes');

     END;
/

Reader Feedback:

Janardanam B. writes: I see a few issues with this code.

1. The total size, V_TOT_SIZE, is not incremented with the column size retrieved.
2. The second cursor, meant for finding the average size of column, just returns the size of the column name instead of the size of the column value. For example: Col. Name: SEQ_NUM Ave. Size: 7 , where 7 is the size of "SEQ_NUM" (7 characters).

Please correct me if I am wrong. I have written a procedure, based on your tip, with a solution for the first issue. But I have not found a solution for the second issue. Please let me know if you have any solution for the second issue.

CREATE OR REPLACE PROCEDURE test2205 (tbname in String, tbowner in String)
IS
       V_SIZE  NUMBER;
       V_TOT_SIZE NUMBER := 0;
       V_COLUMN VARCHAR2(255);
       V_INIT_SIZE NUMBER;

       CURSOR COL_SIZE_CUR IS
         SELECT column_name
         FROM all_tab_columns
         WHERE table_name=upper(tbname)
         AND   owner = upper(tbowner);

         COL_SIZE_REC COL_SIZE_CUR%ROWTYPE;

       CURSOR AVG_COL_SIZE IS
         SELECT avg(vsize(V_COLUMN))
         FROM (select tbowner || '.' || tbname from dual);

BEGIN
        dbms_output.put_line(chr(10));
        FOR COL_SIZE_REC IN COL_SIZE_CUR LOOP
            V_COLUMN := COL_SIZE_REC.COLUMN_NAME;
            OPEN AVG_COL_SIZE;
            FETCH AVG_COL_SIZE INTO V_SIZE;
            CLOSE AVG_COL_SIZE;
            dbms_output.put_line('Col. Name: '||V_COLUMN||'  '||' Ave. Size: '||V_SIZE);
            select V_TOT_SIZE + V_SIZE into V_TOT_SIZE
from dual;  
         END LOOP;

       dbms_output.put_line('Average Table Rowsize is : '||V_TOT_SIZE||' bytes');

END;
/

Author's Response:

With regards to the issues being posted, here are my comments:

1. The total size, V_TOT_SIZE, is not incremented with the column size retrieved. The V_TOT_SIZE variable is increased with the average value, in bytes, of the columns being retrieved as specified in the line:

 V_TOT_SIZE := V_TOT_SIZE + V_SIZE;

2. The second cursor, meant for finding the average size of column, just returns the size of the column name instead of the size of the column value.

Please test it on tables with varying record LENGTH values on a particular column. Of course, if you will test it on a column with a fixed record length, it will return the same value. Try running it on the EMP table and you will get the difference.

Thank you for your feedback.

Reader Feedback:

Beth S. writes: Why not just compute statistics on the table check the AVG_ROW_LEN in USER_TABLES/DBA_TABLES?

Steve S. writes: The idea of the tip is most welcome, but I think the reader Janardanam B. is right in his comment for the second point. In order to get the average length of a column whose name is known only at run time and not at pl/sql compilation time, some kind of dynamic sql should be used. This could be done either by using the DBMS_SQL package (for versions earlier than Oracle8i) or by using the EXECUTE IMMEDIATE statement for Oracle8i and later. It could look like this (this is just a partial code, using the author's variable names):

 
DECLARE
           sql_stmt    VARCHAR2(2000);
           v_table       VARCHAR2(100);
BEGIN
           v_table   := '&tbown&dot&tbnm'
 
           sql_stmt := 'SELECT avg(vsize' || V_COLUMN || ')) FROM ' || V_TABLE ;
 
           EXECUTE IMMEDIATE sql_stmt INTO v_size ;
END;

Steve S. writes: The reader (Janardanam B.) feedback on this tip is correct. I ran the author's code against my tables, and it returned the length of the column name, not the average length of the column values. The author is attempting to do a dynamic SQL query where only static SQL is allowed, so the column name is being interpreted as a character literal and not a database object name.

Using the DBMS_SQL package to do a dynamic query, as described in the Oracle8 PL/SQL Programming book published by Oracle Press, I was able to modify the author's program to work as intended:

     set serverout ON size 1000000
     set ver off
     ACCEPT tbnm PROMPT 'Enter name of table to be computed: '
     ACCEPT tbown PROMPT 'Enter table owner: '
     DEF dot = "."

     spool &tbnm.lst

     DECLARE

       V_SIZE  NUMBER(8,2) := 0;
       V_TOT_SIZE NUMBER(8,2) := 0;
       V_COLUMN VARCHAR2(255);
       V_INIT_SIZE NUMBER;
       V_CURSOR_ID INTEGER;
       V_DYNAMIC_SQL VARCHAR2(500);
       V_DUMMY INTEGER;
       
       CURSOR SLC_TAB_COL_CURSOR IS
         SELECT column_name
         FROM all_tab_columns
         WHERE table_name=upper('&tbnm')
         AND   owner = upper('&tbown');

         SLC_TAB_COL_REC SLC_TAB_COL_CURSOR%ROWTYPE;

     BEGIN

        dbms_output.put_line(chr(10));
        FOR SLC_TAB_COL_REC IN SLC_TAB_COL_CURSOR LOOP

            V_COLUMN := SLC_TAB_COL_REC.COLUMN_NAME;
            
            -- open dynamic cursor for average column size
            V_CURSOR_ID := DBMS_SQL.OPEN_CURSOR;
            
            -- build query
            V_DYNAMIC_SQL := 'SELECT NVL(AVG(VSIZE(' || V_COLUMN || ')), 0) FROM &tbown&dot&tbnm';
            
            -- parse the query
            DBMS_SQL.PARSE(V_CURSOR_ID, V_DYNAMIC_SQL, DBMS_SQL.V7);
            
            -- define the output variable
            DBMS_SQL.DEFINE_COLUMN(V_CURSOR_ID, 1, V_SIZE);
            
            -- execute the statement
            V_DUMMY := DBMS_SQL.EXECUTE(V_CURSOR_ID);
            
            -- fetch the result row (if non-zero return code, bail out)
            IF DBMS_SQL.FETCH_ROWS(V_CURSOR_ID) = 0 THEN 
                EXIT;
            END IF;
            
            -- retrieve the output variable
            DBMS_SQL.COLUMN_VALUE(V_CURSOR_ID, 1, V_SIZE);
                       
            -- close the dynamic cursor 
            DBMS_SQL.CLOSE_CURSOR(V_CURSOR_ID);
            
            V_TOT_SIZE := V_TOT_SIZE + V_SIZE;
            
            dbms_output.put_line('Col. Name: '||V_COLUMN||'  '||' Avg. Size: '||V_SIZE);

       END LOOP;

       dbms_output.put_line(chr(10)||'Average Table Rowsize is : '||V_TOT_SIZE||' bytes');

     END;
/

Mike B. writes: Below is the original script rewritten to calculate the average column size correctly (noted as issue #2 by Janardanam B.) and some sample output. Keep in mind that if the table has user defined datatypes like the oe.customers table in the second output example, this script will not work.

===================== begin script code ==============================
 
set serverout ON size 1000000
set ver off
ACCEPT tbnm PROMPT 'Enter name of table to be computed: '
ACCEPT tbown PROMPT 'Enter table owner: '
DEF dot = "."
 
spool &tbnm.lst
 
DECLARE
       AVG_COL_SIZE  VARCHAR2(2000) ;
       V_TAB_NAME VARCHAR2(128) := '&tbown&dot&tbnm';
       V_SIZE  NUMBER;
       V_TOT_SIZE NUMBER(5,2) := 0;
       V_COLUMN VARCHAR2(255);
       V_INIT_SIZE NUMBER;
 
       CURSOR SLC_TAB_COL_CURSOR IS
         SELECT column_name
         FROM all_tab_columns
         WHERE table_name=upper('&tbnm')
         AND   owner = upper('&tbown');
 
         SLC_TAB_COL_REC SLC_TAB_COL_CURSOR%ROWTYPE;

BEGIN
        dbms_output.put_line(chr(10));
        FOR SLC_TAB_COL_REC IN SLC_TAB_COL_CURSOR LOOP
 
            V_COLUMN := SLC_TAB_COL_REC.COLUMN_NAME;
 
            AVG_COL_SIZE := 'select round(avg(vsize('||V_COLUMN||')),6) from '||V_TAB_NAME ;
 
            EXECUTE IMMEDIATE AVG_COL_SIZE INTO V_SIZE ;
            V_TOT_SIZE := V_TOT_SIZE + V_SIZE;
            dbms_output.put_line('Col. Name: '||rpad(V_COLUMN,32)||'  '
                        ||' Avg. Size: '||V_SIZE);
 
       END LOOP;
 
       dbms_output.put_line(chr(10)||'Average Table Rowsize is : '||V_TOT_SIZE||' bytes');
END;
/
 
--===================== END Script Code =============================
## Output 1
SQL> @get_rowsize.sql
Enter name of table to be computed: emp
Enter table owner: scott

Col. Name: EMPNO                              Avg. Size: 2.928571
Col. Name: ENAME                              Avg. Size: 5
Col. Name: JOB                                Avg. Size: 6.857143
Col. Name: MGR                                Avg. Size: 3
Col. Name: HIREDATE                           Avg. Size: 7
Col. Name: SAL                                Avg. Size: 2.428571
Col. Name: COMM                               Avg. Size: 1.75
Col. Name: DEPTNO                             Avg. Size: 2
 
Average Table Rowsize is : 30.97 bytes
 
PL/SQL procedure successfully completed.
 
## Output 2
SQL> desc oe.customers
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 CUSTOMER_ID                               NOT NULL NUMBER(6)
 CUST_FIRST_NAME                           NOT NULL VARCHAR2(20)
 CUST_LAST_NAME                            NOT NULL VARCHAR2(20)
 CUST_ADDRESS                                       OE.CUST_ADDRESS_TYP
 PHONE_NUMBERS                                      OE.PHONE_LIST_TYP
 NLS_LANGUAGE                                       VARCHAR2(3)
 NLS_TERRITORY                                      VARCHAR2(30)
 CREDIT_LIMIT                                       NUMBER(9,2)
 CUST_EMAIL                                         VARCHAR2(30)
 ACCOUNT_MGR_ID                                     NUMBER(6)
 CUST_GEO_LOCATION                                  MDSYS.SDO_GEOMETRY
 
SQL> @get_rowsize.sql
Enter name of table to be computed: customers
Enter table owner: oe

Col. Name: CUSTOMER_ID                        Avg. Size: 2.996865
Col. Name: CUST_FIRST_NAME                    Avg. Size: 5.695925
Col. Name: CUST_LAST_NAME                     Avg. Size: 6.523511
Col. Name: CUST_ADDRESS                       Avg. Size: 42.473354
DECLARE
*
ERROR at line 1:
ORA-00932: inconsistent datatypes: expected - got OE.PHONE_LIST_TYP
ORA-06512: at line 28

Ofer H. writes: Correct me if I'm wrong, but analyzing the table return you also the average rowsize (in avg_row_len). Since most of the databases are CBO, you probably have an analyze of all tables.

Frank P. writes: The calulation for the column size should consider NULL values. If there is at least one column having no value in any record, the calculation of the total size will bring NULL.

The line 
    AVG_COL_SIZE := 'select round(avg(vsize('||V_COLUMN||')),6) from '||V_TAB_NAME ;

should be replaced by
    AVG_COL_SIZE := 'select nvl(round(avg(vsize('||V_COLUMN||')),6),0) from '||V_TAB_NAME ;

For More Information

• Feedback: E-mail the editor with your thoughts about this tip.
• More tips: Hundreds of free Oracle tips and scripts.
• Tip contest: Have an Oracle tip to offer your fellow DBAs and developers? The best tips submitted will receive a cool prize -- submit your tip today!
• Ask the Experts: Our SQL, PL/SQL, administrative and data warehousing gurus are waiting to answer your toughest questions.
• Forums: Ask your technical Oracle questions -- or help out your peers by answering them -- in our active forums.
• Best Web Links: Oracle tips, tutorials and scripts from around the Web.