ORA-30657 External Table Tips

Question:  I am trying to insert into an external table and I get the ORA-30657 error.  

What are ways to populate an external table?

Answer:  External tables are usually loaded from external sources such as spreadsheet data (csv format) or rows that are punched into a flat file in column delimited format.  

Limitations on Oracle External Tables  

In Oracle, external tables have several limitations, including:

•  No support for DML. Oracle external tables are read-only, but the base data can be edited in any text editor.

•  Poor response for high-volume queries.

Oracle external tables have a processing overhead and are not suitable for large tables.  

You can also use Oracle utilities such as utl_file to write to an external file, which is mapped to an external table.  

The oerr utility shows this for the ORA-30657 error:

ORA-30657:  Operation not supported on external organized table  

Cause: User attempted on operation on an external table which is not supported.  

Action: Don't do that!

See these related notes on external tables:

•  External Tables in Oracle RAC

• Oracle Spreadsheet SQL with External Tables

• Using Oracle External Tables for Data transfer

• Oracle External Table error and ORA-29913

• Oracle Disk I/O Tuning and External Tables

• Updating external tables in Oracle

• Oracle Table Design utl_file External Tables

• SQL*Loader External Tables

• Oracle SQL and External Tables

Get the Complete Oracle SQL Tuning Information  The landmark book "Advanced Oracle SQL Tuning  The Definitive Reference"  is filled with valuable information on Oracle SQL Tuning. This book includes scripts and tools to hypercharge Oracle 11g performance and you can buy it for 30% off directly from the publisher.