如何在oracle触发器器中得到操作当前表的登陆用户及其机器地址_百度...
发布网友
发布时间：2022-04-25 05:23
共2个回答
热心网友
时间：2022-04-09 18:00
select top(1)* from table_name order by update_time desc
我的思路是查找修改时间按降序的第一位的结果不就可以了??
用触发器的话
CREATE TRIGGER 名成
ON 表名
FOR UPDATE
as
select * from 表名 where 主键= ?
热心网友
时间：2022-04-09 19:18
给你一个，你可以稍微修改一下
-- 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 al;
-- 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;
/
DDL trigger
Quite 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 procere, 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
