Solved

How to force a job do not run on schedule?

Posted on 2009-07-01
9
499 Views
Last Modified: 2013-12-19
How to force a job do not run on schedule?
Thanks!
0
Comment
Question by:luyan
[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
  • 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
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!

 

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
 
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

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

Working with Network Access Control Lists in Oracle 11g (part 1) Part 2: http://www.e-e.com/A_9074.html So, you upgraded to a shiny new 11g database and all of a sudden every program that used UTL_MAIL, UTL_SMTP, UTL_TCP, UTL_HTTP or any oth…
Cursors in Oracle: A cursor is used to process individual rows returned by database system for a query. In oracle every SQL statement executed by the oracle server has a private area. This area contains information about the SQL statement and the…
This video shows how to Export data from an Oracle database using the Original Export Utility.  The corresponding Import utility, which works the same way is referenced, but not demonstrated.
Via a live example, show how to restore a database from backup after a simulated disk failure using RMAN.

734 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