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;

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/20976446/viewspace-674322/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/20976446/viewspace-674322/