#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.


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.