Streams rules! OK?

Continuing my love hate relationship with streams - today presented another interesting challenge when this email alert from cloud control appeared in my inbox

So ORA-4080- pretty unusual error (well not the normal kinds of failures anyway) - what is that?

 oerr ora 4080
04080, 00000, "trigger '%s' does not exist"
// *Cause:  The TRIGGER name is invalid.
// *Action: Check the trigger name.

OK sounds simple enough - so whats gone wrong?

Lets drill through the screens in cloud control (which make working with streams much easier - an essential tool if you have streams i feel)

then

then

And when i eventually click on the view error LCR - i see this statement

alter trigger COMPLIANCE_OLD.blah blah blah compile;

But hold on a minute i don't replicate that schema - what on earth is going on and why have all three rulesets (capture/propagate/apply) tried to process this?

My initial though was that the compile statement had come from SYS (where utlrp was run on the source system) - could that somehow be being captured by some special internal hidden sys rules - surely not?

Then i decided to go and have a look at the trigger code itself - perhaps there was something odd about it that could give me a clue?

And indeed there was something that stood out - the contents of the trigger were essentially something like this

create trigger compliance_old on compliance_old.table

blah

blah

do something

insert into compliance.table values blah blah

So the trigger in this schema is inserting in to a schema that i am replicating - aha!

And if we look at the DDL rule set for the schema we are replicating - we see this:

The key part being

"or :ddl.get_base_table_owner() = "

The internals of streams must build some sort of array containing all the schemas on which the trigger is dependent - this then results on the DDL being capture, propagated and then failing to apply.

An interesting side effect - in our case we just deleted the problem LCR and dropped the COMPLIANCE_OLD schema at source as it wasn't needed.

Another case for tidying up i guess......