Oracle 外部表的创建


记录一下自己创建外部表的过程,因为中间出了一些小错误。

-- 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



相关阅读:
解决SQL Server日志文件损坏严重的问题
XHTML+CSS布局之XHTML应用小结
PHP和Mysqlweb应用开发核心技术 第1部分 Php基础-3 代码组织和重用2
Linux系统配置VI或VIM的技巧
在Visual Studio中调试JavaScript
.Net环境下基于Ajax的MVC方案(1)
更多的属性
ie 调试javascript的工具
众OEM厂商助阵微软推Foundation版服务器系统
修改MySQL的默认密码
动感超强的JS图片轮换特效
asp.net 组件开发中的内嵌资源引用
JavaScript的变量作用域深入理解
linux忘记mysql密码处理方法
快速导航

Copyright © 2016 phpStudy |