【故障描述】

使用网络连接的方式登录19c的PDB，然后创建oracle_loader驱动模式的外部表。创建成功后，在检索数据时，出现KUP-04040的报错，过程如下：

[oracle@sdedu ~]$ sqlplus sys/oracle@pdb1 as sysdba

SQL*Plus: Release 19.0.0.0.0 - Production on Sun Mar 7 18:12:34 2021
Version 19.3.0.0.0

Copyright (c) 1982, 2019, Oracle.  All rights reserved.


Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.3.0.0.0

SQL> create directory dmp_dir as '/home/oracle/ext_table/';

Directory created.

SQL> CREATE TABLE emp_load 
  2  (
  3   first_name CHAR(15), 
  4   last_name CHAR(20), 
  5   year_of_birth CHAR(4)
  6  )
  7  ORGANIZATION EXTERNAL 
  8  (
  9    TYPE ORACLE_LOADER 
 10    DEFAULT DIRECTORY dmp_dir
 11    ACCESS PARAMETERS 
 12    (
 13     RECORDS DELIMITED BY '|'
 14     DISABLE_DIRECTORY_LINK_CHECK
 15     FIELDS TERMINATED BY ','
 16    (
 17     first_name CHAR(7),
 18     last_name CHAR(8),
 19     year_of_birth CHAR(4))
 20    )
 21     LOCATION ('info.dat')
 22  )
 23  REJECT LIMIT UNLIMITED
 24  ;

Table created.

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
KUP-04040: file info.dat in DMP_DIR not found

【解决过程】

经过测试发现，如果在session中切换容器到pdb1中，访问就是正常的。

SQL> alter session set container=pdb1;

Session altered.

SQL> select * from emp_load;

FIRST_NAME      LAST_NAME            YEAR
--------------- -------------------- ----
Alvin           Tolliver             1976
Kenneth         Baer                 1963
Mary            Dube                 1973

SQL> 

这个报错，和可能是由于文件和目录权限导致，因此检查一下权限：

[oracle@sdedu ~]$ ls -alt ext_table/
total 24
drwx------. 29 oracle oinstall 4096 Mar  7 21:10 ..
drwxrwxrwx   2 oracle oinstall  100 Mar  7 18:51 .
-rwxrwxrwx   1 oracle oinstall   55 Mar  7 17:59 INFO.DAT
[oracle@sdedu ~]$ 

从文件的权限没有问题，目录的权限也是没有问题。但为啥还是不行呢？

索性换一下目录来出来吧。

我们使用pdb1默认的DATA_PUMP_DIR目录来进行外部表的创建。

/u01/app/oracle/admin/SS19C/dpdump/ADB4AF11A2A07F0BE0530A0200C001E6

此时发现成功了！

[oracle@sdedu ADB4AF11A2A07F0BE0530A0200C001E6]$ sqlplus sys/oracle@pdb1 as sysdba

SQL*Plus: Release 19.0.0.0.0 - Production on Sun Mar 7 21:31:24 2021
Version 19.3.0.0.0

Copyright (c) 1982, 2019, Oracle.  All rights reserved.


Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.3.0.0.0

SQL> drop table emp_load;

Table dropped.
 
SQL> CREATE TABLE emp_load
  2  (
  3   first_name CHAR(15),
  4   last_name CHAR(20), 
  5   year_of_birth CHAR(4)
  6  )
  7  ORGANIZATION EXTERNAL 
  8  (
  9    TYPE ORACLE_LOADER 
 10    DEFAULT DIRECTORY DATA_PUMP_DIR
 11    ACCESS PARAMETERS 
 12    (
 13     RECORDS DELIMITED BY '|'
 14     FIELDS TERMINATED BY ','
 15    (
 16     first_name CHAR(7),
 17     last_name CHAR(8),
 18     year_of_birth CHAR(4))
 19    )
 20     LOCATION ('INFO.DAT')
 21  )
 22  REJECT LIMIT UNLIMITED
 23  ;

Table created.

SQL> select * from emp_load;

FIRST_NAME      LAST_NAME            YEAR
--------------- -------------------- ----
Alvin           Tolliver             1976
Kenneth         Baer                 1963
Mary            Dube                 1973

SQL> 

换一个目录在测试一下，也是成功了，此时发现ORIGIN_CON_ID也为1，1代表根容器。看来要想成功，ORIGIN_CON_ID列必须为1，也就是从根容器继承过来的。

[oracle@sdedu ext_table]$ sqlplus sys/oracle@pdb1 as sysdba

SQL*Plus: Release 19.0.0.0.0 - Production on Sun Mar 7 22:46:22 2021
Version 19.3.0.0.0

Copyright (c) 1982, 2019, Oracle.  All rights reserved.


Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.3.0.0.0

SQL> select * from dba_directories where directory_name='SDO_DIR_ADMIN';

  
OWNER    DIRECTORY_NAME   DIRECTORY_PATH                                   ORIGIN_CON_ID
-------- --------------- ------------------------------------------------- -------------
SYS      SDO_DIR_ADMIN   /u01/app/oracle/product/19.3.0/dbhome_1/md/admin   1


1 rows selected.


SQL> show pdbs;

    CON_ID CON_NAME                       OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
         3 PDB1                           READ WRITE NO

SQL> !cp /home/oracle/ext_table/INFO.DAT /u01/app/oracle/product/19.3.0/dbhome_1/md/admin/

SQL> drop table emp_load;

Table dropped.

SQL> CREATE TABLE emp_load
  2  (
  3   first_name CHAR(15),
  4   last_name CHAR(20), 
  5   year_of_birth CHAR(4)
  6  )
  7  ORGANIZATION EXTERNAL 
  8  (
  9    TYPE ORACLE_LOADER 
 10    DEFAULT DIRECTORY SDO_DIR_ADMIN
 11    ACCESS PARAMETERS 
 12    (
 13     RECORDS DELIMITED BY '|'
 14     FIELDS TERMINATED BY ','
 15    (
 16     first_name CHAR(7),
 17     last_name CHAR(8),
 18     year_of_birth CHAR(4))
 19    )
 20     LOCATION ('INFO.DAT')
 21  )
 22  REJECT LIMIT UNLIMITED
 23  ;

Table created.

SQL> select * from emp_load;

FIRST_NAME      LAST_NAME            YEAR
--------------- -------------------- ----
Alvin           Tolliver             1976
Kenneth         Baer                 1963
Mary            Dube                 1973

SQL> 

在创建Directory时，是无法指定ORIGIN_CON_ID来源的。在那个容器创建，就会是那个容器的ID。这种策略很可能是BUG。

最终，由于目录权限问题导致创建外部表失败，此时我们可以使用PDB默认的DUMP目录，也就是ORIGIN_CON_ID为1的目录，来避免这个错误。

【参考资料】

ORA-29913: Error in Executing ODCIEXTTABLEOPEN Callout (Doc ID 458620.1)