How to cancel long-running queries in Oracle Database

This is a short blog post about a tiny little feature that you are probably unaware of but can make your life easier.

When you work with databases, you sometimes may find yourself in a situation where you got a long-running query because you forgot a WHERE clause or you didn’t realize how many rows the table had, and you wished you could stop the query but not kill your entire database session in the process.
Say you have a transaction running, and now you want to verify your changes before committing but, oh no, you messed up the WHERE clause and the query goes off for a while. What are you going to do? Kill the session but have to redo the entire transaction again, or wait it out but it could be quite a while. Well, if you are on Oracle Database 18c or later, you will find yourself in luck. Say hello to ALTER SYSTEM CANCEL SQL!

Since Oracle Database 18c, you now got a new ALTER SYSTEM CANCEL SQL clause that allows another (privileged) session to cancel the currently running SQL statement without killing the session in the process (like ALTER SYSTEM KILL SESSION will do).

The syntax of CANCEL SQL is rather easy and similar to the KILL SESSION syntax, as you may expect:

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

The documentation describes these parameters as follows:

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

  • SID – Session ID
  • SERIAL – Session serial number

The following clauses are optional in an ALTER SYSTEM CANCEL SQL statement:

  • INST_ID – Instance ID
  • SQL_ID – SQL ID of the SQL statement

The SID and SERIAL are both the same SID and SERIAL# from the session that you cancel the SQL for.
The INST_ID identifies the database instance you want to cancel the SQL on. This is interesting when running the Oracle Database in a RAC cluster and say the SQL runs on a different node or the SQL in question is a parallel query across nodes.
The SQL_ID parameter is the SQL_ID of the SQL statement you want to cancel and provided as a failsafe mechanism. As the documentation says, “If SQL_ID is not specified, the currently running SQL statement in the specified session is terminated.” So when would you want to specify the SQL_ID then? Basically, whenever you want to make sure that you do not cancel the next SQL by accident. It could be that by the time you issue the ALTER SYSTEM CANCEL SQL statement, the long-running statement just finished. If that SQL was executed by you in a tool or command line, that’s probably not a big deal as there is no next SQL statement unless you type it in. However, if the statement was executed, say as part of a script, then the script will have moved on to the following SQL statement, or potentially quite a few SQL statements by the time you hit Enter. However likely that is or not, if you specify the SQL_ID, the ALTER SYSTEM CANCEL SQL command will only cancel the SQL statement with the specified id. If that SQL statement is no longer running, the command will do nothing.

Canceling a SQL statement

To cancel the SQL statement, just type in the command das specified above.

Session 1 (with long-running SQL statement):

SQL> INSERT INTO test SELECT * FROM very_big_table;

Session 2:

SQL> ALTER SYSTEM CANCEL SQL '181, 12538';

System altered.

Session 1:

SQL> INSERT INTO test SELECT * FROM very_big_table;
 INSERT INTO test SELECT * FROM very_big_table;
            *
ERROR at line 1:
ORA-01013: User requested cancel of current operation.

For more information, check out the documentation section on canceling SQL statements.

Introducing gvenzl/oracle-xe: Oracle Database XE Docker images

One of the things that kept me busy lately was experimenting with how much an Oracle XE database setup could be streamlined inside a Docker image for things like CI/CD consumption. Pretty much ever since I put together the first official build scripts for Oracle Database, people have asked for faster image pull and startup times to speed up their continuous integration tests. A lot of things have changed since then, and I’m happy that my engineering colleagues at Oracle have taken on the maintenance and further enhancements of Oracle’s official Docker build files and images, and integrated them into the internal processes.

Continue reading “Introducing gvenzl/oracle-xe: Oracle Database XE Docker images”

How to install Oracle Database 18c XE on Oracle Linux 8

It has been more than two and a half years since Oracle 18c XE has been released for Linux. Since then things have changed and one of these changes was the release of Oracle Linux 8 about nine months later. Unfortunately, installing Oracle 18c XE on Oracle Linux 8 is no longer quite as straightforward as it was with Linux 7 – a simple yum command. That is because the oracle-database-preinstall-18c is not provided for Oracle Linux 8. However, installing Oracle 18c XE on Oracle Linux 8 is still possible, it just requires a few more keystrokes.

tl;dr

Execute all commands as root user:

  1. curl -OL https://download.oracle.com/otn-pub/otn_software/db-express/oracle-database-xe-18c-1.0-1.x86_64.rpm
  2. dnf install -y /bin/bash /bin/sh /etc/redhat-release bc bind-utils binutils ethtool glibc glibc-devel initscripts ksh libaio libaio-devel libgcc libstdc++ libstdc++-devel make module-init-tools net-tools nfs-utils openssh-clients pam procps psmisc smartmontools sysstat unzip util-linux-ng xorg-x11-utils xorg-x11-xauth libnsl
  3. rpm -i --nodeps oracle-database-xe-18c-1.0-1.x86_64.rpm
Continue reading “How to install Oracle Database 18c XE on Oracle Linux 8”

Oracle Database client libraries for Java now on Maven Central

Oracle has published its Oracle Database JDBC client libraries on Maven Central. From Apache Mavennow on you can find Oracle Database related jar files under the com.oracle.database group id. You will find all libraries from version 11.2.0.4 (e.g. ojdbc6) to 19.3.0 (e.g. ojdbc10).

Going forward, Oracle will use Maven Central as one of the primary distribution mechanisms for Oracle Database Java client libraries, meaning that you will also be able to find new versions of these libraries on Maven Central in the future.

To get the latest Oracle Database JDBC driver, use the following dependency GAV in your Maven POM file:

<dependency>
    <groupId>com.oracle.database.jdbc</groupId>
    <artifactId>ojdbc10</artifactId>
    <version>19.3.0.0</version>
</dependency>

Continue reading “Oracle Database client libraries for Java now on Maven Central”

Building the world’s largest Raspberry Pi cluster

Oracle’s Raspberry Pi Supercomputer, the largest Raspberry Pi cluster known to exist, got awarded one of the Top 10 Raspberry Pi Projects of 2019 from Tom’s Hardware.

Here is its story.

Oracle Raspberry Pi Supercomputer
Image credit: ServeTheHome

Continue reading “Building the world’s largest Raspberry Pi cluster”