Solved

Oracle dbms_scheduler Job question

Posted on 2013-11-11
6
748 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
6 Surprising Benefits of Threat Intelligence

All sorts of threat intelligence is available on the web. Intelligence you can learn from, and use to anticipate and prepare for future attacks.

 

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

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

SQL Command Tool comes with APEX under SQL Workshop. It helps us to make changes on the database directly using a graphical user interface. This helps us writing any SQL/ PLSQL queries and execute it on the database and we can create any database ob…
I annotated my article on ransomware somewhat extensively, but I keep adding new references and wanted to put a link to the reference library.  Despite all the reference tools I have on hand, it was not easy to find a way to do this easily. I finall…
This Micro Tutorial will demonstrate how to use longer labels with horizontal bar charts instead of the vertical column chart.
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…

708 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

15 Experts available now in Live!

Get 1:1 Help Now