记录一下自己创建外部表的过程，因为中间出了一些小错误。
-- 1. 创建文件目录
SQL> create directory upload_dir as '/storagepool/upload';
Directory created.
-- 2. 创建外部表
SQL> create table analog_tmp_entrypage_ext_1(
2 PROFILE_ID NUMBER(22),
3 REPORT_TIME NUMBER(22),
4 SESSION_ID NUMBER(22),
5 URL VARCHAR2(2048)
6 )
7 organization external
8 (type oracle_loader
9 default directory upload_dir
10 access parameters
11 (records delimited by newline
12 fields terminated by X'05'
13 missing field values are null
14 (PROFILE_ID, REPORT_TIME, SESSION_ID, URL)
15 )
16 location('weblog.analog_tmp_entrypage_0.dat')
17 );
Table created.
-- 3. 查询报错。因为外部表和sqlldr原理类似，加载外部数据时需要创建log文件，无法加载的数据，会创建bad文件。
-- 这里出错的原因是：oracle对路径'/storagepool/upload'没有访问权限，无法在该路径下创建log文件和bad文件。
SQL> select * from analog_tmp_entrypage_ext_1 where rownum < 2;
select * from analog_tmp_entrypage_ext_1 where rownum < 2
*
ERROR at line 1:
ORA-29913: error in executing ODCIEXTTABLEOPEN callout
ORA-29400: data cartridge error
KUP-04063: unable to open log file ANALOG_TMP_ENTRYPAGE_EXT_1_12190.log
OS error Permission denied
ORA-06512: at "SYS.ORACLE_LOADER", line 19
-- 4. 创建log文件和bad文件的路径，指向tmp分区。
SQL> create directory upload_log_dir as '/tmp/upload_log';
Directory created.
-- 5. 重建外部表，将log和bad指向新路径
SQL> drop table analog_tmp_entrypage_ext_1 purge;
Table dropped.
SQL> create table analog_tmp_entrypage_ext_1(
2 PROFILE_ID NUMBER(22),
3 REPORT_TIME NUMBER(22),
4 SESSION_ID NUMBER(22),
5 URL VARCHAR2(2048)
6 )
7 organization external
8 (type oracle_loader
9 default directory upload_dir
10 access parameters
11 (records delimited by newline
12 badfile upload_log_dir:'analog_tmp_entrypage_ext_1%A_%P.bad'
13 logfile upload_log_dir:'analog_tmp_entrypage_ext_1%A_%P.log'
14 fields terminated by X'05'
15 missing field values are null
16 (PROFILE_ID, REPORT_TIME, SESSION_ID, URL)
17 )
18 location('weblog.analog_tmp_entrypage_0.dat')
19 );
Table created.
-- 6. 重新查询，仍然报错。
SQL> select * from analog_tmp_entrypage_ext_1 where rownum < 2;
select * from analog_tmp_entrypage_ext_1 where rownum < 2
*
ERROR at line 1:
ORA-29913: error in executing ODCIEXTTABLEFETCH callout
# 7. 检查日志文件，原来是因为url默认长度为255，而该列长度为2048，存储了很多长度超过255的记录。
oracle@dw_nearline1:/tmp/upload_log>more analog_tmp_entrypage_ext_1_13056.log
LOG file opened at 07/16/09 13:45:45
Field Definitions for table ANALOG_TMP_ENTRYPAGE_EXT_1
Record format DELIMITED BY NEWLINE
Data in file has same endianness as the platform
Rows with all null fields are accepted
Fields in Data Source:
PROFILE_ID CHAR (255)
Terminated by "05"
Trim whitespace same as SQL Loader
REPORT_TIME CHAR (255)
Terminated by "05"
Trim whitespace same as SQL Loader
SESSION_ID CHAR (255)
Terminated by "05"
Trim whitespace same as SQL Loader
URL CHAR (255)
Terminated by "05"
Trim whitespace same as SQL Loader
KUP-04021: field formatting error for field URL
KUP-04026: field too long for datatype
KUP-04101: record 13 rejected in file /storagepool/upload/weblog.analog_tmp_entrypage_0.dat
KUP-04001: error opening file /storagepool/upload/ANALOG_TMP_ENTRYPAGE_EXT_1_13056.bad
KUP-04017: OS message: Permission denied
-- 8. 再次重建外部表，将列url长度设置为2048。
SQL> drop table analog_tmp_entrypage_ext_1 purge;
Table dropped.
SQL> create table analog_tmp_entrypage_ext_1(
2 PROFILE_ID NUMBER(22),
3 REPORT_TIME NUMBER(22),
4 SESSION_ID NUMBER(22),
5 URL VARCHAR2(2048)
6 )
7 organization external
8 (type oracle_loader
9 default directory upload_dir
10 access parameters
11 (records delimited by newline
12 badfile upload_log_dir:'analog_tmp_entrypage_ext_1%A_%P.bad'
13 logfile upload_log_dir:'analog_tmp_entrypage_ext_1%A_%P.log'
14 fields terminated by X'05'
15 missing field values are null
16 (PROFILE_ID, REPORT_TIME, SESSION_ID, URL char(2048))
17 )
18 location('weblog.analog_tmp_entrypage_0.dat')
19 );
Table created.
SQL> select * from analog_tmp_entrypage_ext_1 where rownum < 2;
PROFILE_ID REPORT_TIME SESSION_ID URL
---------- ----------- -------------- -------------------------
1 20070701 247355 /www.alibaba.com/trade/offer/detail
-- 9. 如果有多个文件，在location参数中加上即可
SQL> drop table analog_tmp_entrypage_ext_1 purge;
Table dropped.
etl@ALIDW> create table analog_tmp_entrypage_ext_1(
2 PROFILE_ID NUMBER(22),
3 REPORT_TIME NUMBER(22),
4 SESSION_ID NUMBER(22),
5 URL VARCHAR2(2048)
6 )
7 organization external(
8 type oracle_loader
9 default directory upload_dir
10 access parameters(
11 records delimited by newline
12 badfile upload_log_dir:'analog_tmp_entrypage_ext_1%A_%P.bad'
13 logfile upload_log_dir:'analog_tmp_entrypage_ext_1%A_%P.log'
14 fields terminated by X'05'
15 missing field values are null
16 (PROFILE_ID, REPORT_TIME, SESSION_ID, URL char(2048))
17 )
18 location(
19 'weblog.analog_tmp_entrypage_0.dat',
20 'weblog.analog_tmp_entrypage_1.dat',
21 'weblog.analog_tmp_entrypage_2.dat',
22 'weblog.analog_tmp_entrypage_3.dat'
23 )
24 );
Table created.
