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.

Manually publish images to GitHub Container Registry (GHCR)

This is just a short little post on how to manually publish images to GitHub Container Registry (GHCR). Looking at the documentation, the usual way to go seems to be to use GitHub Actions and automatically build and publish images onto the registry. However, I do have one use case where images aren’t built via GitHub Actions but offline on a machine of mine, yet I still want to make the images available via ghcr.io. It took me longer than it should have to figure out how to manually authenticate to ghcr.io, so here are the steps.

Generate Personal Access Token

At first, you might think that you can authenticate with GHCR just by simply using your GitHub username and password. That is not the case. To authenticate you will need a token, a so-called personal access token (PAT). Head over to GitHub and click on the drop-down of your user on the top right. From there, head into Settings. Then click on Developer settings at the bottom of the left side. Now click on Personal access token and from there Generate new token:

Next, give your token a meaningful name, choose an expiration of your liking and check the write:packages scope, then hit Generate token at the bottom of the page:

Make sure you copy your token, you will not be able to see it again!

Login to ghcr.io

The next step is rather straightforward. Head into the command line (or whatever tool you are using) and authenticate with ghcr.io via the usual docker login ghcr.io or similar methods. Your username is your GitHub username or the organization that you want to publish the image to and your password is the personal access token. On successful login, you should see something like this:

$ podman login ghcr.io
Username: <your GitHub username/organization>
Password: <your personal access token>
Login Succeeded!

Publish images to GHCR

Once logged in, you are ready to publish your images to ghcr.io/<your GitHub username/organization>/<your package name>, for example:

$ podman push localhost/gvenzl/oracle-xe:latest ghcr.io/gvenzl/oracle-xe:latest

Everything you always wanted to know about Oracle Database NLS_LANG and character sets

I have recently come across the Oracle Database Globalization Support again and the question of what character set is used when, how and where. A lot of this can be or is controlled by the NLS_LANG OS environment variable, buried deep down in the Database Globalization Support Guide. But I just found this “hidden gem” on the Oracle web page that, for some reason, isn’t very well indexed by the search engines: https://www.oracle.com/database/technologies/faq-nls-lang.html

I also found that the page could do with an overhaul and a few more details, so I’ve taken the liberty to provide an updated version of it over here: Oracle Database NLS_LANG FAQ

Hopefully, this will help other users of Oracle Database to understand this environment variable better and maybe help to improve the overall search engine ranking for that page. If you have any questions regarding what character set is used or should be used and how to set what, that FAQ page is your friend.

SOLVED: Oracle XE: ORA-00821: Specified value of sga_target is too small

tl;dr

  • Open ${ORACLE_HOME}/assistants/dbca/templates/XE_Database.dbc with a text editor
  • Find <InitParamAttributes><InitParams>
  • Add the following XML tag under <InitParams>: <initParam name="cpu_count" value="2"/>
Continue reading “SOLVED: Oracle XE: ORA-00821: Specified value of sga_target is too small”

How to get the month name in the IntelliJ copyright velocity template

Every time I try to create a copyright profile in IntelliJ (or PyCharm), I find myself googling around the web trying to find out how to get the month name, e.g. August, into the template. Unfortunately, the IntelliJ documentation is not very helpful by just saying:

IntelliJ documentation about date formatting

In short, it can be done via $today.format("MMMM"), as the DateInfo implements the syntax from java.text.SimpleDateFormat, which has a long list of formatting options:

java.text.SimpleDateFormat formatting options

However, the important part is further below:

Month: If the number of pattern letters is 3 or more, the month is interpreted as text; otherwise, it is interpreted as a number.

  • Letter M produces context-sensitive month names, such as the embedded form of names. Letter M is context-sensitive in the sense that when it is used in the standalone pattern, for example, “MMMM”, it gives the standalone form of a month name and when it is used in the pattern containing other field(s), for example, “d MMMM”, it gives the format form of a month name. For example, January in the Catalan language is “de gener” in the format form while it is “gener” in the standalone form. In this case, “MMMM” will produce “gener” and the month part of the “d MMMM” will produce “de gener”. If a DateFormatSymbols has been set explicitly with constructor SimpleDateFormat(String,DateFormatSymbols) or method setDateFormatSymbols(DateFormatSymbols), the month names given by the DateFormatSymbols are used.
  • Letter L produces the standalone form of month names.

It’s important to use at least 3 letters M to have the month be printed in text form otherwise, you will get the month’s number.

With that in mind, one can now provide a decent copyright profile in IntelliJ, well, to my liking at least, anyway:

Since: $today.format("MMMM") $today.year
Author: $username
Name: $file.fileName
Description: 

Copyright $today.year Gerald Venzl

Licensed under the Apache License, Version 2.0 (the "License");
you may not use this file except in compliance with the License.
You may obtain a copy of the License at

    http://www.apache.org/licenses/LICENSE-2.0

Unless required by applicable law or agreed to in writing, software
distributed under the License is distributed on an "AS IS" BASIS,
WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
See the License for the specific language governing permissions and
limitations under the License.

Which generates:

/*
 * Since: January 2022
 * Author: gvenzl
 * Name: Test.java
 * Description:
 *
 * Copyright 2022 Gerald Venzl
 *
 * Licensed under the Apache License, Version 2.0 (the "License");
 * you may not use this file except in compliance with the License.
 * You may obtain a copy of the License at
 *
 *     http://www.apache.org/licenses/LICENSE-2.0
 *
 * Unless required by applicable law or agreed to in writing, software
 * distributed under the License is distributed on an "AS IS" BASIS,
 * WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
 * See the License for the specific language governing permissions and
 * limitations under the License.
 */