This discussion is archived
1 Reply Latest reply: Mar 1, 2013 5:49 AM by Stefan Jager RSS

Helpful tip - How to disable triggers per session

B Hall Explorer
Currently Being Moderated
I recently stumbled upon a post for an undocumented golden gate procedure that I find VERY useful when repairing / simplifying spatial data as a DBA without causing user triggers to fire and do unnecessary work, etc. Anything with foo in the name must be good, right? ;-)

HINT: Oracle please make this a documented feature!

This will disable triggers – just for a session – no system wide changes or trigger mods needed! Yes, fire needs to be set to true - to disable the triggers from firing.

exec sys.dbms_xstream_gg.set_foo_trigger_session_contxt(fire=>true);

Note, you will need execute permissions on sys.dbms_xstream_gg first.

See for more info:

http://www.pythian.com/blog/disabling-triggers-per-session/

Bryan
  • 1. Re: Helpful tip - How to disable triggers per session
    Stefan Jager Journeyer
    Currently Being Moderated
    B Hall wrote:
    HINT: Oracle please make this a documented feature!
    Seconded!

    Normally I would be very reluctant to use an undocumented feature, and would never rely on it, but this would be extremely helpful in those cases where you have to do something to all your geometry.

    One of the comments on that blog says that this is a security hole, but I disagree. If you are already so far inside that you can call and execute SYS-packages, you're already on the wrong side of the airtight hatch and have the capabilities to do a whole lot more. And since this is session-based, it's not even a problem to forget to turn them back on :-)

    Thanks for this one,
    Stefan

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points