I have the following command to perform the analysis of Oracle indexes:
EXEC DBMS_STATS.GATHER_SCHEMA_STATS('USUARIO', estimate_percent=> 25);
I would like to know how to schedule this command so that it runs once every week.
Edited: Oracle Version: 11g.
The Oracle 11g documentation says the following with respect to Jobs :
A Job is a combination of a scheduled time and a program, along with the additional arguments required by the program.
The example of creating a Job (still using the documentation) is:
BEGIN
DBMS_SCHEDULER.CREATE_JOB (
job_name => 'update_sales',
job_type => 'STORED_PROCEDURE',
job_action => 'OPS.SALES_PKG.UPDATE_SALES_SUMMARY',
start_date => '28-APR-08 07.00.00 PM Australia/Sydney',
repeat_interval => 'FREQ=DAILY;INTERVAL=2', /* every other day */
end_date => '20-NOV-08 07.00.00 PM Australia/Sydney',
job_class => 'batch_update_jobs',
comments => 'My new job');
END;
/
In the example, the OPS.SALES_PKG.UPDATE_SALES_SUMMARY procedure will run every two days, from April 28 to November 20, 2008. There is not much secret, just understand the parameters. In your case you will probably want to use the frequency WEEKLY .
About Schedules , the documentation says the following:
A Schedule defines when a Job should run or when a window should open. Schedules can be shared between users when they are created and saved in the database.
Basically a Schedule allows you to use an existing Job, but the functionality is very similar. Example:
BEGIN
DBMS_SCHEDULER.CREATE_SCHEDULE (
schedule_name => 'my_stats_schedule',
start_date => SYSTIMESTAMP,
end_date => SYSTIMESTAMP + INTERVAL '30' day,
repeat_interval => 'FREQ=HOURLY; INTERVAL=4',
comments => 'Every 4 hours');
END;
/
This link has several examples to suit all tastes.