Solved

Oracle dbms_scheduler Job question

Posted on 2013-11-11
6
835 Views
Last Modified: 2014-04-10
I am using Oracle database 11gR2:
select * from v$version;
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production

I have another database (11g 11.2.0.2.0) where a Oracle command worked but it does not work in the the database above (11g 11.2.0.4.0). I am not sure if it is related to the database version.

This is the complete code that when run inserts a record in the table in one database but does not insert a record in the other database:
---
create table tmp1(f1 varchar2(100));
CREATE OR REPLACE PROCEDURE p_tmp(p_btch_id IN varchar2 DEFAULT '-1') IS
begin
 insert into tmp1(f1) values (to_char(sysdate, 'mm-dd-yy hh:mi:ss'));
 commit;
END p_tmp;

begin
dbms_scheduler.create_program
(
program_name=>'p_tmp_prog',
program_action=>'p_tmp',
program_type=>'STORED_PROCEDURE',
number_of_arguments=>1, enabled=>FALSE
) ;

dbms_scheduler.DEFINE_PROGRAM_ARGUMENT(
program_name=>'p_tmp_prog',
argument_position=>1,
argument_type=>'VARCHAR2',
DEFAULT_VALUE=>'-1');
dbms_scheduler.enable('p_tmp_prog');
 
dbms_scheduler.create_job('TMP_JOB',program_name=>'p_tmp_prog');
dbms_scheduler.set_job_argument_value('TMP_JOB',1,'-1');
dbms_scheduler.disable('TMP_JOB');
dbms_scheduler.set_job_argument_value('TMP_JOB',1,'Test1');
DBMS_SCHEDULER.RUN_JOB('TMP_JOB', FALSE);
end;
----

sql>select * from tmp1;
f1
==
11-11-13 11:04:18

The above is shown in the database (11g 11.2.0.2.0) but no record is shown in the other (11g 11.2.0.4.0).

The above commands including
dbms_scheduler.set_job_argument_value('TMP_JOB',1,'Test1');
DBMS_SCHEDULER.RUN_JOB('TMP_JOB', FALSE);
works without error in both databases.

Could you help me troubleshoot why it is not working in the new database? Thanks a lot.
0
Comment
Question by:toooki
  • 4
  • 2
6 Comments
 

Author Comment

by:toooki
ID: 39639368
Actually my question is like this:

sql>exec DBMS_SCHEDULER.RUN_JOB('TMP_JOB', FALSE);

The above command runs the job in one database (as expected). But the same command does not run the job in the other database. In the other database I do not get error when I run the above command. In both databases I get the same o/p:
PL/SQL procedure successfully completed

Thank you.
0
 
LVL 13

Assisted Solution

by:Alexander Eßer [Alex140181]
Alexander Eßer [Alex140181] earned 500 total points
ID: 39639573
What is the value of the parameter "JOB_QUEUE_PROCESSES" in both DBs?!

If this is set to 0 your job will not be scheduled!

here are some other reasons why jobs do net get scheduled:
http://www.confio.com/logicalread/why-wont-my-oracle-jobs-run/#.UoEqwOKmagY
0
 

Author Comment

by:toooki
ID: 39640598
Thank you. I do not have access to the v$parameter table. So waiting for someone to look at the JOB_QUEUE_PROCESSES values. Thank you.
0
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.

 

Author Comment

by:toooki
ID: 39643130
I checked that the value of the parameter JOB_QUEUE_PROCESSES is 1000.
Is there any other parameters that cause the issue? Thanks a lot.
0
 
LVL 13

Accepted Solution

by:
Alexander Eßer [Alex140181] earned 500 total points
ID: 39643963
Have you checked the link I provided?!

Checklist for job issues
 
For convenience, I've included the brief checklist here:
 •Is the Database Instance in RESTRICTED SESSIONS mode?
 •Is the parameter JOB_QUEUE_PROCESSES set to 0?
 •Is the hidden parameter '_SYSTEM_TRIG_ENABLED' set to FALSE?
 •Is the job marked 'BROKEN' in the DBA_JOBS table?
 •Have you issued a commit after submitting the job?
 •Have you tried setting the hidden parameter, "_job_queue_interval" to a different value (default is 5 seconds)?
 •Has the database server (machine) been up for more than 497 days ¿Check Internal Oracle bug: 3427424 "SLGCSF / SLGCS STOP INCREMENTING AFTER 497 DAYS UPTIME"
 ¿It is fixed in 10.1.0.2.0. - workaround is to reboot of the database server (see also Solution for the 497 day bug below).
 
•Is the job still running in DBA_JOBS_RUNNING?
 •Does the LAST_DATE and NEXT_DATE fields in DBA_JOBS make sense for the particular job? ¿If LAST_DATE is null, the job has never executed automatically.
 
•Does the NEXT_DATE field change per the INTERVAL field in DBA_JOBS? ¿If not, it's not automatically working?
 
•Have you tried changing the value for JOB_QUEUE_PROCESSES to '0' and then back again? ¿This restarts the CJQ process.
 ¿See Oracle Bug 2649244 (fixed by: 9015, 9203, 10201)
 
•Finally, if you have upgraded or refreshed the database, try executing 'exec dbms_ijob.set_enabled(true);' as sysadm.
0
 

Author Comment

by:toooki
ID: 39649677
Thank you. I am trying all that are recommended at the website.
0

Featured Post

Netscaler Common Configuration How To guides

If you use NetScaler you will want to see these guides. The NetScaler How To Guides show administrators how to get NetScaler up and configured by providing instructions for common scenarios and some not so common ones.

Question has a verified solution.

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

Suggested Solutions

Shadow IT is coming out of the shadows as more businesses are choosing cloud-based applications. It is now a multi-cloud world for most organizations. Simultaneously, most businesses have yet to consolidate with one cloud provider or define an offic…
Excel can be a tricky bit of software to get your head around. Whilst you’ll be able to eventually get to grips with the basic understanding of how to get by, there are a few Excel tips that not everybody will even know about let alone know how to d…
The viewer will learn how to create two correlated normally distributed random variables in Excel, use a normal distribution to simulate the return on different levels of investment in each of the two funds over a period of ten years, and, create a …
This Micro Tutorial will demonstrate how to create pivot charts out of a data set. I also added a drop-down menu which allows to choose from different categories in the data set and the chart will automatically update.

808 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