Mike Ault's thoughts on various topics, Oracle related and not. Note: I reserve the right to delete comments that are not contributing to the overall theme of the BLOG or are insulting or demeaning to anyone. The posts on this blog are provided “as is” with no warranties and confer no rights. The opinions expressed on this site are mine and mine alone, and do not necessarily represent those of my employer.
Thursday, September 15, 2011
Fun with External Tables
I used the following to create an external table:
create table ext_C_STOCK
organization external (
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.