PL/SQL script to study DB growth?

From: DGO <dotten_at_infocell.com>
Date: Wed, 10 Mar 1999 16:03:35 -0500
Message-ID: <7c6mpl$n4i$1@winter.news.rcn.net>

Created an instance and imported 6 months worth of a client's data into it. I have been tasked with studying how the database has grown over that time. My idea is to create a procedure that will build a temp table and populate it with data from the user's table for a specified date range, analyze it, drop it, and go on to the rest of the tables in the user's schema. Here's what I have so far:

Create or replace PROCEDURE                ANLZ_CUR

as
cursor table_cur IS
  select * from user_tables where table_name like 'GN%'; BEGIN
    for table_rec in (select * from user_tables where table_name like 'GN%')     loop
 ANLZ_PROC(table_rec.table_name);
   end loop;
end;

--which plugs values into ANLZ_PROC which creates the table

create or replace procedure                anlz_proc(tname in varchar2)
as
p_open      integer;

tempname varchar2(20);
BEGIN p_open := dbms_sql.open_cursor;
DBMS_SQL.PARSE(p_open, 'CREATE TABLE TEMPDO_' || TNAME || ' AS SELECT * FROM ' || TNAME ||'', 2); DBMS_sql.parse(p_open,'select table_name into tempname from user_tables where table_name like "%TEMPDO_%" ', 2);

    anlz_tmp(tempname);

end ;

--which passes the temp table name to ANLZ_TMP which analyzes and then drops
--it.

Create or replace Procedure                ANLZ_TMP

   ( tempname IN varchar2)
as

   v_open integer;

   BEGIN
    v_open := dbms_sql.open_cursor;
    dbms_sql.parse(v_open, 'analyze table ' || tempname || ' compute statistics', 2);

END; ***as you can see, I'm a bit new to PL/SQL coding and this is just a start. I'm taking one step at a time. These compile just fine, but when I kick it of on a limited set of tables, it only gets as far as creating the first table and seems to stop there. I used debug mode in SQL Navigator which didn't seem to tell me much. I'd appreciate any input anyone has...... Received on Wed Mar 10 1999 - 15:03:35 CST