Re: best practices re: scheduled jobs

Ed Stevens <nospam_at_noway.nohow> wrote in news:vl2gbvga8fmmt1r4i01m3rp1lolej418je_at_4ax.com:

> Trying to stretch my wings a bit here, playing around with the job
> scheudling system and with compiled procedures. Looking for some
> 'best practices' advice.
>
> I've built some rather simple procedures to take care of some dba
> chores like refreshing stats for the optimizer and collecting stats
> into a 'stats_history' table for trend analysis, and I have these
> running under the dbms_jobs system on a test database. In getting to
> this point, I worked thru the problem of procedures not having user
> privs that are inherited via a role. I'm also finding it a bit
> difficult to keep up with scheduled jobs running under various
> user-ids. As a result, I'm wondering if I wouldn't be better off just
> setting up the 'procedures' as sql files and feed them into SQL-Plus
> from a cron job. What problems would I be trading for if I went this
> direction? Are there any generally accepted practices or is it really
> pretty much all over the map?
>

That's what I usually do when cron is available. I try to avoid dbms_job unless it's the only scheduler available.

The only thing that comes to mind that you'd be giving up is a complete code portability. If done entirely with stored procedures and dbms_job, your code should run on any oracle databases regardless of platform. Shell scripts coded to run from cron however, will not work on NT. Neither will NT batch files run on Unix. If you keep the logic in pl/sql and only use scripts or batch files to execute them, the conversion between platforms shouldn't be too difficult. Received on Tue May 06 2003 - 15:31:34 CDT