1. Related tables and views

dba_ jobs 　　all_ jobs 　　user_ Jobs contains all the job information DBA of the login user_ jobs_ Running contains information about running jobs. Note that you must use the sys user of Oracle to log in to the database to view the DBA_ jobs_ Running, V $process, V $session table. At the same time, when logging in to the operating system, the Oracle user should be used.

2. Problem description

Solve a problem for colleagues, when the network connection is poor, execute a very long time SQL insert operation.

Since the network condition is not good, we choose to use disposable job to complete the insertion operation. After the job was executed for a period of time, I found that there was something wrong with the inserted table (I’m sorry, I didn’t check it first at that time). I’m going to stop job, because when job is running, all my modifications will report the error that system resources are busy.

It is not feasible to forcibly kill session, because the job will be restarted later. If the executed SQL is also killed, the restarted job will be executed again.

3. Solutions

The better way to do it is to do it;

1. First determine the job number to stop

In 10g, it can pass DBA_ Jobs_ Running to confirm.

Find running job:

    select sid from dba_ jobs_ running;

 

Find the SPID of the running job:

    select a.spid from v$process a ,v$session b where a.addr=b.paddr  and b.sid in (select sid from dba_ jobs_ running);

 

2. Break your confirmed job

Pay attention to DBMS_ Job package to identify your job as broken.

    SQL> EXEC DBMS_ JOB.BROKEN (job#,TRUE);

Note: after executing the command, the job you selected is still running.

 

3. Oracle session corresponding to kill

If the job is required to stop immediately, you need to find the corresponding session (SID, serial #) of the job, and then execute the following command:

    ALTER SYSTEM KILL SESSION ‘sid,serial#’;

Or directly kill the session of the corresponding operating system. If you use alter system kill session to execute for a long time, you can use the OS command to quickly kill the session

     For Windows, at the DOS Prompt: orakill sid spid

     For UNIX at the command line> kill –9 spid

 

4. Check if your job is still running

Check whether the job you want to stop is still running. In most cases, it should have stopped. In particular, the execution of the third step of the “killer” command. If it really doesn’t stop, we have to do it again from the first step and the third step.

 

5. Change the number of job queue processes to 0

First, confirm the current number of job queue processes

    SQL> col value for a10

    SQL> select name,value from v$parameter where name =’job_ queue_ processes’;     

Then change the number of job queue processes to 0

    SQL> ALTER SYSTEM SET job_ queue_ processes = 0;

Make sure all jobs stop.

 

6. Modify anything you want to modify, even the content in the job.

 

7. After the modification is completed, stop the broken state of the job.

SQL>EXEC DBMS_ JOB.BROKEN (job#,FALSE):

 

8. Resume job_ queue_ The original value of processes

     ALTER SYSTEM SET job_ queue_ processes = original_ value;

At this point, the entire stop and modify job completed

    

However, it should be noted that when mark is in a broken state, it may take a while for job to execute SQL internally. Therefore, you must consider it carefully when creating a job. At the same time, if possible, you can add some parts to judge the “stop signal” in your PL/SQL code. To perform the above steps.

After all, Oracle is very stubborn when executing jobs

 

4. Annex: usage of orakill

Oracle’s lock table solution “ora-00031: session marked for kill” often encounters a session that is always active, making the CPU always in use. Although it kills, it can’t make the thread end. Kill session is just to kill the process, but the thread is always active. It needs a real kill thread to solve the problem of high CPU utilization. OS: Windows 2003 orakill uses orakill Sid SPID, where sid is the instance name and SPID is the thread number. How to get this SPID?The following is a statement to find SPID. Select SPID, osuser, s.program from V $process P, V $session s where p.addr = s.paddr and s.sid = XXX; — Note: XXX is the SID of session (not database SID). You can input it yourself.
orakill instance_ Name SPID can successfully solve the problem of high CPU utilization in this way