Schedule Job at exactly the same point in time?

[efrijters]

Hello,

I have a production db on node 1 and a db with Materialized View-replication on node 2. The MV's hold the production data for reporting.

I refresh the MV's via a scheduled job. The job runs for about 30 minutes.

I start the refresh at - let's say - 01:00 hours. When I set it up, I used the following interval formula:

trunc(sysdate)+1/24*(to_number(to_char(sysdate,'HH24'))+1)
(schedule the job every hour, at the whole hour)

I inserted it into the interval-part of the job-page in OEM.

After the refresh runs and completes, Oracle reschedules the job for the next time. But the next scheduled time was 02:30 hours! So the duration of the job is added to the next starting point in time.

When I look to check the formula in OEM, the formula is gone. Oracle translated it to a readable date-format. So, the formula works only once.

Is there a way so Oracle won't translate my formula? My thought is, that if the formula remains, the job gets scheduled at the right moment.

It's important to the customer that the job runs at an exact moment in time, so they can schedule their jobs after mine (a reporting tool at a third node)

(I thought to schedule an extra job that reschedules my first job, but that's only making my problem worse (because this extra job would have the same problem)).

Hope you guys can help.

Erik

[slimdave]

This is a well known feature of DBMS_JOB -- that the next date is clculated at the end of the job execution, not the beginning.

The fix is easy for you though. Specify next as ...

trunc(sysdate,'HH')+1/24

[efrijters]

Thanks for your fast reply slimdave, but the formula isn't the problem (I think).

Okay, your formula is smaller (thus better!), but this doesn't solve my 'rescheduling problem'? Or does it?

I will try your formula and post my result.

[slimdave]

you are submitting interval as a string,aren't you?

DBMS_JOB.SUBMIT( ... , interval => 'trunc(sysdate,''HH'') + 1/24', ...);

[efrijters]

I scheduled it via the screens in OEM, so not via script (I was in a hurry - as always)

So I don't think this is a problem. Oracle accepted the formaula. But after the job was executed, it seems that Oracle read it, translated it to a new point in time and then forget the original formula.

But I'll do some testing now.

Thanks

(please don't stop sending me your comments: the more we read, the more we learn and "knowledge is power")

[pando]

Originally posted by efrijters


(please don't stop sending me your comments: the more we read, the more we learn and "knowledge is power")

thats right, read some manual will do yourself a big favour

[slimdave]

pando needs a hug. Anyone else in Madrid available?

[pando]

I think so! Mr aaroyob!

[efrijters]

If someone sends me an airline ticket, I'll volunteer...
(to go to Madrid, not the hugging part)

Still testing, but the test server doesn't cooperate. Hope to post my result soon.

[slimdave]

A free trip to Madrid, and unwilling to give a hug. Ingrate!