DBAsupport.com Forums - Powered by vBulletin
Results 1 to 4 of 4

Thread: ora-04098 trigger is invalid and failed re-validation

  1. #1
    Join Date
    Jun 2008
    Posts
    38

    ora-04098 trigger is invalid and failed re-validation

    I have a Trigger that will fire whenever any DDL statement is executed.Below the trigger code

    Code:
    create or replace trigger
       DDLTrigger
    AFTER DDL ON DATABASE
    BEGIN
    insert into
       perfstat.stats$ddl_log
    (
       user_name,
       ddl_date,
       ddl_type,
       object_type,
       owner,
       object_name
    )
    VALUES
    (
       ora_login_user,
       sysdate,
       ora_sysevent,
       ora_dict_obj_type,
       ora_dict_obj_owner,
       ora_dict_obj_name
    );
    END;
    When i m testing this trigger by truncating a table i m getting ORA-ERROR as
    "ora-04098 trigger is invalid and failed re-validation"

    How should i solve this issue ?

  2. #2
    Join Date
    Mar 2007
    Location
    Ft. Lauderdale, FL
    Posts
    3,555
    Have you created the trigger as SYS?

    Please execute and post...

    select * from dba_objects where object_name in ('DDLTRIGGER','STATS$DDL_LOG');
    Pablo (Paul) Berzukov

    Author of Understanding Database Administration available at amazon and other bookstores.

    Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.

  3. #3
    Join Date
    Jun 2008
    Posts
    38
    No, i have created the trigger as USER . Below is what i get when i logon as SYS and do the following commands

    select * from dba_objects where object_name in ('DDLTRIGGER','STATS$DDL_LOG');

    Code:
    OWNER	OBJECT_NAME	SUBOBJECT_NAME	OBJECT_ID	DATA_OBJECT_ID	OBJECT_TYPE	CREATED	LAST_DDL_TIME	TIMESTAMP	STATUS	TEMPORARY	GENERATED	SECONDARY
    SCOTT	DDLTRIGGER		30,359.00		TRIGGER	05/20/2009 18:07:36	05/20/2009 18:07:36	2009-05-20:18:07:36	INVALID	N	N	N
    SCOTT	STATS$DDL_LOG		30,347.00	30,347.00	TABLE	05/20/2009 15:39:43	05/20/2009 15:39:43	2009-05-20:15:39:43	VALID	N	N	N

  4. #4
    Join Date
    Mar 2007
    Location
    Ft. Lauderdale, FL
    Posts
    3,555
    Quote Originally Posted by ali560045 View Post
    No, i have created the trigger as USER
    DDLTrigger is supposed to be a system level trigger -create it as SYS.
    Pablo (Paul) Berzukov

    Author of Understanding Database Administration available at amazon and other bookstores.

    Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


Click Here to Expand Forum to Full Width