关于Listener Log的信息的深度挖掘

原址： http://aluocp.itpub.net/post/17417/470952

1. 创建一个目录，以创建外部表，目录指向Listener_Log的目录
create directory LISTENER_LOG_DIR
as 'D:\oracle\ora92\network\log'
/


2. 创建一个完全读取Listener Log的外部表
create table full_listener_log
(
  line varchar2(4000)
)
organization external (
  type oracle_loader  
  default directory LISTENER_LOG_DIR  
  access parameters (
     records delimited by newline     
     nobadfile      
     nologfile      
     nodiscardfile  
  )  
  location ('listener.log')
)
reject limit unlimited
/


3. 查询该外部表，直接读取Listener Log信息
SQL> select * from full_listener_log;

LINE
--------------------------------------------------------------------------------
TNSLSNR for 64-bit Windows: Version 9.2.0.2.0 - Production on 26-SEP-2007 14:26:
Copyright (c) 1991
System parameter file is d:\oracle\ora92\network\admin\listener.ora
Log messages written to d:\oracle\ora92\network\log\listener.log
Trace information written to d:\oracle\ora92\network\trace\listener.trc
Trace level is currently 0
Started with pid=1904
Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(PIPENAME=\\.\pipe\EXTPROC0ipc
Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=MSLIATest.msl.cn)(PORT=1
TIMESTAMP * CONNECT DATA [* PROTOCOL INFO] * EVENT [* SID] * RETURN CODE
26-SEP-2007 14:26:18 * (CONNECT_DATA=(CID=(PROGRAM=)(HOST=)(USER=cas))(COMMAND=s
TNSLSNR for 64-bit Windows: Version 9.2.0.8.0 - Production on 26-SEP-2007 15:44:
Copyright (c) 1991
System parameter file is d:\oracle\ora92\network\admin\listener.ora
Log messages written to d:\oracle\ora92\network\log\listener.log
Trace information written to d:\oracle\ora92\network\trace\listener.trc
Trace level is currently 0
Started with pid=4032
Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(PIPENAME=\\.\pipe\EXTPROC0ipc
Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=MSLIATest.msl.cn)(PORT=1


4. 也可以进行条件查询
SQL> select * from full_listener_log
  2  where line like 'Log messages written%'
  3  /

LINE
--------------------------------------------------------------------------------
Log messages written to d:\oracle\ora92\network\log\listener.log
Log messages written to d:\oracle\ora92\network\log\listener.log
Log messages written to d:\oracle\ora92\network\log\listener.log
Log messages written to d:\oracle\ora92\network\log\listener.log
Log messages written to d:\oracle\ora92\network\log\listener.log
Log messages written to d:\oracle\ora92\network\log\listener.log
Log messages written to d:\oracle\ora92\network\log\listener.log
Log messages written to d:\oracle\ora92\network\log\listener.log
Log messages written to d:\oracle\ora92\network\log\listener.log
Log messages written to d:\oracle\ora92\network\log\listener.log
Log messages written to d:\oracle\ora92\network\log\listener.log
Log messages written to d:\oracle\ora92\network\log\listener.log
Log messages written to d:\oracle\ora92\network\log\listener.log
Log messages written to d:\oracle\ora92\network\log\listener.log
Log messages written to d:\oracle\ora92\network\log\listener.log
Log messages written to d:\oracle\ora92\network\log\listener.log
Log messages written to d:\oracle\ora92\network\log\listener.log
Log messages written to d:\oracle\ora92\network\log\listener.log
Log messages written to d:\oracle\ora92\network\log\listener.log


5. 创建另一个外部表，以得到Listener Log的分类明显信息
create table listener_log
(
   log_date date,   
   connect_string varchar2(300),   
   protocol_info varchar2(300),   
   action varchar2(15),   
   service_name varchar2(15),   
   return_code number(10)
)
organization external (
   type oracle_loader   
   default directory LISTENER_LOG_DIR   
   access parameters   
   (
      records delimited by newline      
      nobadfile      
      nologfile      
      nodiscardfile      
      fields terminated by "*" lrtrim      
      missing field values are null      
      (
          log_date char(30) date_format           
          date mask "DD-MON-YYYY HH24:MI:SS",         
          connect_string,         
          protocol_info,         
          action,         
          service_name,         
          return_code      
       )   
    )   
    location ('listener.log')
)
reject limit unlimited
/


6. 创建信息挖掘函数
create or replace function parse_listener_log_line
(  
    p_in varchar2,       --要求查询的字段
    p_param in varchar2  --要求查询的关键字
)  
return varchar2  
as  
    l_begin     number(3);  
    l_end       number(3);
    l_val       varchar2(2000);
begin
--枚举相关字段可能的值
--防止空值溢出
    if p_param not in (
            'SID',
            'SERVICE_NAME',
            'PROGRAM',
            'SERVICE',
            'HOST',
            'USER',
            'PROTOCOL',
            'TYPE',
            'METHOD',
            'RETRIES',
            'DELAY',
            'PORT',
            'COMMAND'
    ) then
        raise_application_error (-20001,'Invalid Parameter Value '||p_param);
    end if;
--You search for the presence of this parameter, note the position of the parentheses and the equality sign, and determine the position of the value of the paramet
    l_begin := instr (upper(p_in), '('||p_param||'=');
    l_begin := instr (upper(p_in), '=', l_begin);
    l_end := instr (upper(p_in), ')', l_begin);
    l_val := substr (p_in, l_begin+1, l_end - l_begin - 1);
    return l_val;
end;
/


7. 挖掘连接用户信息
SQL> select parse_listener_log_line(connect_string,'USER')
  2  from listener_log;

PARSE_LISTENER_LOG_LINE(CONNEC
--------------------------------------------------------------------------------
acas
acas
acas
acas
Alex
backup
backup
......
Alex
Alex
Alex


8. 挖掘明细分类信息
SQL> col host format a20
SQL> col l_user format a20
SQL> col service format a15
SQL> col logdate format a20
SQL> select to_char(log_date,'mm/dd/yy hh24:mi:ss') logdate,
  2  parse_listener_log_line(connect_string,'HOST') host,
  3         parse_listener_log_line(connect_string,'USER') l_user,
  4         parse_listener_log_line(connect_string,'SERVICE') service
  5  from listener_log
  6  where parse_listener_log_line(connect_string, 'COMMAND') = 'stop';

LOGDATE              HOST                 L_USER               SERVICE
-------------------- -------------------- -------------------- ---------------


9. 挖掘连接程序统计信息
SQL> col program format a70
SQL> col cmt format 999,999
SQL> select parse_listener_log_line(connect_string,'PROGRAM') program,
  2         count(1) cnt
  3  from listener_log
  4  group by parse_listener_log_line(connect_string,'PROGRAM');

PROGRAM                                                                       CNT
---------------------------------------------------------------------- ----------
C:\Dev6i\BIN\IFDBG60.exe                                                       30
C:\Dev6i\BIN\PLUS80W.EXE                                                        1
C:\Dev6i\BIN\RWBLD60.EXE                                                       13
C:\Dev6i\BIN\RWRBE60.exe                                                       14
C:\Dev6i\BIN\RWRUN60.EXE                                                       11
C:\ORA6I\BIN\IFDBG60.exe                                                       31
C:\ORA6I\BIN\PLUS80W.EXE                                                        2
C:\ORA6I\BIN\RWBLD60.EXE                                                       20
C:\ORA6I\BIN\RWRBE60.exe                                                       44
.......
C:\ORA6I\BIN\RWRUN60.EXE                                                        8
C:\ORA6I\BIN\ifbld60.EXE                                                       15
C:\ORA6I\BIN\ifrun60.EXE                                                      304
C:\ORA6I\bin\IFDBG60.exe                                                       90
d:\ORANT\BIN\ifrun60.EXE                                                       34
d:\oracle\ora92\bin\IMP.EXE                                                    25
d:\oracle\ora92\bin\ORACLE.EXE                                                 13
d:\oracle\ora92\bin\nid.exe                                                     6
d:\oracle\ora92\bin\sqlplus.exe                                                32
d:\oracle\ora92\bin\sqlplusw.exe                                                7
                                                                            20367

10. Service Name使用情况
col sid format a15
select parse_listener_log_line(connect_string,'SID') sid, count(1) cnt
from listener_log
group by parse_listener_log_line(connect_string,'SID');


11. Service Names的使用数量
select name from v$services
minus
select distinct parse_listener_log_line(connect_string,'SERVICE_NAME')from listener_log;

select distinct parse_listener_log_line(connect_string,'SERVICE_NAME')  
from listener_log;


12. 跟踪Client机
col host format a40
select parse_listener_log_line(connect_string,'HOST') host, count(1) cnt
from listener_log
group by parse_listener_log_line(connect_string,'HOST');

select
   parse_listener_log_line(connect_string,'SERVICE_NAME') SN,   
   parse_listener_log_line(protocol_info,'HOST') host,   
   count(1) cnt
from listener_log
where
   parse_listener_log_line(connect_string,'HOST') = '__jdbc__'
group by
   parse_listener_log_line(connect_string,'SERVICE_NAME'),   
   parse_listener_log_line(protocol_info,'HOST');


13. 跟踪Service Names
col sn format a15
col host format a45
col cnt format 999,999
select   parse_listener_log_line(connect_string,'SERVICE_NAME') SN,
   parse_listener_log_line(connect_string,'HOST') host,
   count(1) cnt
from listener_log
group by
   parse_listener_log_line(connect_string,'SERVICE_NAME'),   
   parse_listener_log_line(connect_string,'HOST');


14. DBA Service Names
col l_user format a15
col l_host format a30
col cnt format 999,999
select
   parse_listener_log_line(connect_string,'USER') l_user,   
   decode (
         parse_listener_log_line(connect_string,'HOST'),      
         '__jdbc__',      
         parse_listener_log_line(protocol_info,'HOST'),      
         parse_listener_log_line(connect_string,'HOST')   
   ) l_host,   
   count(1) cnt
from listener_log
where
   parse_listener_log_line(connect_string,'SERVICE_NAME') = 'DBA'
group by
   parse_listener_log_line(connect_string,'USER'),   
   decode (
         parse_listener_log_line(connect_string,'HOST'),      
         '__jdbc__',      
         parse_listener_log_line(protocol_info,'HOST'),      
         parse_listener_log_line(connect_string,'HOST')   
   )
order by 1,2,3;

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

转载于:http://blog.itpub.net/7319461/viewspace-765285/