0

How can I find out why my all Oracle database schema packages gets in the invalid status suddenly? When I recompile its, invalid objects compile successfully. I created trigger "before ddl" but it didn't help me. Also, I query from user_errors to see what the problem is, but there are no errors in that view. Are there any suggestions?

Here is my table audit and the trigger:

     create table invalidations (
  operation           varchar2(30),
  invalidating_object varchar2(30),
  invalidating_owner  varchar2(30),
  invalidated_object  varchar2(30),
  invalidated_owner   varchar2(30),
  invalidation_date   date,
  ipaddr varchar2(30),
  host varchar2(30),
  curruser varchar2(30),
  currschema varchar2(30),
  fgjob number,
  bgjob number
);

create or replace trigger befddl_trg
before ddl
on schema
declare
begin
  insert into invalidations
  select ora_sysevent, ora_dict_obj_name, ora_dict_obj_owner, d.name, d.owner, sysdate,
  SYS_CONTEXT('USERENV', 'IP_ADDRESS', 15) 
  ,SYS_CONTEXT('USERENV', 'HOST', 16) 
  ,SYS_CONTEXT('USERENV', 'CURRENT_USER', 8) 
  ,SYS_CONTEXT('USERENV', 'CURRENT_SCHEMA', 8) 
  ,SYS_CONTEXT('USERENV', 'FG_JOB_ID', 4) 
  ,SYS_CONTEXT('USERENV', 'BG_JOB_ID', 4) 
  from   all_dependencies d 
  where  referenced_name = ora_dict_obj_name 
  and    referenced_owner = ora_dict_obj_owner;
end befddl_trg;
BIB
  • 121
  • 3
  • 10
  • You may find help here, but if you don't I'd suggest re-posting on [dba.stackexchange.com](http://dba.stackexchange.com) - the folks there will be much more adept with things like `before ddl` triggers. – Ed Gibbs Apr 30 '13 at 14:01
  • read about Auditing in oracle – haki May 01 '13 at 06:35

0 Answers0