I recently wanted to schedule jobs on my Oracle 9i (9.2.0.8) database so i refreshed my memory on how to do it based on the Oracle's job engine.
I will note down a quick reference post for you to check out:
View all running jobs:
select * from user_jobs
Submit a job (this example analyzes a table every 24 hrs:
VARIABLE jobno NUMBER
BEGIN
DBMS_JOB.SUBMIT(:jobno,
'DBMS_DDL.ANALYZE_OBJECT(''TABLE'',
''HR'', ''EMPLOYEES'',
''ESTIMATE'', NULL, 50);',
SYSDATE, 'SYSDATE + 1');
COMMIT;
END;
/
jobno is returned by the system.
Assuming 14144 for this example.
Remove a job from the job queue:
BEGIN
DBMS_JOB.REMOVE(14144);
END;
/
Change a job:
BEGIN
DBMS_JOB.CHANGE(14144, NULL, NULL, 'SYSDATE + 3');
END;
/
Alter the definition of a job:
BEGIN
DBMS_JOB.WHAT(14144,
'DBMS_DDL.ANALYZE_OBJECT(''TABLE'',
''HR'', ''DEPARTMENTS'',
''ESTIMATE'', NULL, 50);');
END;
/
Alter the next execution day of a job:
BEGIN
DBMS_JOB.NEXT_DATE(14144, SYSDATE + 4);
END;
/
Alter execution interval:
BEGIN
DBMS_JOB.INTERVAL(14144, 'NULL');
END;
/
Brake a scheduled job:
BEGIN
DBMS_JOB.BROKEN(14144, TRUE);
END;
/
Continue to run a previously broken job:
BEGIN
DBMS_JOB.BROKEN(14144, FALSE, NEXT_DAY(SYSDATE, 'MONDAY'));
END;
/
Forcing a job to execute:
BEGIN
DBMS_JOB.RUN(14144);
END;
/
Working with intervals:
'SYSDATE + 7' Exactly seven days from the last execution
'SYSDATE + 1/48' Every half hour
'NEXT_DAY(TRUNC(SYSDATE), ''MONDAY'') + 15/24' Every Monday at 3PM
'NEXT_DAY(ADD_MONTHS(TRUNC(SYSDATE, ''Q''), 3), ''THURSDAY'')' First Thursday of each quarter
Reference:
http://download.oracle.com/docs/cd/B10501_01/server.920/a96521/jobq.htmLabels: broken, DBMS_JOB, next_day, remove, submit, sysdate |
Author's note : Thank you all for your supporting letters. You have been a motivation to this web spot's posting. I wish you all the
very best for you and your families for the new year.
