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


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);

 (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 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) (
       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');

  v_sql VARCHAR2(32760); 

  FOR R IN (SELECT sql1||' '||sql2 S FROM temp_sql) LOOP
    v_sql := R.s;

Need Your Help

Server side code in Razor view


Is there a way to create functions like these in the Razor view engine?

C# string.GetHashCode() returns non int result

c# .net

One of my clients had an app crash and i traced it due to this bug/feature i can't really explain.

About UNIX Resources Network

Original, collect and organize Developers related documents, information and materials, contains jQuery, Html, CSS, MySQL, .NET, ASP.NET, SQL, objective-c, iPhone, Ruby on Rails, C, SQL Server, Ruby, Arrays, Regex, ASP.NET MVC, WPF, XML, Ajax, DataBase, and so on.