ANALYZE statement as reoccurring job via dbms_job

Oracle 8.0.5.x

I have a sql statement that analyzes a particualr table in my database as follows:

ANALYZE TABLE <SCHEMA.TABLENAME> COMPUTE STATISTICS FOR TABLE FOR ALL INDEXES FOR ALL INDEXED COLUMNS; I run this daily as user SYS for a table in one user schema.

I would like to create a stored procedure for this statement and then run this each day at 3am via dbms_job.

I know that I need to have job_queue_processes and job_queue_interval included in my init.ora file.

I know to submit the job I would use something like the following:

>declare
>l_job number;
>begin
>dbms_job.submit(l_job,
> 'name_of_procedure;'
> trunc(sysdate)+1+3/24,
> 'trunc(sysdate)+1+3/24' );
>end;
>/

But I'm not sure how to convert my sql statement into a procedure.

>create or replace procedure name_of_procedure
>as
>beigin
> ???????????

Any help appreciated. Received on Wed Mar 26 2003 - 14:02:45 CST