Oracle Triggers not showing in DBA_SOURCE
In our application, only about 25% of the database triggers show up in DBA_SOURCE. I know I can force the others to show up if I make an actual modification (like adding and removing a space) and then recompile the trigger, but I've got about 400 triggers to modify (it's rather a big application). Just recompiling the triggers with alter trigger <triggername> compile; didn't accomplish anything.
Without the triggers being in DBA_SOURCE, we can't do text searches on the trigger code.
Is there some simpler way to accomplish this? And is there some way to prevent the problem in the future?
We're on Oracle 10.2.0.5.0.
We had the same issue. It's a migration issue from older versions of Oracle.
Triggers were not included in DBA_SOURCE in an earlier version (8?, 9i?) and did not get added to DBA_SOURCE when migrating to newer versions. A recompile did not put them into DBA_SOURCE. But if you drop and recreate the triggers, they will be included in DBA_SOURCE.
So my guess is you have some old triggers and have migrated the database in place to newer versions.
I believe you can find the source in all_triggers. Unfortunately, the data is in a LONG variable (Oracle example of do as I say, not as I do). So, the easiest thing would be to create a scratch table to use, populate it with the data converted to CLOB, and then search:
CREATE TABLE tr (trigger_name VARCHAR2(32), trigger_body CLOB); INSERT INTO tr (SELECT trigger_name, TO_LOB(trigger_body) FROM all_triggers WHERE owner = 'xxx'); SELECT trigger_name FROM tr WHERE trigger_body LIKE '%something%';
I'm not sure why the dba_source view is only sparsely populated for triggers. It's that way on my 10.2.0.4 database as well.
Here is a short script you can use to recreate all your triggers, at which point they should all be in dba_source:
CREATE TABLE temp_sql (sql1 CLOB, sql2 CLOB); INSERT INTO temp_sql (sql1, sql2) ( SELECT 'CREATE OR REPLACE TRIGGER '|| DESCRIPTION||' '||CASE WHEN when_clause IS NULL THEN NULL ELSE 'WHEN('||when_clause||')' END sql1, to_lob(trigger_body) sql2 FROM all_triggers WHERE table_owner = 'theowner'); DECLARE v_sql VARCHAR2(32760); BEGIN FOR R IN (SELECT sql1||' '||sql2 S FROM temp_sql) LOOP v_sql := R.s; EXECUTE IMMEDIATE v_sql; END LOOP; END; /