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;