Hi all DBA gurus....
I am currently stuck at Import Datadump and would like someone to show some lights.....
I used this parfile to run an export from a Source Schema:
source_exp.par
directory=DATA_PUMP_DIR
dumpfile=test.DMP
logfile=_test_exp.log
content=all
exclude=statistics,grant,index,constraint,trigger
tables=item_criteria,item_table,item_criteria_value
query=item_criteria:"where item_id in (-1,-2,-3)"
query=item_table:"where item_id in (-1,-2,-3)"
query=item_criteria_value:"where item_criteria_id in (select t.item_criteria_id from item_criteria t where t.item_id in (-1,-2,-3))"
Then I run this:
$expdp SOURCE/SOURCE@MY_DB parfile=source_exp.par
And the export completed successfully.
I then run the import using these conditions into the Target Schema:
l_job_name := 'IMPDP_'||p_target_schema||'_'||TO_CHAR(SYSDATE,'yyyymmddhh24mi');
l_dp_handle := DBMS_DATAPUMP.open(
operation => 'IMPORT',
job_mode => 'TABLE',
remote_link => NULL,
job_name => l_job_name,
version => 'LATEST');
DBMS_DATAPUMP.add_file(
handle => l_dp_handle,
filename => 'test.DMP',
directory => v_import_dir,
reusefile => 1);
DBMS_DATAPUMP.add_file(
handle => l_dp_handle,
filename => '_test_import.log',
directory => v_import_dir,
filetype => DBMS_DATAPUMP.ku$_file_type_log_file);
DBMS_DATAPUMP.metadata_filter(
handle => l_dp_handle,
name => 'SCHEMA_EXPR',
value => '= ''FPTARGET''');
DBMS_DATAPUMP.metadata_filter(
handle => l_dp_handle,
name => 'EXCLUDE_PATH_EXPR',
value => 'IN (''GRANT'',''INDEX'',''CONSTRAINT'')');
DBMS_DATAPUMP.metadata_filter(
handle => l_dp_handle,
name => 'NAME_EXPR',
value => 'IN (''ITEM_CRITERIA'', 'ITEM_TABLE'', 'ITEM_CRITERIA_VALUE'')',
object_type => 'TABLE');
DBMS_DATAPUMP.set_parameter(
handle => l_dp_handle,
name => 'TABLE_EXISTS_ACTION',
value => 'APPEND');
DBMS_DATAPUMP.log_entry(
handle => l_dp_handle,
message => 'Import job starts at '||TO_CHAR(SYSDATE, 'YYYY-Mon-DD HH24:MI:SS'));
DBMS_DATAPUMP.start_job (
handle => l_dp_handle);
But then I hit the following errors:
ORA-39002: invalid operation
ORA-39040: Schema expression "SCHEMA_EXPR" must identify exactly one schema.
Could someone please point out what I have done wrong/missed/etc please?
Thanks very much.