one example of event triggers
Code:
create table event_log
(log_date varchar2(36),
event varchar2(36),
username varchar2(36),
owner varchar2(36),
object_name varchar2(36),
object_type varchar2(36),
dbname varchar2(36),
sql_text varchar2(2000)
);
create or replace trigger truncate_trg
after truncate on database
declare
l_sql_stmt varchar2(2000);
begin
select sql_text
into l_sql_stmt
from v$sql a, v$session b
where a.address = b.sql_address
and a.hash_value = b.sql_hash_value;
insert into event_log
values(sysdate,
ora_sysevent,
ora_login_user,
ora_dict_obj_owner,
ora_dict_obj_name,
ora_dict_obj_type,
ora_database_name);
end;
/
create or replace trigger alter_user_trg
after alter on database
declare
l_sql_stmt varchar2(2000);
l_sid number;
begin
if (ora_dict_obj_type='USER')
then
select sql_text, sid
into l_sql_stmt, l_sid
from v$sql a, v$session b
where a.address = b.sql_address
and a.hash_value = b.sql_hash_value
and audsid = userenv('SESSIONID');
insert into event_log
values(sysdate,
ora_sysevent,
ora_login_user,
ora_dict_obj_owner,
ora_dict_obj_name,
ora_dict_obj_type,
ora_database_name,
l_sid||' ##### '||l_sql_stmt);
end if;
end;
/