Jobs (dbms_job) in Oracle

Create a job:

Method 1: (job id generated by system)

SET SERVEROUTPUT ON;
DECLARE
   X   NUMBER;
BEGIN
   SYS.DBMS_JOB.SUBMIT (job         => X,
                        what        => 'BEGIN  EMP_DATA_REFRESH; END;',
                        next_date   => (TRUNC (SYSDATE) + 1) + (22 / 24),
                        interval    => '(TRUNC(SYSDATE)+1)+(22/24)', --10pm daily
                        no_parse    => FALSE);

   SYS.DBMS_OUTPUT.PUT_LINE ('Job Number is: ' || TO_CHAR (x));

   SYS.DBMS_JOB.BROKEN (job => X, broken => FALSE);
   COMMIT;
END;
/

Method 2: (job id should be passed)

DECLARE
   X   NUMBER := 888;
BEGIN
   SYS.DBMS_JOB.isubmit (job         => X,
                         what        => 'BEGIN  EMP_DATA_REFRESH; END;',
                         next_date   => (TRUNC (SYSDATE) + 1) + (22 / 24),
                         interval    => '(TRUNC(SYSDATE)+1)+(22/24)', --10pm daily
                         no_parse    => FALSE);
   COMMIT;
END;
/

Submit a job to run once:

BEGIN
   DBMS_JOB.isubmit (job         => 12345,
                     what        => ' BEGIN EMP_DATA_REFRESH; END;',
                     next_date   => SYSDATE + 1 / 1400);
   --SYSDATE + 1 / 1400 for 1 min
   COMMIT;
END;
/

Alter a existing job:

BEGIN
   DBMS_JOB.CHANGE (job         => 789,
                    what        => 'BEGIN OUDATA.EMP_DATA_REFRESH; END;',
                    next_date   => SYSDATE + 15 / 1400,
                    interval    => 'SYSDATE + 15 / 1400');
   COMMIT;
END;
/

BEGIN
   DBMS_JOB.WHAT (job => 789, what => 'BEGIN EMP_DATA_REFRESH;   END;');
   COMMIT;
END;
/

Broke the job:

BEGIN
   DBMS_JOB.BROKEN (789, TRUE);
   COMMIT;
END;

(or)

 EXEC DBMS_JOB.BROKEN (789, TRUE);
 COMMIT;


View the job details:

DBA_JOBS --> Lists all jobs in the database.
ALL_JOBS --> Lists all jobs accessible to the connected user.
USER_JOBS  --> Lists all jobs owned by the connected user.