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?
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