How to create a JOB on Oracle?

Asked

Viewed 13,268 times

8

I have the command below 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 1 time each week.

Edited: Version of Oracle: 11g.

oracle índices job
  • 1

    This question applies to Oracle Scheduler right? Could you also tell what version of Oracle is? 11g?

  • Now you have raised one more question. I must use JOB or SCHEDULER for this case?

1 answer

11


The Oracle 11g documentation reads as follows 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 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 will run every two days, from April 28 until November 20, 2008. 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 for all tastes.

Browser other questions tagged oracle índices job

You are not signed in. Login or sign up in order to post.