create listener log as external table

listener.log file’ların içeriğini external bir tabloda tutabiliriz, bunun için aşağıdaki gibi bir örnek işinize yarayacaktır;

[oracle@tstdbsrv01 ~]$ cd /u01/app/oracle/diag
[oracle@tstdbsrv01 /u01/app/oracle/diag]$ find . -name listener*log
./tnslsnr/tstdbsrv01/listener_jra/trace/listener_jra.log
./tnslsnr/tstdbsrv01/listener/trace/listener.log
./tnslsnr/tstdbsrv01/listenersqlserver/trace/listenersqlserver.log
[oracle@tstdbsrv01 /u01/app/oracle/diag]$ cd tnslsnr/tstdbsrv01/listener/trace
[oracle@tstdbsrv01 /u01/app/oracle/diag/tnslsnr/tstdbsrv01/listener/trace]$ ls -la
total 30580
drwxr-xr-x 2 oracle oinstall 4096 Jan 30 23:11 .
drwxr-xr-x 13 oracle oinstall 4096 Jul 15 2013 ..
-rw-r----- 1 oracle oinstall 6809714 Jan 31 16:55 listener.log

CREATE OR REPLACE DIRECTORY DIR_LOG1 AS '/u01/app/oracle/diag/tnslsnr/tstdbsrv01/listener/trace/';

CREATE OR REPLACE DIRECTORY DIR_LOG2 AS '/u01/app/oracle/diag/tnslsnr/tstdbsrv02/listener/trace/';

GRANT READ,WRITE ON DIRECTORY DIR_LOG1 TO sys;

GRANT READ,WRITE ON DIRECTORY DIR_LOG2 TO sys;

create table listenerlog1
(
 logtime1 timestamp,
 connect1 varchar2(300), 
 protocol1 varchar2(300),
 action1 varchar2(15),
 service1 varchar2(15),
 return1 number(10)
)
organization external (
 type oracle_loader
 default directory DIR_LOG1
 access parameters
 (
 records delimited by newline
 nobadfile 
 nologfile
 nodiscardfile
 fields terminated by "*" lrtrim
 missing field values are null
 (
 logtime1 char(30) date_format 
 date mask "DD-MON-YYYY HH24:MI:SS",
 connect1,
 protocol1,
 action1,
 service1,
 return1
 )
 )
 location ('listener.log')
)
reject limit unlimited
/

grant select on listenerlog1 to ordba;

select * from sys.listenerlog1;

select logtime1, count(*) from sys.listenerlog 
group by logtime1 order
by logtime1;

select * from sys.listenerlog ;

select * from sys.listenerlog where connect1 like '%HOST%';

select * from sys.listenerlog where connect1 NOT like '%HOST=testodb%';

select * from sys.listenerlog where connect1 like '%HOST=10.%';

create table full_listener_log1
(
 line varchar2(4000)
)
organization external (
 type oracle_loader
 default directory DIR_LOG1
 access parameters (
 records delimited by newline
 nobadfile 
 nologfile 
 nodiscardfile
 )
 location ('listener.log')
)
reject limit unlimited
/

grant select on full_listener_log1 to ordba;

select * from sys.full_listener_log1;

select * from ordba.LISTENERLOG1 where connect1 like '%HOST%';

select * from ordba.LISTENERLOG1 where connect1 NOT like '%HOST=tstdbsrv01%';

select * from ordba.LISTENERLOG1 where connect1 like '%HOST=10.%';

-------------------------

select * from ordba.LISTENERLOG2 where connect1 like '%HOST%';

select * from ordba.LISTENERLOG2 where connect1 NOT like '%HOST=tstdbsrv01%';

select * from ordba.LISTENERLOG2 where connect1 like '%HOST=10.%';