All geek questions in one place

Oracle triggers do not appear in DBA_SOURCE

In our application, only about 25% of database triggers are mapped to DBA_SOURCE . I know that I can make others appear if I make the actual modification (for example, adding and removing a space) and then recompile the trigger, but I have about 400 triggers to change (this is rather a great application). Just recompiling triggers with alter trigger <triggername> compile; did nothing.

Without triggers located in DBA_SOURCE, we cannot perform textual searches for the startup code.

Is there an easier way to do this? And is there a way to prevent a problem in the future?

We are on Oracle 10.2.0.5.0.

+4
source share
3 answers

We had the same problem. This is a migration issue from older versions of Oracle.

Triggers were not included in DBA_SOURCE in an earlier version (8 ?, 9i?) And were not added to DBA_SOURCE when upgrading to newer versions. Re-compilation did not put them in DBA_SOURCE. But if you reset and recreate triggers, they will be included in DBA_SOURCE.

So, I assume that you have several old triggers and moved the database into place in newer versions.

+2
source

I believe you can find the source at all_triggers. Unfortunately, the data is in the LONG variable (the Oracle example, as I say, is not the way I am). Thus, the easiest way is to create a scratch table for use, populate it with data converted to a 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 available for triggers. So in my database 10.2.0.4.

EDIT:

The following is a short script that you can use to recreate all of your triggers, after which 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 := Rs; EXECUTE IMMEDIATE v_sql; END LOOP; END; /
+3
source

Who owns the triggers?

and of course you tried

select the owner, object_name from all_objects where object_type = 'TRIGGER' and the owner in ('schema1', 'schema2')

0
source

Source: https://habr.com/ru/post/1383928/

More articles:


All Articles