ORACLE series script 3: life-saving JOB processing script

Background: the long-term execution of JOB in the database leads to excessive resource consumption. Through the following expectations, we can quickly locate JOB, quickly intervene processing, and restore database performance. Long-term operation and maintenance through the following statements? t or above database? Yes, it works every time.

Find out the JOB number that is being executed and its session number

SELECT SID,JOB FROM DBA_JOBS_RUNNING

Stop the execution of the JOB

SELECT SID,SERIAL# FROM V$SESSION WHERE SID='130'

ALTER SYSTEM KILL SESSION'& SID,&SERIAL'

EXEC DBMS_JOB.BROKEN & JOB,TRUE)

Case analysis:

1, query the running Job, there are two through the query, and the process occupies more is consistent with the two ORACLE processes.

SQL > SELECT SID,JOB FROM DBA_JOBS_RUNNING

SID JOB

12 116 16 117

2. Query the information of the running job

SQL > SELECT SID,SERIAL# FROM V$SESSION WHERE SID='12'

SID SERIAL#

12 4

SQL > SELECT SID,SERIAL# FROM V$SESSION WHERE SID='16'

SID SERIAL#

16 1

3. Use the queried job information to end the job.

SQL > ALTER SYSTEM KILL SESSION '127.4'

System altered.

SQL > ALTER SYSTEM KILL SESSION '16jue 1'

System altered.

4. If you don't want to run the above job, you can set job to broken.

EXEC DBMS_JOB.BROKEN (116 heroin true)

EXEC DBMS_JOB.BROKEN (117 true)