As of Oracle 18c , you can not only abort a database session, but also just a single SQL command. This tip shows how it works.

There are situations where the execution of SQL commands takes too long and there is a desire to cancel them. Be it because the SQL command is incorrect, or the current data situation leads to this problem. If, in a database session, such a SQL command is to be executed and aborted externally, the database session itself must be aborted by Oracle 12c by identifying it via the SID and Serial #:

With the abort of the database session but lost more than just the running SQL command. The context of a database session, such as work steps already executed or set application parameters, is then no longer available. For this reason, there is often a desire to abort only the running SQL and continue to persist the database session. This is easily possible starting with Oracle 18c by using the following instead of the command shown above:

For example, in the affected database session, this looks like this:

If the aborted SQL command was executed as part of a script, the script is processed further. In the following example, a script contains two SQL commands and the first command is aborted by the administrator:

The command ALTER SYSTEM CANCEL SQL can be used with up to four parameters:

The first two parameters SID and SERIAL# must be specified. In a RAC environment, use the third parameter to specify the instance to which the SID and SERIAL# values ​​refer. If you do not specify anything here, the instance in which you call the command is meant. With the fourth parameter you can still specify the SQL_ID of the SQL command which should be aborted. If this parameter is not specified, the current command is aborted. The fourth parameter is useful if you want to make sure that the currently running command, which should be aborted, is not terminated by chance and that the next command is affected by its “CANCEL SQL“.

Conclusion

If you want to break an out-of-control SQL command, you can now execute it in such a way that the affected database session continues to run.

Source

Stay tuned for More articles on Oracle 18c 

Thank you for giving your valuable time to read the above information.

If you want to be updated with all our articles send us the Invitation or Follow us:

Skant Gupta’s LinkedIn: www.linkedin.com/in/skantali/

Joel Perez’s LinkedIn: Joel Perez’s Profile

Anuradha’s LinkedIn: Anuradha’s Profile

LinkedIn Group: Oracle Cloud DBAAS

Facebook Page: OracleHelp

Leave a Reply