使用外部表加载监听日志信息,观察并分析用户连接数变化趋势,按照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.
这次的查询就成功了。
--转自