Script: Computing Table Size

Script: Computing Table Size 

Abstract 
This script calculates the average row size for all tables in a schema, using the VSIZE
function of SQL.  
  
Product Name, Product Version
 Oracle Server, 7.3.x to 9.x
 
Platform  Platform Independent
Date Created  28-Jul-2002
  
Instructions 
Execution Environment:
     <SQL, SQL*Plus, iSQL*Plus>

Access Privileges:
     The scripts needs to be executed with the user who owns the table to be
     analyzed.

Usage:
     sqlplus <user>/<pw> @rowsz

Instructions:
     Copy the script into a file named rowsz. Execute the script from SQL*Plus
     connected as the user who ones the tables to be analyzed.

PROOFREAD THIS SCRIPT BEFORE USING IT! Due to differences in the way text
editors, e-mail packages, and operating systems handle text formatting (spaces,
tabs, and carriage returns), this script may not be in an executable state
when you first receive it. Check over the script to ensure that errors of
this type are corrected. The script will produce an output file named getavgcol.sql.
This needs to be executed to obtain a report of the average size of the tables in
the schema.

 
  
Description 
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 off
REM NAME:   ROWSZ.SQL
REM USAGE:"@rowsz"
REM --------------------------------------------------------------------------
REM REQUIREMENTS:
REM    Should be run under the schema ID of the tables being reported.
REM    Tables with LONG columns will not report row size properly!!
REM --------------------------------------------------------------------------
REM PURPOSE:
REM    Calculate the average row size for all tables in a schema.
REM    It generates a script (getavgcol.sql) off of USER_TABLES and then runs
REM    it.  The following type of SELECT is generated for table in USER_TABLES:
REM         SELECT round(avg(nvl(vsize(COL1),0)) +  
REM                round(avg(nvl(vsize(COL2),0)) + ... + 
REM                round(avg(nvl(vsize(COLn),0))
REM             
REM Where n=# of cols. on the table
REM
REM    Input:           NONE
REM    Output:          getavgcol.sql - Script that is generated and run
REM     getavgcol.lst - Report of each table and its
REM                     average row length
REM            
REM DISCLAIMER:
REM    This script is provided for educational purposes only. It is NOT 
REM    supported by Oracle World Wide Technical Support.
REM    The script has been tested and appears to work as intended.
REM    You should always run new scripts on a test instance initially.
REM --------------------------------------------------------------------------
REM Main text of script follows:
 
drop 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 0
set tab on
set trim on
set verify off
set feedback off
set termout off
set head off
set lines 100
set recsep off
set embedded on
spool getavgcol.sql
prompt column TB format A30
prompt set head off recsep off
prompt set lines 80 feedback off pages 0
prompt spool getavgcol
REM
column select_line format A8 
column end_line format A1 
column from_stmt format A34 word_wrap
column col_nm format A100 
column col_val format A32 
column tnm1 noprint 
column tnmprint format A37 
column column_id noprint
break on tnm1 skip 2
set null ''
clear breaks
select 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_nm
from user_tab_columns UTC, column_counts CC
where UTC.data_type not like 'LONG%' AND UTC.table_name = CC.table_name
order by UTC.table_name, UTC.column_id;
prompt spool off
prompt exit
spool off
drop table column_counts;
exit

 
  
  
Disclaimer 
EXCEPT WHERE EXPRESSLY PROVIDED OTHERWISE, THE INFORMATION, SOFTWARE,
PROVIDED ON AN "AS IS" AND "AS AVAILABLE" BASIS. ORACLE EXPRESSLY DISCLAIMS
ALL WARRANTIES OF ANY KIND, WHETHER EXPRESS OR IMPLIED, INCLUDING, BUT NOT
LIMITED TO, THE IMPLIED WARRANTIES OF MERCHANTABILITY, FITNESS FOR A PARTICULAR
PURPOSE AND NON-INFRINGEMENT. ORACLE MAKES NO WARRANTY THAT: (A) THE RESULTS
THAT MAY BE OBTAINED FROM THE USE OF THE SOFTWARE WILL BE ACCURATE OR
RELIABLE; OR (B) THE INFORMATION, OR OTHER MATERIAL OBTAINED WILL MEET YOUR
EXPECTATIONS. ANY CONTENT, MATERIALS, INFORMATION OR SOFTWARE DOWNLOADED OR
OTHERWISE OBTAINED IS DONE AT YOUR OWN DISCRETION AND RISK. ORACLE SHALL HAVE
NO RESPONSIBILITY FOR ANY DAMAGE TO YOUR COMPUTER SYSTEM OR LOSS OF DATA THAT
RESULTS FROM THE DOWNLOAD OF ANY CONTENT, MATERIALS, INFORMATION OR SOFTWARE.

ORACLE RESERVES THE RIGHT TO MAKE CHANGES OR UPDATES TO THE SOFTWARE AT ANY
TIME WITHOUT NOTICE.

 
  
Limitation of Liability 
IN NO EVENT SHALL ORACLE BE LIABLE FOR ANY DIRECT, INDIRECT, INCIDENTAL,
SPECIAL OR CONSEQUENTIAL DAMAGES, OR DAMAGES FOR LOSS OF PROFITS, REVENUE,
DATA OR USE, INCURRED BY YOU OR ANY THIRD PARTY, WHETHER IN AN ACTION IN
CONTRACT OR TORT, ARISING FROM YOUR ACCESS TO, OR USE OF, THE SOFTWARE.

SOME JURISDICTIONS DO NOT ALLOW THE LIMITATION OR EXCLUSION OF LIABILITY.
ACCORDINGLY, SOME OF THE ABOVE LIMITATIONS MAY NOT APPLY TO YOU.