Wednesday, August 20, 2008

External table to select from Oracle listener log

create directory LISTENER_LOG_DIR
as '/u01/app/oracle/10.1/db1/network/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_prolin01.log')
)
reject limit unlimited
/