select * from v$session
解决方案 »
- 安装完oracle之后没有oracle ORCL vss WriterService这项服务
- 如何避免TABLE ACCESS BY GLOBAL INDEX ROWID
- 怎样写一个嵌套UPDATE语句?
- OracleDb10g_home1TNListenner 服务启动后又停怎么回事?
- 请教!!!!!!!!!!!!!!!!
- 用Drop table(100000条记录)后磁盘可用空间没增大?怎样真正删除,以增大磁盘可用空间?
- 我服务里为何没有 Management Server
- 游标中的FETCH...INTO...语句是起什么作用的?
- oracle 加了个regexp_replace查询很慢
- 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;