Re: Need Help With SQL Statement Please

Charlie Peck wrote:
>
> 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
 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

Thanks to everyone who helped me with this problem. I am not the strongest SQL writer in the world, but this gave me an opportunity to hone my skills a little. I ended up writing a PL/SQL program that uses the DBMS_DDL package ANALYZE_TABLE. Here's the script:

DECLARE CURSOR c_getdata IS

	SELECT object_name, object_type, owner
	 FROM dba_objects
	  WHERE owner = 'DBO'
	   AND object_type = 'TABLE';

v_name	varchar(30);
v_type	varchar(30);
v_schema varchar(30);
v_method VARCHAR2(20) := 'ESTIMATE';

END LOOP; END; The odd thing about this is that, according to the documentation, I should be able to set v_method to COMPUTE and leave v_rows and v_percent null. However, when I do this, it errors out. SO, I figured that an estimate of 100 percent was as good as COMPUTE. Works very nicely.

-- 
James A. Mumper
Senior Oracle DBA
Pacific Telesis Video Services