监控oracle的触发器语句(转)[@more@]首先建一个日志表，然后分别建立3个触发器
create table EVENTLOG
(
EVENTNAME VARCHAR2(20) not null,
OPER_TIME DATE default sysdate,
INST_NUM NUMBER,
DB_NAME VARCHAR2(50),
SRV_ERROR NUMBER,
USERNAME VARCHAR2(30),
OBJ_TYPE VARCHAR2(20),
OBJ_NAME VARCHAR2(30),
OBJ_OWNER VARCHAR2(30)
)
create or replace trigger trig_ddl
after create or alter or drop on database
declare
event varchar2(20);
typ varchar(20);
name varchar(30);
owner varchar(30);
begin
--读取DDL事件属性
event:=sysevent;
typ:=dictionary_obj_type;
name:=dictionary_obj_name;
owner:=dictionary_obj_owner;
insert into eventlog(eventname,obj_type,obj_name,obj_owner)
values(event,typ,name,owner);
end ;
create or replace trigger trig_shutdown
before LOGOFF or shutdown on database
declare
event varchar2(20);
instance number;
dbname varchar(50);
user varchar(30);
begin
event:=sysevent;
if event = 'LOGOFF' then
user:=login_user;
insert into eventlog(eventname,username)
values(event,user);
else
instance:=instance_num;
dbname:=database_name;
insert into eventlog(eventname,inst_num,db_name)
values(event,instance,dbname);
end if;
end ;
create or replace trigger trig_startup
after LOGON or STARTUP or SERVERERROR on database
declare
event varchar2(20);
instance number;
dbname varchar(50);
err_num number;
user varchar(30);
begin
event:=sysevent;
if event = 'LOGON' then
user:=login_user;
insert into eventlog(eventname,username)
values(event,user);
elsif event = 'SERVERERROR' then
err_num:=server_error(1);
insert into eventlog(eventname,srv_error)
values(event,err_num);
else
instance:=instance_num;
dbname:=database_name;
insert into eventlog(eventname,inst_num,db_name)
values(event,instance,dbname);
end if;
end ;
来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/10617731/viewspace-962928/，如需转载，请注明出处，否则将追究法律责任。