Oracle kill session

Kill session is one of the common problems that DBAs encounter. If you kill a session that you do not want to kill, it will be destructive, so try to avoid such an error as much as possible. You should also note that,

If the kill session belongs to the Oracle background process, the database instance may be down.

Generally, the Oracle session process does not need to be killed from the operating system level, but this is not always the case. The following description describes how to kill the session at the Oracle level and how to kill the process at the operating system level.

1. obtain information about the kill session (use the V $ SESSION and GV $ SESSION Views)

Set linesize 180

COLUMN spid FORMAT A10

COLUMN username FORMAT A10

COLUMN program FORMAT A40

SELECT s. inst_id,

S. sid,

S. serial #,

P. spid,

S. username,

S. program,

S. paddr,

S. STATUS

FROM gv $ session s

JOIN gv $ process p ON p. addr = s. paddr AND p. inst_id = s. inst_id

WHERE s. type! = 'Background ';

INST_ID sid serial # SPID USERNAME PROGRAM PADDR STATUS

---------------------------------------------------------------------------------------------------------------

1 146 23 27573 TEST sqlplus @ oracle10g (TNS V1-V3) 4C621950 INACTIVE

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

1 144 42 27641 SCOTT sqlplus @ oracle10g (TNS V1-V3) 4C624730 INACTIVE

Ii. Run the alter system kill session command

Syntax:

SQL> ALTER SYSTEM KILL SESSION 'sid, serial #';

SQL> ALTER SYSTEM KILL SESSION 'sid, serial # 'immediate;

For the kill session in the RAC environment, you need to find out the node where the session to be killed is located. You can query the GV $ SESSION view to obtain it.

Kill a session by killing it. In some cases, kill sessions cannot be killed immediately due to large transactions or long SQL statements. For this situation

You will receive the "marked for kill" prompt (as shown below). Once the current transaction or operation of the session is completed, the session will be killed immediately.

Alter system kill session '2017 71'

*

ERROR at line 1:

ORA-00031: session marked for kill

In the following operations, session 146,144 will be killed.

Sys @ AUSTIN> alter system kill session '2017, 23 ';

System altered.

Sys @ AUSTIN> alter system kill session '2014, 42 ';

System altered.

Sys @ AUSTIN> select inst_id, saddr, sid, serial #, paddr, username, status, program from gv $ session where username is not null;

INST_ID saddr sid serial # PADDR USERNAME STATUS PROGRAM

-------------------------------------------------------------------------------------------------------------

1 4C70BF04 144 42 4C6545A0 scott killed sqlplus @ oracle10g (TNS V1-V3)

1 4C70E6B4 146 23 4C6545A0 test killed sqlplus @ oracle10g (TNS V1-V3)

1 4C71FC84 160 17 4C624174 sys active sqlplus @ oracle10g (TNS V1-V3)

Note: In the query, we can see that the PADDR address of the session to be killed has changed. Refer to the red font in the query results. If multiple sessions are killed, the PADDR of multiple sessions

Changed to the same process address.

Use the following statement to retrieve the previous ADDR address that was killed.

SELECT s. username, s. status,

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

Decode (bitand (x. ksuprflg, 2), 0, null, 1)

FROM x $ ksupr x, v $ session s

WHERE s. paddr (+) = x. addr

And bitand (ksspaflg, 1 )! = 0;

Username status addr ksllapsc ksllapsn ksllaspo ksl1_1r KS D

-----------------------------------------------------------------------

ACTIVE 4C623BB8 99 4 27468 275 EV 1

ACTIVE 4C623040 9 24 27444 0 1

ACTIVE 4C622A84 101 4 27480 274 EV 1

ACTIVE 4C6224C8 1 48 27450 0 1

ACTIVE 4C621F0C 1 48 27450 0 1

ACTIVE 4C6235FC 2 4 27468 0 1

Sys active 4C624174 2 15 27442 0

ACTIVE 4C62081C 1 48 27440 0 1

ACTIVE 4C621394 1 48 27440 0 1

ACTIVE 4C620DD8 11 24 27476 0 1

ACTIVE 4c61f6e8-15 4 27610 0 1

ACTIVE 4C620260 222 24 27450 0 1

ACTIVE 4C61FCA4 7 25 27573 0 1

ACTIVE 4C61F12C 6 25 27573 0 1

ACTIVE 4C61EB70 4 24 27458 0 1

ACTIVE 4C61E5B4 1 48 27440 0 1

ACTIVE 4c61dff82 24 27444 0 1

4C624730 0 0

4C621950 0 0 0

4C61DA3C 0 0 0

Or use the following statement to obtain the changed addr.

Sys @ AUSTIN> select p. addr from v $ process p where pid <> 1

2 minus

3 select s. paddr from v $ session s;

ADDR

--------

4C621950

4C624730

3. Killing sessions at the operating system level

Process ID of the operating system corresponding to the session

Sys @ AUSTIN> select SPID from v $ process where ADDR in ('4c621950', '4c624730 ');

SPID

----------

27573

27641

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

Kill session-9 27573

Kill session-9 27641

4. Obtain the SID of the current session

SQL> select userenv ('sid ') from dual;

USERENV ('sid ')

--------------

627

5. How to kill multiple sessions

1. Find the information of the session to be killed based on the specified SID (user name), including the instance on which the session is located.

Set linesize 160

Col program format a35

Col username format a18

Select inst_id, saddr, sid, serial #, paddr, username, status, program from gv $ session

Where sid in ('123', '123', '123', '123', '123 ')

And username = 'cticust'

Order by inst_id;

INST_ID saddr sid serial # PADDR USERNAME STATUS PROGRAM

-------------------------------------------------------------------------------------------------------------------

1 100000003daf870 2678 8265 00000003DBC6CA08 MSS4USR inactive jdbc Thin Client

1 100000003daf98e48 2685 83 00000003DBC08510 MSS4USR active jdbc Thin Client

1 100000003dafc7b80 2720 5 00000003DBBEDA20 MSS4USR inactive jdbc Thin Client

1 100000003dafd66f8 2731 3 100000003dbbe9ae0 sys active racgimon @ svdg0028 (TNS V1-V3)

1 100000003dafda730 2734 15 00000003DBBEC268 MSS4USR inactive jdbc Thin Client

2) 0003dafd66f8 2731 1 0003dbbe92f8 ACTIVE oracle @ svdg0029 (ARC0)

In the preceding query, A 2731 SID is located on node 2.

You can also obtain the node information of RAC in the following way to determine the node where the session to be killed is located.

Set linesize 160

Col HOST_NAME format a25

SQL> select INSTANCE_NUMBER, INSTANCE_NAME, HOST_NAME, VERSION, STATUS from gv $ instance order by 1;

INSTANCE_NUMBER INSTANCE_NAME HOST_NAME VERSION STATUS

-------------------------------------------------------------------------------------

1 O02WMT1A svd0051 10.2.0.4.0 OPEN

2 O02WMT1B svd0052 10.2.0.4.0 OPEN

3 O02WMT1C svd0053 10.2.0.4.0 OPEN

2. Use the following query to generate the kill session Statement.

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

Where sid in ('123', '123', '123', '123', '123 ')

Order by inst_id;

Obtain the following kill session Statement. As required, because all the sessions to be killed this time are in node 1, log on to node 1 and execute the following statement.

Alter system kill session '2010 5 ';

Alter system kill session '2014, 83 ';

Alter system kill session '2017, 5 ';

Alter system kill session '2017, 3 ';

Alter system kill session '2017, 15 ';

Alter system kill session '2017, 1'; -- this command does not need to be executed, and this session is located at Node 2.


Oracle video tutorial follow: http://u.youku.com/user_video/id_UMzAzMjkxMjE2.html