Solved

dbms_job question

Posted on 2006-11-20
2
1,471 Views
Last Modified: 2012-06-27
This job is scheduled to run every Sunday at 7 a.m.


DECLARE
    JobNo dba_jobs.job%TYPE;
BEGIN
  DBMS_JOB.SUBMIT(JobNo,'DBMS_STATS.GATHER_SCHEMA_STATS(ownname=>''BB_BB60'',
   cascade=>TRUE, method_opt=>''FOR ALL INDEXED COLUMNS SIZE AUTO'');',
   trunc(sysdate) + 7/24 + 8 - to_char(sysdate,'D'), 'trunc(sysdate) + 7 + 7/24');
  COMMIT;
END;

What is the 'D' in (sysdate,'D') ?
What specifies 7 a.m ?
Is there a link to docs ?
what is the +8 ?


0
Comment
Question by:xoxomos
2 Comments
 
LVL 35

Accepted Solution

by:
Mark Geerlings earned 250 total points
ID: 17982287
What is the 'D' in (sysdate,'D') ?
This returns the number for the day of the week (1-7) where 1 = Sunday, 2=Monday, etc.

What specifies 7 a.m ?
trunc(sysdate) + 7/24  Explanation: "trunc(sysdate)" = midnight, and "7/24" means that fraction of a day, or 7 hours after midnight

Is there a link to docs ?
Not specifically that I know of.  This is a combination of various "to_date" formats and Oracle's date arithmetic

what is the +8
A number (like "8") added to a date value in Oracle means "add that number of days"

I would use this interval instead to schedule a job for every Sunday at 7:00am:
trunc(next_day(sysdate, 'SUNDAY'))+7/24
0
 

Author Comment

by:xoxomos
ID: 17984098
Thanks
0

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Note: this article covers simple compression. Oracle introduced in version 11g release 2 a new feature called Advanced Compression which is not covered here. General principle of Oracle compression Oracle compression is a way of reducing the d…
How to Create User-Defined Aggregates in Oracle Before we begin creating these things, what are user-defined aggregates?  They are a feature introduced in Oracle 9i that allows a developer to create his or her own functions like "SUM", "AVG", and…
This video shows how to copy a database user from one database to another user DBMS_METADATA.  It also shows how to copy a user's permissions and discusses password hash differences between Oracle 10g and 11g.
This video shows how to configure and send email from and Oracle database using both UTL_SMTP and UTL_MAIL, as well as comparing UTL_SMTP to a manual SMTP conversation with a mail server.

679 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question