一个奇怪的Oracle SQL Loader错误

今天在用Oracle SQL Loader导入文本文件时，发生了一个奇怪的问题，就是：我的输入文本文件dbim.dat放在oracle用户根目录（$HOME）下时，导入不成功；而将其放在/u01/app/oracle目录下时，则可以成功。

先来看失败的情况：

[oracle@ocp23c test]$ pwd
/home/oracle/test

[oracle@ocp23c test]$ ls -l dbim*
-rw-r--r-- 1 oracle oinstall 1748681 Jan  5 01:08 dbim.dat
-rw-r--r-- 1 oracle oinstall    2321 Jan  5 09:47 dbim.log

[oracle@ocp23c test]$ sqlldr ssb@orclpdb1 table=dbim
Password:

SQL*Loader: Release 23.0.0.0.0 - Production on Fri Jan 5 09:47:46 2024
Version 23.3.0.23.09

Copyright (c) 1982, 2023, Oracle and/or its affiliates.  All rights reserved.

Express Mode Load, Table: DBIM
Path used:      External Table, DEGREE_OF_PARALLELISM=AUTO
SQL*Loader-807: error loading table
ORA-29913: error while processing ODCIEXTTABLEOPEN routine
ORA-29400: data cartridge error
KUP-04040: file dbim.dat in SYS_SQLLDR_XT_TMPDIR_00000 not found
Help: https://docs.oracle.com/error-help/db/ora-29913/

Table DBIM:
  0 Rows successfully loaded.

Check the log files:
  dbim.log
  dbim_%p.log_xt
for more information about the load.

[oracle@ocp23c test]$ ls -l dbim*
-rw-r--r-- 1 oracle oinstall 1748681 Jan  5 01:08 dbim.dat
-rw-r--r-- 1 oracle oinstall    2321 Jan  5 09:47 dbim.log

错误KUP-04040说，输入文件dbim.dat找不到，但这个文件明明就在当前目录。并且日志文件dbim.log也产生了，但是外部表日志文件dbim_%p.log_xt确实没有。

再来看下SQL Loader的日志文件：

[oracle@ocp23c test]$ cat dbim.log

SQL*Loader: Release 23.0.0.0.0 - Production on Fri Jan 5 09:47:46 2024
Version 23.3.0.23.09

Copyright (c) 1982, 2023, Oracle and/or its affiliates.  All rights reserved.

Express Mode Load, Table: DBIM
Data File:      dbim.dat
  Bad File:     dbim_%p.bad
  Discard File:  none specified

 (Allow all discards)

Number to load: ALL
Number to skip: 0
Errors allowed: 50
Continuation:    none specified
Path used:      External Table

Table DBIM, loaded from every logical record.
Insert option in effect for this table: APPEND

   Column Name                  Position   Len  Term Encl Datatype
------------------------------ ---------- ----- ---- ---- ---------------------
A                                   FIRST     *   ,       CHARACTER

Generated control file for possible reuse:
OPTIONS(EXTERNAL_TABLE=EXECUTE, TRIM=LRTRIM)
LOAD DATA
INFILE 'dbim'
APPEND
INTO TABLE DBIM
FIELDS TERMINATED BY ","
(
  A
)
End of generated control file for possible reuse.

created temporary directory object SYS_SQLLDR_XT_TMPDIR_00000 for path /home/oracle/test

enable parallel DML: ALTER SESSION ENABLE PARALLEL DML

creating external table "SYS_SQLLDR_X_EXT_DBIM"

CREATE TABLE "SYS_SQLLDR_X_EXT_DBIM"
(
  "A" VARCHAR2(16)
)
ORGANIZATION external
(
  TYPE oracle_loader
  DEFAULT DIRECTORY SYS_SQLLDR_XT_TMPDIR_00000
  ACCESS PARAMETERS
  (
    RECORDS DELIMITED BY NEWLINE CHARACTERSET US7ASCII
    BADFILE 'SYS_SQLLDR_XT_TMPDIR_00000':'dbim_%p.bad'
    LOGFILE 'dbim_%p.log_xt'
    READSIZE 1048576
    FIELDS TERMINATED BY "," LRTRIM
    REJECT ROWS WITH ALL NULL FIELDS
    (
      "A" CHAR(255)
    )
  )
  location
  (
    'dbim.dat'
  )
)REJECT LIMIT UNLIMITED

executing INSERT statement to load database table DBIM

INSERT /*+ append parallel(auto) */ INTO DBIM
(
  A
)
SELECT
  "A"
FROM "SYS_SQLLDR_X_EXT_DBIM"
SQL*Loader-807: error loading table
ORA-29913: error while processing ODCIEXTTABLEOPEN routine
ORA-29400: data cartridge error
KUP-04040: file dbim.dat in SYS_SQLLDR_XT_TMPDIR_00000 not found
Help: https://docs.oracle.com/error-help/db/ora-29913/


dropping external table "SYS_SQLLDR_X_EXT_DBIM"

Table DBIM:
  0 Rows successfully loaded.

Run began on Fri Jan 05 09:47:46 2024
Run ended on Fri Jan 05 09:47:52 2024

Elapsed time was:     00:00:05.40
CPU time was:         00:00:00.03

其中的临时目录对象SYS_SQLLDR_XT_TMPDIR_00000并没有问题。

百思不得其解，然后将dbim.dat放在另一个目录，再试一次，居然成功了。

$ sqlldr ssb@orclpdb1 table=dbim
Password:

SQL*Loader: Release 23.0.0.0.0 - Production on Fri Jan 5 09:39:07 2024
Version 23.3.0.23.09

Copyright (c) 1982, 2023, Oracle and/or its affiliates.  All rights reserved.

Express Mode Load, Table: DBIM
Path used:      External Table, DEGREE_OF_PARALLELISM=AUTO

Table DBIM:
  158971 Rows successfully loaded.

Check the log files:
  dbim.log
  dbim_%p.log_xt
for more information about the load.

[oracle@ocp23c test]$ ls -l dbim*
-rw-r--r-- 1 oracle oinstall       497 Jan  5 09:39 dbim_28651.log_xt
-rw-r--r-- 1 oracle oinstall   1748681 Jan  5 01:08 dbim.dat
-rw-r--r-- 1 oracle oinstall      2078 Jan  5 09:39 dbim.log

我们看到，除了成功外，和上一个例子有所不同的是，外部表日志文件dbim_28651.log_xt也产生了。

所以，很明确，一定是权限的问题，而且是操作系统文件目录权限的问题。

和同事沟通后，总结原因如下，但目前还没有看到具体的文档说明：

• SQL Loader日志文件dbim.log是由sqlldr程序写的，所以权限是没有问题
• 对外部表的读取，以及写外部表日志的操作，是有数据库内部进程操作的，这个进程的owner在本例中是grid

这样就很好解释了，先看以下的输出：

[oracle@ocp23c ~]$ ls -ld $HOME/test
drwxr-xr-x 4 oracle oinstall 4096 Jan  5 09:47 /home/oracle/test
[oracle@ocp23c ~]$ ls -ld $HOME
drwx------ 9 oracle oinstall 4096 Jan  5 09:47 /home/oracle

[oracle@ocp23c ~]$ ls -ld /u01/app/oracle/test
drwxr-xr-x 2 oracle oinstall 4096 Jan  5 03:43 /u01/app/oracle/test
[oracle@ocp23c ~]$ ls -ld /u01/app/oracle
drwxr-xr-x 10 oracle oinstall 4096 Jan  5 01:23 /u01/app/oracle
[oracle@ocp23c ~]$ ls -ld /u01/app
drwxr-xr-x 7 root oinstall 4096 Dec 20 03:15 /u01/app
[oracle@ocp23c ~]$ ls -ld /u01
drwxr-xr-x 5 root oinstall 4096 Dec 20 03:14 /u01

可以看到，除了oracle用户，其它用户并不能进入其$HOME目录（因为缺省x权限），所以之前的找不到文件错误，是因为grid用户没有权限读取文件所在目录。而/u01/app/oracle目录则所有用户都可以进入。

教训：不要在oracle用户$HOME目录下放置数据文件，建议的数据文件目录可以是/u02下。

遗留问题：找到Oracle文档中对此问题的描述。

参考

• Permission issues when loading text file to external table
• Proper permissions to user for loading external tables
• File not found when querying external table.