Oracle 外部表加载监听日志,使用外部表访问监听日志

某天工作是使用外部表加载监听日志信息，观察并分析用户连接数变化趋势

按照eygle循序渐进oracle的教程一步步进行：

测试使用外部表先创建目录：

connect  / as sysdba

create(replace) directory sqldr as 'E:\app\john.tam\oradata\sqldr';

SQL> select * from dba_directories where directory_name =

'SQLDR';

OWNER DIRECTORY_

DIRECTORY_PATH

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

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

SYS SQLDR E:\app\john.tam\oradata\sqldr

然后可以创建外部表了，下面是流程：

创建指向监听器日志目录的Driectory：

SQL> select * from dba_directories where directory_name =

'SQLDR';

OWNER DIRECTORY_

DIRECTORY_PATH

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

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

SYS SQLDR

E:\app\john.tam\oradata\sqldr

SQL> create or replace directory

LISTENER_LOG

2 as

'E:\app\john.tam\product\11.2.0\dbhome_1\NETWORK\log'

3 /

SQL> select * from dba_directories where

DIRECTORY_NAME='LISTENER_LOG';

OWNER DIRECTORY_

DIRECTORY_PATH

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

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

SYS LISTENER_L

E:\app\john.tam\product\11.2.0

OG

\dbhome_1\NETWORK\log

创建外部表：

创建指向监听器日志的外部表：

SQL> CREATE TABLE listener_log(text VARCHAR2(4000))

2 ORGANIZATION

EXTERNAL (

3 TYPE oracle_loader

4 DEFAULT DIRECTORY listener_log

5 ACCESS PARAMETERS (

6 RECORDS DELIMITED BY NEWLINE

7 NOBADFILE

8 NOLOGFILE

9 NODISCARDFILE

10 )

11 LOCATION

('listener.log')

12 )

13 REJECT LIMIT UNLIMITED

14

/

Table created.

查询外部表

现在可以通过SQL查询和访问外部表的数据了：(监听日志共190000行，但12月份的信息都有19000以上行)

SQL> set pagesize 99

SQL> select * from listener_log where rownum

< 12;

select * from listener_log where rownum < 12

*

ERROR at

line 1:

ORA-29913: error in executing ODCIEXTTABLEFETCH callout

ORA-29400:

data cartridge error

KUP-04020: found record longer than buffer size

supported, 524288,

in

E:\app\john.tam\product\11.2.0\dbhome_1\NETWORK\log\listener.log

数据量太大，表内容不能显示。

其实这是因为导入的txt文本没用按格式换行。系统默认一行来处理，数据量当然太大

现抽取10年12月1日的信息生成表：

创建指向监听器日志目录的Driectory：

SQL> create or replace directory

LISTENER121_LOG

2 as

'E:\app\john.tam\product\11.2.0\dbhome_1\NETWORK\log'

3 /

SQL> select * from dba_directories where

DIRECTORY_NAME='LISTENER121_LOG';

OWNER DIRECTORY_

DIRECTORY_PATH

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

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

SYS LISTENER_L

E:\app\john.tam\product\11.2.0

OG

\dbhome_1\NETWORK\log

创建外部表：

创建指向监听器日志的外部表：

SQL> CREATE TABLE listener_log(text VARCHAR2(4000))

2 ORGANIZATION

EXTERNAL (

3 TYPE oracle_loader

4 DEFAULT DIRECTORY

listener121_log

5 ACCESS PARAMETERS (

6 RECORDS DELIMITED BY

NEWLINE

7 NOBADFILE

8 NOLOGFILE

9 NODISCARDFILE

10 )

11

LOCATION ('listener121.log')

12 )

13 REJECT LIMIT

UNLIMITED

14 /

Table created.

这次的查询就成功了。