LOCATION Parameter Of Create External Table Using ORACLE_DATAPUMP Driver Does Not Create All Files (Doc ID 1310873.1)

Last updated on NOVEMBER 28, 2016

Applies to:

Oracle Server - Enterprise Edition - Version: 11.2.0.1 and later   [Release: 11.2 and later ]
Oracle Server - Enterprise Edition - Version: 11.1.0.6 to 11.2.0.2   [Release: 11.1 to 11.2]
Information in this document applies to any platform.

Symptoms

The LOCATION parameter of the Create (external) Table command using the ORACLE_DATAPUMP driver  does not create the requested amount of files on a consistent basis.

For example:

- database version 11.1.0.7 and PARALLEL_DEGREE_POLICY=MANUAL.
- the following Create Table command, even though it asks for three DMP files to be created in the LOCATION parameter, only created one DMP file:

create table TEST_1
(
   COL1
)
ORGANIZATION EXTERNAL
(
   TYPE oracle_datapump
   DEFAULT DIRECTORY TEST_DIR
   ACCESS PARAMETERS
   (
      LOGFILE 'TEST.LOG'
   )
   LOCATION ('TEST1.DMP', 'TEST2.DMP', 'TEST3.DMP')
)
PARALLEL 1
as SELECT
COL1
from TEST_2
where (1=1);

#> ls -ltr *.DMP

-rw-r----- 1 oracle oinstall 12288 Apr 5 08:55 TEST1.DMP

The following Create table command creates all three DMP files (notice the value set for PARALLEL):

create table TEST_3
(
   COL1
)
ORGANIZATION EXTERNAL
(
   TYPE oracle_datapump
   DEFAULT DIRECTORY TEST_DIR
   ACCESS PARAMETERS
   (
      LOGFILE 'TEST.LOG'
   )
   LOCATION ('TEST1.DMP','TEST2.DMP','TEST3.DMP')
)
PARALLEL 3
as SELECT
COL1
from TEST_2
where (1=1);

#> ls -ltr *.DMP

-rw-r----- 1 oracle oinstall 12288 Apr 5 09:40 TEST3.DMP
-rw-r----- 1 oracle oinstall 12288 Apr 5 09:40 TEST2.DMP
-rw-r----- 1 oracle oinstall 12288 Apr 5 09:40 TEST1.DMP

All three DMP files contain all of the data from table TEST_3.

Cause

Sign In with your My Oracle Support account

Don't have a My Oracle Support account? Click to get started

My Oracle Support provides customers with access to over a
Million Knowledge Articles and hundreds of Community platforms