使用Oracle外部表监听日志

--创建目录

create or replace directory LISTENER_LOG
  as '/opt/oracle/product/9.2.0/network/log'
/

 

--创建外部表

CREATE TABLE listener_log (text VARCHAR2(4000))
  ORGANIZATION EXTERNAL (
    TYPE oracle_loader
    DEFAULT DIRECTORY listener_log
    ACCESS PARAMETERS (
       RECORDS DELIMITED BY NEWLINE
       NOBADFILE
        NOLOGFILE
        NODISCARDFILE
    )
    LOCATION ('listener-hsboss.log')
  )
  REJECT LIMIT UNLIMITED
/

 

--类似的，可以查询连接数据库的应用等信息

SELECT DISTINCT SUBSTR (text,
                    INSTR (text, 'PROGRAM') + 8,
                     INSTR (SUBSTR (text, INSTR (text, 'PROGRAM') + 8),
                                 ')'
                                )
                        - 1
                   ) PROGRAM
       FROM (SELECT *
                FROM listener_log
                WHERE text LIKE '%PROGRAM%' AND ROWNUM < 1000000)
/

 

--通过“*”分隔符，可以将外部表进一步细化

CREATE TABLE listener_log
(
   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
   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-hsboss.log')
)
REJECT LIMIT UNLIMITED
/

 

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

set timing on
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
                   WHERE connect_data LIKE '%PROGRAM%' AND ROWNUM < 1000000))
GROUP BY HOST
ORDER BY 2
/