Hello, We are on 8.1.7.4.0 (on Solaris), and are having a problem with a logoff trigger and "ORA-00164: autonomous transaction disallowed within distributed transaction". The logoff trigger is defined as: CREATE OR REPLACE TRIGGER get_logoff_stats_trg BEFORE LOGOFF ON DATABASE BEGIN capture_session_stats_prc; END;
The purpose of the procedure called by the trigger is to capture session stats and commit them to a table, so the procedure is defined as an autonomous transaction; removing the autonomous transaction pragma results in: ORA-00604: error occurred at recursive SQL level 1 ORA-04092: cannot ROLLBACK in a trigger ORA-06512: at "CAPTURE_SESSION_STATS_PRC", line 116 ORA-04092: cannot COMMIT in a trigger Most of our users only encounter this error when executing some SELECT which involves a reference to a database link, which apparently qualifies as a distributed transaction. Anyway, the SELECT executes successfully, but we fail to capture their session stats upon exit. The suggested oerr action for ora-164 is to rollback or commit before calling the autonomous transaction pragma; but Oracle does not allow a rollback or commit in the trigger which calls the autonomous transaction procedure. An exception handler doesn't seem to catch an ora-00164 error. (Anyone know why? I thought all ORA- errors could be captured by exception handlers.) ORA-00164 is fixed in Oracle9i (actually, Oracle says this is not a bug, but a feature that was changed, not fixed), but we can't upgrade to 9i because some of our third-party tools are not yet compatible. The only thing I can think of is to ask users to do a commit or rollback in their sessions just before exiting if they referenced a db link. I know this isn't practical. Does anyone have a better suggestion? As always, many thanx to any responders. -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: INET: [EMAIL PROTECTED] Fat City Network Services -- 858-538-5051 http://www.fatcity.com San Diego, California -- Mailing list and web hosting services --------------------------------------------------------------------- To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
