-
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;
/
"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
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|