记录一下自己创建外部表的过程，因为中间出了一些小错误。
--
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
责任编辑：小草
您现在的位置： 