PL/SQL (MOSC)

MOSC Banner

Help with DDL Trigger

edited Mar 23, 2015 1:28PM in PL/SQL (MOSC) 5 commentsAnswered ✓

I am trying to capture DDL events that happen on the source tables of materialized views. Below is the simplified trigger:

CREATE OR REPLACE TRIGGER DDL_TRG1 AFTER ALTER OR DROP ON DATABASE
BEGIN
DBMS_OUTPUT.PUT_LINE('ORA_DICT_OBJ_OWNER: ' || ORA_DICT_OBJ_OWNER );   /* displayed value is 'HR' */
DBMS_OUTPUT.PUT_LINE('ORA_DICT_OBJ_NAME: ' || ORA_DICT_OBJ_NAME );     /* displayed value is 'EMPLOYEE' */

FOR CUR1 IN (
            SELECT NAME FROM DBA_DEPENDENCIES 
            WHERE OWNER = 'DATA_MART1' 
              AND TYPE = 'MATERIALIZED VIEW'
              AND REFERENCED_OWNER = ORA_DICT_OBJ_OWNER    
              --AND REFERENCED_OWNER = 'HR'            
              AND REFERENCED_TYPE = 'TABLE' 
              AND REFERENCED_NAME = ORA_DICT_OBJ_NAME
              --AND REFERENCED_NAME = 'EMPLOYEE'
           )
LOOP 
  INSERT INTO 

Howdy, Stranger!

Log In

To view full details, sign in to My Oracle Support Community.

Register

Don't have a My Oracle Support Community account? Click here to get started.

Category Leaderboard

Top contributors this month

New to My Oracle Support Community? Visit our Welcome Center

MOSC Help Center