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/，如需轉載，請註明出處，否則將追究法律責任。