CODEWITHSUNDEEP
oracle-databasesql-updatedatabase-administration
sumit vedi
sumit vedi

Reputation: 777

Find out the history of SQL queries

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?

Upvotes: 66

Views: 464883

Answers (5)

WesternGun
WesternGun

Reputation: 12807

I have to combine these two tables because

  • v$sql has complete SQL text(wider column width?) but only recent queries, while
  • dba_hist_sqltext has older records, but a limited column width and does not show the complete SQL text(but search on them works even the hit part is invisible)
  • recent queries are in v$sql, older ones in dba_hist_sqltext, but never in both places, so it's full outer join without intersection.
select
    vs.sql_text, vs.sql_id, dhs.sql_text, dhs.sql_id
from
    v$sql vs
full outer join
    dba_hist_sqltext dhs
on
    vs.sql_id = vs.sql_id -- full join always on the left side column itself
where
    UPPER(vs.sql_text) like '%FROM%MYTABLE%' and dhs.sql_id is null
    or
    UPPER(dhs.sql_text) like '%FROM%MYTABLE%' and vs.sql_id is null
;

Upvotes: 0

Marmite Bomber
Marmite Bomber

Reputation: 21115

Oracle 23c Update

A new view V$SQL_HISTORY was introduced - from the documentation

V$SQL_HISTORY displays SQL statements tracked by SQL history monitoring. This view is populated only when the SQL_HISTORY initialization parameter is set to true.

The SQL history monitoring is a best effort feature, so there will be no complete picture of the statements executed, but it will be definitively more complete than in a plain V$SQL.

Upvotes: 0

Ashish Pandey
Ashish Pandey

Reputation: 171

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

Upvotes: 15

grokster
grokster

Reputation: 6297

For recent SQL:

select * from v$sql

For history:

select * from dba_hist_sqltext

Upvotes: 52

Mehmet Balioglu
Mehmet Balioglu

Reputation: 2302

    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.

Upvotes: 88

Related Questions