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.
Hi John,
I could not find your e-mail so I am contacting you via comments, hope you don’t mind :-). I came across your blog when I was searching for awesome tech experts online and I love your work on Seeking Oracle out of the matrix! You have excellent knowledge and is very informed thus I want to personally invite you to be one of the top Tech & Gadget Advisors on ShopSquad http://www.shopsquad.com/landing/bloggers/?vsrc=tech119, a free shopping advice website where shoppers and your readers can ask Advisors (i.e. you!) for advice.
If you recommend something that is purchased, you get a percentage of the sale. You're already sharing your expertise through your blog, so why not earn more money for it? You can also earn $5 for each of your readers who signs up.
Read our great press http://www.shopsquad.com/about/?vsrc=tech119 coverage or learn how ShopSquad works http://www.shopsquad.com/howitworks/?vsrc=tech119. When you're ready, sign up free at www.shopsquad.com. http://www.shopsquad.com/?vsrc=tech119
Feel free to ask me any questions: marshall@shopsquad.com.
Thanks!