I have an issue I have been battling for a while now. I am exporting certain tables using DBMS_DATAPUMP from one schema (say, a test environment) and I would like to import just a SINGLE table from that dump file into another schema (say, a dev environment). At the same time, I am remapping the source table to a temp table with the same structure.
Let me preface by saying, I used this script to perform the export and import within the SAME schema and it worked fine. This problem only arose when I went to import the data into a different schema, using METADATA_REMAP. Here is the import code.
BEGIN
SELECT TO_CHAR (SYSDATE, 'YYYYMMDDHH24MISS') INTO L_JOB_NUM FROM DUAL;
SELECT TO_CHAR (SYSDATE, 'YYYYMMDD') INTO L_SHORT_DT FROM DUAL;
V_JOB_NUM :=
DBMS_DATAPUMP.OPEN (OPERATION => 'IMPORT',
JOB_MODE => 'TABLE',
JOB_NAME => 'BMF_CASE_IMP_' || L_JOB_NUM,
VERSION => 'COMPATIBLE');
DBMS_DATAPUMP.SET_PARALLEL (HANDLE => V_JOB_NUM, DEGREE => 1);
DBMS_DATAPUMP.ADD_FILE (
HANDLE => V_JOB_NUM,
FILENAME => 'BMF_CASE_IMP_BATCH_' || L_SHORT_DT || '.LOG',
DIRECTORY => G_DUMP_DIRECTORY,
FILETYPE => DBMS_DATAPUMP.KU$_FILE_TYPE_LOG_FILE);
DBMS_DATAPUMP.METADATA_FILTER (HANDLE => V_JOB_NUM,
NAME => 'NAME_EXPR',
VALUE => q'|in ('BATCH')|',
OBJECT_PATH => 'TABLE');
DBMS_DATAPUMP.METADATA_REMAP (HANDLE => V_JOB_NUM,
NAME => 'REMAP_TABLE',
OLD_VALUE => 'BATCH',
VALUE => 'BATCH_TMP');
d('Remapping from schema '|| G_FROM_SCHEMA || ' to ' || G_TO_SCHEMA );
DBMS_DATAPUMP.METADATA_REMAP (HANDLE => V_JOB_NUM,
NAME => 'REMAP_SCHEMA',
OLD_VALUE => G_FROM_SCHEMA,
VALUE => G_TO_SCHEMA);
DBMS_DATAPUMP.ADD_FILE (
HANDLE => V_JOB_NUM,
FILENAME => 'BMF_CASE_EXP_' || i_case_control_id || '.DMP',
DIRECTORY => G_DUMP_DIRECTORY,
FILETYPE => DBMS_DATAPUMP.KU$_FILE_TYPE_DUMP_FILE);
DBMS_DATAPUMP.SET_PARAMETER (HANDLE => V_JOB_NUM,
NAME => 'INCLUDE_METADATA',
VALUE => 0);
DBMS_DATAPUMP.START_JOB (HANDLE => V_JOB_NUM,
SKIP_CURRENT => 0,
ABORT_STEP => 0);
If I remove the metadata filter for the BATCH table and run this, it completes and I get output like the following in the LOG file:
...
. . imported "CMR2_DEV"."NTC_ACTION":"SYS_P1932" 13.84 KB 0 rows
. . imported "CMR2_DEV"."BATCH_TMP":"SYS_P343" 16.70 KB 1 rows
(...and records for all tables in the dump file)
However, as soon as I enable the NAME_EXPR or NAME_LIST filter, I get nothing imported. Just the following errors:
- ORA-31627: API call succeeded but more information is available
- ORA-31655: no data or metadata objects selected for job
This worked when I wasn't moving between schemas so is there some other way I need to write my table filter expression so that will identify the BATCH table when a schema remap is used?
Thanks in advance.
Adam