ORA_CLIENT_IP_ADDRESS用来返回访问oracle数据库客户端的ip地址，windows本机登录时，返回ip地址可能为空。
1.ORA_CLIENT_IP_ADDRESS定义：
- CREATE PUBLIC SYNONYM ORA_CLIENT_IP_ADDRESS FOR SYS.CLIENT_IP_ADDRESS
- CREATE OR REPLACE FUNCTION SYS.CLIENT_IP_ADDRESS
- return varchar2 is
- begin
- return dbms_standard.client_ip_address;
- end;
- CREATE PUBLIC SYNONYM ORA_CLIENT_IP_ADDRESS FOR SYS.CLIENT_IP_ADDRESS
- CREATE OR REPLACE FUNCTION SYS.CLIENT_IP_ADDRESS
- return varchar2 is
- begin
- return dbms_standard.client_ip_address;
- end;
2.ORA_CLIENT_IP_ADDRESS 使用示例：
- CREATE OR REPLACE TRIGGER sysevent_trig
- AFTER LOGON
- ON DATABASE
- BEGIN
- insert into dw.ora_fun_logs(id,value)
- values(dw.ora_fun_logs_s.nextval,
- ora_sysevent||' : ORA_CLIENT_IP_ADDRESS--'||NVL(ora_client_ip_address, 'N/A'));
- END;
- show errors
- truncate table dw.ora_fun_logs;
- conn dw/dw@dw
- set line 80
- column value format a60
- select * from dw.ora_fun_logs;
- CREATE OR REPLACE TRIGGER sysevent_trig
- AFTER LOGON
- ON DATABASE
- BEGIN
- insert into dw.ora_fun_logs(id,value)
- values(dw.ora_fun_logs_s.nextval,
- ora_sysevent||' : ORA_CLIENT_IP_ADDRESS--'||NVL(ora_client_ip_address, 'N/A'));
- END;
- show errors
- truncate table dw.ora_fun_logs;
- conn dw/dw@dw
- set line 80
- column value format a60
- select * from dw.ora_fun_logs;
- 22:11:08 sys@dw>CREATE OR REPLACE TRIGGER sysevent_trig
- 22:11:09 2 AFTER LOGON
- 22:11:09 3 ON DATABASE
- 22:11:09 4 BEGIN
- 22:11:09 5 insert into dw.ora_fun_logs(id,value)
- 22:11:09 6 values(dw.ora_fun_logs_s.nextval,
- 22:11:09 7 ora_sysevent||' : ORA_CLIENT_IP_ADDRESS--'||NVL(ora_client_ip_address, 'N/A'));
- 22:11:09 8 END;
- 22:11:09 9 /
- 触发器已创建
- 已用时间: 00: 00: 00.04
- 22:11:10 sys@dw>truncate table dw.ora_fun_logs;
- 表被截断。
- 已用时间: 00: 00: 00.03
- 22:11:18 sys@dw>conn dw/dw@dw
- 已连接。
- 22:11:24 dw@dw>set line 80
- 22:11:24 dw@dw>column value format a60
- 22:11:24 dw@dw>select * from dw.ora_fun_logs;
- ID VALUE
- ---------- ------------------------------------------------------------
- 17 LOGON : ORA_CLIENT_IP_ADDRESS--N/A
- 18 LOGON : ORA_CLIENT_IP_ADDRESS--127.0.0.1
关于Oracle系统事件函数ora_client_ip_address的知识就介绍到这里了，希望能够带给您一些收获，谢谢了！
【编辑推荐】
