Solved

dbms_job question

Posted on 2006-11-20
2
1,468 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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

Have you ever had to make fundamental changes to a table in Oracle, but haven't been able to get any downtime?  I'm talking things like: * Dropping columns * Shrinking allocated space * Removing chained blocks and restoring the PCTFREE * Re-or…
Background In several of the companies I have worked for, I noticed that corporate reporting is off loaded from the production database and done mainly on a clone database which needs to be kept up to date daily by various means, be it a logical…
This video shows information on the Oracle Data Dictionary, starting with the Oracle documentation, explaining the different types of Data Dictionary views available by group and permissions as well as giving examples on how to retrieve data from th…
Video by: Steve
Using examples as well as descriptions, step through each of the common simple join types, explaining differences in syntax, differences in expected outputs and showing how the queries run along with the actual outputs based upon a simple set of dem…

914 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

Need Help in Real-Time?

Connect with top rated Experts

14 Experts available now in Live!

Get 1:1 Help Now