?
Solved

Oracle dbms_scheduler Job question

Posted on 2013-11-11
6
Medium Priority
?
997 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 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 Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 

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

Get your problem seen by more experts

Be seen. Boost your question’s priority for more expert views and faster solutions

Question has a verified solution.

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

Windows Explorer let you handle zip folders nearly as any other folder: Copy, move, change, and delete, etc. In VBA you can also handle normal files and folders, but zip folders takes a little more - and that you'll find here.
Among the most obnoxious of Exchange errors is error 1216 – Attached Database Mismatch error of the Jet Database Engine. When faced with this error, users may have to suffer from mailbox inaccessibility and in worst situations, permanent data loss.
In this video, Percona Director of Solution Engineering Jon Tobin discusses the function and features of Percona Server for MongoDB. How Percona can help Percona can help you determine if Percona Server for MongoDB is the right solution for …
Enter Foreign and Special Characters Enter characters you can't find on a keyboard using its ASCII code ... and learn how to make a handy reference for yourself using Excel ~ Use these codes in any Windows application! ... whether it is a Micr…

612 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