- Community Home
- >
- Servers and Operating Systems
- >
- Operating Systems
- >
- Operating System - HP-UX
- >
- dbms_jobs
Categories
Company
Local Language
Forums
Discussions
Forums
- Data Protection and Retention
- Entry Storage Systems
- Legacy
- Midrange and Enterprise Storage
- Storage Networking
- HPE Nimble Storage
Discussions
Discussions
Discussions
Discussions
Forums
Forums
Discussions
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
- BladeSystem Infrastructure and Application Solutions
- Appliance Servers
- Alpha Servers
- BackOffice Products
- Internet Products
- HPE 9000 and HPE e3000 Servers
- Networking
- Netservers
- Secure OS Software for Linux
- Server Management (Insight Manager 7)
- Windows Server 2003
- Operating System - Tru64 Unix
- ProLiant Deployment and Provisioning
- Linux-Based Community / Regional
- Microsoft System Center Integration
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Community
Resources
Forums
Blogs
- Subscribe to RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Printer Friendly Page
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
тАО08-05-2003 11:24 AM
тАО08-05-2003 11:24 AM
dbms_jobs
DBMS_JOB.SUBMIT (:v_jobnum, 'my_procedure;', trunc(sysdate) + 1, 'sysdate + 1/288');
Is this the right way?
and also what is :v_jobnum supposed to be?.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
тАО08-05-2003 11:52 AM
тАО08-05-2003 11:52 AM
Re: dbms_jobs
Script your job and then add it to the cron schedule.
5,10,15,20,25,30,25,40,45,50,55 * * * * jobname 2>&1
contents of jobname
su - oracle -c "script running your procedure"
SEP
Owner of ISN Corporation
http://isnamerica.com
http://hpuxconsulting.com
Sponsor: http://hpux.ws
Twitter: http://twitter.com/hpuxlinux
Founder http://newdatacloud.com
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
тАО08-05-2003 12:03 PM
тАО08-05-2003 12:03 PM
Re: dbms_jobs
crontab -e oracle
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
тАО08-05-2003 01:15 PM
тАО08-05-2003 01:15 PM
Re: dbms_jobs
The following should work for what you want to do. Please note that because Oracle only schedules at the end of the job, the job will eventually drift. This is the reason for the extra code.
declare
jobout binary_integer;
begin
dbms_job.submit(jobout,'my_procedure;',trunc(SYSDATE+(5/1440),'MI')-(mod(to_char(sysdate,'MI'),5)/1440),'trunc(SYSDATE+(5/1440),''MI'')-(mod(to_char(sysdate,''MI''),5)/1440)');
end;
/
Let me know if this works for you, or if you have any questions.
Thanks,
Brian
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
тАО08-05-2003 03:51 PM
тАО08-05-2003 03:51 PM
Re: dbms_jobs
DBMS_JOB.SUBMIT(job OUT BINARY_INTEGER,
what IN VARCHAR2,
next_date IN DATE,
interval IN VARCHAR2,
no_parse IN BOOLEAN)
Job: When a job is created a job number is
assigned to it. This job number is
unique. You must use the job number
whenever you alter or remove the job.
As long as the job exists, the number
will remain the same.
What: The PL/SQL code you wish to execute. It is
usually a call to a stored procedure, which
can have any number of parameters. Use two
single quotes around strings and a
semicolon at the end of the job definition.
Next_date: The date when the job will execute. Default
is SYSDATE.
Interval: A function that calculates the next time the
job is to execute. Default is NULL.
No_parse: If TRUE, Oracle parses the code the first
time the job is executed. If FALSE,
the job is parsed when submitted. Default is
FALSE.
The following is an example of a DBMS_JOB.SUBMIT:
SQL> EXECUTE dbms_job.submit(:x,'procedure_name;',sysdate,'sysdate+1')
SQL> COMMIT;
When a job is submitted to the job queue, it runs automatically depending
on the NEXT_DATE and INTERVAL parameters. Jobs will not run automatically
when submitted on newly created databases that have not been bounced.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
тАО08-05-2003 10:22 PM
тАО08-05-2003 10:22 PM
Re: dbms_jobs
First of all, in order for DBMS_JOB to function correctly, you need to do a little setup in the database.
There are two init.ora parameters that you need to set:
JOB_QUEUE_PROCESSES
JOB_QUEUE_PROCESSES specifies the number of SNPn background processes per instance, where n is 0 to 9 followed by A to Z. Job queue processes are used to process requests created by DBMS_JOB. Some job queue requests are created automatically; an example is refresh support for table snapshots. If you wish to have your table snapshots updated automatically, you must set JOB_QUEUE_PROCESSES to a value of one or higher.
JOB_QUEUE_INTERVAL
JOB_QUEUE_INTERVAL specifies the interval between wake-ups for the SNPn background processes of the instance. Jobs will only be run every N seconds (where N is the setting for this parameter). So, if you set job_queue_interval = 60 but you have asked that a job be run every 30 seconds -- it will only run once a minute as the queues are only inspected for jobs every 60 seconds. A setting of 1-5 minutes is usually sufficient for this parameter.
To get the 5 minutes interval you would do a:
trunc(sysdate)+(trunc(to_char(sysdate,'sssss')/300)+1)*5/24/60
trunc(sysdate) is MIDNIGHT today (wipe out the time).
Then rest of it was figuring out how many 5 minute intervals had happened today alert (the /300 stuff) and adding 1 more 5 minute interval to it -- so we get nice 1:5, 1:10, 1:15, 1:20, 1:25 and so on.
A simple example would be:
declare
l_job number;
begin
dbms_job.submit(job => l_job,
what => 'my_procedure;',
next-date => trunc(sysdate)+(trunc(to_char(sysdate,'sssss')/300)+1)*5/24/60,
interval => 'trunc(sysdate)+(trunc(to_char(sysdate,'sssss')/300)+1)*5/24/60)');
end;
As for the field "job" (your question about what :v_jobnum supposed to be?)
JOB - A job identifier. It is system-assigned (it is an OUT only parameter). You can use this to query the USER_JOBS or DBA_JOBS views by job ID to see the information about that job. Additionally, some routines such as RUN and REMOVE take the job ID as their only input, to uniquely identify the job to run or be removed.
You can check this by doing the following:
yd@MYDB.MU> desc dbms_job.submit
PROCEDURE dbms_job.submit
Argument Name Type In/Out Default?
------------------------------ ----------------------- ------ --------
JOB BINARY_INTEGER OUT
WHAT VARCHAR2 IN
NEXT_DATE DATE IN DEFAULT
INTERVAL VARCHAR2 IN DEFAULT
NO_PARSE BOOLEAN IN DEFAULT
INSTANCE BINARY_INTEGER IN DEFAULT
FORCE BOOLEAN IN DEFAULT
yd@MYDB.MU>
This supplements the above replies.
Hope this helps!
Regards
Yogeeraj