ORA-00001 causes MTS (shared) server to di

From: NetComrade <andreyNSPAM_at_bookexchange.net>
Date: Wed, 28 Apr 2004 20:55:53 GMT
Message-ID: <408ff548.768544929@localhost>

We have started seeing a weird issue over the past months of so. The following appears in alert.log file

Errors in file /u71/app/oracle/admin/BSBL/bdump/bsbl_s001_12771.trc:

ORA-00604: error occurred at recursive SQL level 1
ORA-02067: transaction or savepoint rollback required
ORA-00001: unique constraint (USER1.TEAMS_U1) violated

Wed Apr 28 16:17:07 2004
found dead multi-threaded server 'S001', pid = (19, 232)

I believe this to be an issue of transactions failing over the link b/c of constraint violation, but cannot be 100% sure.

I believe ORA-00604 indicates that this is a trigger that's failing. The trigger we have is an 'AFTER INSERT' trigger that inserts over the link (database link). USER1 is _local_ user, so the constraint fails there. Oracle has suggested that we catch ORA-00001 in application code and rollback. We modified the app to savepoint and rollback to savepoint in case of ORA-00001, however it didn't help.

The problem is that the trace files don't really report on which statement it fails. We wouldn't even care if this continued showing in alert.log file, but the fact that the error causes a server to die worries me. We have identified 2 statements in trace files that could've potentially caused the constraint violations (insert/update), and wrapped them with savepoing/rollback..

Don't know what else we can do, besides tracing every session, which is not feasible, given database traffic.

Application server (home-brewed) doesn't report any errors

Anyone seen this? Any suggestions?

Thanks.
.......
We use Oracle 8.1.7.4 on Solaris 2.7 boxes remove NSPAM to email Received on Wed Apr 28 2004 - 15:55:53 CDT