Solved

How to force a job do not run on schedule?

Posted on 2009-07-01
9
485 Views
Last Modified: 2013-12-19
How to force a job do not run on schedule?
Thanks!
0
Comment
Question by:luyan
  • 5
  • 3
9 Comments
 
LVL 3

Expert Comment

by:garysadler
ID: 24757950
depending on whether you're using the old or new scheduler:

old:
exec dbms_job.broken(myjobnumber,TRUE);

new:
exec dbms_scheduler.disable('myjobname');
0
 

Author Comment

by:luyan
ID: 24758006
How to find job name? I would like to use the command:
exec dbms_scheduler.disable('myjobname');
0
 
LVL 3

Expert Comment

by:garysadler
ID: 24758658
select job_name,program_name,job_action,enabled from dba_scheduler_jobs;
0
 

Author Comment

by:luyan
ID: 24759263
I knew the job number, but not job_name. Which table or view has job number and job_name? Then I can get the right job_name, and can run exec dbms_scheduler.disable('myjobname')
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.

 
LVL 3

Expert Comment

by:garysadler
ID: 24759274
The new scheduler does not use job numbers.  If the job does not appear in the dba_scheduler_jobs view, then you are using the old scheduler and can view the job in the dba_jobs view.  In that case you must use the dbms_job.broken method to halt job execution.
0
 

Author Comment

by:luyan
ID: 24759360
sqlplus / as sysdba
SQL> execute dbms_job.broken(333, true)
BEGIN dbms_job.broken(333, true); END;
*
ERROR at line 1:
ORA-01013: user requested cancel of current operation
ORA-06512: at "SYS.DBMS_IJOB", line 525
ORA-06512: at "SYS.DBMS_JOB", line 245
ORA-06512: at line 1

What's wrong?
0
 

Author Comment

by:luyan
ID: 24759594
Please ignore the previous one. Please take a look the following:
sqlplus / as sysdba
SQL> execute dbms_job.broken(333, true)
BEGIN dbms_job.broken(333, true); END;
*
ERROR at line 1:
ORA-23421: job number 333 is not a job in the job queue
ORA-06512: at "SYS.DBMS_SYS_ERROR", line 86
ORA-06512: at "SYS.DBMS_IJOB", line 529
ORA-06512: at "SYS.DBMS_JOB", line 245
ORA-06512: at line 1
Job #333 is a job defined in system.  Why I couldn't use - execute dbms_job.broken(333, true)?

0
 
LVL 28

Accepted Solution

by:
Naveen Kumar earned 500 total points
ID: 24777045
can you query the dba_jobs like the below

select *
from dba_jobs
where job = 333;

just see the log_owner column and you need to connect to that user and then try

execute dbms_job.broken(333, true)

Also you need to give a commit at the end otherwise even if it gets broken successfully in one session, if you query from some other session - it will be still shown as not broken only unless you put a commit at the end if you use any dbms_job.proc(..)
0
 

Author Closing Comment

by:luyan
ID: 31598937
thanks for your help!
0

Featured Post

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.

Join & Write a Comment

Why doesn't the Oracle optimizer use my index? Querying too much data Most Oracle developers know that an index is useful when you can use it to restrict your result set to a small number of the total rows in a table. So, the obvious side…
Configuring and using Oracle Database Gateway for ODBC Introduction First, a brief summary of what a Database Gateway is.  A Gateway is a set of driver agents and configurations that allow an Oracle database to communicate with other platforms…
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function
This video explains what a user managed backup is and shows how to take one, providing a couple of simple example scripts.

757 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

18 Experts available now in Live!

Get 1:1 Help Now