这周由于工作中需要将一个有30M左右的txt格式的数据导入到数据库中,由于文件还是比较大的就趁此机会使用了下oracle的外部表进行了数据的导入,具体步骤如下:
1.使用外部表先要创指定一个ORACLE DIRECTORY,创建的目录名为temp_dir,指向操作系统文件:/home/oracle/temp_dir
2.将我要导入的txt文件上传到服务器,我的文件名为msisdn_imsi0610.txt;
3.创建外部表,我的表名为msis
4.创建成功后进行查询,出现错误:
开初,以为是没有给用户授权新创的DIRECTORY的使用权限,因此用sys登录进行授权:
用原来用户登录查询还是报错误:
检查发现是我的sisdn_imsi0610.txt文件没有放到创建的目录temp_dir中(即对应的操作系统目录
/home/oracle/temp_dir),将文件放到对应的目录中,
再进行查询时发现已经能够进行查询了,30M的文件有1038706数据,再将这个外部表的数据转入到内部表中
再看temp_dir(/home/oracle/temp_dir)目录,发现除开上传的TXT文件外多了3个log文件;
查看这些log文件的信息,发现这些日志是访问外部表时的错误信息
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.
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;
点击(此处)折叠或打开
- 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
点击(此处)折叠或打开
- 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
点击(此处)折叠或打开
- $ mv msisdn_imsi0610.txt temp_dir
点击(此处)折叠或打开
- SQL> select count(*) from msisdn_imsi_ex;
-
- COUNT(*)
- ----------
- 1038706
- --建立表
- SQL> create table msisdn_imsi as select * from msisdn_imsi_ex;
-
- Table created.
点击(此处)折叠或打开
- $ ls
- MSISDN_IMSI_EX_23789758.log MSISDN_IMSI_EX_39190554.log
- MSISDN_IMSI_EX_31326264.log msisdn_imsi0610.txt
点击(此处)折叠或打开
- $ 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博客 ” ,链接:http://blog.itpub.net/24107336/viewspace-1210327/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/24107336/viewspace-1210327/