如题:我在oracle里建立了一个job,job启动一个存储过程,这个存储过程执行时间非常长
所以想停止job,重新编译一个存储过程,但是停止不了,于是这个存储过程也就没法修改
job在运行的过程中 我使用的方式是 dbms_job.broken(2,true);
select job,broken from user_jobs;
输出是:2,Y
这样可以停止job运行,但是不能把一个正在运行的job干掉
kill sessionid 和processid 的方式,我发现找不到这个job
使用select * from user_jobs 有job的一条纪录
可是使用select * from user_job_running 没有数据
这样就无法确定v$session这个view里这个job的 sid了pid也就找不到了有哪位高手告诉我还有什么方式来kill 正在运行的job
急急。! 谢谢了
所以想停止job,重新编译一个存储过程,但是停止不了,于是这个存储过程也就没法修改
job在运行的过程中 我使用的方式是 dbms_job.broken(2,true);
select job,broken from user_jobs;
输出是:2,Y
这样可以停止job运行,但是不能把一个正在运行的job干掉
kill sessionid 和processid 的方式,我发现找不到这个job
使用select * from user_jobs 有job的一条纪录
可是使用select * from user_job_running 没有数据
这样就无法确定v$session这个view里这个job的 sid了pid也就找不到了有哪位高手告诉我还有什么方式来kill 正在运行的job
急急。! 谢谢了
解决方案 »
- oracle
- 谁能形象的说一下,什么是索引?是实的还是虚的?
- oracle数据库中,如何能够查询到一条记录产生的时间?
- vm+solaris+crs报“虚拟主机名似乎已分配”的错误,无法安装crs
- PL/SQL Developer 下面的状态栏的执行时间是指总时间吗?-------------
- 数据表设计问题
- ORA-06553错误什么意思?
- trigger或package停止运行可能会有哪些原因(经验丰富的高手/准高手请进)
- 谁能快点告诉我一个问题啊!江湖救急啊!百分救急!
- 报错ora00937不是单组分组函数
- windows 2003下安装EM grid control,hosts文件如何设置对应
- select 取一个字段的后三位,该如何取?
Let's face it, Oracle's job scheduling facility is a wonderful tool for scheduling Oracle related jobs without having to maintain a cron job on Unix or an AT job in windows. It is also very robust and reliable. It is that very reliability and robustness that gives many of us our problems.
If you have any form of jobs running on your system, you will at one time or another come across the issue of a run-away job that just doesn't seem to want to end. Or maybe you will try and shutdown the database only to find out that it is waiting to complete a job. I would like to offer some help in the management of those job queues when they just don't seem to want to end or go away.
A while back I needed to find information on how to clear the job queue for jobs running with no apparent end in sight. Some had hung, while others just were taking a bad access path to data. I needed to bring down these jobs, do a bit of tuning and then restart the jobs. Well, to my amazement, there wasn't very much information out on the web that gave good insight into this process. Basically the method suggested was to first break the job and then issue an ALTER SYTEM KILL SESSION command. This method does not always work and unfortunately--never on my system, for the jobs I had. I then called Oracle support and basically got the same answer as I found out on the web. They did give me one added piece of information. They said, if the ALTER SYSTEM KILL SESSION didn't work, I was supposed to bounce my database in order to bring down the job queue processes. First of all, this wasn't an option and when I did get the opportunity to bounce the database box, many of the jobs seemed to come right back as strong as ever.
Before writing this article I did another quick search on the topic of killing dbms_jobs and to my amazement there still wasn't much good information out there. This is why I want to share my method, so that you won't be stuck up against the wall with this problem and nowhere to turn, as I was.
Viewing Scheduled dbms_jobs
When looking at what jobs have been scheduled, there is really only one view that you need to go to. The dba_jobs view contains all of the information you need, to see what has been scheduled, when they were last run, and if they are currently running. Use the following simple script to take a look. Bear with me on the sub-select, I will build on this query as we go on in the presentation.scheduled_dbms_jobs.sql
set linesize 250
col log_user for a10
col job for 9999999 head 'Job'
col broken for a1 head 'B'
col failures for 99 head "fail"
col last_date for a18 head 'Last|Date'
col this_date for a18 head 'This|Date'
col next_date for a18 head 'Next|Date'
col interval for 9999.000 head 'Run|Interval'
col what for a60select j.log_user,
j.job,
j.broken,
j.failures,
j.last_date||':'||j.last_sec last_date,
j.this_date||':'||j.this_sec this_date,
j.next_date||':'||j.next_sec next_date,
j.next_date - j.last_date interval,
j.what
from (select dj.LOG_USER, dj.JOB, dj.BROKEN, dj.FAILURES,
dj.LAST_DATE, dj.LAST_SEC, dj.THIS_DATE, dj.THIS_SEC,
dj.NEXT_DATE, dj.NEXT_SEC, dj.INTERVAL, dj.WHAT
from dba_jobs dj) j;What Jobs are Actually RunningA simple join to the dba_jobs_running view will give us a good handle on the scheduled jobs that are actually running at this time. This is done by a simple join through the job number. The new column of interest returned here is the sid which is the identifier of the process that is currently executing the job.running_jobs.sqlset linesize 250
col sid for 9999 head 'Session|ID'
col log_user for a10
col job for 9999999 head 'Job'
col broken for a1 head 'B'
col failures for 99 head "fail"
col last_date for a18 head 'Last|Date'
col this_date for a18 head 'This|Date'
col next_date for a18 head 'Next|Date'
col interval for 9999.000 head 'Run|Interval'
col what for a60
select j.sid,
j.log_user,
j.job,
j.broken,
j.failures,
j.last_date||':'||j.last_sec last_date,
j.this_date||':'||j.this_sec this_date,
j.next_date||':'||j.next_sec next_date,
j.next_date - j.last_date interval,
j.what
from (select djr.SID,
dj.LOG_USER, dj.JOB, dj.BROKEN, dj.FAILURES,
dj.LAST_DATE, dj.LAST_SEC, dj.THIS_DATE, dj.THIS_SEC,
dj.NEXT_DATE, dj.NEXT_SEC, dj.INTERVAL, dj.WHAT
from dba_jobs dj, dba_jobs_running djr
where dj.job = djr.job ) j;What Sessions are Running the JobsNow that we have determined which jobs are currently running, we need to find which Oracle session and operating system process is accessing them. This is done through first joining v$process to v$session by way of paddr and addr which is the address of the processs that owns the sessions, and then joining the results back to the jobs running through the sid value. The new columns returned in our query are spid which is the operating system process identifier and serial# which is the session serial number.session_jobs.sqlset linesize 250
col sid for 9999 head 'Session|ID'
col spid head 'O/S|Process|ID'
col serial# for 9999999 head 'Session|Serial#'
col log_user for a10
col job for 9999999 head 'Job'
col broken for a1 head 'B'
col failures for 99 head "fail"
col last_date for a18 head 'Last|Date'
col this_date for a18 head 'This|Date'
col next_date for a18 head 'Next|Date'
col interval for 9999.000 head 'Run|Interval'
col what for a60
select j.sid,
s.spid,
s.serial#,
j.log_user,
j.job,
j.broken,
j.failures,
j.last_date||':'||j.last_sec last_date,
j.this_date||':'||j.this_sec this_date,
j.next_date||':'||j.next_sec next_date,
j.next_date - j.last_date interval,
j.what
from (select djr.SID,
dj.LOG_USER, dj.JOB, dj.BROKEN, dj.FAILURES,
dj.LAST_DATE, dj.LAST_SEC, dj.THIS_DATE, dj.THIS_SEC,
dj.NEXT_DATE, dj.NEXT_SEC, dj.INTERVAL, dj.WHAT
from dba_jobs dj, dba_jobs_running djr
where dj.job = djr.job ) j,
(select p.spid, s.sid, s.serial#
from v$process p, v$session s
where p.addr = s.paddr ) s
where j.sid = s.sid;Now that we have a good handle on how we can look at the jobs and the key columns involved, let's go through the steps needed to bring down a job. The following is a 5 to 11 step process that should solve all of your problems.(未完待续...)
SQL> select name,value from v$parameter where name = 'job_queue_processes';7. Alter the Job Queue to ZeroSQL> ALTER SYSTEM SET job_queue_processes = 0;This will bring down the entire job queue processes.8. Validate that No Processes are Using the Job QueueRe-run the session_jobs.sql script to see if any jobs are still running. Since we have given a hard stop to the job queue and issued the kill commands, you can now wait until no more jobs are running. After all the jobs have quit running, you can do whatever maintenance or tuning you need to do before proceeding.9. Mark the DBMS_JOB as Not Broken
You can now reset the broken job to not broken so they can run again. Just issue the command.SQL>EXEC DBMS_JOB.BROKEN(job#,FALSE):10. Alter the Job Queue to Original Value
Set the job queue to its' original value so that the jobs can run again.ALTER SYSTEM SET job_queue_processes = original_value;11. Validate that DBMS_JOB Is Running
To make sure everything is back to normal, re-run the above scripts to validate that jobs are scheduled, not broken, and are executing with the next and last dates columns changing.Oracle have given us a great tool for scheduling activities within the database. As with many things inside the database, not everything goes as planned, nor are we given adequate tools to fix some of the problems we encounter. With the eleven steps outlined here, hopefully you will have increased your arsenal to handle those run away jobs that have given the best of us a few tense moments.
--------------------------------------------------------------------------------------------------------------------------
注意:第四步 删除操作系统层次的进程或线程操作,Oracle在Linux/Unix中后台进程是以进程方式运作,在Windows系列操作系统中是以线程方式运作。
对于Windows,启动命令提示符:
orakill sid spid
其中sid是数据库实例的名称,而不是前面脚本中查出来的sid。
可以通过一下命令来得到:
select name from v$database;
select instance_name from v$instance;(完)
user_job_running 应该是正在运行的job,就是不知道怎么样算是JOB执行完成,是否只是触发存储过程执行了就完成了呢,还是要等到该JOB触发的存储过程也执行完成才算完成。