• Skip to primary navigation
  • Skip to main content
  • Skip to primary sidebar
  • Skip to footer navigation

The Geek Diary

  • OS
    • Linux
    • CentOS/RHEL
    • Solaris
    • Oracle Linux
    • VCS
  • Interview Questions
  • Database
    • oracle
    • oracle 12c
    • ASM
    • mysql
    • MariaDB
  • DevOps
    • Docker
    • Shell Scripting
  • Big Data
    • Hadoop
    • Cloudera
    • Hortonworks HDP

How To Cancel A SQL Query In Oracle Database 18c

by admin

The idea of this post is to explain the steps to cancel a query in Oracle Database 18c. In this version, you have the option to cancel just the query instead of terminating the session.

The following clauses are required in an “ALTER SYSTEM CANCEL” SQL statement:

  1. SID – Session ID
  2. SERIAL – Session serial number

Here is an example:

1. Find SID and SERIAL#

Let us first find the SID and SERIAL# for the query to be cancelled.

##### Session 1 #####
SQL> select sid, serial# from v$session where sid = userenv('SID');

       SID    SERIAL#
---------- ----------
       262      28686

SQL>  select count(*) from test,test;

2. Cancel the query

Use the SQL statement “ALTER SYSTEM CANCEL” to cancel the query using the SID and SERIAL# found in step 1 above.

##### Session 2 #####
SQL>  alter system cancel sql '262,28686';

System altered.

3. Verify

For the Session 1, you can verify if the SQL query has been killed. You should see the output below for the query fired in the step 1:

SQL>  select count(*) from test,test;
  select count(*) from test,test
                      *
ERROR at line 1:
ORA-01013: user requested cancel of current operation.
Note: Using this method you do not need to kill the whole session.

Filed Under: 18c, oracle

Some more articles you might also be interested in …

  1. What Happens and What to Do when the SPFILE has been Manually Modified
  2. How to Perform Manual Archiving in Oracle Database
  3. How to Use Udev Rules to Create oracleasm Disks in CentOS/RHEL 8
  4. Oracle ASM : Shell script to map physical disk devices to ASMLIB disks
  5. Manage ASM Audit Files with syslog – configure lograte and auditing
  6. ORA-12518: TNS:listener Could Not Hand Off Client Connection
  7. Oracle Data Guard – Tempfiles created at primary are not automatically created at standby
  8. Oracle 12.2 : RMAN Cross-Platform Transport of PDB into Destination CDB
  9. How to quiesce an Oracle 12c RAC database
  10. Oracle RAC Interview Questions – Coherence and Split-Brain

You May Also Like

Primary Sidebar

Recent Posts

  • aws ec2: CLI for AWS EC2 (Command Examples)
  • aws cur – Create, query, and delete AWS usage report definitions (Command Examples)
  • aws configure – Manage configuration for the AWS CLI (Command Examples)
  • aws cognito-idp: Manage Amazon Cognito user pool and its users and groups using the CLI

© 2023 · The Geek Diary

  • Archives
  • Contact Us
  • Copyright