Solved

Oracle dbms_scheduler Job question

Posted on 2013-11-11
6
887 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
Get Database Help Now w/ Support & Database Audit

Keeping your database environment tuned, optimized and high-performance is key to achieving business goals. If your database goes down, so does your business. Percona experts have a long history of helping enterprises ensure their databases are running smoothly.

 

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

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

In Part II of this series, I will discuss how to identify all open instances of Excel and enumerate the workbooks, spreadsheets, and named ranges within each of those instances.
A Stored Procedure in Microsoft SQL Server is a powerful feature that it can be used to execute the Data Manipulation Language (DML) or Data Definition Language (DDL). Depending on business requirements, a single Stored Procedure can return differe…
The viewer will learn how to use a discrete random variable to simulate the return on an investment over a period of years, create a Monte Carlo simulation using the discrete random variable, and create a graph to represent the possible returns over…
This Micro Tutorial demonstrates in Microsoft Excel how to consolidate your marketing data by creating an interactive charts using form controls. This creates cool drop-downs for viewers of your chart to choose from.

751 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