Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

Oracle dbms_scheduler Job question

Posted on 2013-11-11
6
Medium Priority
?
965 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 14

Assisted Solution

by:Alexander Eßer [Alex140181]
Alexander Eßer [Alex140181] earned 2000 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
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 

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 14

Accepted Solution

by:
Alexander Eßer [Alex140181] earned 2000 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

What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

Question has a verified solution.

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

Your data is at risk. Probably more today that at any other time in history. There are simply more people with more access to the Web with bad intentions.
This article describes how to use a set of graphical playing cards to create a Draw Poker game in Excel or VB6.
This Micro Tutorial will demonstrate in Google Sheets how to use the HYPERLINK function to create live links inside your spreadsheet.
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.

670 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