数据库运维之--手工杀掉一个会话

来源：互联网发布：网络人netman 编辑：程序博客网时间：2024/05/17 01:06

如何手工杀掉一个oracle会话
Sometimes it may necessary for the DBA to terminate certain user sessions.A user session can be terminated by using the alter system command .The SID and SERIAL# from the v$session view are required to kill the session.

when you kill a session,first the session is terminated by Oracle to preventthe session from executing any more SQL statements.If any SQL statement is in progress when the session is terminated ,the statement is terminated and all changes are rolled back.The locks and other resources used by the session are also release.

SESSION A
SQL> l
1 select username,sid,serial#,status
2 from v$session
3* where username='CX'
SQL> run
1 select username,sid,serial#,status
2 from v$session
3* where username='CX'

USERNAME SID SERIAL# STATUS
------------------------------ ---------- ---------- --------
CX 1606 568 ACTIVE

SQL>
SQL>
SQL>

SQL> select count(*) from tabs;
select count(*) from tabs
*
ERROR at line 1:
ORA-00028: your session has been killed

SESSION B
$
$ sqlplus / as sysdba

SQL*Plus: Release 10.2.0.4.0 - Production on Thu Jan 21 17:22:09 2010

Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit Production
With the Partitioning, Real Application Clusters, OLAP, Data Mining
and Real Application Testing options

SQL>
SQL>
SQL>
SQL> alter system kill session '1606,568';

System altered.

SQL>

|||||||||||||||||||||||||||||||||||

If you want the user to complete the current transaction and then terminate their session,you can use the disconnect session option of the ALTER SYSTEM command.
If the session has no pending or active transactions ,this command has the same effect as kill session.Here is an example:

Alter system disconnect session '9,3' POST_TRANSATION;
You can also use the IMMEDIATE clause with the KILL SESSION or DISCONNECT
SESSION to roll back ongoing transactions,release all session locks,recover
the entire session state,and return control to you immediately.Here are
some examples.

Alter system disconnect session '9,3' immediate;
Alter system kill session '9,3' immediate;