安装oracle ora-01005,Exteernal table ORA-29913，ORA-30653，KUP-01005

今天再做外部文件数据转移测试的时候报错：

ORA-29913: 执行 ODCIEXTTABLEFETCH

调出时出错

ORA-30653: 已达到拒绝限制值

发现时外部文件的内容和数据库内建表结构不符合导致：在网上搜了一下有一篇博客他是因为外部文件里有列名，所以他处理的时候加了一个语句

records delimited by

newline

skip=1

—————————————————————————————————————————————

1. create

directory$ sqlplus / as

sysdba

SQL> create or replace directory

DPUMPDIR as '/home/oracle/dmp';

SQL> col owner for a5;

SQL> col directory_name for a15;

SQL> col directory_path for a20;

SQL> SELECT * FROM dba_directories where

directory_name='DPUMPDIR';

OWNER DIRECTORY_NAME DIRECTORY_PATH

----- --------------- ----------------

SYS DPUMPDIR /home/oracle/dmp

SQL>grant read ,write on

directory DPUMPDIR to

&user;

2.编辑数据文件emp.dat 将emp.dat存放到emp.dat放到directory对应的目录下，即/home/oracle/dmp

emp.dat 内容如下：

EMPNO,ENAME,JOB,MGR,HIREDATE,SAL,COMM,DEPTNO

7369,SMITH,CLERK,7902,17-DEC-80,800,,20

7499,ALLEN,SALESMAN,7698,20-FEB-81,1600,300,30

7521,WARD,SALESMAN,7698,22-FEB-81,1250,500,30

7566,JONES,MANAGER,7839,02-APR-81,2975,,20

7654,MARTIN,SALESMAN,7698,28-SEP-81,1250,1400,30

7698,BLAKE,MANAGER,7839,01-MAY-81,2850,,30

7782,CLARK,MANAGER,7839,09-JUN-81,2450,,10

7788,SCOTT,ANALYST,7566,19-APR-87,3000,,20

7839,KING,PRESIDENT,,17-NOV-81,5000,,10

7844,TURNER,SALESMAN,7698,08-SEP-81,1500,0,30

7944,TURNER,SALESMAN,7698,08-SEP-81,1500,0,30

3.创建外部表 create

external_tableSQL> conn scott/tiger

Connected.

SQL> show user;

USER is "SCOTT"

SQL> create table

ext_emp

2 (EMPNO NUMBER(4) ,

3 ENAME VARCHAR2(10),

4 JOB VARCHAR2(9),

5 MGR NUMBER(4),

6 HIREDATE DATE,

7 SAL NUMBER(7, 2),

8 COMM NUMBER(7, 2) ,

9 DEPTNO NUMBER(2)

10 )

11 ORGANIZATION EXTERNAL

12 ( type oracle_loader

13 default directory DPUMPDIR

14 access parameters

15 (

16 fields terminated by ',' )

17 location ('emp.dat')

18 );

Table created SQL> select * from ext_emp;

select * from ext_emp

*ERROR at

line 1:

ORA-29913: error in executing ODCIEXTTABLEFETCH callout

ORA-30653: reject limit reached

在对应的directory目录下查看外部表对应的bad和log日志，发现加载的数据文件中有标题。

修改创建外部表的语句，在其中加入 skip=1

重新创建。(或者直接删除标题行也可以)

SQL> create table

ext_emp

2 (EMPNO NUMBER(4) ,

3 ENAME VARCHAR2(10),

4 JOB VARCHAR2(9),

5 MGR NUMBER(4),

6 HIREDATE DATE,

7 SAL NUMBER(7, 2),

8 COMM NUMBER(7, 2) ,

9 DEPTNO NUMBER(2)

10 )

11 ORGANIZATION EXTERNAL

12 ( type oracle_loader

13 default directory DPUMPDIR

14 access parameters

15 (

16skip=1 17 fields terminated by ',' )

18 location ('emp.dat')

19 );

Table created

SQL> select * from ext_emp;

select * from ext_empORA-29913: 执行 ODCIEXTTABLEOPEN

调出时出错

ORA-29400: 数据插件错误KUP-00554: error encountered while parsing access

parameters

KUP-01005: syntax error: found "skip": expecting one of: "column,

fields, records"

KUP-01007: at line 1 column 1在创建外部表的语句中加入结束标记 records delimited by

newline，

指定记录以换行符结束，重新创建。 SQL> drop table ext_emp;

Table dropped

SQL> create table ext_emp

2 (EMPNO NUMBER(4) ,

3 ENAME VARCHAR2(10),

4 JOB VARCHAR2(9),

5 MGR NUMBER(4),

6 HIREDATE DATE,

7 SAL NUMBER(7, 2),

8 COMM NUMBER(7, 2) ,

9 DEPTNO NUMBER(2)

10 )

11 ORGANIZATION EXTERNAL

12 ( type oracle_loader

13 default directory DPUMPDIR

14 access parameters

15 (

16

records delimited by

newline 17

skip=1 18 fields terminated by "," )

19 location ('emp.dat')

20 );

Table created

SQL> select count(1) from

ext_emp;

COUNT(1)

----------

11