Solved

DBMS_JOB

Posted on 2001-09-09
4
1,420 Views
Last Modified: 2012-05-04
WHAT IS DBMS_JOB. How can I use it? Please explain in detail as far as practicable. Help highly appreciated.
0
Comment
Question by:BIKASHMISHRA
4 Comments
 
LVL 2

Accepted Solution

by:
AlbertYou earned 30 total points
ID: 6470199

Hi,


1. What is DBMS_JOB ?
    There are up to 36 Oracle background processes(SNPn processes) you can configure and use them to run user  stored procedures periodically and automatically on the server side. User jobs are submitted and managed by the Oracle supplied package called DBMS_JOBS.

2. Configure Oracle to startup SNPn processes.
   (1) SHUTDOWN DATABASE
   (2) Edit your initDIS.ora, add/modify the following parameters.
       job_queue_processes = 4 # Upper limit=36
       job_queue_interval = 10 # No. of seconds to awake SNP processes ,Range = 1-3600
       processes = 123 # Enlarge it for high value of job_queue_processes
   (3) Restart Oracle

3. Submit your job to the database.
   (1) Connect to Oracle as an valid user who is granted with the execute privilege of the DBMS_JOB PACKAGE.
   (2) Assume you have a stored procedure test_proc and want to run it automatically in Oracle once an hour.
======================================
DECLARE
   job_no  BINARY_INTEGER;
begin
   job_no := 1000; -- Assign you job No. whatever you want
   DBMS_JOB.ISUBMIT(job_no, -- Job No.
          'test_proc;',     -- procedure name to run
          TO_CHAR(SYSDATE), -- job start time
          'SYSDATE+1/24' ); -- time interval in unit of DAY to restart
end;
/

You may save the parameters required by the stored procedure in a table or just hard code in the
DBMS_JOB.ISUBMIT line like the following

dbms_job.isubmit(job_no,'proc_test(''arg1'');',
          sysdate,'sysdate+1/24');

=======================================
4. Remove your job from the database.
begin
   dbms_job.remove(1000);
end;
/

5. View your jobs submitted
       select * from USER_JOBS;

Hope this helps
0
 
LVL 6

Expert Comment

by:Mindphaser
ID: 7045839
Please update and finalize this old, open question. Please:

1) Award points ... if you need Moderator assistance to split points, comment here with details please or advise us in Community Support with a zero point question and this question link.
2) Ask us to delete it if it has no value to you or others
3) Ask for a refund so that we can move it to our PAQ at zero points if it did not help you but may help others.

EXPERT INPUT WITH CLOSING RECOMMENDATIONS IS APPRECIATED IF ASKER DOES NOT RESPOND.

Thanks,

** Mindphaser - Community Support Moderator **

P.S.  Click your Member Profile, choose View Question History to go through all your open and locked questions to update them.
0
 
LVL 49

Expert Comment

by:DanRollins
ID: 7058306
Recommended disposition:

    Accept AlbertYou's comment(s) as an answer.

DanRollins -- EE database cleanup volunteer
0
 
LVL 1

Expert Comment

by:Moondancer
ID: 7059237
Thanks for your help, Dan. :)
Item finalized today by Moondancer - EE Moderator
0

Featured Post

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
querying by the sum of a column in decimal 7 57
Oracle - Stored Procedure Privilge access 3 39
SQL Query 34 82
Oracle 12c database link between pdb not working 20 48
Working with Network Access Control Lists in Oracle 11g (part 1) Part 2: http://www.e-e.com/A_9074.html So, you upgraded to a shiny new 11g database and all of a sudden every program that used UTL_MAIL, UTL_SMTP, UTL_TCP, UTL_HTTP or any oth…
Introduction A previously published article on Experts Exchange ("Joins in Oracle", http://www.experts-exchange.com/Database/Oracle/A_8249-Joins-in-Oracle.html) makes a statement about "Oracle proprietary" joins and mixes the join syntax with gen…
This video shows how to recover a database from a user managed backup
This video shows how to copy an entire tablespace from one database to another database using Transportable Tablespace functionality.

863 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

28 Experts available now in Live!

Get 1:1 Help Now