database

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