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.