Tag Archives: ddl trigger

track DDL changes

Why is my package being invalidated? This is the question I asked myself a few times those days. In order to find out what is happening in my schema, I created this simple DDL trigger which tracks all DDL changes in my schema.


CREATE TABLE AUDIT_DDL (
  d date,
  OSUSER varchar2(255),
  CURRENT_USER varchar2(255),
  HOST varchar2(255),
  TERMINAL varchar2(255),
  owner varchar2(30),
  type varchar2(30),
  name varchar2(30),
  sysevent varchar2(30),
  sql_txt varchar2(4000));
   
create or replace trigger audit_ddl_trg after ddl on schema
declare
  sql_text ora_name_list_t;
  stmt VARCHAR2(4000) := '';
  n number;
begin
  if (ora_sysevent='TRUNCATE')
  then
    null;
  else
    n:=ora_sql_txt(sql_text);
    for i in 1..n
    loop
      stmt:=substr(stmt||sql_text(i),1,4000);
    end loop; 
    insert into audit_ddl(d, osuser,current_user,host,terminal,owner,type,name,sysevent,sql_txt)
    values(
      sysdate,
      sys_context('USERENV','OS_USER') ,
      sys_context('USERENV','CURRENT_USER') ,
      sys_context('USERENV','HOST') , 
      sys_context('USERENV','TERMINAL') ,
      ora_dict_obj_owner,
      ora_dict_obj_type,
      ora_dict_obj_name,
      ora_sysevent,
      stmt
    );
  end if;
end;
/

Thanks Yas for your comment, I added the SQL text