Find out the history of SQL queries

Question

An update SQL query was executed on the server, which caused many problems later.

How can I get the list of update queries executed in last 2 months, so that I can trace the exact problematic SQL query?

bonsvr · Answer 1 · 2013-02-14T18:33:46.943

85

    select v.SQL_TEXT,
           v.PARSING_SCHEMA_NAME,
           v.FIRST_LOAD_TIME,
           v.DISK_READS,
           v.ROWS_PROCESSED,
           v.ELAPSED_TIME,
           v.service
      from v$sql v
where to_date(v.FIRST_LOAD_TIME,'YYYY-MM-DD hh24:mi:ss')>ADD_MONTHS(trunc(sysdate,'MM'),-2)

where clause is optional. You can sort the results according to FIRST_LOAD_TIME and find the records up to 2 months ago.

edited Feb 14 '13 at 18:33

answered Feb 12 '13 at 11:47

bonsvr

2,262
5
22
33

2

Many queries will have aged out of the shared_pool. V$SQL will likely not have all the queries for the past 2 months. – Jon Heller Feb 14 '13 at 18:50
I recently stumbled on this question and @JonHeller is correct. `v$sql` doesn't go back far enough in time. However, there is a view called `dba_hist_sqlstat` that may have the information you need. You'll need to join the `snap_id` to `dba_hist_snapshot` to get the date though. – Mr. Llama Dec 10 '14 at 16:13

score 50 · Answer 2 · answered Oct 13 '14 at 22:49

50

For recent SQL:

select * from v$sql

For history:

select * from dba_hist_sqltext

answered Oct 13 '14 at 22:49

grokster

5,919
1
36
22

Is there a way to find history with date? – damndemon Apr 22 '20 at 13:29
1

select * from v$sql order by LAST_ACTIVE_TIME – grokster May 12 '20 at 17:43

score 13 · Answer 3 · edited Oct 18 '18 at 12:40

13

You can use this sql statement to get the history for any date:

SELECT * FROM V$SQL V where  to_date(v.FIRST_LOAD_TIME,'YYYY-MM-DD hh24:mi:ss') > sysdate - 60

edited Oct 18 '18 at 12:40

Toolkit

10,779
8
59
68

answered Jun 05 '15 at 04:32

Ashish Pandey

151
1
2

Find out the history of SQL queries

3 Answers3

Linked