oracle 从外部表中选择时出现 sqlplus 错误：ORA-29913：执行 ODCIEXTTABLEOPEN 标注时出错
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/9066191/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
sqlplus error on select from external table: ORA-29913: error in executing ODCIEXTTABLEOPEN callout
提问by Zack Macomber
I have setup a simple Oracle external table test that I (alongside a DBA and Unix admin) can't get to work.
我已经设置了一个简单的 Oracle 外部表测试，我（与 DBA 和 Unix 管理员一起）无法开始工作。
The following is based on Oracle's External Tables Concepts. The database we're using is 11g.
以下内容基于 Oracle 的外部表概念。我们使用的数据库是 11g。
This is the external table definition:
这是外部表定义：
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')
);
This is the contents of "external_table_test.dat":
这是“external_table_test.dat”的内容：
56november, 15, 1980 baker mary alice 09/01/2004
87december, 20, 1970 roper lisa marie 01/01/1999
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:
我能够毫无问题地运行创建“emp_load”的脚本。我也可以很好地描述表格。当我尝试“select * from emp_load”时，出现以下错误：
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
EDIT 1
oracle has read/write permissions on the directory.
编辑 1
oracle 对该目录具有读/写权限。
EDIT 2
I was able to get passed this error by using the following external table definition:
编辑 2
我能够通过使用以下外部表定义来传递此错误：
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;
My .dat file looks like this...
我的 .dat 文件看起来像这样......
056,baker,beth,mary
057,smith,teddy,john
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.
我必须将“EMP.BAD”、“EMP.LOG”和“external_table_test2.dat”的权限设置为 777 才能使其工作。oracle 用户不拥有这些文件，但与这些文件在同一组中。
Any idea why I can't get this to work when I set the permissions on those files to 770? Again, oracle is in the same group as those files, so I figured that 770 would be OK for permissions...
知道为什么当我将这些文件的权限设置为 770 时无法使其工作吗？同样，oracle 与这些文件在同一组中，所以我认为 770 对权限来说是可以的...
采纳答案by Zack Macomber
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 版本在 Red Hat Enterprise Linux 上运行。我们尝试了几种不同类型的组权限，但无济于事。/defaultdir 目录有一个组，它是 oracle 用户的辅助组。当我们将 /defaultdir 目录更新为具有一组“oinstall”（oracle 的主要组）时，我可以毫无问题地从该目录下的外部表中进行选择。
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.
因此，对于其他出现并可能遇到此问题的人，请使目录将 oracle 的主要组作为组，它可能会像对我们一样为您解决问题。我们能够将目录和文件的权限设置为 770，现在在外部表上选择工作正常。
回答by Timo
We faced the same problem:
我们遇到了同样的问题：
ORA-29913: error in executing ODCIEXTTABLEOPEN callout
ORA-29400: data cartridge error error opening file /fs01/app/rms01/external/logs/SH_EXT_TAB_VGAG_DELIV_SCHED.log
In our case we had a RAC with 2 nodes. After giving write permission on the log directory, on both sides, everything worked fine.
在我们的例子中，我们有一个带有 2 个节点的 RAC。在对日志目录授予写权限后，双方一切正常。
回答by DCookie
Keep in mind that it's the user that is running the oracle database that must have write permissions to the /defaultdir directory, not the user logged into oracle. Typically you're running the database as the user "Oracle". It's not the same user (necessarily) that you created the external table with.
请记住，运行 oracle 数据库的用户必须对 /defaultdir 目录具有写权限，而不是登录到 oracle 的用户。通常，您以用户“Oracle”的身份运行数据库。它不是您创建外部表的同一个用户（必然）。
Check your directory permissions, too.
还要检查您的目录权限。
回答by Carlo
We had this error on Oracle RAC 11g on Windows, and the solution was to create the same OS directory tree and external file on both nodes.
我们在 Windows 上的 Oracle RAC 11g 上遇到此错误，解决方案是在两个节点上创建相同的操作系统目录树和外部文件。
回答by Farzam Ahmadi
When you want to create an external_table, all field's name must be written in UPPERCASE.
当你想创建一个external_table时，所有字段的名字都必须大写。
Done.
完毕。
