oracle彻底杀死killed会话,oracle killed会话不释放的问题

system disconnect session

The POST_TRANSACTION setting allows ongoing transactions to

complete before the session is disconnected.

If the session has no ongoing transactions, then this clause has

the same effect described for as KILL SESSION.

The IMMEDIATE setting disconnects the session and recovers the

entire session state immediately, without waiting for ongoing

transactions to complete.

If you also specify POST_TRANSACTION and the session has ongoing

transactions, then the IMMEDIATE keyword is ignored.

If you do not specify POST_TRANSACTION, or you specify

POST_TRANSACTION but the session has no ongoing transactions, then

this clause has the same effect as described for KILL SESSION

IMMEDIATE.

ORACLE建议的DCD解决方法

修改sqlnet.ora文件，新增expire_time=x(单位是分钟)

通过ALTER PROFILE DEFAULT LIMIT IDLE_TIME x; 命令修改,重启后生效。

通过OS杀进程终止会话

SELECT spid, osuser, s.program,

schemaname

FROM gv$process p, gv$session

s

WHERE p.addr = s.paddr;

1.UNIX

kill -9 5745

ps -ef | grep pmon_$ORACLE_SID | awk '{print

$2}' | xargs kill –9

#kill 一批会话

2. WINDOWS

orakill

如果会话已经在DB里killed，上面的SQL已经查不出spid,可以用下面的SQL查出SPID

select addr, pid, spid

FROM v$process p

where addr in (select

p.addr

from v$process p

where pid <> 1

minus

select s.paddr from v$session

s)；

Killing Oracle Sessions

There are a number of ways to kill rogue sessions both within

Oracle and externally.

Identify the Session to be Killed

Killing sessions can be very destructive if you kill the wrong

session, so be very careful when identifying the session to be

killed. If you kill a session belonging to a background process you

will cause an instance crash.

Identify the offending session using the [G]V$SESSION

and [G]V$PROCESS views as follows.

SET LINESIZE 100 COLUMN spid FORMAT A10

COLUMN username FORMAT A10

COLUMN program FORMAT A45

SELECT s.inst_id,

s.sid,

s.serial#,

p.spid,

s.username,

s.program

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

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

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

1 30 15 3859 TEST sqlplus@oel5-11gr2.localdomain (TNS V1-V3)

1 23 287 3834 SYS sqlplus@oel5-11gr2.localdomain (TNS V1-V3)

1 40 387 4663 oracle@oel5-11gr2.localdomain (J000)

1 38 125 4665 oracle@oel5-11gr2.localdomain (J001)

SQL>

The SID and SERIAL# values of the

relevant session can then be substituted into the commands in the

following sections.

ALTER SYSTEM KILL SESSION

The basic syntax for killing a session is shown below.

SQL> ALTER SYSTEM KILL SESSION

'sid,serial#';

In a RAC environment, you optionally specify the

INST_ID, shown when querying the

GV$SESSION view. This allows you to kill a session on

different RAC node.

SQL> ALTER SYSTEM KILL SESSION

'sid,serial#,@inst_id';

The KILL SESSION command doesn't actually kill the

session. It merely asks the session to kill itself. In some

situations, like waiting for a reply from a remote database or

rolling back transactions, the session will not kill itself

immediately and will wait for the current operation to complete. In

these cases the session will have a status of 'marked for kill'. It

will then be killed as soon as possible.

In addition to the syntax described above, you can add the

IMMEDIATE clause.

SQL> ALTER SYSTEM KILL SESSION

'sid,serial#' IMMEDIATE;

This does not affect the work performed by the command, but it

returns control back to the current session immediately, rather

than waiting for confirmation of the kill.

If the marked session persists for some time you may consider

killing the process at the operating system level. Before doing

this it's worth checking to see if it is performing a rollback. You

can do this by running this script (session_undo.sql).

If the USED_UREC value is decreasing for the session

in question you should leave it to complete the rollback rather

than killing the session at the operating system level.

ALTER SYSTEM DISCONNECT SESSION

The ALTER SYSTEM DISCONNECT SESSION syntax is an

alternative method for killing Oracle sessions. Unlike the

KILL SESSION command which asks the session to kill

itself, the DISCONNECT SESSION command kills the

dedicated server process (or virtual circuit when using Shared

Sever), which is equivalent to killing the server process from the

operating system. The basic syntax is similar to the KILL

SESSION command with the addition of the

POST_TRANSACTION clause. The SID and

SERIAL# values of the relevant session can be

substituted into one of the following statements.

SQL> ALTER SYSTEM DISCONNECT

SESSION 'sid,serial#' POST_TRANSACTION; SQL>

ALTER SYSTEM DISCONNECT SESSION 'sid,serial#'

IMMEDIATE;

The POST_TRANSACTION clause waits for ongoing

transactions to complete before disconnecting the session, while

the IMMEDIATE clause disconnects the session and

ongoing transactions are recovered immediately.

The POST_TRANSACTION and IMMEDIATE

clauses can be used together, but the documentation states that in

this case the IMMEDIATE clause is ignored. In

addition, the syntax diagram suggests both clauses are optional,

but in reality, one or both must be specified or you receive an

error.

SQL> alter system disconnect session '30,7'; alter

system disconnect session '30,7' * ERROR at line 1: ORA-02000:

missing POST_TRANSACTION or IMMEDIATE keyword

SQL>

This command means you should never need to switch to the operating

system to kill sessions, which reduces the chances of killing the

wrong process.

The Windows Approach

To kill the session on the Windows operating system, first identify

the session, then substitute the relevant SID and

SPID values into the following command issued from the

command line.

C:> orakill ORACLE_SID spid

The session thread should be killed immediately and all resources

released.

The UNIX Approach

To kill the session on UNIX or Linux operating systems, first

identify the session, then substitute the relevant

SPID into the following command.

% kill spid

If after a few minutes the process hasn't stopped, terminate the

session using the following.

% kill -9 spid

If in doubt check that the SPID matches the UNIX

PROCESSID shown using.

% ps -ef | grep ora

The session thread should be killed immediately and all resources

released.

For more information see: