SQL Goodies In Oracle 18c….

Oracle database 18c has come up with some really useful features and the new features guide is the best place to get started-as always. In this post, which is the 1st post about 18c, I shall mention two very simple yet very helpful features introduced for SQL statements. So without further adiue, let’s get to it.

Showing SQLID in the SQL*Plus

If you have ever tried to do anything with a query that’s already been executed, you must have fetched the SQLID of the statement from V$SQL etc. Not a difficult task but still, if it can be done quickly then why not? And that’s why probably, 18c has introduced the option of SQL_ID in the SHOW FEEDBACK command. Here is an example,

SQL> set feedback on sql_id
SQL> select sysdate from dual;


SYSDATE
———
31-MAY-18


1 row selected.


SQL_ID: 7h35uxf5uhmm1

And there you go-SQLID is right there! How cool is that!

Cancelling a SQL statement

In the previous versions, if a SQL statement goes hung and the session becomes unresponsive, the only alternative was to kill the offending session. From 18c, this is not needed as there is a new command available now to kill just  the SQL statement.

ALTER SYSTEM CANCEL SQL ‘SID, SERIAL[, @INST_ID][, SQL_ID]’;

For example, if you want to kill a session with SID=22 and SERIAL=100, it will look like this,

SQL>alter system cancel SQL ‘22,100,7h35uxf5uhmm1’;

So there you go! Two very simple, short but probably really useful features. Stay tuned for more 18c posts in the future!