#Oracle: #SQL to get the latest executed queries in a schema

By | February 20, 2021

I was trying to obtain the last executed query on an Oracle instance.

Seems easy but I wanted also:

  • to exclude the queries that are done by sys user as maintenance
  • to focus on queries executed by a specific client type.

    The best way to do it is to use the V$SQLAREA system table.

V$SQLAREA lists statistics on shared SQL area and contains one row per SQL string. It provides statistics on SQL statements that are in memory, parsed, and ready for execution.

https://docs.oracle.com/cd/B19306_01/server.102/b14237/dynviews_2129.htm#REFRN30259

So not to waste more text. The bellow query will show the full text of the last executed query originating from the JDBC thin client on a given schema ‘MY_USER’ ordered by descending execution call time.

SELECT sql_fulltext from v$sqlarea
   WHERE parsing_schema_name = 'MY_USER'
   AND module='JDBC Thin Client'
   ORDER BY first_load_time DESC;

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.