In latest version of database 18C instead of killing user session you can cancel its currently running or opened SQL statement using the ALTER SYSTEM CANCEL SQL statement.
Full syntax
ALTER SYSTEM CANCEL SQL 'SID, SERIAL, @INST_ID, SQL_ID';
Required parameters
- SID – Session ID
- SERIAL – Session serial number
Optional parameters
- INST_ID – Instance ID
- SQL_ID – SQL ID of the SQL statement
Example
Canceling a SQL statement having the session identifier of 15, session serial number of 20, instance 4
ALTER SYSTEM CANCEL SQL '15,20,@4';
Canceling a SQL statement having the session identifier of 20, session serial number of 51142, and SQL ID of 8vu7s907prbgr:
ALTER SYSTEM CANCEL SQL '20, 51142, 8vu7s907prbgr';
It means you can just cancel one of many opened cursors in a given session – it’s great option.
NOTE: When you cancel a DML statement, the statement is rolled back
Have a fun 🙂
Tomasz
Good feature, in this way, current session won’t be killed, only running SQLs will be killed.
But all other part is same as ‘kill session’.In case, of running DML for killed session, there also, it will roll back as like this ” Cancel SQL;”.