ORA-39001 When Using DBMS_DATAPUMP.DATA_FILTER To Export With INCLUDE_ROWS Set To 0 (Doc ID 1059801.1)

Last updated on FEBRUARY 05, 2010

Applies to:

Oracle Server - Enterprise Edition - Version: 10.2.0.1 to 11.2.0.0 - Release: 10.2 to 11.2
Information in this document applies to any platform.

Symptoms

When attempting to use the Data Pump API (DBMS_DATAPUMP) , it fails when attempting to use ESTIMATE with INCLUDE_ROWS set to 0, with the following error:

For example:
SQL> connect system/oracle
Connected.
SQL> declare
2 h1 NUMBER;
3 begin
4 h1 := dbms_datapump.open (operation => 'EXPORT', job_mode => 'SCHEMA',job_name => 'SCOTTDOO', version => 'COMPATIBLE');
5 dbms_datapump.set_parallel(handle => h1, degree => 1);
6 dbms_datapump.add_file(handle => h1, filename => 'EXPDAT.LOG', directory => 'DATA_PUMP_DIR', filetype => 3);
7 dbms_datapump.set_parameter(handle => h1, name => 'KEEP_MASTER', value=> 0);
8 dbms_datapump.metadata_filter(handle => h1, name => 'SCHEMA_EXPR', value => 'IN(''SCOTT'')');
9 dbms_datapump.add_file(handle => h1, filename => 'EXPDAT%U.DMP', directory => 'DATA_PUMP_DIR', filetype => 1);
10 dbms_datapump.set_parameter(handle => h1, name => 'INCLUDE_METADATA', value => 1);
11 dbms_datapump.data_filter(handle => h1, name => 'INCLUDE_ROWS', value => 0);
12 dbms_datapump.set_parameter(handle => h1, name => 'DATA_ACCESS_METHOD', value => 'AUTOMATIC');
13 dbms_datapump.set_parameter(handle => h1, name => 'ESTIMATE', value =>'BLOCKS');
14 dbms_datapump.start_job(handle => h1, skip_current => 0, abort_step =>0);
15 dbms_datapump.detach(handle => h1);
16 end;
17 /
declare
*
ERROR at line 1:
ORA-39001: invalid argument value
ORA-06512: at "SYS.DBMS_SYS_ERROR", line 79
ORA-06512: at "SYS.DBMS_DATAPUMP", line 2953
ORA-06512: at "SYS.DBMS_DATAPUMP", line 4742
ORA-06512: at line 13

We see on line 13 that we are setting the ESTIMATE parameter to BLOCKS, but on line 11 we have  INCLUDE_ROWS set to 0.

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