这周由于工作中需要将一个有30M左右的txt格式的数据导入到数据库中，由于文件还是比较大的就趁此机会使用了下oracle的外部表进行了数据的导入，具体步骤如下:
1.使用外部表先要创指定一个ORACLE DIRECTORY，创建的目录名为temp_dir,指向操作系统文件:/home/oracle/temp_dir

点击(此处)折叠或打开

SQL> conn / as sysdba
Connected.
SQL> create or replace directory temp_dir as \'/home/oracle/temp_dir\'
2 ;
Directory created.

2.将我要导入的txt文件上传到服务器,我的文件名为msisdn_imsi0610.txt；
3.创建外部表，我的表名为msis

点击(此处)折叠或打开

CREATE TABLE msisdn_imsi_ex
(
msisdn varchar2(20),
imsi varchar2(18)
)
ORGANIZATION EXTERNAL
(
type ORACLE_LOADER
DEFAULT DIRECTORY temp_dir
ACCESS PARAMETERS
(
records delimited by newline
fields
REJECT ROWS WITH ALL NULL FIELDS
)
LOCATION
(
\'msisdn_imsi0610.txt\'
)
)
REJECT LIMIT unlimited;

4.创建成功后进行查询,出现错误:

点击(此处)折叠或打开

SQL> select * from msisdn_imsi_ex;
select * from msisdn_imsi_ex
*
ERROR at line 1:
ORA-29913: error in executing ODCIEXTTABLEOPEN callout
ORA-29400: data cartridge error
KUP-04040: file msisdn_imsi0610.txt in TEMP_DIR not found

开初，以为是没有给用户授权新创的DIRECTORY的使用权限，因此用sys登录进行授权:

点击(此处)折叠或打开

SQL> conn / as sysdba
Connected.
SQL> grant read, write on directory temp_dir to mobile_v2;
Grant succeeded.

用原来用户登录查询还是报错误:

点击(此处)折叠或打开

SQL> select count(*) from msisdn_imsi_ex;
select count(*) from msisdn_imsi_ex
*
ERROR at line 1:
ORA-29913: error in executing ODCIEXTTABLEOPEN callout
ORA-29400: data cartridge error
KUP-04040: file msisdn_imsi0610.txt in TEMP_DIR not found

检查发现是我的sisdn_imsi0610.txt文件没有放到创建的目录temp_dir中（即对应的操作系统目录/home/oracle/temp_dir），将文件放到对应的目录中，

点击(此处)折叠或打开

$ mv msisdn_imsi0610.txt temp_dir

再进行查询时发现已经能够进行查询了,30M的文件有1038706数据，再将这个外部表的数据转入到内部表中

点击(此处)折叠或打开

SQL> select count(*) from msisdn_imsi_ex;
COUNT(*)
----------
1038706
--建立表
SQL> create table msisdn_imsi as select * from msisdn_imsi_ex;
Table created.

再看temp_dir(/home/oracle/temp_dir)目录,发现除开上传的TXT文件外多了3个log文件；

点击(此处)折叠或打开

$ ls
MSISDN_IMSI_EX_23789758.log MSISDN_IMSI_EX_39190554.log
MSISDN_IMSI_EX_31326264.log msisdn_imsi0610.txt

查看这些log文件的信息，发现这些日志是访问外部表时的错误信息

点击(此处)折叠或打开

$ cat MSISDN_IMSI_EX_23789758.log
LOG file opened at 07/01/14 12:30:57
KUP-05004: Warning: Intra source concurrency disabled because parallel select was not requested.
KUP-05008: Warning: Intra source concurrency disabled because the input data is in DELIMITED record format and the characterset is neither fixed width nor UTF8.
KUP-04040: file msisdn_imsi0610.txt in TEMP_DIR not found

ITPub博客

ORACLE外部表使用

最新文章