18c新特性:alter system cancel sql

联系:手机(+86 13429648788) QQ(107644445)QQ咨询惜分飞

标题:18c新特性:alter system cancel sql

作者:惜分飞©版权所有[未经本人同意,不得以任何形式转载,否则有进一步追究法律责任的权利.]

根据18c官方描述cancel sql功能是在18c中引起,但是实测发现在oracle 12.2中已经有了cancel sql功能,可以实现终止掉某个sql的当前sql正在执行的sql语句,而不是传统的直接kill某个会话.ALTER SYSTEM CANCEL SQL语句有四个参数分别为:
cancel_sql

--会话1
SQL> set lines 150
SQL> select * from v$version;

BANNER                                                                               CON_ID
-------------------------------------------------------------------------------- ----------
Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production              0
PL/SQL Release 12.2.0.1.0 - Production                                                    0
CORE    12.2.0.1.0      Production                                                        0
TNS for Linux: Version 12.2.0.1.0 - Production                                            0
NLSRTL Version 12.2.0.1.0 - Production                                                    0


SQL> select sid, serial# from v$session where sid in
  2  (select  sid from v$mystat where rownum=1);

       SID    SERIAL#
---------- ----------
       278       4019

SQL> create table t_xifenfei tablespace users as select * from dba_source;

Table created.

SQL> insert into t_xifenfei select * from t_xifenfei;

274132 rows created.                    <<===没有提交

SQL> select count(*)from t_xifenfei;

  COUNT(*)
----------
    548264

SQL> insert into t_xifenfei select * from t_xifenfei;

548264 rows created.     <<===没有提交

SQL> select count(*)from t_xifenfei;

  COUNT(*)
----------
   1096528

SQL> insert into t_xifenfei select * from t_xifenfei;


--会话2
SQL> select count(*)from t_xifenfei;

  COUNT(*)
----------
    274132

SQL> alter system cancel sql '278,4019';

System altered.

SQL> select count(*)from t_xifenfei;

  COUNT(*)
----------
    274132

--会话1
SQL> insert into t_xifenfei select * from t_xifenfei;
insert into t_xifenfei select * from t_xifenfei
*
ERROR at line 1:
ORA-01013: user requested cancel of current operation

SQL> select count(*)from t_xifenfei;

  COUNT(*)
----------
   1096528

这里可以看到会话1的最后一个insert被cancel,但是前面两个没有提交的insert没有被回滚/提交,看到了cancel sql的功能的实现.

此条目发表在 ORACLE 12C, ORACLE 18C 分类目录,贴了 , , 标签。将固定链接加入收藏夹。

发表评论

电子邮件地址不会被公开。 必填项已用 * 标注

您可以使用这些 HTML 标签和属性: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>