Feb 122015

There’s a problem most people who’ve had to do production datafixes have encountered at some point: you need to update a dataset. Unfortunately there’s a trigger on the target table. The trigger either prevents the update outright or just has some unwanted side effects (firing off a business process, etc.). The trigger is necessary for the application to function correctly, so you can’t just drop it.

How do you apply the changes?

You can take an outage so you can drop/disable the trigger while applying the fix. Alternatively you can modify the application so that the trigger is no longer necessary. Scheduling the outage can be difficult (particularly if the fix is urgent). Changing the application may take a long time or even be infeasible if it’s supplied by a third party.

How can you get out of this dilemma?

Fortunately in Oracle 11gR2 and higher there is another way:

create edition to_do_your_dirty_work;

alter session set edition = 

drop trigger which_breaks_your_datafix;

update the_table_with_the_trigger
set    the_data = 
  'values prevented by your trigger';

alter session set edition = 

drop edition to_do_your_dirty_work cascade;

Using edition-based redefinition you can create a new edition and drop the trigger just in that edition. The application will still function as desired because the trigger still exists in the old edition. All you need to worry about now is concurrency issues… ;)

Warning: you must first editions enable your application before you can do this. Only some object types are “editionable” (see the comments). If you have non-editioned objects that depend upon editioned objects you’ll need to make some changes to your application before you can do this! Doing this is also a “one-way” operation – to revert it you need to recreate your user/restore from backup!

For a complete script showing this in action, click here.

For some further reading on EBR, have a look at the following:

Oracle-BASE article
Tom Kyte’s Oracle Magazine article, Edition-Based Redefinition, Part 1
Edition-Based Redefinition Whitepaper, Bryn Llewellyn July 2009

  5 Responses to “Using Edition-Based Redefinition to Bypass Those Pesky Triggers”

  1. Hi Chris,

    looks very useful and a nice use case of editions, so I tried it out immediately on our test-environment.
    I had to give the USER I was using the grants “create any edition”, “drop any edition” in order to follow the above steps.
    I used your technique to update a table, which had originally 3 valid triggers on it. I did the update in a way so that one of these triggers raised an exception due to it.
    So I applied your technique, created a new edition, set my session to it, dropped the disturbing trigger, updated successfully the table in the forbidden way, set back to ORA$BASE ( where I started from ) and then saw to my surprise that the trigger was still dropped !! The table only had 2 triggers.

    So, why did that happen to me ?
    In your complete script, I see the line
    alter user edition_user enable editions;
    Is it that the user I was using was not enabled editions ?
    However, when I try to alter it, I get
    [code language=”sql”]
    system@11.2 > alter user sokrates enable editions;
    alter user sokrates enable editions
    ERROR at line 1:
    ORA-38819: user SOKRATES owns one or more objects whose type is editionable and that have noneditioned dependent objects
    [/code language=”sql”]

    What does that mean and how to get around that ?


    • Good point on the permissions, I run most of my stuff as a DBA user on my VM ;)

      Only certain types of objects are “editionable”. For example, you can’t have editions of tables, materialized views and public synonyms(!) to name a few.

      If your user owns objects with dependents that aren’t editionable you’ll get your error when “editions enabling” it.

      You can find all these dependencies with a query like:

      select * from ALL_DEPENDENCIES
      where referenced_owner = user
      and (owner, name) in
      (select owner, object_name from dba_objects where editionable = ‘N’);

      To bypass this you can:

      alter user sokrates enable editions force;

      though it will invalidate the non-editionable objects.

  2. Thanks for this addendum

    Unfortunately, the queryto find all these dependencies does not run in Oracle 11gR2 ( only when higher )

  3. […] Edition-Based Redefinition to Bypass Those Pesky […]

 Leave a Reply

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong>