Replacing / Compiling Database Triggers in a HA/High Load system

Ronnie_in_Craigavon

Hi there,

My collegue has just asked me if downtime needs to be scheduled to replace an After IUD Database trigger where I've made a minor change.

If this were a package or procedure, the answer would be an obvious yes, as I'd be wary of my users getting the dreaded: ORA-04068... State of Package has been disguarded.

What about when working with database triggers?
If the trigger is firing at the same time that I perform my Create or Replace Trigger DDL, will everything fall into a screaming heap?

Notes:
Oracle 10g R2 db.
High DML rates on the table that the After IUD Trigger is 'attached' to.
It's a near certainty that when the Create or Replace Trigger DDL runs, the trigger will be active at the time.
The Trigger is 'simple' in that it's use: is to call a DB package with some of the :new values as params.

+(I'm happy to rtfm - if someone can tell me where!!)+

Cheers -


Ron Marks

Zoltan Kecskemethy

Hello,

I'm not sure that it is necessary but how about to lock your table before you recreate your trigger and release after that...
If you have a test system you may able to test if this works...

HTH. Regards, Zoltan

Ronnie_in_Craigavon

Hi Zoltan,

Locking the entire table (for the two seconds that it would take to recompile this trigger) was an option that I thought of as well, however, I initially discounted it: as I cannot guarantee that some transactions on the table involved won't fail with an ORA-00054 (...nowait specified.)

(As I write this, I'm looking through dba_source to see how many select for update clauses are present with the nowait clause)

It however may be the option that we go with at the time of night with the lowest DML count on the table involved.

Thanks for the help though.

Ronnie.

Timur Akhmadeev

If this were a package or procedure, the answer would be an obvious yes, as I'd be wary of my users getting the dreaded: ORA-04068... State of Package has been disguarded.

Not true. This exception is thrown only if sessions have some state represented in package variables. If there is no associated session state, then no exception would be thrown after recompile.

If the trigger is firing at the same time that I perform my Create or Replace Trigger DDL, will everything fall into a screaming heap?

You'll wait - to lock an object in library cache which represents your trigger. Not sure if this would require a library cache lock on underlying table, but I would guess that it is required. BTW, locking a table would make no any sense: CREATE TRIGGER is a DDL, which means before it began it issues a COMMIT => all locks are released, and it is quite possible that a TM lock for CREATE TRIGGER won't be acquired since resource would be busy.

Oracle 11gR2 claims to address problems of application upgrades on a high-load environments with a feature called "Editions" (not a good name - try to find something about it via Google...)

Ronnie_in_Craigavon

A simple test shows that the trigger re-compile will wait (enqueue) until the trigger is idle, ie, not firing.
The world does not end, and there is no error, well, not in my tests anyway.

1) create a normal empty table to place in some test data.
2) create a trigger that will upon firing, do something that will take a long time (a minute or more); ie, insert a million rows into your test data.
3) create a small anon pl/sql block that will fire your trigger several times in quick succession, ie, do an insert, and update... whatever.

4) Run your pl/sql block... This will then run for several minutes.
5) In a seperate window at the same time that your pl/sql block is running, re-compile your trigger with a small change.

Result:
The TRigger re-compile will wait until the trigger has stopped firing, even if that means that it has to wait several minutes.

If someone else can verify that the result is always an enqueue, I'll consider this question answered.

Ron.

Ronnie_in_Craigavon

Thanks Timur,

Your answer that the trigger re-compile will wait, confirms what I found in my test.

Thanks for your help, and experience.

Ron.