查看和终止 Oracle 连接会话以及正在执行的 SQL 语句

文章目录

        查看当前连接的会话
        终止正在执行的 SQL 语句
        终止指定的连接会话
            通过 SQL 命令终止连接会话
            通过操作系统命令终止连接会话
        总结

作为 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 视图获得。

更多请见：http://www.mark-to-win.com/tutorial/51521.html