DBMS_DATAPUMP "SUBQUERY" Used Along With "METADATA_REMAP" Does Not Seem To Filter Rows Over "NETWORK_LINK" (REMOTE_LINK) (Doc ID 750409.1)

Last updated on NOVEMBER 28, 2016

Applies to:

Oracle Server - Enterprise Edition - Version: 10.2.0.1 to 10.2.0.4
This problem can occur on any platform.

Symptoms

Using "SUBQUERY" in DataPump API network import still fetches all rows.

For example :

``````````````````

#1 User selected data from one day from the source database using SUBQUERY parameter to import in the target database over the network using NETWORK_LINK parameter.

This will not work as expected because it will fetch all the rows.

#2 User is trying to port the data in to an another schema in the target database , using METADATA_REMAP

DBMS_DATAPUMP.OPEN (operation => 'IMPORT',
job_mode => 'TABLE',
remote_link => 'ORACLE.NET',
job_name => l_job_name
);


-- Specify logfile


DBMS_DATAPUMP.add_file (handle => l_dp_handle,
filename => l_filename || '.LOG',
DIRECTORY => 'DATA_PUMP_DIR',
filetype => DBMS_DATAPUMP.ku$_file_type_log_file
);

--Specify the source owner

DBMS_DATAPUMP.metadata_filter (handle => l_dp_handle,
NAME => 'SCHEMA_EXPR',
VALUE => 'IN (''SCOTT'')'
);


--Specify source table name


DBMS_DATAPUMP.metadata_filter (handle => l_dp_handle,
NAME => 'NAME_EXPR',
VALUE => 'IN (''EMP'')'
);


-- Set subset data export


DBMS_DATAPUMP.data_filter (handle => l_dp_handle,
NAME => 'SUBQUERY',
VALUE => 'WHERE JOIN_DATE =
TO_DATE(''20081118'',''YYYYMMDD'')',
table_name => 'EMP',
schema_name => 'SCOTT'
);

-- Remaps schema SCOTT to HR (source owner to target owner)

DBMS_DATAPUMP.metadata_remap (handle => l_dp_handle,
NAME => 'REMAP_SCHEMA',
old_value => 'SCOTT',
value => 'HR'
);

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