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.

Answers


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.

EDIT:

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

Need Your Help

Server side code in Razor view

razor asp.net-mvc-3

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.