Wednesday, January 20, 2016
Comparing triggers (or other objects) in two Oracle schemas
We had a customer ask a question recently about whether the trigger DDL in two different Oracle schemas was the same. While this is not 100% accurate, it is a simple way to see if there are any discrepancies:
select t.name ,sum(coalesce(length(trim(replace(replace(t.text,chr(10)),chr(13)))),0)) ddl_chars
from user_source t
where type = 'TRIGGER'
group by t.name
order by t.name
It counts the number of characters in the trigger DDL. You can then compare the output in Excel or Beyond Compare or something similar (or an OUTER JOIN).