I am running a sql query from a java application. The query is running successfully and is able to fetch data and perform the required action.

However when trying to look for the sql id in GV$SQL and V$SQLAREA, the query does not show up. I have tried all combinations of my query keywords in the like clause.

SQL Query:

select * from GV$SQL where UPPER(sql_text) like UPPER('%{query part here}%');

This gives no results. Any suggestions or ideas on where to look for sql id of my query?

CodePudding user response：

By default SQL_TEXT only contains the first 1000 characters, so its possible that you are looking for a component of the query that is past that. You could guard against that by using the SQL_FULLTEXT column which is a clob.

There is also a chance that the query has been aged out of the shared pool and thus is no longer visible in there. You can also query V$SQLSTATS which typically has a longer retention period.

Also, double check that something else is not pertubating your SQL on the way into the database, eg, cursor sharing which means if you are searching for a literal, it may have been stripped from the SQL