In V$SESSION, column SQL_ID is not NULL while STATUS is INACTIVE

Applies to:

Symptoms

 When a session is inactive, the SQL_ID for this session is not null.

The issue can be reproduced by following these steps:
 
 - connect from sqlplus as a test user
 
 - connect from a second session as sys and run the following query:
 
 SQL> select sid, username, status, sql_id, sql_exec_start from v$session;
 
 Note: the first session should should be inactive and having SQL_ID NULL
 
 - go back to the first session and run a query
 
 SQL> select * from dual;
 
 - go back to the SYS session and verify again the sessions
 
 NOTE: the first session will show again as INACTIVE but this time the SQL_ID  has an explicit value
 

Cause

This is a product defect as when a session is inactive the sql_id for this session is supposed to be null.
 
 As per the Oracle documentation :
 SQL_ID VARCHAR2(13) SQL identifier of the SQL statement that is currently being executed

Reference:

The issue matches  : IN V$SESSION, SQL_ID IS NOT NULL BUT STATUS IS INACTIVE

Solution

At the time of writing this document, the developers are still working on a fix for this issue under .

References