script for populating table

Hi,

I have to do a "collect statistics on....INDEX, etc" for 14 tables, and and an error table(if there is an error in collecting statistics) has to be populated as follows:

Insert into error_history values (Date, "Table Name",Status, etc..)

HOw do i achieve this using only ONE COLLECT STATISTICS statement, instead of writing the collect statistics statement for each of the 14 tables....? The error table has to be populated with all the table names if there is an error.

Thanks for your help!

You could use a procedure similar to this:

Code:

---

Declare
Errc Number(8);
Errm Varchar2(512);
Begin
  For T In (Select Table_Name From User_Tables)
  Loop
    Begin
      Dbms_Stats.Gather_Table_Stats
        (Ownname => User
        ,Tabname => T.Table_Name
        ,Cascade => True
        );
    Exception
      When Others Then
        Errc:=Sqlcode;
        Errm:=Sqlerrm;
        Insert Into Error_History
          Values (Sysdate
                ,T.Table_Name,
                ,Errc
                ,Errm);
    End;
  End Loop;
End; 
/

---

:D