Oracle – Fetching Recent/Historical Queries Run on a Database

oracleoracle-11g-r2oracle-12coracle-sql-developer

I'm getting some database errors from an application related to unique constraints however none of the records have any duplicate values so I want to check what query was run that produced the error.

How can I check a history log of queries to pinpoint the problem? I'm using Oracle 12c

Best Answer

There is no such thing enabled by default in the database. V$ or DBA_HIST views are not guaranteed to contain all your SQL statements all the time. The amount of memory to store SQL statements in V$ views is limited, and DBA_HIST views contain only sampled data. Furthermore, they do not store information about constraint violation errors.

If you want to find the SQLs causing unique constraint violation errors, you can create a trigger that catches the errors and logs information, for example:

create table userLog
(error_date date,
 username varchar2(30),
 error_msg clob,
 error_sql clob);

create or replace trigger log_errors after servererror on database
declare
   sql_text ora_name_list_t;
   msg clob := null;
   stmt clob := null;
   errnum number;
begin

   errnum := ORA_SERVER_ERROR(1);

   if (errnum = 1) then
       for i in 1 .. ora_server_error_depth loop
          msg := msg||ora_server_error_msg(i);
       end loop;
       for i in 1..ora_sql_txt(sql_text) loop
          stmt := stmt||sql_text(i);
       end loop;
       insert into userLog(error_date,username,error_msg,error_sql)values (sysdate,ora_login_user,msg,stmt);
   end if;
end;
/

The above trigger catches number 1 errors (that is for ORA-00001: unique constraint () violated) on the top of the stack (that is ORA_SERVER_ERROR(1)) in the database, and logs them in the userlog table with the SQL text, so you can find later the SQL statements that caused them.

Related Question