Oracle DDL trigger

Oracle DDL trigger--Watch DB operation （ZT)

Oracle DDL trigger--Watch DB operationQuite often, DBAs need to know what  DDL operations the users have done in a 
test environment. Here is the way I can know what they did.

This table and the  trigger (you may name them the way you like) should be in 
SYS, SYSTEM or an account with DBA role. It will store who did what, when 
and from where (machine and IP), by what method (sqlplus, toad, sql 
worksheet etc).

There is a limitation: when a  DDL operation has more than 2000 characters, 
it won't go through. For example, when a user is trying to create a new or 
modify an existing stored procedure, s/he may be in trouble if the code has 
more than 2000 characters.

I have caught follwoing DDLs:
alter,
analyze,
comment,
create,
drop,
grant,
revoke,
truncate



Here is the script.

------------------------------------
-- create table
CREATE table  ddl_event (
timestamp date,
user_name varchar2(30),
os_user varchar2(30),
machine varchar2(20),
ip_addr varchar2(20),
program VARCHAR2(30),
event varchar2(20),
Object_name varchar2(30),
object_type varchar2(30),
object_owner varchar2(30),
statement varchar2(256) )
/


-- who changed what and when and how
create or replace  trigger ddl_watcher
after  ddl on database
when (user not in ('SYS', 'SYSTEM'))
declare
v_osuser varchar2(30);
v_machine varchar2(20);
v_ip_addr varchar2(20);
v_program VARCHAR2(30);
event varchar2(30);
obj_name varchar2(30);
obj_type varchar2(30);
obj_owner varchar2(30);
sql_text ora_name_list_t;
stmt VARCHAR2(256);
n number;
begin
select osuser,
machine,
nvl(program, 'sqlplus'),
sys_context('userenv','ip_address')
into
v_osuser,
v_machine,
v_program,
v_ip_addr
from v$session
where audsid = userenv('sessionid');

-- select sys_context('userenv','ip_address') into v_ip_addr from dual;
-- v_ip_addr := ora_client_ip_address;

event := ora_sysevent;
obj_name := ora_dict_obj_name;
obj_type := ora_dict_obj_type;
obj_owner := ora_dict_obj_owner;

n := ora_sql_txt(sql_text);
if n > 256 then
n:= 256;
end if;

FOR i IN 1..n LOOP
stmt := stmt || sql_text(i);
END LOOP;

insert into  ddl_event (timestamp, user_name, os_user, machine, ip_addr,
program, event, object_name, object_type, object_owner, statement)
values (sysdate, user, v_osuser, v_machine, v_ip_addr, v_program,
event, obj_name, obj_type, obj_owner, stmt);
end;
/

转载： http://hi.baidu.com/snxquviufibadtd/item/5c8e6726b91035112b0f1ce4