Seeking Oracle out of the matrix

Google
 
Web aboutoracle.blogspot.com
Oracle New Articles
Oracle Critical Patches
Oracle jDeveloper News
Oracle Blogs of interest


This blog records my personal experience.
Latest news from the front
               

Thoughts about oracle 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.
Interested to know about a next post? You can suscribe to my RSS feed
HOW TO:

DBMS_JOB Cheat sheet
Monday, October 10, 2011
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.htm

Labels: , , , , ,


posted by Admin @ 7:39 AM  
add to del.icio.us Digg it! Furl this! add to reddit! add to dzone!
7 Comments:
  • At 3:32 AM, Blogger Marshall said…

    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!

     
  • At 1:30 PM, Blogger Aleena Smith said…

    Hello

    Great work has done by you and I really appreciate your this work. There are many people using this information.

    Aleena Smith
    error 1310

     
  • At 9:22 AM, Blogger Jimmy Alex said…

    Hello

    Great work has done by you and most of people like this information. Because this information is useful for them.

    Jimmy Alex
    Registry Booster

     
  • At 1:48 PM, Blogger geetha said…

    wonderful information, I had come to know about your blog from my friend nandu , hyderabad,i have read atleast 7 posts of yours by now, and let me tell you, your website gives the best and the most interesting information. This is just the kind of information that i had been looking for, i'm already your rss reader now and i would regularly watch out for the new posts, once again hats off to you! Thanks a ton once again, Regards, Oracle Portal online trainingamong the Oracle Portal in Hyderabad. Classroom Training in Hyderabad India

     
  • At 1:25 PM, Blogger vignesh m said…

    Thanks for that..sharing for effective information..hadoop training chennai

     
  • At 10:13 AM, Blogger Kathiresan Muthu said…

    Thanks for sharing the useful information on the blog and is really understanding.

    Besant Technologies Reviews

     
  • At 4:05 AM, Blogger 文章 said…

Post a Comment
<< Home

About Me

Name: John Galanopoulos
Home: The NeverLands
About Me: A source code wonderer since the early 80s with my first ZX81 by Sinclair, home computer.
See my complete profile
Previous Post
Archives
Links
ΣΚΛΗΡΥΝΣΗ ΚΑΤΑ ΠΛΑΚΑΣ - ΕΓΚΕΦΑΛΟΣ - ΕΓΚΕΦΑΛΟΓΡΑΦΗΜΑ - ΑΝΟΙΑ - ΝΕΥΡΟΛΟΓΟΣ - ΨΥΧΙΑΤΡΟΣ - ΛΟΙΜΩΔΗΣ ΜΟΝΟΠΥΡΗΝΩΣΗ - ΠΑΡΚΙΝΣΟΝ - ΑΓΧΟΣ - ΚΑΤΑΘΛΙΨΗ - ALZHEIMER - EPSTEIN BARR Eurolife
Email notification

Enter your email address and get notified whenever there is a new post:

Delivered by FeedBurner

This website abides by a strict policy : no spam, just posts; and that's a promise.

Powered by

Free Blogger Templates

BLOGGER


Register for a skinnyscore at www.blogskinny.com and increase traffic
Software Blogs -  Blog Catalog Blog Directory

© 2005 Seeking Oracle out of the matrix Template by Isnaini Dot Com