script for populating table
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 2 of 2

Thread: script for populating table

  1. #1
    Join Date
    May 2004
    Posts
    14

    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!

  2. #2
    Join Date
    Jul 2002
    Location
    Lake Worth, FL
    Posts
    1,477

    Lightbulb

    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;  
    /
    "The person who says it cannot be done should not interrupt the person doing it." --Chinese Proverb

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Click Here to Expand Forum to Full Width