Friday, January 17, 2014

EVENT vs. EVENTS

I figure it's "a must" to blog any time I learn something new.  I've set events on databases many times over the years but for the most part I've never paid that much attention to the syntax.  I knew about SESSION vs. SYSTEM, that CONTEXT lets you define how long the event should be active, etc., but I never paid attention to the impact of EVENT vs. EVENTS.

Using "ALTER SYSTEM SET EVENT '…';" changes the parameter EVENT and overrides previous settings (more on that later), "ALTER SYSTEM SET EVENTS '…';" makes the listed event active immediately but not persistent across database restarts.  I guess that should be obvious, because without specifying SCOPE the change would only be in memory.  What makes this different, though, is that using EVENT gives you the ability to define SCOPE (and SID) while EVENTS does not.  Using EVENTS actually changes a memory structure (see Tanel Poder's blog on "Why doesn't ALTER SYSTEM SET EVENTS set the events or tracing immediately?": http://tinyurl.com/kh852ea).


Back to EVENT, setting it to a value overrides previous setting(s), just as it would for any other instance/database parameter.  This means it's important to first check for any existing values for EVENT before setting new one(s).