0

It looks like the column Last_active_time is the last time that the query executed in an oracle DB, but it is date format. How to check the SQL executed history and order them by the last active or executed time?

SELECT a.SQL_ID,
  executions,
  a.LAST_LOAD_TIME,
  TO_CHAR(a.ELAPSED_TIME/1000/1000, '999,999,999.000')
  || ' s' AS TIME,
  a.MODULE,
  a.SQL_TEXT
FROM v$sql a
WHERE a.module IS NOT NULL
AND a.module    = 'JDBC Thin Client'
ORDER BY a.LAST_LOAD_TIME DESC
MT0
  • 143,790
  • 11
  • 59
  • 117
user9302460
  • 1
  • 1
  • 1
  • don't forget that the query can be aged out of the shared pool ... –  Jan 26 '22 at 09:32
  • Please formulate clear your problem. Is it a `DATE` format? You will *never* get *executed history and order* from `V$SQL`. For full detail of executed statements you'll need to activate 10046 trace. A good *approximation* is in *Active Session History* views (requires licence). – Marmite Bomber Jan 26 '22 at 10:44

2 Answers2

0

It's an Oracle "DATE", which means it also contains time.

You can format the output via the TO_CHAR function, to show the time part.

... 
  TO_CHAR(a.LAST_ACTIVE_TIME, 'YYYY-MM-DD HH24:MI:SS') AS LAST_ACTIVE_TIME, 
... 
ORDER BY a.LAST_ACTIVE_TIME DESC, a.LAST_LOAD_TIME DESC
...
LukStorms
  • 28,916
  • 5
  • 31
  • 45
0

In Oracle, a DATE is a binary data type that is stored in the database as 7 bytes (for century, year-of-century, month, day, hour, minute and second). It will ALWAYS have all of those components and is NEVER stored with a particular display format.

Client applications typically have a default human-readable format which they use to display the binary DATE data to the user; this often defaults to the Oracle session parameter NLS_DATE_FORMAT and, unfortunately, the default NLS_DATE_FORMAT does not show the time component of a date.

If you want to change how dates are shown in your session then either change it in your client application or, if it is based on the NLS_DATE_FORMAT, you can change the format for your current Oracle session using:

ALTER SESSION SET NLS_DATE_FORMAT = 'YYYY-MM-DD HH24:MI:SS;

Then run:

SELECT SQL_ID,
       executions,
       LAST_LOAD_TIME,
       LAST_ACTIVE_TIME,,
       TO_CHAR(ELAPSED_TIME/1000/1000, '999,999,999.000') || ' s' AS TIME,
       MODULE,
       SQL_TEXT
FROM   v$sql
WHERE  module = 'JDBC Thin Client'
ORDER BY
       LAST_ACTIVE_TIME DESC;

If you want to change how the date is formatted in your query then convert the DATE to a string using TO_CHAR:

SELECT SQL_ID,
       executions,
       LAST_LOAD_TIME,
       TO_CHAR(LAST_ACTIVE_TIME, 'YYYY-MM-DD HH24:MI:SS') AS last_active_time,
       TO_CHAR(ELAPSED_TIME/1000/1000, '999,999,999.000') || ' s' AS TIME,
       MODULE,
       SQL_TEXT
FROM   v$sql
WHERE  module = 'JDBC Thin Client'
ORDER BY
       LAST_ACTIVE_TIME DESC

db<>fiddle here

MT0
  • 143,790
  • 11
  • 59
  • 117