Re: Scheduling job at database startup. How?

From: <nasof_at_hotmail.com>
Date: Sat, 31 Oct 1998 14:21:22 GMT
Message-ID: <71f6d2$45c$1@nnrp1.dejanews.com>

If you are on NT, you could try modifying the databases' startup script, usually located in x:\orant\database (replace x with correct drive letter.)

It will be named strs<sid-name>.cmd (ex: strtorcl.cmd) and contains an NT level command for starting the database. On the line after the startup command, put a server manager command to submit a stored procedure to the database's job queue (providing you have setup the database to run jobs). Below is just an example and requires some modificiation:

FILE: strtorcl.cmd.. --------------------- e:\orant\bin\oradim80.exe -startup

-sid ORCL -usrpwd oracle -starttype srvc,inst -pfile e:\orant\database\initorcl.ora set ORACLE_SID=ORCL set ORACLE_HOME=x:\orant x:\orant\bin\svrmgr30.exe "connect system/manager" <x:\orant\database\mycmd.sql

>x:\orant\database\mycmd.out

Then in a file called mycmd.sql, put:

---

#
# Start my batch job...
#

spool mycmd.log
set echo on
declare

    jobno number;
begin
execute dbms_job.submit(jobno, 'my_stored_procedure', sysdate+5/24/60, NULL ); end;
/
spool off
exit

The sysdate+5/24/60 will allow for 5 minutes before the proc starts and the NULL parameter will remove the job once it is run one time.

I believe this should give you the result you want.

-Frank

In article <01be043b$fe86c7e0$0300a8c0_at_WORKSTATION>,   "Jonathan Lewis" <jonathan_at_jlcomp.demon.co.uk> wrote:
>
> I've only just seen this thread, so someone may have
> come up with this before.
>
> How about dispatching dbms_job with a job that runs
> once per hour (say). The procedure called should
> check how long the database has been up (v$instance)
> and run if the time is strictly less than an hour.
>
> The overhead is the cost of dbms_job cycling, and the
> maximum delay after startup is one hour - however,
> if the database is shutdown for a while, I think dbms_job
> will cut in almost immediately after the next startup.
>
> NB This is a just a thought, I haven't tried it myself
>
> Jonathan Lewis.
>
>

-----------== Posted via Deja News, The Discussion Network ==---------- http://www.dejanews.com/ Search, Read, Discuss, or Start Your Own Received on Sat Oct 31 1998 - 08:21:22 CST