Oem Active User Session Showing Current Sql Command As Unknown

Applies to:

Oracle Server - Enterprise Edition - Version: 10.2.0.4 and later   [Release: 10.2 and later ]
Information in this document applies to any platform.

Goal

Why the SQL on top activity showing SQL TYPE as UNKNOWN

Solution

The situations under which such a thing happens is the sqltext has been flushed out of shared pool and does not exist any more to reference to.And due t this
you would not find this in any dynamic views( v$sql or v$sql_text) as well.
The queries are kept in the shared pool till they are aged out.If a query is using high resource for considerable amount of time, it should be caught in previous
or successive ash/awr reports.Flushing out from shared pool could be
because of invalidation of related object( to which there is no solution), undersized shared pool( you may increase the same to avoid flushing of queries so fast).

Although the sqlid and the hash value are available, the actual sqltext is flushed

Source: Oracle Doc ID 1315664.1
Comments