"Phil Kaufman" <philk_at_dbcsmartsoftware.com> wrote in message news:<W%uya.127$Fl3.55278659_at_mantis.golden.net>...
> Hi folks,
>
> My environment is Oracle 8.0.5 db server.
>
> I have a script which performs some basic ddl statements, which fails.
> Here's the basics of the SQL:
>
> ===========================================================
> ALTER TABLE s1_vehicle_processing
> ADD inventory_dollar_value number(18,6) DEFAULT 0 not null
> /
>
> ALTER TABLE s1_vehicle_processing
> ADD inventory_contract_exch_rate number(20,10) DEFAULT 1 not null
> /
> ===========================================================
>
> the table s1_vehicle_processing has before update (statement level), before
> update (row level), and after update (statement level) triggers. Through
> SQL*Plus, the first alter table works, the second gives me an Oracle error:
>
>
> ===========================================================
> (c) Copyright 2001 Oracle Corporation. All rights reserved.
>
> Connected.
>
> Table altered.
>
> ALTER TABLE s1_vehicle_processing
> *
> ERROR at line 1:
> ORA-00604: error occurred at recursive SQL level 1
> ORA-04045: errors during recompilation/revalidation of
> GRAINDBA.BUS_S1_VEHICLE_PROCESSING
> ORA-04020: deadlock detected while trying to lock object
> GRAINDBA.S1_VEHICLE_PROCESSING
> ===========================================================
>
> Interestingly, the BUS_S1_VEHICLE_PROCESSING trigger gets invalidated by the
> first alter, but doesn't seem to be automatically revalidated by the second
> alter. Upon further investigation, when I re-validate manually the trigger
> between running statements, all seems to be fine.
>
> My question is, can anyone make sense what is happening exactly behind the
> scenes here? I'll double check this, but it looks like I'll need to disable
> this trigger(s) first, do my alters, then re-enable. But I would still like
> to understand what is happening (I hate the try again later messages as
> 04020 suggests).
>
> Thanks in advance,
>
> Phil
If you are performing the ddl in plsql using dbms_sql, there was a
known bug. I do not remember if it was in 8.0.5 or prior versions. I
tested on 8.1.6, 9.0.1 and 9.2.0.3 that works fine when you alter a
table that has statement level and row level before update triggers
and an after update trigger.
Received on Tue May 20 2003 - 21:15:10 CDT
