select * from v$session
解决方案 »
- Developer插入的数据跟命令窗口的 不同步 怎么解决?
- SQL查询not in,或是同一字段多个!=效率超低,怎样优化??
- create index..(bsm asc) 与create index ..(bsm)的区别
- 怎么导出某个schema下所用的DB object?
- 几个关于C#连接Oracle数据库的问题
- PL/SQL中如何查看一个表的表结构?
- 执行insert操作时数据库报错:ORA-12535:TNS:操作超时
- 请问如何把文本格式的数据导入到Oracle中?
- ORACLE响应速度的问题
- 一个oracle错误!!
- oracle覆盖本来的web站点,想恢复!
- 求救,如何用一条语句查询两个数据库的数据,结果显示在一屏上?
再建一个触发器
create or replace trigger SYS.login_on_info -- 紀錄登入信息的觸發器
after logon on database
Begin
insert into dbsys.login_log(session_id,login_on_time,login_off_time,user_in_db,machine,ip_address,run_program)
select AUDSID,sysdate,null,sys.login_user,machine,SYS_CONTEXT('USERENV','IP_ADDRESS'),program
from v$session where AUDSID = USERENV('SESSIONID'); --当前SESSION
END;
再建立一个触发器
create or replace trigger SYS.drop_info
after drop on dbsys.schema -- 在mfg0513user用戶上創建審計drop的觸發器
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;这是记录DROP表,其它如CREATE和ALTER可以参与上面的写,对于DELETE、UPDATE、INSERT只能在表上写了!
sql语句可以从V$SQL视图中找
SYS_CONTEXT('USERENV','TERMINAL') terminal,
SYS_CONTEXT('USERENV','LANGUAGE') language,
SYS_CONTEXT('USERENV','SESSIONID') sessionid,
SYS_CONTEXT('USERENV','INSTANCE') instance,
SYS_CONTEXT('USERENV','ENTRYID') entryid,
SYS_CONTEXT('USERENV','ISDBA') isdba,
SYS_CONTEXT('USERENV','NLS_TERRITORY') nls_territory,
SYS_CONTEXT('USERENV','NLS_CURRENCY') nls_currency,
SYS_CONTEXT('USERENV','NLS_CALENDAR') nls_calendar,
SYS_CONTEXT('USERENV','NLS_DATE_FORMAT') nls_date_format,
SYS_CONTEXT('USERENV','NLS_DATE_LANGUAGE') nls_date_language,
SYS_CONTEXT('USERENV','NLS_SORT') nls_sort,
SYS_CONTEXT('USERENV','CURRENT_USER') current_user,
SYS_CONTEXT('USERENV','CURRENT_USERID') current_userid,
SYS_CONTEXT('USERENV','SESSION_USER') session_user,
SYS_CONTEXT('USERENV','SESSION_USERID') session_userid,
SYS_CONTEXT('USERENV','PROXY_USER') proxy_user,
SYS_CONTEXT('USERENV','PROXY_USERID') proxy_userid,
SYS_CONTEXT('USERENV','DB_DOMAIN') db_domain,
SYS_CONTEXT('USERENV','DB_NAME') db_name,
SYS_CONTEXT('USERENV','HOST') host,
SYS_CONTEXT('USERENV','OS_USER') os_user,
SYS_CONTEXT('USERENV','EXTERNAL_NAME') external_name,
SYS_CONTEXT('USERENV','IP_ADDRESS') ip_address,
SYS_CONTEXT('USERENV','NETWORK_PROTOCOL') network_protocol,
SYS_CONTEXT('USERENV','BG_JOB_ID') bg_job_id,
SYS_CONTEXT('USERENV','FG_JOB_ID') fg_job_id,
SYS_CONTEXT('USERENV','AUTHENTICATION_TYPE')
authentication_type,
SYS_CONTEXT('USERENV','AUTHENTICATION_DATA')
authentication_data
from dual
/
不过"飘"兄的语句好像只能得到目前本机连接数据库的几乎所有信息 , 但小弟要得是能够过滤ip地址并且在相当长时间里的登陆情况 , 虽然Listener.log文件中已经比较详细 , 可是老板需要过虑ip之后的信息 .
不过看了各位写的 , 收益匪浅 .
我写的代码正在测试 , 成功后会给各位散分 .
如何写在用户执行PL/SQL 或 SQL 语句时触发某个触发器 . 类似下面 :create or replace trigger SYS.login_on_info
after logon on database
Begin
...
End;
中的logon on database 事件 .
before insert or update on students
referencing new as new_student /// referencing_clause
for each row
begin
select student_sequence.nextval
into :new_student.id /// referencing_clause
from dual;
end;