A user with a Create any job privilege can create a job in any schema except the sys schema. Attaching a job with a particular class requires the execute privileges for that class.
Oracle database 10g offers a collection a rich set of functionality for complex scheduling tasks.
The component that causes something to be executed at a specified time is called a Job.
We can use DBMS_SCHEDULER.CREATE_JOB procedure of the Dbms_scheduler package to create a job
which is in disabled state by default.
A job becomes running and scheduled when it is explicitly enabled.
Example: - How to create a job, For this you provide a name in the form [schema.]name and also need privileges from the sysdba.
1. Connect to your database as user SYSTEM using SQL*Plus.
2. Create a table to store times, and set your date format to show the date and
time.
SQL> create table times (c1 date);
SQL> alter session set nls_date_format='dd-mm-yy hh24:mi:ss';
3. Create a job to insert the current time into the table every minute.
SQL> begin
dbms_scheduler.create_job(
job_name=>'savedate',
job_type=>'plsql_block',
job_action=>'insert into times values(sysdate);',
start_date=>sysdate,
repeat_interval=>'freq=minutely;interval=1',
enabled=>true,
auto_drop=>false);
end;
/
PL/SQL procedure successfully completed.
4. Query the job table to see that the job is scheduled.
SQL> select job_name,enabled,to_char(next_run_date,'dd-mm-yy hh24:mi:ss'),run_count
from user_scheduler_jobs;
JOB_NAME ENABL TO_CHAR(NEXT_RUN_ RUN_COUNT
------------------------- ----- ----------------- ----------
SAVEDATE TRUE 15-01-05 14:58:03 2
5. Query the times table to demonstrate that the inserts are occurring.
SQL> select * from times;
6. Disable the job.
SQL> exec dbms_scheduler.disable('savedate');
7. Re-run the queries from Steps 4 and 5 to confirm that the job is disabled, and
that no more inserts are occurring.
8. Drop the job:
SQL> exec dbms_scheduler.drop_job('savedate');