In V$SESSION, column SQL_ID is not NULL while STATUS is INACTIVE (文档 ID 1923905.1)

Applies to:

Oracle Database - Standard Edition - Version 11.1.0.7 and later
Oracle Database - Enterprise Edition - Version 11.1.0.7 and later
Information in this document applies to any platform.

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

-- First session

SQL> conn scott/tiger
Connected.
SQL> create table test1(id number);

Table created.

SQL> select * from dual;

D
-
X

-- Second session as sysdba

SQL> select sql_id,status from v$session where username='SCOTT';

SQL_ID STATUS
------------- --------
INACTIVE

SQL> /

SQL_ID STATUS
------------- --------
7jnhyr6g49s8p INACTIVE

SQL> /

SQL_ID STATUS
------------- --------
a5ks9fhw2v9s1 INACTIVE

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: http://docs.oracle.com/cd/B28359_01/server.111/b28320/dynviews_3016.htm

The issue matches Bug 14393463 : 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 Bug 14393463.

References

BUG:14393463 - IN V$SESSION, SQL_ID IS NOT NULL BUT STATUS IS INACTIVE