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: