How to Make an In-Database listener.log File

Ever wished the listener.log file was a table in the database? Wish no more! About three years ago, I sent this recipe in an email to my co-workers. Just recently, Shakir re-sent it after using the method in an emergency. Since it seems to have proved its value, I now offer it to our readers.

Using Oracle’s external tables, we can “query” the listener.log file:

Step 1: Create an “oracle directory” of where your file is:

create directory TNSLOG as '$ORACLE_HOME/network/log';

Step 2: Create an external table definition. Note that no data is loaded, just the method reading the file:

CREATE TABLE listener_log (
timestamp date,
connect_data VARCHAR2(2000),
protocol_info VARCHAR2(80),
EVENT VARCHAR2(200),
SID VARCHAR2(200),
RETURN_CODE number
)
ORGANIZATION EXTERNAL
(TYPE oracle_loader
DEFAULT DIRECTORY TNSLOG
ACCESS PARAMETERS
(
RECORDS DELIMITED BY newline
NOBADFILE NODISCARDFILE NOLOGFILE
FIELDS TERMINATED BY "*" LRTRIM (timestamp char date_format DATE mask "DD-MON-YYYY hh24:mi:ss", connect_data, protocol_info, event,sid,return_code)
)
LOCATION ('listener.log')
)
REJECT LIMIT UNLIMITED;

Step 3: Query the table directly or load it into an Oracle table for better performance and consistency. You can limit your date range here to load only the period you need:

create table listener_log2 as 
select TIMESTAMP,connect_data, event, sid, return_code,
substr(connect_data, instr(connect_data,'HOST=')+5, instr(connect_data,')', instr(connect_data,'HOST='))-instr(connect_data,'HOST=')-5) 
as host from listener_log where timestamp >= sysdate - interval '3' day;

Note that I extract the “host” to see where connections are coming from.

Step 4: Query grouping by hour. If you need say by minute, replace hh with mi.

select host,trunc(timestamp,'hh'),count(*)-count(nullif(return_code,0)) as success, count(nullif(return_code,0)) as failure,count(*) as total
from listener_log2 group by host,trunc(timestamp,'hh') order by 2,1;

This a quick and dirty way of doing it, but it serves its purpose. Feel free to improve on this (perhaps have it extract all the connect data, including “service updates” etc.) and post the improved version in a comment.