This content has been marked as final. Show 17 replies
Your command is correct :
You can remove currently executing jobs from the job queue.
You can remove only jobs you own.
Something like this might allow you to drop it.
DBMS_JOB.CHANGE(61, null, null, 'SYSDATE + 3');
See A76956-01 Oracle8i Administrator's Guide for more information.
Actually do it the other way around. It is safer.
If you kill a job, the job process fails and the Job Queue Manager will detect it - and restart the job.
It can do that restart before you have successfully deleted that job from the job queue.
Thus is is better to first delete the job (ensuring that it will not be restarted) and then kill the session running that job.
As said you have to be the owner of the job and then you could run something like this
select 'exec dbms_job.broken(job => '||a.job||',broken => true);','alter system kill session '''||a.sid||','||a.SERIAL#||''';',a.* from (
select d.*,(select s.USERNAME from v$session s where s.sid = d.sid) username,(select s.SERIAL# from v$session s where s.sid = d.sid) SERIAL# from dba_jobs_running d
This is what I use anyways
You already received plenty of good advise. One thing I haven't seen is stopping the job from further executions with
You can check the status of a job in dba_jobs.broken column (Y/N). If the job is currently running you can follow above advise and kill its process then remove the job.
SQL> exec dbms_job.broken(<job_id>, TRUE); SQL> commit;
There is nothing wrong with using DBMS_IJOB package as Paul suggested. It saves a lot of time if the job is in another schema. You can find enough information on this package on Metalink. It also could be useful in other situations like creating a job with predefined JOB_ID. As far as I know it's used internally by import.
Max Seleznev wrote:So opening a giant security hole, allowing a user or application schema to execute a job as sys, with the ability to do anything, is not wrong?
There is nothing wrong with using DBMS_IJOB package as Paul suggested. It saves a lot of time if the job is in another schema.
I would not only call it wrong. I would call it foolish and just plain stupid. It is an offense that can get you fired when security is compromised.
If you want to expose sys functions to user land then that needs to be implemented via trusted code wrappers that rigidly control access to, and usage of, that functionality - together will full auditing.
That's a pretty strong wording.
Nowhere in OP post or in the reply it says that any privileges have to be granted to anyone. There is a good chance OP already has DBA privileges or can connect as sys. Besides there're situations when one has no other choice than to use DBMS_IJOB package because of its unique functionality.
Max Seleznev wrote:Being fired after a disciplinary because you've opened a security hole that was exploited, is a lot worse... don't you think? ;-)
That's a pretty strong wording.
Nowhere in OP post or in the reply it says that any privileges have to be granted to anyone. There is a good chance OP already has DBA privileges or can connect as sys.It is about security principles. Neither you or Paul mentioned the security issues that goes with using this system package. Someone (even the OP perhaps) is bound to interpret this as being a kewl package to use, better that just plain old DBMS_JOB, and then proceed to create and open barn doors in the security of that database. Not realising the implications of exposing system packages to user land applications and code.
Besides there're situations when one has no other choice than to use DBMS_IJOB package because of its unique functionality.That is why one write wrappers - to provide this type of functionality, but in a secure and controlled fashion.