个人中心
退出
查看和终止 Oracle 连接会话以及正在执行的 SQL 语句
作者: 不剪发的Tony老师
毕业于北京航空航天大学，十多年数据库管理与开发经验，目前在一家全球性的金融公司从事数据库架构设计。CSDN学院签约讲师以及GitChat专栏作者。csdn上的博客收藏于以下地址：https://tonydong.blog.csdn.net
文章目录
查看当前连接的会话
终止正在执行的 SQL 语句
终止指定的连接会话
通过 SQL 命令终止连接会话
通过操作系统命令终止连接会话
总结
大家好，我是只谈技术不剪发的 Tony 老师。作为 DBA，有时候为了执行管理操作需要终止当前的用户会话；当某个 SQL
语句长时间运行并占用大量系统资源时，我们也可能需要手动终止正在执行的语句。因此，今天我们来讨论一下如何查看和终止 Oracle
的连接会话，以及正在执行的 SQL 语句。
查看当前连接的会话
Oracle 系统视图 V$SESSION 和对应的 GV$SESSION（全局视图主要用于 RAC 环境）提供了当前所有会话的相关信息。例如：
select inst_id, sid, serial#, username,
status, server, program, sql_id
from gv$session
where type != 'BACKGROUND'
and username is not null
and nvl(osuser,'x') <> 'SYSTEM';
INST_ID|SID|SERIAL#|USERNAME|STATUS |SERVER |PROGRAM |SQL_ID |
-------|---|-------|--------|--------|---------|----------------------------------------|-------------|
1|143| 9323|TONY |ACTIVE |DEDICATED|DBeaver 7.1.3 - SQLEditor <Script-2.sql>|9b1bawv3upw70|
1|265| 24368|TONY |INACTIVE|DEDICATED|DBeaver 7.1.3 - Main |3fnstj6ydy4xk|
1|399| 16226|TONY |INACTIVE|DEDICATED|sqlplus@sqlhost (TNS V1-V3) | |
以上查询只返回了用户的连接会话，不包括后台进程的连接会话。其中，
inst_id 是会话所在的实例 id；
sid 是会话标识符，serial# 是会话序列号，两者唯一确定实例中的会话；
username 是连接数据库的用户；
status 是会话的状态，例如 ACTIVE、INACTIVE、KILLED 等；
server 是服务器的类型，例如 DEDICATED、SHARED 等；
program 是会话客户端的应用程序名；
sql_id 是正在执行的 SQL 语句 id。
📝关于 V$SESSION 视图的更多字段可以参考官方文档。
终止正在执行的 SQL 语句
从 Oracle 18c 开始增加了ALTER SYSTEM CANCEL SQL命令，可以用于终止指定会话中正在执行的 SQL 语句，但不会终止连接会话。该语句的语法如下：
ALTER SYSTEM CANCEL SQL 'sid, serial# [, @inst_id][, sql_id]';
其中，
sid 是会话标识符；
serial# 是会话序列号；
inst_id 是实例 id，如果省略表示当前实例；
sql_id 是 SQL 标识符，如果省略表示终止该会话中正在运行的语句。
以上输入信息都可以通过查询上文中的 V$SESSION 或者 GV$SESSION 视图获得。使用该语句可以终止高负载的 SQL 语句；如果终止的是 DML 语句，该语句将会被回滚。
我们首先在一个会话（sid = 399、serial# = 16226）中执行以下语句：
SQL> EXEC DBMS_LOCK.sleep(300);
该会话将会睡眠 300 秒，然后在另一个会话中终止该语句（可以再次查询 GV$SESSION 视图获得相关信息）：
-- 另一个会话
ALTER SYSTEM CANCEL SQL '399, 16226';
此时，上面的第一个会话返回以下错误：
SQL> EXEC DBMS_LOCK.sleep(300);
BEGIN DBMS_LOCK.sleep(300); END;
*
ERROR at line 1:
ORA-01013: user requested cancel of current operation
ORA-06512: at "SYS.DBMS_LOCK", line 205
ORA-06512: at line 1
📝如果想要查看会话中正在执行的 SQL 语句内容，可以通过 sql_id 字段关联查询系统视图 V$SQL 或者 GV$SQL。
终止指定的连接会话
通过 SQL 命令终止连接会话
⚠️如果终止了错误的会话，可能会造成严重的影响，因此在确定要终止的会话时要格外小心。如果终止了后台进程对应的会话，将会导致实例崩溃。
当会话终止被时，该会话的所有活动事务都会被回滚，并且该会话持有的资源（例如锁和内存）将立即释放。终止用户会话的基于命令如下：
ALTER SYSTEM KILL SESSION 'sid, serial# [, @inst_id]';
其中，
sid 是会话标识符；
serial# 是会话序列号；
inst_id 是实例 id，如果省略表示当前实例。
以上输入信息都可以通过查询上文中的 V$SESSION 或者 GV$SESSION 视图获得。
我们使用以下语句终止指定会话（sid = 399、serial# = 16226）：
alter system kill session '399, 16226';
此时再次查询 gv$session 可以看到该会话的状态为 KILLED：
select inst_id, sid, serial#, username,
status, server, program, sql_id
from gv$session s
where type != 'BACKGROUND'
and username is not null
and nvl(osuser,'x') <> 'SYSTEM';
INST_ID|SID|SERIAL#|USERNAME|STATUS |SERVER |PROGRAM |SQL_ID |
-------|---|-------|--------|--------|---------|----------------------------------------|-------------|
1|143| 9323|TONY |ACTIVE |DEDICATED|DBeaver 7.1.3 - SQLEditor <Script-2.sql>|b3upx6u952qfb|
1|265| 24368|TONY |INACTIVE|DEDICATED|DBeaver 7.1.3 - Main |3fnstj6ydy4xk|
1|399| 16226|TONY |KILLED |DEDICATED|sqlplus@sqlhost (TNS V1-V3) | |
如果我们在被终止的会话中执行操作，将会返回以下错误：
SQL> select 1 from dual;
select 1 from dual
*
ERROR at line 1:
ORA-00028: your session has been killed
SQL> select 1 from dual;
select 1 from dual
*
ERROR at line 1:
ORA-01012: not logged on
Process ID: 27994
Session ID: 399 Serial number: 16226
ALTER SYSTEM KILL SESSION 语句实际上并不会立即终止会话，它只是请求会话自己终止。在某些情况下，例如等待远程数据库的响应或者事务的回滚时，会话不会立即终止，而是等待当前操作完成。此时会话的状态为 KILLED，表示“标记为终止”。
ALTER SYSTEM KILL SESSION 命令支持使用 IMMEDIATE 选项：
ALTER SYSTEM KILL SESSION 'sid, serial# [, @inst_id]' IMMEDIATE;
IMMEDIATE 选项不会改变该命令执行的操作，但是会立即返回，而不会等待会话被终止。
除了以上命令之外，ALTER SYSTEM DISCONNECT SESSION 语句也可以用于终止会话：
DISCONNECT SESSION 'sid, serial#' POST_TRANSACTION；
DISCONNECT SESSION 'sid, serial#' IMMEDIATE；
该命令与 ALTER SYSTEM KILL SESSION 语句不同之处在于它会终止专用服务器进程（或者共享服务器进程中的虚拟链路，相当于通过操作系命令终止服务器进程。其中 POST_TRANSACTION 选项表示等待正在执行中的事务结束后终止连接，IMMEDIATE 选项表示立即断开连接并且回滚事务。
对于上面终止会话的示例（如果没有执行的话），也可以使用以下语句实现相同的目的：
alter system disconnect session '399, 16226' post_transaction;
这种方式可以避免通过操作系统命令终止会话，减少了错误操作的风险。
通过操作系统命令终止连接会话
如果想要通过操作系统命令终止会话，首先需要找出会话对应的操作系统进程 id，可以通过 V$PROCESS 或者 GV$PROCESS 进行查看：
select s.inst_id,
s.sid,
s.serial#,
s.sql_id,
p.spid,
s.username,
s.status,
s.server,
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'
and s.username is not null
and nvl(s.osuser,'x') <> 'SYSTEM';
INST_ID|SID|SERIAL#|SQL_ID |SPID |USERNAME|STATUS |SERVER |PROGRAM |
-------|---|-------|-------------|-----|--------|--------|---------|----------------------------------------|
1|399| 22691| |12195|TONY |INACTIVE|DEDICATED|sqlplus@sqlhost (TNS V1-V3) |
1|265| 24368|3fnstj6ydy4xk|9152 |TONY |INACTIVE|DEDICATED|DBeaver 7.1.3 - Main |
1|143| 9323|7yyukqn3pkt89|9193 |TONY |ACTIVE |DEDICATED|DBeaver 7.1.3 - SQLEditor <Script-2.sql>|
其中，spid 就是会话对应的操作系统进程 id。
有了进程 id ，就可以通过操作系统命令终止相应的进程。对于 Unix/Linux 操作系统，可以使用 kill 命令终止进程：
kill spid
kill -9 spid
其中，-9 表示强制终止。在终止进程之前也可以使用 ps 命令进行二次确认：
ps -ef | grep ora
我们使用以下命令终止会话（sid = 399、serial# = 22691）对应的操作系统进程（12195）：
[root@sqlhost ~]# kill 12195
此时，如果在该会话中执行任何语句都会返回错误：
SQL> select 1 from dual;
select 1 from dual
*
ERROR at line 1:
ORA-03135: connection lost contact
Process ID: 12195
Session ID: 399 Serial number: 22691
⚠️Oracle 12c 开始支持 Unix/Linux 环境下的多线程模型；如果使用了这种模型，不要使用操作系统命令终止会话，否则会一次终止多个会话。
对于 Windows 操作系统，可以使用 orakill 命令行工具终止进程：
orakill sid spid
其中，sid 是会话标识符，spid 是会话对应的进程 id。
总结
Oracle 提供了几种终止连接会话/进程和运行中的 SQL 语句的方法。首先可以通过 GV$SESSION 和 GV$PROCESS 等视图查看会话和对应的系统进程信息，然后使用 ALTER SYSTEM KILL SESSION 或者 ALTER SYSTEM DISCONNECT SESSION 命令终止连接会话，或者利用操作系统命令（kill、orakill）终止相应的进程；另外，也可以使用 ALTER SYSTEM CANCEL SQL 命令终止正在运行的 SQL 语句。
如果觉得文章对你有用，欢迎关注❤️、评论📝、点赞👍！
分类导航