从外部表中选择时出现sqlplus错误:ORA-29913:执行ODCIEXTTABLEOPEN标注时出错 [英] sqlplus error on select from external table: ORA-29913: error in executing ODCIEXTTABLEOPEN callout

问题描述

我已经设置了一个简单的Oracle外部表测试，我(以及DBA和Unix管理员)无法正常工作.

I have setup a simple Oracle external table test that I (alongside a DBA and Unix admin) can't get to work.

以下内容基于Oracle的外部表概念.我们正在使用的数据库是11g.

The following is based on Oracle's External Tables Concepts. The database we're using is 11g.

这是外部表定义:

drop table emp_load;

CREATE TABLE emp_load
    (employee_number      CHAR(5),
     employee_dob         DATE,
     employee_last_name   CHAR(20),
     employee_first_name  CHAR(15),
     employee_middle_name CHAR(15),
     employee_hire_date   DATE)
  ORGANIZATION EXTERNAL
    (TYPE ORACLE_LOADER
     DEFAULT DIRECTORY defaultdir
     ACCESS PARAMETERS
       (RECORDS DELIMITED BY NEWLINE
        FIELDS (employee_number      CHAR(2),
                employee_dob         CHAR(20),
                employee_last_name   CHAR(18),
                employee_first_name  CHAR(11),
                employee_middle_name CHAR(11),
                employee_hire_date   CHAR(10) date_format DATE mask "mm/dd/yyyy"
               )
       )
     LOCATION ('external_table_test.dat')
);

这是"external_table_test.dat"的内容:

This is the contents of "external_table_test.dat":

56november, 15, 1980  baker             mary       alice      09/01/2004
87december, 20, 1970  roper             lisa       marie      01/01/1999

我能够运行没有问题的创建"emp_load"的脚本.我还可以描述这张桌子.当我尝试从emp_load中选择*"时，出现以下错误:

I am able to run the script that creates "emp_load" with no issues. I can also describe the table fine. When I attempt "select * from emp_load", I get the following errors:

SQL> select * from emp_load;
select * from emp_load
              *
ERROR at line 1:
ORA-29913: error in executing ODCIEXTTABLEOPEN callout
ORA-29400: data cartridge error
error opening file /defaultdir/EMP_LOAD_29305.log

编辑1
oracle对目录具有读/写权限.

EDIT 1
oracle has read/write permissions on the directory.

编辑2
我可以通过使用以下外部表定义来传递此错误:

EDIT 2
I was able to get passed this error by using the following external table definition:

CREATE TABLE emp_load
    (employee_number      CHAR(3),
     employee_last_name   CHAR(20),
     employee_middle_name CHAR(15),
     employee_first_name  CHAR(15)
     )
  ORGANIZATION EXTERNAL
    (TYPE ORACLE_LOADER
     DEFAULT DIRECTORY defaultdir
     ACCESS PARAMETERS
       (RECORDS DELIMITED BY NEWLINE
        BADFILE DHHSMAPSIS:'EMP.BAD'
        LOGFILE DHHSMAPSIS:'EMP.LOG'
        FIELDS TERMINATED BY ','
       )
    LOCATION ('external_table_test2.dat')
)
REJECT LIMIT UNLIMITED;

我的.dat文件看起来像这样...

My .dat file looks like this...

056,baker,beth,mary
057,smith,teddy,john

我必须在"EMP.BAD"，"EMP.LOG"和&为了使它正常工作，请将"external_table_test2.dat"设置为777. oracle用户不拥有这些文件，但与文件属于同一组.

I had to set the permissions on "EMP.BAD", "EMP.LOG" & "external_table_test2.dat" to 777 in order to get it to work. The oracle user doesn't own those files but is in the same group as the files are.

有人知道为什么当我将这些文件的权限设置为770时为什么无法使它正常工作吗?同样，oracle与这些文件位于同一组，因此我认为770可以允许使用权限...

推荐答案

我们的Oracle版本在Red Hat Enterprise Linux上运行.我们尝试了几种不同类型的组权限，但无济于事. /defaultdir目录具有一个组，该组是oracle用户的辅助组.当我们将/defaultdir目录更新为具有一组"oinstall"(oracle的主要组)时，我可以从该目录下的外部表中进行选择，而没有任何问题.

Our version of Oracle is running on Red Hat Enterprise Linux. We experimented with several different types of group permissions to no avail. The /defaultdir directory had a group that was a secondary group for the oracle user. When we updated the /defaultdir directory to have a group of "oinstall" (oracle's primary group), I was able to select from the external tables underneath that directory with no problem.

因此，对于其他可能出现此问题的人，请使目录具有oracle的主要组作为该组，并且它可以像我们一样为您解决该问题.我们已经能够在目录和文件上将权限设置为770，并且在外部表上进行选择现在可以正常工作.

So, for others that come along and might have this issue, make the directory have oracle's primary group as the group and it might resolve it for you as it did us. We were able to set the permissions to 770 on the directory and files and selecting on the external tables works fine now.

这篇关于从外部表中选择时出现sqlplus错误:ORA-29913:执行ODCIEXTTABLEOPEN标注时出错的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！