Oracle 18c new feature: ALTER SYSTEM CANCEL SQL

In the past when you wanted to terminate an Oracle database session that is causing a problem we used the classical SQL command:

SQL> ALTER SYSTEM KILL SESSION ‘sid,serial#’;

In 18c a new command is introduced , that will cancel the SQL statement being executed in the session without actually terminating the session:

SQL> ALTER SYSTEM CANCEL SQL ‘SID, SERIAL,SQLID’;

To simulate:

To find the 3 values SID, SERIAL, and SQLID you can use the following query:

SELECT s.inst_id,

s.sid,

s.serial#,

s.sql_id,

p.spid,

s.username,

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’;

PIC1

SQL> ALTER SYSTEM CANCEL SQL ‘874,51416,a5ks9fhw2v9s1’;

PIC2

The user session will generate the following error:

ORA-01013: user requested cancel of current operation

However, the user will still continue working fine (his session will not be terminated as shown below)

PIC3

 

 

 

 

 

 

Presenting at the Saudi National GIS Symposium in 2014

last year i have presented at the ” 9th Saudi National GIS Symposium“.  In this conference i talked about GIS Database Security which is a unique topic, as it mixes IT & GIS fields in enterprise systems.

The presentation tackled many topics such as : the architecture of Security layers in enterprise IT GIS system, implementing best security practices in GIS database using Oracle Security products (Network Encryption, Label Security, Access Controls).

عماد الموسى

عماد الموسى - مؤتمر

http://www.saudigis.org/