監控oracle的觸發器語句(轉)

監控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 ;