How to parallel load direct path using external Oracle tables?

Question

A few years ago, Thomas Kite said ( here ):

My favorite use of external tables:

Download this really large ASAP file. Previously, scripts were configured to load a direct path in parallel. Coordinate the launch of these scripts. Browse the log files to make sure they are all complete. Split the input file or use skip and load to cut the file up. In short, the pain in the butt.

Now:

create table ET parallel; create table new_table (...) parallel as select * from ET; 

Parallel direct path loading is trivial.

Except for this invalid syntax ...

How to use Oracle external tables to perform "parallel load on the direct path"?

Answer 1

The parallel capabilities of external tables are somewhat limited. As far as I know, you need to either have several files with the same format that can be processed in parallel (see below), or one file with a fixed-length format:

 CREATE TABLE WORKING_HOURS_EXT ( employee_id NUMBER(8), project_id VARCHAR2(20), start_time VARCHAR2(25), end_time VARCHAR2(25) ) ORGANIZATION EXTERNAL ( TYPE ORACLE_LOADER DEFAULT DIRECTORY loader_data_dir ACCESS PARAMETERS ( records delimited by newline fields terminated by ';' ( employee_id, project_id, start_time, end_time ) ) LOCATION ('hours01.txt', 'hours02.txt', 'hours03.txt') ) PARALLEL; ALTER SESSION ENABLE PARALLEL DML; MERGE INTO WORKING_HOURS a USING WORKING_HOURS_EXT b ON (...