3 Replies Latest reply: Nov 19, 2012 5:52 AM by akm-2205195 RSS

    how to find sql query history

      hi friends ,

      am using orcl 10g in my oracle enterprise server, we use this interface to connect the database Sql Developer Ver 1.1.3,
      how to find the sql history, now i need to check 3 to 4 months back what are the update statement that has been queried and how to check the logs,
      though i have checked lots of views such as v$sqlarea ... etc i couldnot find any queries, and why i couldnot open redo logs.

      plz tell me is there any option to restrict update command in the future,

      also help me how to find the history , or what i should do further ,

      Waiting for ur results
        • 1. Re: how to find sql query history

          Please see:

          SQL history
          Updating Backend Database in EBS

          Complete List of SQL Text - awr report
          Complete List of SQL Text - awr report

          • 2. Re: how to find sql query history
            Nikolay Savvinov

            welcome to the forum!

            1) you can use AWR (DBA_HIST_%) and ASH (DBA_HIST_ACTIVE_SESS_HISTORY) views to find some historic information. Unfortunately, it won't go back as long as 3 or 4 months unless you specifically configured the retention period so. Plus, if your UPDATE was using bind variables, most likely you won't be able to retrieve the values (although you may try digging DBA_HIST_SQLBIND
            2) you can query your audit trail, but you need audit enabled and configured for that
            3) you can dig your old logs using Log Miner, assuming that your database preserves archived logs

            In order to prevent such incidents in the future, you need to have a structured security model in your database. Users shouldn't be able to UPDATE database tables on production outside an API.

            Best regards,
            • 3. Re: how to find sql query history
              Use this query to get last 3 month queries order by execution.

              select * from (select module,sql_id,round(sum(CPU_TIME_DELTA)/1000000) "CPUTIME(S)",round(sum(ELAPSED_TIME_DELTA)/1000000) "E
              LPSED(S)",round(sum(EXECUTIONS_DELTA)) "NUMOFEXEC",round(round(sum(ELAPSED_TIME_DELTA))/1000000)/(round(sum(EXECUTIONS_DELTA))) "Per Execution",sum(ROWS_PROCESSED_DELTA) "NUMOFROW",sum(DISK_READS_DELTA)/1024/1024 "DISK
              READ(MB)",sum(BUFFER_GETS_DELTA)/1024/1024 "BUFFERREAD(MB)" from dba_hist_sqlstat where (snap_id between (select min(snap_id)
              from dba_hist_snapshot where trunc(begin_interval_time)=trunc(sysdate-92 )) and (select max(snap_id) from dba_hist_snapshot
              where trunc(begin_interval_time)=trunc(sysdate ))) and EXECUTIONS_DELTA>10 group by module,sql_id order by "NUMOFEXEC" desc
              ) where rownum < 51;