Using External Table

Step 1) Check the listener log location

Lsnrctl status

Listener Log File             /u01/app/oracle/diag/tnslsnr/PRDG11G/listener/alert/log.xml

Step 2) Create directory

Sql > create directory listener_log_directory as ‘/u01/app/oracle/diag/tnslsnr/PRDG11G/listener/alert’ ;

Step 3) create a external table

              create table listener_log_detail

( 

 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_directory 

 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 ('log.xml') 

) 

reject limit unlimited 

/ 

Step 4) Create function for parsing the listener log

          create or replace function parse_listener_log_file 

 ( 

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; 

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; 

 /

Step 5) Execute the following query

Sql > set null ?

Sql > select parse_listener_log_file(connect_string,’USER’) from listener_log_detail;

Find values when listener was stopped 

   

   col host format a20 

col l_user format a20 

col service format a15 

col logdate format a20 

select to_char(log_date,'mm/dd/yy hh24:mi:ss') logdate, 

parse_listener_log_file(connect_string,'HOST') host, 

 parse_listener_log_file(connect_string,'USER') l_user, 

 parse_listener_log_file(connect_string,'SERVICE') service 

from listener_log_detail 

where parse_listener_log_file(connect_string, 'COMMAND') = 'stop'; 

  Program Usage

     col program format a70 

col cmt format 999,999 

select parse_listener_log_file(connect_string,'PROGRAM') program, 

 count(1) cnt 

from listener_log_detail 

group by parse_listener_log_file(connect_string,'PROGRAM');