oracle监听表的变化,Oracle之外部表监听日志

使用Oracle外部表监听日志

第一步：创建目录

create or replace directory listener_log_list as '/u01/oracle/network/log';

第二步：创建外部表 通过“*”分隔符，可以将外部表进一步细化

CREATE TABLE listener_log_list

(

ldate                DATE,

connect_data         VARCHAR2(400),

address_protocol     VARCHAR2(400),

action               VARCHAR2(20),

service_name         VARCHAR2(20),

return_code          NUMBER(10)

)

ORGANIZATION EXTERNAL (

TYPE oracle_loader

DEFAULT DIRECTORY listener_log_list

ACCESS PARAMETERS

(

RECORDS DELIMITED BY NEWLINE

NOBADFILE

NOLOGFILE

NODISCARDFILE

FIELDS TERMINATED BY "*" LRTRIM

MISSING FIELD VALUES ARE NULL

(

ldate CHAR(30) DATE_FORMAT DATE MASK "DD-MON-YYYY HH24:MI:SS",

connect_data,

address_protocol,

action,

service_name,

return_code

)

)

LOCATION ('listener.log')

)

REJECT LIMIT UNLIMITED

/

第三步：其他信息都可以从这个外部表中查询得到，通过以下查询我们能够获得连接数据库的用户及主机等信息

SELECT      HOST, COUNT (*)

FROM (SELECT SUBSTR (connect_data,

INSTR (connect_data, 'HOST') + 5,

INSTR (SUBSTR (connect_data,

INSTR (connect_data, 'HOST') + 5

),

')'

)

- 1

) HOST

FROM (SELECT *

FROM listener_log_list

WHERE connect_data LIKE '%PROGRAM%' AND ROWNUM < 1000000))

GROUP BY HOST

ORDER BY 2

/

第四步：需要信息查询

alter session set nls_date_format='yyyy-mm-dd hh24:mi:ss';

select ldate,action,service_name,return_code from listener_log_list where rownum<11;

select connect_data from listener_log_list where rownum<30;

第五步：查看外部表信息

SQL> select table_name directory_name from dba_external_locations;