Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
Solved

dbms_job question

Posted on 2006-11-20
Medium Priority
1,478 Views
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
Question by:xoxomos
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

• Help others & share knowledge
• Earn cash & points

LVL 35

Accepted Solution

Mark Geerlings earned 1000 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

ID: 17984098
Thanks
0

Featured Post

Question has a verified solution.

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

Introduction A previously published article on Experts Exchange ("Joins in Oracle", http://www.experts-exchange.com/Database/Oracle/A_8249-Joins-in-Oracle.html) makes a statement about "Oracle proprietary" joins and mixes the join syntax with gen…