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;
1 row selected.
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!