| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Usenet -> c.d.o.server -> Re: Autonomous transaction in database event triggers - a bug?
On Mon, 4 Oct 1999 21:53:51 +0100, "Jonathan Lewis"
<jonathan_at_jlcomp.demon.co.uk> wrote:
>You don't need to declare an autonomous transaction
>for an AFTER LOGON trigger. Check the SQL manual
>about 10 pages into the bit on triggers.
>
>The content of any DDL-based triggers automatically
>starts an autonomous transaction, executes your code
>and commits.
>
>Of course, this doesn't mean that your redundant
>> 4 pragma autonomous_transaction;
>should cause an ora-00600, but it does bypass
>the problem,
Hi Jonathan,
Thanks for your answer. More or less I was aware of the behavior of the database event triggers that you described.
The story about how I ended in trying to use explicit authonomous transaction in this kind of trigger is somewhat long (and scarring). I was trying to use some DDL commands like "CREATE something ..." inside logon trigger (using EXECUTE IMMEDIATE version of dynamic sql) and it always failed with "ORA-3113: end-of-file on communication chanel". So as a last resort (tryal-and-error method) I also tried with explicitly specifying autonomous transaction in a trigger, but this resulted in ORA-600. So as a bottom line I discovered that you are able to compile a database event trigger using autonomous transaction, however it will result in ORA-600 when the trigger actually fires. So at the end I think I will have to report is to Oracle support as a bug.
The scarring thing about the above story was that I was declaring this kind of trigger on the database level initially, and consequently *any* connection to the database resulted in ORA-600. Obviously the only type of connections that doesn't fire this trigger is when you connect as internal or as sysdba/syoper. So I was able to connect as internal, but when I tried to drop the trigger with "DROP TRIGGER trigger_name;" or disabled it with "ALTER TRIGGER trigger_name DISABLE;" it refused to execute this with "ORA-4092: cannot COMMIT or ROLLBACK in a trigger" !!!?? So finally when I have already given up all the hopes and was preparing to recreate the database (it is only a testing instance, thank god), I tried with the last shot in the dark:
DECLARE
PRAGMA autonomous_transaction;
BEGIN
EXECUTE IMMEDIATE 'DROP TRIGGER trigger_name';
END;
And guess what - it succeeded, the trigger was dropped and I was able
to log on normally again.
The moral of the above story: Now I'm very careful when experimenting with this kind of triggers. I never again use a database as a scope, I always specify a particular schema as a scope. And I thing there is no workaround if this kind of error happens in the database startup trigger - the database simply wouldn'n come up and there would be no way to drop the offending trigger. So IMHO Oracle should provide some kind of init parameter to disable all database event triggers when this kind of troubles occur.
Regards,
Jurij Modic <jmodic_at_src.si>
Certified Oracle DBA (7.3 & 8.0 OCP)
 |
 |