Fun with External Tables

During recent testing, I came upon an interesting error. This is on Oracle Linux in an 11.2.0.2 enterprise database. I am doing TPCC testing and wanted a faster way to reload the tables between the tests rather than using the Benchmark Factory loader, which takes forever! I decided to use a datapump format external table using a CTAS to create it for each table after reloading the data then simply do an insert using an APPEND hint to reload the tables following a test and subsequent table recreation (since some are single table clusters and hash clusters truncate doesn’t work so they must be dropped and rebuilt.)

I used the following to create an external table:

create table ext_C_STOCK
organization external (
type oracle_datapump
default directory ext_tab
location ('ext_C_STOCK.ext')) AS
select * from C_STOCK;

The C_STOCK table is part of a single table cluster.

After a test run I dropped and recreated the C_STOCK cluster and table as an empty table then I tried to reload it from the external table:

insert /*+append*/ into C_STOCK select * from ext_C_STOCK;

To which I received:

insert /*+append*/ into C_STOCK select * from ext_C_STOCK
*
ERROR at line 1:
ORA-29913: error in executing ODCIEXTTABLEOPEN callout
ORA-31619: invalid dump file "/mnt/exttables/ext_C_STOCK.ext"
ORA-27072: File I/O error
Linux-x86_64 Error: 22: Invalid argument
Additional information: 4
Additional information: 1
Additional information: -1

The problem turned out to be that the initialization parameter filesystemio_options was set to setall, changing it’s value to NONE fixed the issue.