2

I have a delete query which was run on a table in Oracle 11g Enterprise Edition about 2 months back. I need to find the exact time of when the query was executed.

I found the query in the DBA_HIST_SQLTEXT view. But I do not see any date/time field in the table to find the exact time of execution. Is there any way that this can be found out?

I also tried to extract the same from AWR report. But snapshots are available only till 1.5 months back.

1
  • 2
    Logminer is probably your only option if the relevant data has aged out of the DBA_HIST tables – Philᵀᴹ Nov 3 '16 at 8:51
0

The typical retention period for an AWR report is 45 days. So unless, this period has been modified to 60 days (2 months) or more in your DB, the DBA_HIST* views will not have the information you are looking for. You can verify the retention period by querying the DBA_HIST_WR_CONTROL view. In case the retention period has not been modified you can query the DBA_HIST_ACTIVE_SESS_HISTORY view and get SQL_EXEC_START time for the SQL_ID for a given period.

4
  • I checked the DBA_HIST_WR_CONTROL view. The retention for the said environment is set to 8 days. So is there any way to extract the timestamp of the query before that? – Neel Nov 3 '16 at 7:21
  • No don't think there is any other way through the database queries. – vishad Nov 3 '16 at 9:03
  • As mentioned by @Phil, in the comment you can use Logminer, but even that might not have data from so long back – vishad Nov 3 '16 at 9:34
  • @vishad The sql_exec_start will tell you when it was executed but not when its finished, – moudiz Jun 27 '19 at 10:33
0

It means you have the SQL_ID of the SQL statement and now you want to know when it was executed.

You can use the following query where you can supply the SQL_ID of the SQL query that you have in DBA_HIST_SQLTEXT.

SQL> SELECT timstamp FROM dba_hist_sql_plan WHERE sql_id='your sql_id';
2
  • Thanks JSapkota . But the query returns no result. How can it be that the DBA_HIST_SQLTEXT view contains the query but DBA_HIST_SQL_PLAN doesn't? – Neel Nov 3 '16 at 7:24
  • 1
    Oracle may have captured the SQL, but not the plan, that is why. if you need it to be captured, make it is colored sql (there is a procedure in dbms_workload_repository, and it is documented in the manuals too). – Raj Nov 4 '16 at 12:47

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.