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


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
   sql_text ora_name_list_t;
   msg clob := null;
   stmt clob := null;
   errnum number;

   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;

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.

| improve this answer | |

The data you require is exposed by the view v$sql.

You can narrow down the statement you're looking for by filtering on the SQL_TEXT column.

Documentation link.

If you have a diagnostics pack license, you can use the DBA_HIST_SQLTEXT view - it keeps snapshots of SQL statements that may have aged out of v$sql.

| improve this answer | |

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.