Solved

dbms_job question

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

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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Join & Write a Comment

How to Unravel a Tricky Query Introduction If you browse through the Oracle zones or any of the other database-related zones you'll come across some complicated solutions and sometimes you'll just have to wonder how anyone came up with them.  …
From implementing a password expiration date, to datatype conversions and file export options, these are some useful settings I've found in Jasper Server.
Via a live example, show how to take different types of Oracle backups using RMAN.
This video shows how to Export data from an Oracle database using the Datapump Export Utility.  The corresponding Datapump Import utility is also discussed and demonstrated.

757 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

22 Experts available now in Live!

Get 1:1 Help Now