-
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 ?
-
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.
-
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
-
Originally Posted by ali560045
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
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|