Re: ANALYZE statement as reoccurring job via dbms_job

Jason wrote:
>
> 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.

because its 8.0.5 (bad idea to start with...) you'll need to use dbms_sql. The syntax below will be wrong because I'm miles away from a database at the moment but something like:

procedure my_ana_proc is
 t integer := dbms_sql.open_cursor;
begin

 dbms_sql.parse(t,'analyze table .....',dbms_sql.native);
 dbms_sql.execute(t);
 dbms_sql.close_cursor(t);

hth
connor

-- 
=========================
Connor McDonald
http://www.oracledba.co.uk

"Some days you're the pigeon, some days you're the statue"