External Tables - Oracle 10g/11g Data and Database Management Utilities - page 77

access parameters (
records delimited by NEWLINE
nobadfile
nodiscardfile
nologfile
fields terminated by "*" LDRTRIM
reject rows with all null fields
(
"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;
The structure of interest is specified above, so there will be several rows rejected.
Seeing as this file is not fully structured, you will find some non formatted
information; the bad file and the log file are not meaningful in this context.
Another application of the LISTENER_LOG External Table is usage trend analysis. This
query can be issued to detect usage peak hours.
SQL> select to_char(round(TIMESTAMP, 'HH'), 'HH24:MI') HOUR,
2 lpad('#', count(*), '#') CX
3 from listener_log
4 group by round(TIMESTAMP, 'HH')
5 order by 1;
HOUR CX
----- ------------------------------------------------
14:00 ###
15:00 ##########################
16:00 ######################
17:00 #####################
18:00 #####################
19:00 ###############
Reading the listener.log file this way allows the DBA not only to keep track
of the listener behavior, but also it allows a security administrator to easily spot
hacking attempts.