[Oracle Cloud] - SQL_ID new feedback option in SQL*PLUS

Here in this article I will be demonstrating one of the new cute feature introduced in Oracle Database 18c,

Earlier we used to spend quite few seconds to get the problematic SQL_ID of a user session to diagnose the performance issue reported, most of the time user calls or shoot out an email saying that there is his one job which is running longer than expected, and we got to get his username and find the things running in there.


In Oracle Database 18c, a recent release by Oracle, there is new feedback option(SQL_ID) added which developers can use in their code that will directly print the SQL_ID of their reporting jobs or something that is being run the current user session.

Let's go through below example to see how this new feature works in Oracle Database 18c.

I am logging to my Oracle Cloud 18c instance.

 [RAJKUMAR@ORA18C ~]sqlplus "/as sysdba"  
 SQL*Plus: Release 18.0.0.0.0 Production on Tue Apr 17 04:45:35 2018  
 Version 18.1.0.0.0  
 Copyright (c) 1982, 2017, Oracle. All rights reserved.  
 Connected to:  
 Oracle Database 18c EE Extreme Perf Release 18.0.0.0.0 - Production  
 Version 18.1.0.0.0   

 SQL>  

 SQL> set lines 300  
 SQL>  
 SQL>  
 SQL> select name, open_mode, database_role , cdb from v$database;  
 NAME   OPEN_MODE      DATABASE_ROLE  CDB  
 --------- -------------------- ---------------- ---  
 ORCL18C  READ WRITE      PRIMARY     YES  
 SQL>  
 SQL> show pdbs  
   CON_ID CON_NAME            OPEN MODE RESTRICTED  
 ---------- ------------------------------ ---------- ----------  
      2 PDB$SEED            READ ONLY NO  
      4 PDB1              READ WRITE NO  
 SQL>  

Now I will be logging to my PDB1 pluggable database instance.

 SQL> alter session set container = pdb1;  
 Session altered. 

 SQL> show con_name  
 CON_NAME  
 ------------------------------  
 PDB1  

Now Let's use the new SQL_ID option of feedback prompt of SQL*PLUS utility. and some SQL commands to check its magic of prompting the SQL_ID for the executed SQL statement.

 SQL> set feedback on SQL_ID  
 SQL> 

 SQL> select count(*) from dba_objects;  
  COUNT(*)  
 ----------  
    73564  
 1 row selected.  
 SQL_ID: g4pkmrqrgxg3b 

Yes, you can see above that after enabling the SQL_ID option of feedback at SQL*PLUS, it prints the SQL_ID of the executed SQL statements, now lets verify the SQL Text for the printed SQL_ID.

 SQL> select sql_id ,sql_fulltext from v$sql where sql_id='g4pkmrqrgxg3b';  
 SQL_ID    SQL_FULLTEXT  
 ------------- --------------------------------------------------------------------------------  
 g4pkmrqrgxg3b select count(*) from dba_objects  
 1 row selected.  

Happy Learning....!!