在运行ORACLE_LOADER类型的外部表查询时出现这个错误。
数据库的情况比较复杂，简单描述一下。数据库是安装在操作系统oracle用户下，默认组为oinstall，当前使用的是操作系统用户tingkun.yang，同样是oinstall组，因此当前用户可以用SYSDBA身份登录数据库。
以当前用户tingkun.yang运行sqlplus连接数据库，创建目录和外部表，在读取外部表时报错：
[tingkun.yang@enmoteam2 ~]$ id uid=505(tingkun.yang) gid=501(oinstall) groups=501(oinstall),502(dba) [tingkun.yang@enmoteam2 ~]$ ps -ef|grep lgwr oracle 1552 1 0 Sep07 ? 00:04:14 ora_lgwr_enmot2 505 28312 28185 0 22:52 pts/0 00:00:00 grep lgwr [tingkun.yang@enmoteam2 ~]$ sqlplus olasuser/olasuser SQL*Plus: Release 11.2.0.3.0 Production ON Sat Nov 24 22:53:06 2012 Copyright (c) 1982, 2011, Oracle. ALL rights reserved. Connected TO: Oracle DATABASE 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production WITH the Partitioning, OLAP, DATA Mining AND REAL Application Testing options SQL> CREATE directory d_alert AS '/home/oracle/sqlldr'; Directory created. SQL> CREATE TABLE t_alert 2 (text varchar2(4000)) 3 organization external 4 (TYPE oracle_loader 5 DEFAULT directory d_alert 6 access parameters 7 (records delimited BY newline 8 FIELDS (text (1:255) CHAR)) 9 location ('alert_enmot2.log')); TABLE created. SQL> SELECT * FROM t_alert WHERE rownum = 1; SELECT * FROM t_alert WHERE rownum = 1 * ERROR at line 1: ORA-29913: error IN executing ODCIEXTTABLEOPEN callout ORA-29400: DATA cartridge error KUP-04040: file alert_enmot2.log IN D_ALERT NOT found |
查询MOS发现所有的已知问题都和当前的现象不同，该文件已经存在于指定的目录下，甚至在这个目录下，错误日志已经生成：
[oracle@enmoteam2 sqlldr]$ pwd /home/oracle/sqlldr [oracle@enmoteam2 sqlldr]$ ls -l total 664 -rw-rw-rw- 1 oracle oinstall 674964 Nov 22 14:06 alert_enmot2.log -rw-r--r-- 1 oracle oinstall 95 Nov 24 22:54 T_ALERT_28321.log [oracle@enmoteam2 sqlldr]$ more T_ALERT_28321.log LOG file opened at 11/24/12 22:54:52 KUP-04040: file alert_enmot2.log IN D_ALERT NOT found |
经过多次测试发现，如果使用oracle用户启动sqlplus，则可以读取t_alert表：
[oracle@enmoteam2 ~]$ id uid=502(oracle) gid=501(oinstall) groups=501(oinstall),502(dba),506(asmdba) [oracle@enmoteam2 ~]$ sqlplus olasuser/olasuser SQL*Plus: Release 11.2.0.3.0 Production ON Sat Nov 24 22:58:57 2012 Copyright (c) 1982, 2011, Oracle. ALL rights reserved. Connected TO: Oracle DATABASE 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production WITH the Partitioning, OLAP, DATA Mining AND REAL Application Testing options SQL> SELECT * FROM t_alert WHERE rownum = 1; TEXT ---------------------------------------------------- Fri Aug 10 13:39:20 2012 |
一直认为，这种DIRECTORY的读取，使用的是oracle用户的身份和权限，和登录的操作系统用户无关。没有想到，Oracle是根据操作系统用户是否有权限访问这个文件作为外部表访问的条件。但是外部表的日志并没有受权限的控制而已经产生了。