Manage a Job

Manage Oracle Jobs:

Create a job using DBMS_JOB Package:

DECLARE
X NUMBER;
BEGIN
SYS.DBMS_JOB.SUBMIT
( job => X
,what => 'DBMS_MVIEW.REFRESH(''TEST.DW_CUSTOMERLIFECYCLE'', ATOMIC_REFRESH => FALSE);'
,next_date => to_date('02/06/2009 02:00:00','dd/mm/yyyy hh24:mi:ss')
,interval => 'TRUNC(SYSDATE+1) + 2/24 '
,no_parse => TRUE
);
SYS.DBMS_OUTPUT.PUT_LINE('Job Number is: ' to_char(x));
END;
/

commit;

Note: Assume in the above process a job is created whose job# is 745.

Alter the NEXT_DATE of a Job using DBMS_JOB package:
SQL>execute DBMS_JOB.NEXT_DATE (
job IN BINARY_INTEGER,
next_date IN DATE);
SQL>commit;

Example:
SQL>execute DBMS_JOB.NEXT_DATE(745,to_date('03/06/2009 02:00:00','dd/mm/yyyy hh24:mi:ss'));
SQL>Commit;

Alter the INTERVAL of a Job using DBMS_JOB package:
SQL>execute DBMS_JOB.INTERVAL (
job IN BINARY_INTEGER,
interval IN VARCHAR2);

SQL>commit;

Example:
SQL> execute DBMS_JOB.INTERVAL(745, 'TRUNC(SYSDATE+1) + 3/24');
SQL> Commit;

Alter the WHAT of a Job using DBMS_JOB package:
SQL>execute DBMS_JOB.WHAT (
job IN BINARY_INTEGER,
what IN VARCHAR2);
SQL>commit;

Example:
SQL>execute DBMS_JOB.WHAT(745, ‘DBMS_MVIEW.REFRESH(''XXBLIS.DW_CUSTOMERLIFECYCLE'');');
SQL>Commit;

Stop Jobs:
It is not always easy to stop a running job. There are different ways to stop a job:

1: You can change the job_queue_process parameter to 0. This will stop all the jobs running in the database.
2: You can find the server process of the job and kill the server process which will stop the job.
Find the SID from dba_jobs_running and get the server process for the same SID. Kill the server process bi kill -9 and you are all set.

Runing Jobs:
How do you know which job is running on the server. To find the list of jobs running on the server, execute the below:

SQl> select job.log_user, schema_user, what, failures, broken from dba_jobs where this_date is not null;

Failures or Broken Jobs:
How do you know what are all the jobs failed or in broken stage. Execute the below to find the same.

SQL> select job, schema_user, failures, broken, what from dba_jobs where failures>0 or broken ='Y';

Fix Broken Jobs:
When the job is broken how to fix the broken job. To do that run the below:

SQL> execute DBMS_JOB.BROKEN(job,FALSE);
SQL> commit;