oracle触发器 触发事件,请教:如何在触发器中获得当前触发事件的操作者

REM 记录登陆信息的触发器

CREATE OR REPLACE TRIGGER LOGON_HISTORY

AFTER LOGON ON DATABASE --WHEN (USER='EQSP') --ONLY FOR USER 'EQSP'

BEGIN

insert into session_history

select username,sid,OSUSER,ACTION,SYSDATE,null,SYS_CONTEXT('USERENV','IP_ADDRESS'),TERMINAL,PROGRAM

from v$session

where audsid = userenv( 'sessionid' ) ;

END;

SQL> CREATE OR REPLACE TRIGGER T_LOGON

2 AFTER LOGON on DATABASE

3 BEGIN

4 IF sys_context('userenv','session_user') = 'SCOTT' THEN

5 RAISE_APPLICATION_ERROR (-20001, 'You are not allowed to logon to this database');

6 END IF;

7 END;

8 /

create or replace trigger tri_a2303_ins

after insert

on a2303

REFERENCING NEW AS NEW_CLIENT

for each row

declare

aa number;

bb varchar2(30);

begin

select aa,bb into aa,bb from a2303 where aa=:new_client.id;

if :new_client.name='abc' then

insert into a2304 values(:new_Client.id,20);

else

insert into a2304 values(:new_Client.id,30);

end if;

end;

++++++++++++++++++++++++++++++++++++++++++++++++++++++++++

REM 以下表和触发器用来记录登陆信息

REM 以SYS用户建立

REM 如果只对单个表空间操作，可建立On schema触发器

connect sys

REM 需要基于基本表的授权(以sys用户建立无需)

grant select on v_$session to user

/

drop table session_history

/

REM Session进程历史纪录表

REM 记录登陆信息

create table session_history

(

USERNAME varchar2(20),

SID number,

AUDSID number,

OSUSER varchar2(30),

ACTION varchar2(100),

LOGIN_TIME date,

LOGOFF_TIME date,

IP varchar2(20),

TERMINAL varchar2(30),

PROGRAM varchar2(100)

)

/

REM 记录登陆信息的触发器

CREATE OR REPLACE TRIGGER LOGON_HISTORY

AFTER LOGON ON DATABASE --WHEN (USER='EQSP') --ONLY FOR USER 'EQSP'

BEGIN

insert into session_history

select username,sid,OSUSER,ACTION,SYSDATE,null,SYS_CONTEXT('USERENV','IP_ADDRESS'),TERMINAL,PROGRAM

from v$session

where audsid = userenv( 'sessionid' ) ;

END;

/

REM 记录退出时间的触发器

CREATE OR REPLACE TRIGGER LOGOFF_HISTORY

BEFORE LOGOFF ON DATABASE --WHEN (USER='EQSP') --ONLY FOR USER 'EQSP'

BEGIN

UPDATE SESSION_HISTORY

SET LOGOFF_TIME = SYSDATE

WHERE audsid = userenv( 'sessionid' );

end;

/

++++++++++++++++++++++++++++++++++++++++++++++++++++++++++

connect sys

grant select on v_$session to user;

rem 这个user是建触发器的user,需要sys直接授权

create table session_history

(username varchar2(20),

log_time date,

ip varchar2(20)

)

/

CREATE OR REPLACE TRIGGER logon_history

AFTER LOGON ON SCHEMA

BEGIN

insert into session_history

select username,SYSDATE,SYS_CONTEXT('USERENV','IP_ADDRESS')

from v$session

where audsid = userenv( 'sessionid' ) ;

commit;

END;

但是我查oracle帮助，AFTER LOGON事件可用的属性只有ora_sysevent，ora_login_user，ora_instance_num，ora_database_name，ora_client_ip_address几种，我怎么才能够唯一确定该session的sid并对它进行监控呢？alter session set sql_tarce好像只能由session自己来执行阿，能不能由监控程序来设定某个session的sql_trace值呢？还望指点一二。

可以创建个 AFTER LOGON 触发器

然后检查使用的应用程序

调用DBMS_SYSTEM.SET_SQL_TRACE_IN_SESSION设置session级的sql_trace

create or replace trigger after_login

after logon on database

begin

execute immediate 'alter session set sql_trace=true';

end;

/

版主、大侠帮忙，我在8i数据库中测试成功如下：

CREATE OR REPLACE TRIGGER logontrig

AFTER LOGON ON DATABASE

declare

log_user char(20);

terminal char(16);

begin

log_user := ora_login_user;

terminal := userenv('TERMINAL');

if log_user='xxx' and terminal='xxxx' then

execute immediate 'alter session set sql_trace=true';

end if

end;

但是客户的数据库是8.0.5的，没有logon触发器，怎么办，有没有别的办法？

col sid format 9999

col user format a11

col program format a20

select a.username "user",a.sid, a.program "program",

substr(d.sql_text,1,1000)

from v$session a, v$sql d

where a.sid = &sid and

d.address(+) = a.sql_address and d.hash_value(+) = a.sql_hash_value

/

开启trace,3种方法

1) alter session set sql_trace=true;

2) dbms_session.set_sql_trace

3) dbms_system.set_sql_trace_in_session 设置其他session

关闭trace

1) alter session set sql_trace=false

建立AFTER LOGON ON DATABASE( or schema )触发器

用WHEN (USER='username') 选项

But you can use it to log all the logon info,try this,

create it by sys:

create TRIGGER drop_info

after drop on database

begin

insert into drop_log

(session_id,

drop_time,

ip_address,

object_owner,

object_name,

object_type,

drop_by_user)

values(USERENV('SESSIONID'),

sysdate,

SYS_CONTEXT('USERENV','IP_ADDRESS'),

sys.dictionary_obj_owner,

sys.dictionary_obj_name,

sys.dictionary_obj_type,

sys.login_user);

end;