From: charliep@infocom.com (Charlie Peck)
Subject: Re: Need Help With SQL Statement Please
Date: 1996/05/05
Message-ID: <charliep-0505961636070001@blue12.infocom.com>#1/1
references: <4m85du$t18@newshost.ptes.com>
organization: infocom, inc
newsgroups: comp.databases.oracle



In article <4m85du$t18@newshost.ptes.com>, jamumper@ptes.com wrote:

> In Loney's DBA book and the Oracle DBA book is a script which allows you
 to use existing
> data in a table to size the table. The script looks something like this:
> 
> 
>         select
>            avg(nvl(vsize(column1),0))+
>            avg(nvl(vsize(column2),0)) avg_row_length
>         from table_name;
> 
> 
> What I would like to do is see if it is possible to make this script
 build itself. I have a database
> with umpteen tables in it, all with umpteen columns and I would rather
 not have to do a 
> describe on all of them and then write this script.

You can get the table and column name information from dba_tables and
dba_columns.  In PL/SQL (or some other procedural wrapper) you could:

   write "select "
   fetch a table name from dba_tables
      fetch a column name for that table from dba_columns
      write "avg(nvl(vsize(<column_name>),0) "
      until no more columns
   write "from table_name;" 
   until no more tables

> The alternative to this is to ANALYZE the tables and then use the
 avg_row_len from 
> dba_tables. Does anyone know whether these would be the same as the sql
 script would
> generate?

I imagine it would give about the same information, and probably much faster
overall.  

charlie peck