New Features for DBA’s

Subscribe to Posts by Email


 

Subscriber Count

    701

Disclaimer

All information is offered in good faith and in the hope that it may be of use for educational purpose and for Database community purpose, but is not guaranteed to be correct, up to date or suitable for any particular purpose. db.geeksinsight.com accepts no liability in respect of this information or its use. This site is independent of and does not represent Oracle Corporation in any way. Oracle does not officially sponsor, approve, or endorse this site or its content and if notify any such I am happy to remove. Product and company names mentioned in this website may be the trademarks of their respective owners and published here for informational purpose only. This is my personal blog. The views expressed on these pages are mine and learnt from other blogs and bloggers and to enhance and support the DBA community and this web blog does not represent the thoughts, intentions, plans or strategies of my current employer nor the Oracle and its affiliates or any other companies. And this website does not offer or take profit for providing these content and this is purely non-profit and for educational purpose only. If you see any issues with Content and copy write issues, I am happy to remove if you notify me. Contact Geek DBA Team, via geeksinsights@gmail.com

Pages
«  
  »

18c Database : Cancel SQL Instead of Killing Session

Hi

From 12cR2 and 18c, you can cancel the session instead of killing it which is long running and consuming time and resources.
The following is the syntax for cancelling a SQL statement:

ALTER SYSTEM CANCEL SQL 'SID, SERIAL, @INST_ID, SQL_ID';

If @INST_ID is not specified, the instance ID of the current session is used.
If SQL_ID is not specified, the currently running SQL statement in the specified session is terminated.

-Suresh

March 17th, 2018 | Tags: , , , | Category: 18c Database

5 comments to 18c Database : Cancel SQL Instead of Killing Session

  • Bhanu
    March 17, 2018 at 2:01 pm

    Good to know this . How does it different from killing session.

    • Geek DBA
      March 17, 2018 at 2:22 pm

      We are killing the SQL not the session, so the session remain intact, if that SQL is DML then they will be rolled back.
      -Suresh

  • Bhanu
    March 17, 2018 at 3:02 pm

    Thanks for making it clear .

  • Sasikumar
    March 17, 2018 at 4:56 pm

    What will happen in case of I’m running SQL script which contains multiple SQLs

    • Geek DBA
      March 17, 2018 at 8:23 pm

      As you see, the command itself contain a sql_id, if you omit all SQL will be cancelled for that Session, however at any point of time a session can run one given sql id not multiple though.