create or replace trigger ddl_trigger

after create or alter or drop on SCHEMA

declare

l_sysevent varchar2(25);

l_extra varchar2(4000);

begin

select ora_sysevent into l_sysevent from dual;

if ( l_sysevent in ('DROP','CREATE') )

then

if l_sysevent = 'CREATE'

then

begin

select 'storage ( initial ' || initial_extent ||

' next ' || next_extent || ' .... )'

into l_extra

from all_tables

where table_name = ora_dict_obj_name

and owner = user;

exception

when no_data_found then null;

end;

end if;

insert into log

select ora_sysevent, module, ora_dict_obj_owner,

ora_dict_obj_name, l_extra

from v$session

where sid = (select sid from v$mystat where rownum=1);

elsif ( l_sysevent = 'ALTER' )

then

insert into log

select ora_sysevent, module, ora_dict_obj_owner,

ora_dict_obj_name, sql_text

from v$open_cursor,

(select module

from v$session

where sid = (select sid from v$mystat where rownum=1))

where upper(sql_text) like 'ALTER%' ||

ora_dict_obj_name || '%'

and sid = ( select sid

from v$session

where audsid=userenv('sessionid') );

end if;

end;

/