Re: Need Help With SQL Statement Please
Date: 1996/05/05
Message-ID: <charliep-0505961636070001_at_blue12.infocom.com>#1/1
In article <4m85du$t18_at_newshost.ptes.com>, jamumper_at_ptes.com wrote:
> In Loney's DBA book and the Oracle DBA book is a script which allows you
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 "
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.
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?