Shulou(Shulou.com)06/01 Report--

In this issue, the editor will bring you an example analysis of the thorough kill session of Oracle. The article is rich in content and analyzes and narrates it from a professional point of view. I hope you can get something after reading this article.

Oracle thorough kill sessionkillsession is one of the things that often happens to DBA. If kill drops the session that should not be kill, it is destructive, so try to avoid such errors as much as possible. At the same time, we should also pay attention to

If the session of kill belongs to the Oracle background process, it is easy to cause the database instance to go down.

In general, it is not necessary to kill an Oracle session process at the operating system level, but this is not always the case. Killing the session at the Oracle level and killing the process at the operating system level are given in the description below.

First, get the information that requires kill session (using V$SESSION and GV$SESSION views)

SETLINESIZE180

COLUMNspid FORMAT A10

COLUMNusername FORMAT A10

COLUMNprogram FORMAT A40

SELECTs.inst_id

S.sid

S.serial#

P.spid

S.username

S.program

S.paddr

S.STATUS

FROM gv$session s

JOINgv$process pONp.addr = s.paddrANDp.inst_id = s.inst_id

WHERE s.type! = 'BACKGROUND'

INST_ID SID SERIAL# SPID USERNAME PROGRAM PADDR STATUS

--

1146 2327573 TEST sqlplus@oracle10g (TNS V1-V3) 4C621950INACTIVE

1 160 1727610 SYS sqlplus@oracle10g (TNS V1-V3) 4C624174 ACTIVE

1 144 4227641 SCOTT sqlplus@oracle10g (TNS V1-V3) 4C624730INACTIVE

Second, use the ALTERSYSTEMKILLSESSION command to realize

Syntax:

SQL > ALTERSYSTEMKILLSESSION'sid,serial#'

SQL > ALTERSYSTEMKILLSESSION'sid,serial#'IMMEDIATE

For killsession in RAC environment, you need to find out which node the session that requires kill is located, which can be obtained by querying the GV$SESSION view.

When killsession, you just kill the conversation. In some cases, due to large transactions or the need to run longer SQL statements, the session that requires kill cannot be killed immediately. For this kind of situation

The situation will receive a "marked for kill" prompt (as follows), and the session will be killed as soon as the current transaction or operation of the session is completed.

Altersystemkillsession'4730,39171'

*

ERRORatline1:

ORA-00031:sessionmarkedforkill

Session 146144 will be killed in the following operation

Sys@AUSTIN > altersystemkillsession'146,23'

Systemaltered.

Sys@AUSTIN > altersystemkillsession'144,42'

Systemaltered.

Sys@AUSTIN > selectinst_id,saddr,sid,serial#,paddr,username,status,programfromgv$sessionwhereusernameisnotnull

INST_ID SADDR SID SERIAL# PADDR USERNAME STATUS PROGRAM

--

14C70BF04 144424C6545A0SCOTT KILLED sqlplus@oracle10g (TNS V1-V3)

14C70E6B4 146234C6545A0TEST KILLED sqlplus@oracle10g (TNS V1-V3)

14C71FC84 160 174C624174SYS ACTIVE sqlplus@oracle10g (TNS V1-V3)

Note: you can see that the PADDR address of the killed session has changed in the query. Refer to the red font in the query results. If more than one session is dropped by kill, the PADDR of multiple session

Is changed to the same process address.

Retrieve the previous address of ADDR dropped by kill through the following statement

SELECTs.username,s.status

X.ADDR,x.KSLLAPSC,x.KSLLAPSN,x.KSLLASPO,x.KSLLID1R,x.KSLLRTYP

Decode (bitand (x.ksuprflgMagne2), 0recordnullre1)

FROMx$ksupr xrem vandalism session s

WHEREs.paddr (+) = x.addr

Andbitand (ksspaflg,1)! = 0

USERNAME STATUS ADDR KSLLAPSC KSLLAPSN KSLLASPO KSLLID1RKSD

ACTIVE 4C623BB8 99 427468 275EV1

ACTIVE 4C623040 9 2427444 0 1

ACTIVE 4C622A84 101 427480 274EV1

ACTIVE 4C6224C8 1 4827450 0 1

ACTIVE 4C621F0C 1 4827450 0 1

ACTIVE 4C6235FC 2 427468 0 1

SYS ACTIVE 4C624174 2 1527442 0

ACTIVE 4C62081C 1 4827440 0 1

ACTIVE 4C621394 1 4827440 0 1

ACTIVE 4C620DD8 11 2427476 0 1

ACTIVE 4C61F6E8 15 427610 0 1

ACTIVE 4C620260 222 2427450 0 1

ACTIVE 4C61FCA4 7 2527573 0 1

ACTIVE 4C61F12C 6 2527573 0 1

ACTIVE 4C61EB70 4 2427458 0 1

ACTIVE 4C61E5B4 1 4827440 0 1

ACTIVE 4C61DFF8 2 2427444 0 1

4C624730 0 0 0

4C621950 0 0 0

4C61DA3C 0 0 0

Or get the changed addr according to the following statement

Sys@AUSTIN > selectp.addrfromv$process pwherepid 1

2 minus

3 selects.paddrfromv$session s

ADDR

-

4C621950

4C624730

Kill the session at the operating system level

Find the process ID of the operating system corresponding to the session

Sys@AUSTIN > selectSPIDfrom v$processwhereADDRin (4C621950)

SPID

-

27573

27641

Use the kill command to kill the operating system-level process ID

Killsession-927573

Killsession-927641

4. Get the SID of the current session

SQL > selectuserenv ('sid') fromdual

USERENV ('SID')

-

six hundred and twenty seven

5. Multiple sessions need to be handled by kill

1. Find the information that needs to kill the session based on the given SID (user name), including which instance it is located in

Setlinesize160

Col program format a35

Col username format a18

Selectinst_id,saddr,sid,serial#,paddr,username,status,programfromgv$session

Wheresidin ('2731, 2734, 2720, 2678, 2685)

Andusername='CTICUST'

Orderbyinst_id

INST_ID SADDR SID SERIAL# PADDR USERNAME STATUS PROGRAM

100000003DAF8F870 2678 826500000003DBC6CA08 MSS4USR INACTIVE JDBC Thin Client

100000003DAF98E48 2685 8300000003DBC08510 MSS4USR ACTIVE JDBC Thin Client

100000003DAFC7B80 2720 500000003DBBEDA20 MSS4USR INACTIVE JDBC Thin Client

100000003DAFD66F8 2731 300000003DBBE9AE0SYS ACTIVE racgimon@svdg0028 (TNS V1-V3)

100000003DAFDA730 2734 1500000003DBBEC268 MSS4USR INACTIVE JDBC Thin Client

200000003DAFD66F8 2731 100000003DBBE92F8 ACTIVE oracle@svdg0029 (ARC0)

One of the above queries with a SID of 2731 is on node 2.

The node information of the RAC can also be obtained in the following ways to determine which node the session that needs kill is located.

Setlinesize160

Col HOST_NAME format a25

SQL > selectINSTANCE_NUMBER,INSTANCE_NAME,HOST_NAME,VERSION,STATUSfromgv$instanceorderby1

INSTANCE_NUMBER INSTANCE_NAME HOST_NAME VERSION STATUS

--

1O02WMT1A svd0051 10.2.0.4.0 OPEN

2O02WMT1B svd0052 10.2.0.4.0 OPEN

3O02WMT1C svd0053 10.2.0.4.0 OPEN

two。 Use the following query to generate the statement for killsession

Select'alter system kill session''| | sid | |','| | SERIAL# | |'| |'; 'from gv$session

Wheresidin ('2731, 2734, 2720, 2678, 2685)

Orderbyinst_id

Get the following kill session statement, according to the request, since all the session to be killed this time are located in node 1, log in to node 1 and execute the following statement

Altersystemkillsession'2678,8265'

Altersystemkillsession'2685,83'

Altersystemkillsession'2720,5'

Altersystemkillsession'2731,3'

Altersystemkillsession'2734,15'

Altersystemkillsession'2731,1';-- this command does not need to be executed, and the session is located in node 2.

★★

Cymbals

★★

Several other articles related to Kill Session

Original address: http://www.eygle.com/archives/2004/06/kill_session.html

Research on Kill session in Oracle

Author: eygle | select saddr,sid,serial#,paddr,username,status from v$session where username is not null SADDR SID SERIAL# PADDR USERNAME STATUS--542E0E6C 11 314 542B70E8 EYGLE INACTIVE542E5044 18 662 542B6D38 SYS ACTIVESQL > alter system kill session '11314' System altered.SQL > select saddr,sid,serial#,paddr,username,status from v$session where username is not null SADDR SID SERIAL# PADDR USERNAME STATUS--542E0E6C 11 314 542D6BD4 EYGLE KILLED542E5044 18 662 542B6D38 SYS ACTIVESQL > select saddr Sid,serial#,paddr,username,status from v$session where username is not null SADDR SID SERIAL# PADDR USERNAME STATUS--542E0E6C 11 314 542D6BD4 EYGLE KILLED542E2AA4 14397 542B7498 EQSP INACTIVE542E5044 18 662 542B6D38 SYS ACTIVESQL > alter system kill session '14397' System altered.SQL > select saddr,sid,serial#,paddr,username,status from v$session where username is not null SADDR SID SERIAL# PADDR USERNAME STATUS--542E0E6C 11 314 542D6BD4 EYGLE KILLED542E2AA4 14 397 542D6BD4 EQSP KILLED542E5044 18 662 542B6D38 SYS ACTIVE in this case A lot of times Resources cannot be released, we need to query spid to kill these processes at the operating system level.

But because v$session.paddr has changed at this time, we cannot get spid through v$session and v$process association

What else can I do?

Let's look at the following query:

SQL > SELECT s.usernameforce.status, 2x.ADDRJournal x.KSLLAPSCSCrex.KSLLAPSNrex.KSLLASPO1RCommx.KSLLASPOY x.KSLLRTYP, 3 decode (bitand (x.ksuprflg 2), 0 decode nullco1) 4 KSLLAPSCSCrex session 5 WHERE s.paddr (+) = x.addr 6 and bitand (ksspaflg,1)! = 0 USERNAME STATUS ADDR KSLLAPSC KSLLAPSN KSLLASPO KSLLID1R KS Dmuri -542B44A8 0 0 ACTIVE 542B4858 1 14 24069 01 ACTIVE 542B4C08 26 16 15901 0 1 ACTIVE 542B4FB8 7 46 24083 0 1 ACTIVE 542B5368 12 15 24081 0 1 ACTIVE 542B5718 15 46 24083 0 1 ACTIVE 542B5AC8 79 4 15923 0 1 ACTIVE 542B5E78 50 16 24085 0 1 ACTIVE 542B6228 754 15 24081 0 1 ACTIVE 542B65D8 1 14 24069 0 1 ACTIVE 542B6988 2 30 14571 0 1USERNAME STATUS ADDR KSLLAPSC KSLLAPSN KSLLASPO KSLLID1R KS Dmurmuri- -SYS ACTIVE 542B6D38 2 8 24071 0 542B70E8 1 15 24081 195 EV 542B7498 1 15 24081 195 EVSYS INACTIVE 542B7848 0 0 0SYS INACTIVE 542B7BF8 1 15 24081 195 EV16 rows selected.

We notice that the part marked in the scarlet letter is the process address of the process dropped by Kill.

To simplify, it is actually the following concepts:

SQL > select p.addr from v$process p where pid 1 2 minus 3 select s.paddr from v$session minus OK, now that we have the process address, we can find spid in v$process, and then we can use Kill or orakill to kill these processes at the system level.

In fact, I guess:

After kill session in Oracle, Oracle simply points the paddr of the relevant session to the same virtual address.

At this point, v$process and v$session lose their connection, and the process is interrupted.

Then Oracle waits for PMON to clear the Session. So it usually takes a long time to wait for a Session exit marked Killed.

If you try to execute the task again by the process of Kill at this time, you will immediately receive a prompt that the process is interrupted, process exits, and Oracle will immediately start PMON.

To clear the session. This is treated as an exception interrupt.

-The End-

★★

Cymbals

★★

Original address: http://space.itpub.net/22578826/viewspace-702988

In many cases, due to exceptions or program errors, individual processes take up a lot of system resources, and you need to end these processes. You can usually use the following command Kill process:

Alter system kill session 'sid,serial#'

However, this command releases resources very slowly. In order to release resources more quickly, we usually use the following steps to Kill the process:

1. First, the kill process at the operating system level

two。 Kill session inside the database

This usually allows you to quickly abort the process and release resources.

Today, I came across such a case where other friends kill session in the database, but it still has no effect for a long time:

[oracle@danaly ~] $sqlplus "/ as sysdba"

SQL*Plus: Release 10.2.0.1.0-Production on Thu Oct 27 11:09:50 2005

Copyright (c) 1982, 2005, Oracle. All rights reserved.

Connected to:

Oracle Database 10g Enterprise Edition Release 10.2.0.1.0-Production

With the Partitioning, Oracle Label Security, OLAP and Data Mining Scoring Engine options

SQL > select sid,username,status from v$session

SID USERNAME STATUS

....

154 SCOTT KILLED

...

30 rows selected.

Then follow the steps I mentioned earlier, first query to get the OS process number corresponding to the session:

SQL > select 'kill-9' | | spid from v$process where addr = (select paddr from v$session where sid=&sid)

Enter value for sid: 154

Old 1: select 'kill-9' | | spid from v$process where addr = (select paddr from v$session where sid=&sid)

New 1: select 'kill-9' | | spid from v$process where addr = (select paddr from v$session where sid=154)

'KILL-9' | | SPID

-

Kill-9 22702

SQL >!

Kill this process at the operating system level:

[oracle@danaly ~] $ps-ef | grep 22702

Oracle 22702 1 0 Oct25? 00:00:02 oracledanaly (LOCAL=NO)

Oracle 12082 12063 0 11:12 pts/1 00:00:00 grep 22702

[oracle@danaly] $kill-9 22702

[oracle@danaly ~] $ps-ef | grep 22702

Oracle 12088 12063 0 11:12 pts/1 00:00:00 grep 22702

[oracle@danaly ~] $exit

Exit

SQL > select sid,username,status from v$session

SID USERNAME STATUS

...

154 SCOTT KILLED

...

30 rows selected.

SQL > select sid,serial#,username from v$session where sid=154

SID SERIAL# USERNAME

154 56090 SCOTT

Kill the session in the database again, and specify the immediate option:

SQL > alter system kill session '154pm 56090' immediate

System altered.

SQL > select sid,serial#,username from v$session where sid=154

No rows selected

At this point, the process is quickly cleared.

★★

Cymbals

★★

Original address: http://space.itpub.net/17203031/viewspace-683786

Analysis of Kill conversation process

In actual development, we often need to forcibly disconnect the user's session. For example: transaction execution timeout, code deadloop, deadlock, or inadvertently lock the data table. This is a very practical method to use kill session. So, what happened to the system during kill session?

Basic knowledge

The user connects to the database and several objects are involved. The first is the listener, the local naming service (tnsname.ora) we often use, which is actually connected to the listener. However, for our connection process, the time to deal with listeners is still very short (see the author's other articles on listeners and connections for details). The second is Server Process, which is the operation representative of the client application on the database server. All operations on database instances, data files, and SGA are actually performed by Server Process. Finally, there is the background process (shadow process) represented by PMON, which is responsible for managing all aspects of the instance and ensuring the correct realization of all aspects of functions.

On another logical level, user session session is an important concept. In certain cases, we can say that the interaction between the user and the database is done in a continuous session. In a session, users can perform multiple transactions and can be idle (Inactive).

In any case, if we are forced to disconnect, release the session (active). The PMON background process will take the initiative to do recycling work (there is a delay in busy times). Recycling includes cleaning up session information, rolling back uncommitted transactions, and releasing Server Process resources (in dedicated mode).

In some cases, we can use alter system kill session 'sid, serial#'; to force users to disconnect manually. So what happened when Oracle was doing kill? Let me study it together.

Construction of experimental environment

Connected to Oracle Database11gEnterpriseEdition Release11.2.0.1.0

Connected as SYS

/ / View component version: log in using SYS

SQL > select * from v$version

BANNER

Oracle Database11gEnterpriseEdition Release11.2.0.1.0-Production

PL/SQL Release11.2.0.1.0-Production

CORE 11.2.0.1.0 Production

TNS for Linux: Version11.2.0.1.0-Production

NLSRTL Version11.2.0.1.0-Production

First of all, for simplicity, the author starts PL/SQL Developer and opens a Command window. After that, start a sqlplus window and observe the session corresponding to that window.

In the sqlplus window.

SQL > conn scott/tiger@wilson

Connected.

Observe the conversation and query v$session.

SQL > select saddr, sid, serial#, paddr, username, program,action,status from v$session where username in ('SYS','SCOTT')

SADDR SID SERIAL# PADDR USERNAME PROGRAM ACTION STATUS

--

382F0074 1 64 38BC6C94 SCOTT sqlplusw.exe INACTIVE

382B30C0 24 80 38BC61BC SYS plsqldev.exe Main session INACTIVE

3829B2F4 33 10 38BC8244 SYS plsqldev.exe Command Window ACTIVE

-New

You can see that there are three more session in the session. The session of the two usernames SYS is opened by PL/SQL Developer (see the previous blog content for the principle). The sqlplusw.exe opened by another SCOTT user is our experimental subject and its sid=1,Serial#=64 is found. The Server Process physical address for the session is 38BC6C94.

After that, we look for the corresponding server Process information from v$process.

SQL > select addr,pid,spid,username,serial#,program from v$process where addr='38BC6C94'

ADDR PID SPID USERNAME SERIAL# PROGRAM

38BC6C94 25 5803 oracle 23 oracle@oracle11g

We can see that the Server Process corresponding to the Scott user session SID=1, the process number is 5803 (PID is the internal number of the Oracle-related process, and SPID represents the operating system level).

Finally, we look at the operating system-level process information.

[oracle@oracle11g~] $ps-ef | grep LOCAL

Oracle 5780 1 0 05:47? 00:00:03 oraclewilson (LOCAL=NO)

Oracle 5788 1 0 05:48? 00:00:00 oraclewilson (LOCAL=NO)

Oracle 5803 1 0 05:50? 00:00:00 oraclewilson (LOCAL=NO) / / for that Server Process

Experiment with a kill session.

In the observation PL/SQL Developer, disconnect the SCOTT session.

SQL > alter system kill session '1mai 64'

System altered

The execution of the Kill operation completed with no error. So, has this session message really been deleted? Let's re-examine the v$session.

/ / found that the session SCOTT (SID=1,Serial#=64 information still exists)

SQL > select saddr, sid, serial#, paddr, username, program,action,status from v$session where username in ('SCOTT')

SADDR SID SERIAL# PADDR USERNAME PROGRAM ACTION STATUS

--

382F0074 1 64 38058594 SCOTT sqlplusw.exe KILLED

/ / searching for Server Process information according to the original Server Process address still exists

SQL > select addr,pid,spid,username,serial#,program from v$process where addr='38BC6C94'

ADDR PID SPID USERNAME SERIAL# PROGRAM

38BC6C94 25 5803 oracle 23 oracle@oracle11g

"something strange" happened, and we lost the conversation on kill. However, the session information still exists, which is different from the result just now: one is that the state of the session has changed to the KILLED state, indicating that it has been kill. The second is that the address of the corresponding Server Process has been modified to the location of 38058594.

Look at the v$process process view and find that the Server Process information that used to serve it still exists! So, does it really exist? Let's look at the operating system level:

[oracle@oracle11g~] $ps-ef | grep LOCAL

Oracle 5780 1 0 05:47? 00:00:03 oraclewilson (LOCAL=NO)

Oracle 5788 1 0 05:48? 00:00:00 oraclewilson (LOCAL=NO)

Oracle 5803 1 0 05:50? 00:00:00 oraclewilson (LOCAL=NO)

Looks like Server Process does exist. So what is this new Server Process address 38058594?

SQL > select addr,pid,spid,username,serial#,program from v$process where addr='38058594'

ADDR PID SPID USERNAME SERIAL# PROGRAM

To sum up the above: at kill, Oracle did two things. One is to change the state of the session by KILLED, which is equivalent to marking it. The second is to cut off the mapping association between the session information and the Server Process by modifying the Server Process address corresponding to the session to a virtual address. In addition, Server Process is not recycled.

After waiting for a while, I found that it was still the same! There is no active recycling action by PMON.

So, what if the session dropped by kill initiates an operation at this time?

On sqlplus:

SQL > select * from emp

Select * from emp

*

An error occurred on line 1:

ORA-00028: your session has been terminated

The disconnected session rejects the operation, informing that the session has been terminated.

At this point, can the system still see this session information?

SQL > select saddr, sid, serial#, paddr, username, program,action,status from v$session where username in ('SCOTT')

SADDR SID SERIAL# PADDR USERNAME PROGRAM ACTION STATUS

--

SQL > select addr,pid,spid,username,serial#,program from v$process where addr='38BC6C94'

ADDR PID SPID USERNAME SERIAL# PROGRAM

38BC6C94 25 5803 oracle 23 oracle@oracle11g

Conclusion: when we perform an operation in the original window and try to communicate with each other, it is rejected. Through the view query, it is found that the original session information of kill has been reclaimed. But the Server Process still exists on the view, but does not correspond to any session.

At the operating system level:

[oracle@oracle11g~] $ps-ef | grep LOCAL

Oracle 5780 1 0 05:47? 00:00:03 oraclewilson (LOCAL=NO)

Oracle 5788 1 0 05:48? 00:00:00 oraclewilson (LOCAL=NO)

Oracle 5803 1 0 05:50? 00:00:00 oraclewilson (LOCAL=NO)

The Server Process still exists and is not recycled.

Note: when we close the sqlplusw window, that is, when we close the client, we query again and find:

[oracle@oracle11g~] $ps-ef | grep LOCAL

Oracle 5780 1 0 05:47? 00:00:03 oraclewilson (LOCAL=NO)

Oracle 5788 1 0 05:48? 00:00:00 oraclewilson (LOCAL=NO)

When the Server Process is recycled, the v$process should naturally have no corresponding record.

SQL > select addr,pid,spid,username,serial#,program from v$process where addr='38BC6C94'

ADDR PID SPID USERNAME SERIAL# PROGRAM

38BC6C94 255956 oracle 26 oracle@oracle11g (J000)

Ah, why is there a record? Take a closer look: SPID has changed, not 5803, but 5956, a newly started process. Just using the address that has just been released.

Conclusion: the Server Process that resides on the database server will always exist until the client application is disconnected, not in Server Process communication. Notice how the client reconnects to the conn at this time, and the client communicates with the listener again to get a new Server Process redirection, instead of looking for the old Server Process. Once re-logged in, the old Server Process will be recycled and released.

To sum up: in the study of kill, we have figured out several aspects:

1. Alter system kill session: just mark the session as recyclable and sever the mapping relationship between the session and Server Process. There is no resource release and recovery.

2. Once a connection is attempted, PMON initiatively starts to clean up the kill session, while Oracle rejects the connection operation.

3. Server Process is a process loyal to the client, maintaining the connection between the two as long as the client is still starting. Server Process will not be recycled. Until the client actively stops communicating with Server Process, Server Process is released

★★

Cymbals

★★

Original address: http://space.itpub.net/17203031/viewspace-684039

Analysis of Kill conversation process (2)

This paper is a sequel to the author's Kill conversation process Analysis (http://space.itpub.net/17203031/viewspace-683786). Do some more in-depth analysis.

We know that when you need to force a session to disconnect, you can use the SQL command alter system kill session 'sid, serial#'; to force the session to disconnect. Where SID is the number of the session and Serial is the serial number of the session. These two values are the only flags for Oracle a session. Information about the session can be obtained by querying the v$session view.

According to our previous analysis, we think that the alter system kill session command has some shortcomings.

First, the kill session command is a forced disconnect at the session level. In essence, it is equivalent to a flag that cuts off the mapping between Server Process and conversation. The resources occupied by the session will not be directly recycled.

Secondly, the kill session command is not effective in some special situations. For example, when running in Oracle Job, the corresponding session object cannot be located.

Finally, in some emergencies, kill session still has some poor adaptability. For example, it is impossible to log in to the SQL command window at all.

So what is a more effective and direct approach than kill session? Kill operations for Server Process, that is, kill at the OS operating system level. Consider using this method when we can't use alter system kill session or when it doesn't work.

First, we look at some Linux environments. The selection is a dedicated connection mode.

When there is no connection, we check the connection.

[oracle@oracle11g~] $ps-ef | grepwilson | grep-v grep

Oracle 5583 1 0 02:52? 00:00:00 ora_pmon_wilson

Oracle 5585 1 0 02:52? 00:00:00 ora_vktm_wilson

…… (for reasons of length, some parts are omitted)

Oracle 5672 1 0 02:52? 00:00:00 ora_q001_wilson

Oracle 5700 1 0 02:57? 00:00:00 ora_smco_wilson

Oracle 5702 1 0 02:57? 00:00:00 ora_w000_wilson

When there is no connection, you can only see the running of multiple background process of the instance in the process list. At this point, we connect to a client. Check the progress: (set the filter to omit the space)

[oracle@oracle11g~] $ps-ef | grep LOCAL | grep-v grep

Oracle 5777 1 1 03:09? 00:00:00oraclewilson (LOCAL=NO)

[oracle@oracle11g~] $

A client is connected, and a Server Process is associated with it in dedicated mode. Here is a brief explanation of the meaning of each column.

The first column represents the executor Owner, and the second column is PID, which represents the system unique number of the process. The third column represents the PPID number of the parent process of the process, which is the process by which it was started. After that, there are two time values, which are the time from the start of the process to now and the time spent in CPU. The last column is usually the startup command line.

Among them, we are more concerned about PID. The unique identity of PID, if the process is forcibly terminated, we can forcibly end the session. At the same time, in the forced termination at the OS level, the resources corresponding to the process session can be directly reclaimed.

Under the Unix/Linux platform, you can use kill-9 PID to delete.

[oracle@oracle11g~] $kill-9 5777max / termination

[oracle@oracle11g~] $ps-ef | grep LOCAL | grep-v grep;// confirm

[oracle@oracle11g~] $

After use, the Server Process on the operating system process tree is cleared. At this point, if the connected client initiates a request.

/ / previously established connection

SQL > conn scott/tiger@wilson

Connected.

/ / after kill, try to connect

SQL > select count (*) from emp

Select count (*) from emp

*

An error occurred on line 1:

ORA-03113: file end of communication channel / / error!

Using the kill-9 command, we can also manage the background process process and kill and rebuild some problem processes, which is also a tool we often use.

Conclusion: on the Linux platform, you can use the kill-9 command when you need to do kill at the operating system level.

Next, we have some problems on the Windows platform. Due to the difference of architecture, the instance structure implemented by Oracle under Windows is not multi-process architecture, but multi-thread mode. Background process and Server Process under Unix/Linux become threads in an ORACLE.exe process. So, how do we deal with this situation?

To deal with this situation, Oracle provides a command for orakill.exe. This command is essentially the same as alter system kill session, but does not require a login to connect to the database. Just enter the thread number and SID.

In this way, the problem becomes if we get the thread number of a thread. On the Windows platform, there are many tools for viewing threads. Such as Qslice.exe, QuickSlice.exe and Pstat.

Syntax: orakill

Where sid is the sid number of Oracle. Thread is the number of the thread corresponding to the session. We can get the information from v$session and v$process.

SQL > select b.username, a.spid, b.username, b.sid, b.osuser, b.action

2 from v$process a, v$session b

3 where a.ADDR=b.PADDR and b. Usernamekeeper Sys'

USERNAME SPID USERNAME SID OSUSER ACTION

-

SYS 1464 SYS 152WWW-0E6111DFF74\ Administrator Command Window-New

SYS 632 SYS 141 WWW-0E6111DFF74\ Administrator Main session

Notice the SPID column of v$process. In the Linux/Unix environment, the SPID of this column represents the number of the Process. In the Windows environment, this column represents the thread number under the ORACLE.exe process.

We try to delete the command window thread (SPID=1464).

C:\ > orakill orcl 1464

Kill of thread id1464 ininstance orcl successfully signalled.

When the command window tries to connect again.

SQL > select count (*) from dba_objects

Warning: connection was lost and re-established

Description: the connection is cut off.

In addition, the orakill command can also be used at the SQL command prompt.

SQL > host orakill orcl 3140

Kill of thread id3140 ininstance orcl successfully signalled.

SQL >

Conclusion: on Windows platform, orakill tool can be used to kill session and server.

Finally, the author would like to emphasize. Both kill session and kill-9 operations are dangerous operations, especially in production environments. The reason is that if the background process, especially the core background process (PMON,SMON,DBWN,LGWR), is deleted by mistake, it is equivalent to the death of the instance, which is a major accident. Without confirming the need and without the permission of a professional, choose a less destructive solution to deal with it as much as possible.

The above is the example of Oracle thoroughly kill session that Xiaobian shared for you. If you happen to have similar doubts, you might as well refer to the above analysis to understand. If you want to know more about it, you are welcome to follow the industry information channel.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.