Detail about how schedule DBMS_JOBS

Hi

       If you want to schedule different dbms_job here are some example to solve your problem regarding scheduling dbms jobs.

        

DBMS_JOB

Path: {ORACLE_HOME}/rdbms/admin/dbmsjob.sql

===========================================================

Important Views:

job$      dba_jobs      all_jobs      user_jobs

dba_jobs_running        all_jobs_running  

user_jobs_running

=======================================================================

Error which are commonly generated while operating jobs

Error Code Reason:      ORA-00001 Unique constraint (SYS.I_JOB_JOB) violated

                                    ORA-23420 Interval must evaluate to a time in the future

=========================================================================

Interval Definations:     Execute daily 'SYSDATE + 1'
              Execute once per week 'SYSDATE + 7'
              Execute hourly 'SYSDATE + 1/24'
              Execute every 10 min. 'SYSDATE + 10/1440'
              Execute every 30 sec. 'SYSDATE + 30/86400'
              Do not re-execute NULL

===============================================================

Why any job Failed:

Oracle has failed to successfully execute the job after 16 attempts.

OR

You have marked the job as broken, using the procedure DBMS_JOB.BROKEN

Once a job has been marked as broken, Oracle will not attempt to execute the job until it is either marked not broken, or forced to be execute by calling the DBMS_JOB.RUN.

=======================================================================

If you want to make any job forcefully broken then:

Use : exec dbms_job.broken(42, TRUE)

The following example marks job 14144 as not broken and sets its next execution date to - - the following Monday:

exec dbms_job.broken(14144, FALSE, NEXT_DAY(SYSDATE, 'MONDAY'));

===========================================================

Change any attribute of JOB:

exec dbms_job.change(14144, NULL, NULL, 'SYSDATE + 3');

=======================================================================

Assign a specific instance to run a job:

To do so, First get the instance information by:

SELECT instance_number FROM gv$instance;

then run the job :

exec dbms_job.instance(42, 1);

===========================================================

Reset the job Interval:

exec dbms_job.interval(179, 'TRUNC(SYSDATE) + 24/24');

Submit a Job:

exec dbms_job.isubmit(4242, 'MYPROC', SYSDATE);

Reset next Execution:

exec dbms_job.next_date(134, SYSDATE + 1/24);

To remove a job from job queue: First find the job to remove thorugh:

SELECT job FROM user_job;

then

exec dbms_job.remove(23);

To run a job immediatly:

exec dbms_job.run(job_no);

Various method to schedule a job to run with different timing:

-- To run everynight at midnight starting tonight
exec dbms_job.submit(:v_JobNo, 'proc1;', TRUNC(SYSDATE)+1, 'TRUNC(SYSDATE)+1');

-- To run every hour, on the hour, starting at the top of the hour
exec dbms_job.submit(:v_JobNo, 'proc2;', TRUNC(SYSDATE+(1/24), 'HH'),
'TRUNC(SYSDATE+(1/24),''HH'')');

-- To run every hour, starting now
exec dbms_job.submit(:v_JobNo, 'proc3;', INTERVAL => 'SYSDATE+(1/24)');

-- To run every ten minutes at 0,10,20,etc. minutes past the hour,
-- starting at the top of the hour
exec dbms_job.submit(:v_JobNo, 'proc4;', TRUNC(SYSDATE+(1/24), 'HH'),
'TRUNC(SYSDATE+(10/24/60),''MI'')');

-- To run every 2 min., on the minute, starting at the top of the
-- minute
exec dbms_job.submit(:v_JobNo, 'proc5;', TRUNC(SYSDATE+(1/24/60), 'MI'),
'TRUNC(SYSDATE+(2/24/60),''MI'')');

-- To run every two minutes, starting now
exec dbms_job.submit(:v_JobNo, 'proc6;', INTERVAL => 'SYSDATE+(2/24/60)');

-- To run every half hour, starting at the top of the hour
exec dbms_job.submit(:v_JobNo, 'proc7;', TRUNC(SYSDATE+(1/24), 'HH'),
'TRUNC(SYSDATE+(30/24/60),''MI'')');

execute dbms_lock.sleep(300);

===========================================================

Online Acitivity Performed on LGSOUTH DATABASE 10.100.201.12

select distinct job,log_user from dba_jobs where log_user='CSN';

job=62,122,149,185

BEGIN   DBMS_JOB.ISUBMIT(JOB=>62,WHAT=>'SP_CALLSUM_REPORT;',

NEXT_DATE=>TO_DATE('2009-01-25:06:00:00','YYYY-MM-DD:HH24:MI:SS'),

INTERVAL=>'TRUNC(SYSDATE)+1+(6/24)',NO_PARSE=>TRUE); END;

PL/SQL procedure successfully completed.

EXEC DBMS_JOB.NEXT_DATE(723,NEXT_DATE=>TO_DATE('2009-04-08:10:00:00','YYYY-MM-DD:HH24:MI:SS'));

BEGIN   DBMS_JOB.ISUBMIT(JOB=>122,WHAT=>'SP_REPAIRLIST_REPORT_CENTRAL;',

NEXT_DATE=>TO_DATE('4000-01-01:00:00:00','YYYY-MM-DD:HH24:MI:SS'),INTERVAL=>'TRUNC(SYSDATE)+1+(1/24)',

NO_PARSE=>TRUE); END;

PL/SQL procedure successfully completed.

BEGIN  DBMS_JOB.ISUBMIT(JOB=>149,WHAT=>'SP_PDP_LIVE_REPORT;',

NEXT_DATE=>TO_DATE('4000-01-01:00:00:00','YYYY-MM-DD:HH24:MI:SS'),

INTERVAL=>'FNC_DYNAMIC_SCHEDULE(8,18,90)',NO_PARSE=>TRUE); END;

PL/SQL procedure successfully completed.

BEGIN DBMS_JOB.ISUBMIT(JOB=>185,WHAT=>'SYNC_PO_RECEIPT;',

NEXT_DATE=>TO_DATE('2009-01-25:08:15:00','YYYY-MM-DD:HH24:MI:SS'),

INTERVAL=>'FNC_DYNAMIC_SCHEDULE (8.25, 15, 465)',NO_PARSE=>TRUE); END;

PL/SQL procedure successfully completed.