dbms_job submit

[sonaliak]

I want to run a stored procedure last day midnight of every month. Will this work ?

VARIABLE v_JobNum Number
Begin
DBMS_JOB.SUBMIT(:v_JobNum , 'PR_TBalance_Add;', sysdate, 'last_day(sysdate) +1');
commit;
end;
/

Do I have to have any permissions or have to run any scripts to be able to submit this job ?
I will have to run this logging in as that user and not as system/manager right ?

What is dbms_job.run ?

thanks

[Edited by sonaliak on 07-20-2001 at 09:15 AM]

[pipo]

you call PR_TBalance_Add, check that the user who submits the job can exec this procedure.
to submit it every midnight of the last day of the month (that is to say at 00:00 first day of next month), you need :
1) to set the first date to midnight of the last day of the month at the moment you submit your job
2) have the correct interval (you MUST use trunc) :

VARIABLE v_JobNum Number
Begin
DBMS_JOB.SUBMIT(:v_JobNum , 'PR_TBalance_Add;', trunc(last_day(sysdate))+1, 'trunc(last_day(sysdate))+2');
commit;
end;
/

you won't have to log to run the job since it will run alone, and it will be submitted using the user and environment of its creator.

[appajip]

Hi,

sorry for raising different topic.

The problem is....

In our production database one package has been deleted.

Here all it IT guys knows the Adminstrative password.

can we find out the machine name of the user who has done this,the problem here is audit_trail is not enabled in init.ora

pl help me


Thanks

[pipo]

please open a new thread

[rlowe]

Pipo -

You say that one MUST use trunc to get the correct interval. I am having issues with the scheduling, the interval being sysdate+1. The job starts later and later each day. Will adding a trunc help my situation?

Thanks,

R

[pipo]

sure
a job evaluates next_date using interval once he is launched. if your processes wake up every 5 minutes, your job will be late of 0 to 4:59 minuts, so if you specify interval without trunc, the gap will grow evary day.
for exemple you launch a job at noon, and it is supposed to run every hour.
1st execution : between 12:00 and 12:05 (12:03 for example)
2nd execution : between 13:03 and 13:08 (13:06 fe)
3rd : between 14:06 and 14:11

and so on, so if you trunc, you are sure to define a precise time, and you do not have this gap between the expected date and the real one.

[sonaliak]

VARIABLE v_JobNum Number
Begin
DBMS_JOB.SUBMIT(:v_JobNum , 'PR_TBalance_Add;', trunc(last_day(sysdate))+1, 'trunc(last_day(sysdate))+2');
commit;
end;
/

Why did you do 'trunc(last_day(sysdate))+2' , + 2 here ?
Shouldn't this be + 1 as I want the midnight of the last day of the month.

Also do I have to use dbms_job.run to execute this job, or submit is all I have to do.

thanks

[pipo]

you're right, use +1 instead of +2, I made a typo
+1 is because :
if you're May 5th 10:41 AM, trunc(sysdate) will give
May 5th 00:00, and trunc(last_day(sysdate)) will give
May 31th 00:00, that is to say one day too early compared to what is wanted

+1 rocks, forget +2

[sonaliak]

Thanks Pipo..
But it is not working ..I can't figure out if I am missing something.
For testing purpose I did following:

VARIABLE v_JobNum Number
Begin
DBMS_JOB.SUBMIT(:v_JobNum , 'PR_TBalance_Add;', trunc(sysdate), 'trunc(sysdate +(1/24))');
commit;
end;
/

I ran this job sumit at 9.22 am and was expecting it to execute this procedure at 9.22 am and 10.22 am( after one hour).
But it did not do anything till now.

Do I have to run dbms_job.run every hour ?
What is wrong in this ?

Thanks

[jm]

col job for 999
col log_user for a10
col interval for a35
col tt for 9999
select job,LOG_USER,LAST_DATE,LAST_SEC,next_sec,interval,broken,total_time,
failures from dba_jobs;

to see your job reschedule time is correct or not