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.
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 td> TR> tbody> Table> in this way
- Solutions to the problem of “there are stopped jobs” in Linux
- Solve the problem that the interface between C # WinForm program and Oracle doesn’t respond for a long time
- The solution to the error of [Oracle] ora-00054
- ORA-19502: write error on file “”, block number (block size=)
- Oracle quick replacement undo table space method
- How can Oracle query tables of other users without adding a table user name
- How to solve oserror: [errno 98] address already in use and kill the python3 process
- How to Fix “Oracle sp2-0640 unable to connect” Error
- Error: Could not fork child process: There are no available terminals (-1).
- Stop: job failed while stopping
- MySQL error 1205 (HY000): lock wait timeout exceeded; try restarting transaction
- E / Art: failed sending reply to debugger: a solution to broken pipe
- Mac reports an error zsh: fork failed: resource temporarily unavailable (reasons and solutions)
- Oracle 11gR2 RAC ohasd failed to start solution
- Oracle error collection solution
- Processing method of SVN error “failed to run the WC DB work queue associated with…”
- Oracle database file is damaged, Error:ORA-01033 :ORACLE initialization or shutdown in progress
- Resolve – bash: fork: Retail: resource temporarily unavailable error
-  MySQL error: error 1205: lock wait timeout exceeded solution
- Insufficient table space ORA-00604 unable to extend table SYS.AUD by 8192