Analyze Objects using a Stored Procedure

[THoey]

I am trying to write a stored procedure that will be ran on occasion to analyze the two tables and three indexes owned by a schema. I have tried a couple different formats to the calls and the procedures compile cleanly, but they do not execute. I am sure it is something simple, but something I can't find an answer to. The latest code:

CREATE OR REPLACE  PROCEDURE "RCADMIN"."PURGE_ALL_CALLS"  IS
   sql_msg   VARCHAR2(1000) :=NULL;
BEGIN
   --*******************************
   --* Clean-up of ALL_CALLS Table *
   --*******************************
   DELETE FROM RCADMIN.ALL_CALLS WHERE DATEOFCALL <= TRUNC(SYSDATE - 120);
   COMMIT;
   
   --***********************
   --* Analyze all objects *
   --***********************
   sql_msg := 'exec dbms_stats.gather_table_stats(ownname=> ''RCADMIN'', tabname=> ''REPEAT_CALLER_THRESHOLDS'', partname=> NULL);';
   EXECUTE IMMEDIATE sql_msg;

   sql_msg := 'exec dbms_stats.gather_table_stats(ownname=> ''RCADMIN'', tabname=> ''ALL_CALLS'', partname=> NULL);';
   EXECUTE IMMEDIATE sql_msg;

   sql_msg := 'exec dbms_stats.gather_index_stats(ownname=> ''RCADMIN'', indname=> ''ALL_CALLS_BTN_IDX'', partname=> NULL);';
   EXECUTE IMMEDIATE sql_msg;

   sql_msg := 'exec dbms_stats.gather_index_stats(ownname=> ''RCADMIN'', indname=> ''ALL_CALLS_WTN_IDX'', partname=> NULL);';
   EXECUTE IMMEDIATE sql_msg;

   sql_msg := 'exec dbms_stats.gather_index_stats(ownname=> ''RCADMIN'', indname=> ''REPEAT_CALLER_THRESHOLDS_IDX'', partname=> NULL);';
   EXECUTE IMMEDIATE sql_msg;

END;

I am also sure that this can be done with one command as these five objects are the only objects owned by this schema. Suggestions Please?


Terry
**************************
* General Disclaimer - Please read *
**************************
Please make sure your post is in the CORRECT forum, has a descriptive title, gives as much detail to the problem as possible, and has examples of expected results. This will enable me and others to help you faster...

 

[THoey]

Okay, a little more research turned up the command to do the whole schema:

DBMS_UTILITY.ANALYZE_SCHEMA ('SCHEMA', 'COMPUTE');

So:

CREATE OR REPLACE  PROCEDURE "RCADMIN"."PURGE_ALL_CALLS"  IS
   sql_msg   VARCHAR2(1000) :=NULL;
BEGIN
   --*******************************
   --* Clean-up of ALL_CALLS Table *
   --*******************************
   DELETE FROM RCADMIN.ALL_CALLS WHERE DATEOFCALL <= TRUNC(SYSDATE - 120);
   COMMIT;
   
   --***********************
   --* Analyze all objects *
   --***********************
   sql_msg := 'DBMS_UTILITY.ANALYZE_SCHEMA (''SCHEMA'', ''COMPUTE'');'   
   EXECUTE IMMEDIATE sql_msg;
END;

Error:

BEGIN purge_all_calls; END;

*
ERROR at line 1:
ORA-00900: invalid SQL statement
ORA-06512: at "RCADMIN.PURGE_ALL_CALLS", line 14
ORA-06512: at line 1

Terry
**************************
* General Disclaimer - Please read *
**************************
Please make sure your post is in the CORRECT forum, has a descriptive title, gives as much detail to the problem as possible, and has examples of expected results. This will enable me and others to help you faster...

 

[THoey]

Never mind. Kept playing with it and got it:

CREATE OR REPLACE  PROCEDURE "RCADMIN"."PURGE_ALL_CALLS"  IS
   sql_msg   VARCHAR2(1000) :=NULL;
BEGIN
   --*******************************
   --* Clean-up of ALL_CALLS Table *
   --*******************************
   DELETE FROM RCADMIN.ALL_CALLS WHERE DATEOFCALL <= TRUNC(SYSDATE - 120);
   COMMIT;
   
   --***********************
   --* Analyze all objects *
   --***********************
   DBMS_UTILITY.ANALYZE_SCHEMA ('RCADMIN', 'COMPUTE');   
END;

Terry
**************************
* General Disclaimer - Please read *
**************************
Please make sure your post is in the CORRECT forum, has a descriptive title, gives as much detail to the problem as possible, and has examples of expected results. This will enable me and others to help you faster...