How to Capture offending sql of ORA-00001 error

 

Using sql*plus run the sql
 
alter system set events='0001 trace name errorstack level 12';

Now login to other session and run the command ,

SQL> oradebug setmypid
Statement processed.
SQL> oradebug eventdump system
1 trace name ERRORSTACK level 12 >>>> This indicates event is present in the DB

Please also check the folloine note , to check for the events in the db
How To List All The Named Events Set For A Database (Doc ID 436036.1)

Simulate the error of ORA-00001 ,

create table a1(a number primary key);
insert into a1 values (1);
commit;

SQL> insert into a1 values (1);
insert into a1 values (1)
*
ERROR at line 1:
ORA-00001: unique constraint (SCOTT.SYS_C0079247) violated

SQL> show parameters user_dump_dest

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
user_dump_dest                       string      /home/ias/oracle/product/10.2.0/db10gr2/admin/db10gr2/udump

In the latest file you will find the entries as ,

*** 2012-10-05 14:00:11.038
ksedmp: internal or fatal error
ORA-00001: unique constraint (SCOTT.SYS_C0079247) violated
Current SQL statement for this session:
insert into a1 values (1)
----- Call Stack Trace -----

Turn off the debug logs ,
SQL> alter system set events='1 trace name errorstack off';

To confirm whether the event is off or not run the following ,

SQL> oradebug setmypid
Statement processed.
SQL> oradebug eventdump system

Statement processed.