Kill Sessions
In some situations, you might want to terminate current user sessions. For example, you might want to perform an administrative operation and need to terminate all non-administrative sessions.
This section describes the various aspects of terminating sessions, and contains the following topics:
When a session is terminated, the session's transaction is rolled back and resources (such as locks and memory areas) held by the session are immediately released and available to other sessions.
Terminate a current session using the SQL statement ALTER SYSTEM KILL SESSION.
The following statement terminates the session whose system identifier is 7 and serial number is 15:
ALTER SYSTEM KILL SESSION '7,15';
Identifying Which Session to Terminate
To identify which session to terminate, specify the session's index number and serial number. To identify the system identifier (sid) and serial number of a session, query the V$SESSION dynamic performance view.
The following query identifies all sessions for the user jward:
SELECT SID, SERIAL#, STATUS
FROM V$SESSION
WHERE USERNAME = 'JWARD';
SID SERIAL# STATUS
----- --------- --------
7 15 ACTIVE
12 63 INACTIVE
A session is ACTIVE when it is making a SQL call to Oracle. A session is INACTIVE if it is not making a SQL call to Oracle.
See Also: for a description of the status values for a session |
Terminating an Active Session
If a user session is processing a transaction (ACTIVE status) when it is terminated, the transaction is rolled back and the user immediately receives the following message:
ORA-00028: your session has been killed
If, after receiving the ORA-00028 message, a user submits additional statements before reconnecting to the database, Oracle returns the following message:
ORA-01012: not logged on
If an active session cannot be interrupted (it is performing network I/O or rolling back a transaction), the session cannot be terminated until the operation completes. In this case, the session holds all resources until it is terminated. Additionally, the session that issues the ALTER SYSTEM statement to terminate a session waits up to 60 seconds for the session to be terminated. If the operation that cannot be interrupted continues past one minute, the issuer of the ALTER SYSTEM statement receives a message indicating that the session has been "marked" to be terminated. A session marked to be terminated is indicated in V$SESSION with a status of KILLED and a server that is something other than PSEUDO.
Terminating an Inactive Session
If the session is not making a SQL call to Oracle (is INACTIVE) when it is terminated, the ORA-00028 message is not returned immediately. The message is not returned until the user subsequently attempts to use the terminated session.
When an inactive session has been terminated, STATUS in the V$SESSION view is KILLED. The row for the terminated session is removed from V$SESSION after the user attempts to use the session again and receives the ORA-00028 message.
In the following example, an inactive session is terminated. First, V$SESSION is queried to identify the SID and SERIAL# of the session, then the session is terminated.
SELECT SID,SERIAL#,STATUS,SERVER
FROM V$SESSION
WHERE USERNAME = 'JWARD';
SID SERIAL# STATUS SERVER
----- -------- --------- ---------
7 15 INACTIVE DEDICATED
12 63 INACTIVE DEDICATED
2 rows selected.
ALTER SYSTEM KILL SESSION '7,15';
Statement processed.
SELECT SID, SERIAL#, STATUS, SERVER
FROM V$SESSION
WHERE USERNAME = 'JWARD';
SID SERIAL# STATUS SERVER
----- -------- --------- ---------
7 15 KILLED PSEUDO
12 63 INACTIVE DEDICATED
2 rows selected.
來自 “ ITPUB部落格 ” ,連結:http://blog.itpub.net/35489/viewspace-84308/,如需轉載,請註明出處,否則將追究法律責任。
相關文章
- Kill all sessions of a specified user nameSession
- SESSIONS, PROCESSES, TRANSACTIONSSession
- Sessions & Processes parameterSession
- limit active sessionsMITSession
- 會話控制利器 gorilla/sessions會話GoSession
- Inhomogeneous deployment for replicated sessions is not allowed.Session
- How to get complete sessions informationSessionORM
- sessions,processes的監控方法Session
- Connections and Sessions (110)Session
- kill sessionSession
- 【kill session】Oracle 徹底 kill session(轉載)SessionOracle
- Kill Oracle Local=NOOracle
- kill oracle jobsOracle
- awk -- kill sessionSession
- Oracle kill sessionOracleSession
- linux Kill多個程式的妙招--案例:Kill(LOCAL=NO)Linux
- How To Automate Disconnection of Idle SessionsSession
- oracle profile sessions_per_user的用法OracleSession
- sessions 與 processes 的計算關係Session
- Alter system kill session and ORA-00031: session marked for killSession
- linux kill 命令Linux
- mysql批次kill sessionMySqlSession
- alter system kill sessionSession
- Kill 所有MySQL程式MySql
- linux kill & jobsLinux
- Oracle kill session scriptOracleSession
- oracle_kill_sessionOracleSession
- KILL SESSION 相關Session
- Oracle中Kill sessionOracleSession
- Oracle批量kill sessionOracleSession
- 定時kill長事務指令碼kill_long_session.sh指令碼Session
- ORA-00018:maximum number of sessions exceedeSession
- ORA-00018: maximum number of sessions exceededSession
- DIFFERENCES BETWEEN PROCESSES, SESSIONS AND CONNECTIONS(asktom)Session
- ORA-00018 maximum number of sessions exceededSession
- sqlserver kill long time lockSQLServer
- 程式命令ps/top/kill
- 【linux】kill命令模板Linux