log ddl sqls

from asktom

create table log
  2  ( operation varchar2(25),
  3    owner     varchar2(25),
  4    name      varchar2(25),
  5    extra     varchar2(4000) );

Table created.

tkyte@TKYTE816>
tkyte@TKYTE816> create or replace trigger ddl_trigger
  2  after create or alter or drop on SCHEMA
  3  declare
  4      l_sysevent varchar2(25);
  5      l_extra    varchar2(4000);
  6  begin
  7      select ora_sysevent into l_sysevent from dual;
  8
  9      if ( l_sysevent in ('DROP','CREATE') )
 10      then
 11          if l_sysevent = 'CREATE'
 12          then
 13          begin
 14              select 'storage ( initial ' || initial_extent ||
 15                     ' next ' || next_extent || ' .... )' 
                   into l_extra
 16                from all_tables 
                  where table_name = ora_dict_obj_name
 17                 and owner = user;
 18          exception
 19              when no_data_found then null;
 20          end;
 21          end if;
 22
 23          insert into log
 24          select ora_sysevent, ora_dict_obj_owner,
 25                         ora_dict_obj_name, l_extra
 26            from dual;
 27      elsif ( l_sysevent = 'ALTER' )
 28      then
 29          insert into log
 30          select ora_sysevent, ora_dict_obj_owner,
 31                         ora_dict_obj_name, sql_text
 32            from v$open_cursor
 33           where upper(sql_text) like 'ALTER%'  ||
 34                                ora_dict_obj_name || '%'
 35             and sid = ( select sid
 36                           from v$session
 37                          where audsid=userenv('sessionid') );
 38      end if;
 39  end;
 40  /

Trigger created.

tkyte@TKYTE816> drop table t;
Table dropped.

tkyte@TKYTE816> create table t ( x int );
Table created.

tkyte@TKYTE816> alter table t add y date;
Table altered.

tkyte@TKYTE816> select * from log;

OPERATION OWNER NAME EXTRA
--------- ----- ---- -------------------------------------------
DROP      TKYTE T
CREATE    TKYTE T    storage ( initial 131072 next 131072 .... )
ALTER     TKYTE T    alter table t add y date

i wrote myself:

CREATE OR REPLACE TRIGGER TRG_TEST_DML
BEFORE INSERT OR DELETE OR UPDATE 
ON TEST
REFERENCING OLD AS OLD NEW AS NEW
declare  
    n        number; 
    stmt     varchar2(4000); 
    sql_text ora_name_list_t;

 -- log functions
 function getnow return varchar2;
 procedure logwrite(msg varchar2);
 logopt boolean := true;
 dirname v$parameter.value%type;
 ft  utl_file.file_type;
 pgname  varchar2(4000) := 'trg_test_dml';
 fn  varchar2(4000) := pgname||'_'||getnow||'.log';
 procedure logopen is
 begin
  if logopt then
   select value into dirname from v$parameter where name = 'utl_file_dir';
   ft := utl_file.fopen(dirname, fn, 'a');
   logwrite('log begin.');
  end if;
 end;
 procedure logwrite(msg varchar2) is
 begin
  if logopt then
   utl_file.put_line(ft, getnow || ' ' || msg);
   utl_file.fflush(ft);
  end if;
 end;
 procedure logclose is
 begin
  if logopt then
   utl_file.fclose(ft);
  end if;
 end;
 function getnow return varchar2 is
 begin
   return to_char(sysdate, 'yyyy.mm.dd_hh24.mi.ss');
 end;
begin
 logopen;
 -- normal program begin
 
    n := ora_sql_txt(sql_text);
 logwrite(n);
 if n is not null then
     FOR i IN 1..n LOOP
         stmt := stmt || sql_text(i);
     END LOOP;
     insert into sql_log(sql_text) values(stmt);
 else
  logwrite('WARING: ora_sql_txt returns null');
 end if;
 
 -- normal program end
 logwrite('log normal end.');
 logclose;
exception
when others then
 logwrite('log abnormal end.');
 logclose;
 raise;
end;
/