Oracle JOB Lock & job_queue_processes
JOB Lock & job_queue_processes
標籤： Oracle
系統出現ORA-00054: resource busy and acquire with NOWAIT specified，時間點約在22:00:36分，猜測應該是在做Analysis的時候，有lock住檔案。
1.Check Job是否在Running
2.Stop job
3.Kill job session
4.Check job status
5.Start Job
若不讓job暫時啟動起來：
1.Check Jobs啟動數量
2.job_queue_processes = 0;
3.工事處理完之後，job_queue_processes = original_value;
1. Check the running Job (From here you will get the SID that running the Job)
SQL> SELECT * FROM DBA_JOBS_RUNNING;
2. Make Job become Broken/offline
SQL> BEGIN SYS.DBMS_IJOB.BROKEN(job#,TRUE); END;
3. Kill the Oracle’s or O/S Session
Oracle Session
SQL> ALTER SYSTEM KILL SESSION 'sid,serial#';
OS Session
OR Kill the O/S Process ID (PID)
SELECT p.spid FROM v$session s, v$process p
WHERE s.paddr = p.addr
AND s.sid = :sid;
#kill -9 pid
4.Check if the Job is Still Running
SQL> SELECT * FROM DBA_JOBS_RUNNING;
5. Mark the DBMS_JOB as Not Broken
BEGIN SYS.DBMS_IJOB.BROKEN(job#,FALSE); END;
******************************************************
若要停線，可以把job全部停掉
Check Job 數量:
select name,value from v$parameter where name = 'job_queue_processes';
Alter the Job Queue to Zero，讓Job不要在執行起來
SQL> ALTER SYSTEM SET job_queue_processes = 0;
This will bring down the entire job queue processes.
工事處理完之後，記的把job_queue_processes改回原來的值
Alter the Job Queue to Original Value
SQL> ALTER SYSTEM SET job_queue_processes = original_value;
相關連結：
http://levicorp.com/2009/05/22/how-to-kill-the-running-job/
