- 浏览: 684624 次
- 性别:
- 来自: 中山
文章分类
最新评论
-
wuhuizhong:
jFinal支持Rest风格吗?可以想spring mvc那样 ...
在JFinal的Controller中接收json数据 -
wuhuizhong:
在jfinal中应如何获取前端ajax提交的Json数据?ht ...
在JFinal的Controller中接收json数据 -
wuhuizhong:
jfinal如何处理json请求的数据:问题: 在某些api接 ...
在JFinal的Controller中接收json数据 -
wuhuizhong:
Ubuntu14.04 安装 Oracle 11g R2 Ex ...
Oracle 11g release 2 XE on Ubuntu 14.04 -
alanljj:
这个很实用,已成功更新,谢过了!
odoo薪酬管理模块l10n_cn_hr_payroll
Take control of Oracle's queue with a step by step approach to getting rid of those pesky DBMS_JOBs.
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.
Lets first go through a few different methods of viewing the information about job queues.
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 a60 select 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 Running
A 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.sql
set 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 Jobs
Now 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.sql
set 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.
Bringing Down a DBMS_JOB
1. Find the Job You Want to Bring Down
In order to do anything you first need to find the job that is giving you a headache. Go ahead and run the running_jobs.sql. This will give you the prime information, job, sid, serial#, and spid, for the following actions in bringing down the job.
2. Mark the DBMS_JOB as Broken
Use the following command for the job that you have to deal with.
SQL> EXEC DBMS_JOB.BROKEN(job#,TRUE);
All this command does is mark the job so that if we get it to stop, it won't start again. Let's make one thing perfectly clear, after executing this command the job is still running.
As a side note, if you are trying to shut down a database with jobs that run throughout the day, they may hinder your attempts to bring down the database cleanly. This is a wonderful command to make sure no jobs are executing during the shutdown process. Just be aware that you will need to mark the jobs as unbroken when the database comes back up, more on that later.
3. Kill the Oracle Session
Since the job is still running and it isn't going to end soon, you will need to kill the Oracle session that is executing the job. Use the following command for to kill the job.
ALTER SYSTEM KILL SESSION 'sid,serial#';
4. Kill the O/S Process
More often than not the previous step will still leave the job attached to the database and still running. When this happens you will need to go out to the operating system level and get rid of the process that has spawned from the running job. In order to do this you must login to the database box and issue the following command, depending on the type of operating system you have.
For Windows, at the DOS Prompt: orakill sid spid
For UNIX at the command line> kill '9 spid
The orakill is an Oracle command, while kill is a Unix command.
5. Check if the Job is Still Running
Re-run the session_jobs.sql script to see if you have gotten rid of the job. If you have there is no reason to go further. Usually steps 1 through 4 will be sufficient to get rid of a job but when the job is running wild you will have to continue with steps 6 through 11 which describes a process for bouncing the job queue process.
6. Determine the Current Number of Job Queue Processes
SQL> col value for a10
SQL> select name,value from v$parameter where name = 'job_queue_processes';
7. Alter the Job Queue to Zero
SQL> 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 Queue
Re-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.
发表评论
-
用函数unistr将Oracle数据库中的Unicode转换为中文
2016-07-19 11:51 7943例子: DECLARE V_EXT_DES V ... -
ORACLE APPLICATION EXPRESS 5.0 升级
2016-05-12 11:43 596Oracle11GR2 XE 缺省是安装了oracle ap ... -
Oracle ACL(Access Control List)
2016-05-12 11:36 899在oralce 11g中假如你想获取server的ip或者h ... -
了解systemstate dump
2016-04-26 14:09 497当数据库出现严重的性能问题或者hang了的时候,我们非常需要 ... -
通过ORACLE的UTL_HTTP工具包发送包含POST参数的请求
2016-03-18 16:25 5171DECLARE req utl_http. ... -
Shell: extract more from listener.log(分析监听日志)
2016-03-16 14:57 1164统计一天内每小时的session请求数 # fgrep ... -
ORA-01031: insufficient privileges 问题解决笔记
2016-02-01 15:53 1195A) File $Oracle_HOME/network/a ... -
listener.log中报Warning: Subscription For Node Down Event Still Pending问题的解决方法
2016-01-07 16:34 1641一套Oracle 10.2.0.1 for aix的数据库环 ... -
Oracle触发器和MySQL触发器之间的区别
2015-11-19 12:55 684Oracle触发器格式: CREATE [OR RE ... -
查询正在执行的存储过程
2015-11-13 09:27 20631、找正在执行的PROCEDURE的 sid ,serial# ... -
undo表空间损坏的处理过程
2015-10-14 13:49 1227磁碟陣列故障,分區/rman上包括undo和archivel ... -
登录oracle资料库时很久无反应的问题处理一例
2015-10-11 10:56 1005原因是系统存在僵死的进程,促使session处于激活状态.首 ... -
TNS-12560问题解决
2015-10-01 19:52 626tnsping远程主机实例出现TNS-12560: TNS ... -
查看undo中sql语句的占用情况
2015-08-06 17:18 1784查看undo中sql语句的占用情况 select * ... -
Install Open System Architect And ODBC Instant Client
2015-05-21 14:03 760How to Install Open System Arc ... -
恢复oracle中用pl sql误删除drop掉的表
2015-04-03 16:12 564查看回收站中表 select object_name,or ... -
在Oracle Linux 6.6上安装Oracle 10gR2
2015-01-15 15:36 2700查看硬體配置 # df -h Filesystem ... -
kill
2015-01-03 11:36 469--根据某一对象查询进程 col owner fo ... -
Oracle 数据库Storage存储迁移笔记
2014-12-27 11:08 9971.确认数据文件、控制文件、临时文件、日志文件 位置 / ... -
異地備份資料庫的開啟步驟
2014-11-19 14:03 496使用EMC設備執行異地備份, 資料庫的複製是開啟的狀態下, ...
相关推荐
3. 名词辨析 - 野生动植物保护:"Laws have been made to prevent people from killing _____animals and birds ."法律旨在保护野生动植物,所以用wild animals。 4. 名词辨析 - 损失:"suffered a heavy _______...
用法 要使用此示例,您必须安装Node.js并附带一个程序包管理器。 我建议使用yarn,但是npm对于像这样的简单示例同样适用。 安装软件包并运行启动脚本: npm install npm start 要么 yarn yarn start ...
This is a program is writen when i study program on GUN, it could delete all the exiting semaphore, if you just start to study c programing on linux ,maybe it will be one good example to help you ...
在提供的"2D_killing_time-main"文件中,很可能包含了项目的源代码、资源文件和配置信息。分析这些文件可以帮助我们更深入地理解这个项目的实现细节。例如,源代码文件可能包含C#脚本,资源文件可能有Sprite图像、...
16. Indeed, it is arguable that body shattering is the very point of football, as killing and maiming ______________. 题目要求填入谓语动词。选项D "are of war" 表明“杀伤和致残”是战争的本质,与前面的...
8. **动名词作主语** - 句子"_________ from the job for a long time makes my father have a sense of loss."中,动名词"Retiring"作主语,表示退休这个行为,因此选项B "Retiring"正确。 9. **宾语从句** - 句子...
5. "It suddenly ________ to me that we could use a computer to do the job."这道题考察固定句型"It occurred to sb. that…"表示“某人突然想到…”,因此正确答案是D. occurred。 【知识点二:动词短语辨析】 ...
The report about that famous singer killing people will ____________(disappear) in tomorrow’s newspapers. - 正确答案:disappear - 解析:此处需要填入动词形式,表示“消失”,因此使用disappear。 17....
"killing" 是现在分词,表示自然而然的结果。 13. "There are lots of places of interest _____(need; repair) in our city." "needing repair" 是现在分词短语作定语,城市中有许多需要修复的名胜古迹。 14. ...
我们在四个维度上数值构造渐近的反德西特(AdS)黑洞,这些黑洞仅包含一个Killing向量场。 通过将超辐射不稳定性的发生点连接到称为geons的光滑无水平几何体,将这些解决方案(我们称之为黑色谐振器)将Kerr-AdS的超...
在句子"A all planes crashed into a hillside five miles east of the city, _______ all forty people on board."中,现在分词"killing"用作结果状语,表示自然而然的结果,表明飞机坠毁导致了所有人的死亡。...
5. "______(hunt) like ______(kill) wild animals" 主语是猎人,因此填入"hunters"和"killing"。6. "He was even ______(ill) this morning." 更病态用"more ill"。7. "The dishes in the restaurant are very ____...
6. Populate KillAllProcesses.xaml from the Framework folder with killing the process used. 7. Populate the Process.xaml file with the following actions: Web scraping, Filtering and Appending to Excel....
我们使用具有扩展周期电势的伪标量场模型,该模型取决于与Killing向量的模量成比例的其他参数。 在我们的方法中,它们扮演模型指导功能的角色。 具有修改后的潜力的轴力场的协变模型配备了Killing向量场的扩展形式...
在IT行业中,"killing-the-monster"这个标题可能是在指代解决一个具有挑战性的问题或者优化一个复杂的技术难题。在这种上下文中,我们将其与JavaScript关联起来,这意味着我们可能正在探讨如何利用JavaScript来处理...
scrcpy是Android投屏工具,此项目为开源项目在Github上,为了方便大家下载使用,本人把该资源放在这里了。scrcpy就是通过adb调试的方式来将手机屏幕投到电脑上,并可以通过电脑控制您的Android设备。...
我们介绍了所有扭曲的AdS k×w M d − k,k> 2的所有对称超代数g $$ \ mathfrak {g} $$,在d = 10、11维上的通量背景保留了任何数量的超对称性。 首先,我们给出g分解为AdS k的等距代数与内部空间M d-k的直接和的...
当我们在使用安卓设备进行刷机操作时,尤其是尝试刷入自定义的recovery镜像时,可能会遇到一个错误提示:“adb killing”,这个错误提示会导致刷机过程无法正常进行。"adb killing"错误通常与Android Debug Bridge ...
我们证明,对于M理论或类型II,在维D≥4中保留N $$ \ mathcal {N} $$超对称的通用Minkowski通量背景正好对应于可积广义GN $$ {G} _ {\ mathcal {N} } $$结构,其中GN $$ {G} _ {\ mathcal {N}} $$是Killing旋转子...
- _He fired, killing one of the passers-by._(这里,“killing”引导了一个现在分词结构,作为结果状语。) - _Being tired with the work, he sat down to rest._(在这个例子中,“Being tired with the work...