This discussion is archived
6 Replies Latest reply: Aug 22, 2013 8:16 AM by Richard Harrison . RSS

Import Specified Tables in Conjunction with Schema Remap

ThatAdamGuy Newbie
Currently Being Moderated

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

  • 1. Re: Import Specified Tables in Conjunction with Schema Remap
    Richard Harrison . Expert
    Currently Being Moderated

    Hi,

    I get the exact same behaviour as you - i thought maybe as the table name was remapped you then had to filter on the new name rather than the old name - that gives a different error but still doesn't work. It doesn't seem to work on the command line either - as soon as you remap a table you lose the ability to filter on the name it would seem.....(and it would seem to be the filter stops working completely - even if i try and remap one table and filter another that filter doesn't work either).

     

    Looks like you need to find some kind of 2 stage process......

     

    Regards,

    Harry

  • 2. Re: Import Specified Tables in Conjunction with Schema Remap
    Dean Gagne Expert
    Currently Being Moderated

    Let me first say that I never use the api, I always use the client, but this looks like your name_list filter does not specify a schema so the schema running the job is defaulted.  So, if you run the job as system and the table you want to import is emp., then it is looking for system.emp.  I'm pretty sure that you need to add a schema to the name_list filter.

     

    Dean

  • 3. Re: Import Specified Tables in Conjunction with Schema Remap
    ThatAdamGuy Newbie
    Currently Being Moderated

    Thanks Dean.  I tried your suggestion (the best way I know how) and in some cases the error changed but it still never worked.  Here are some of my attempts and their results.

     

    If my filter looks like this (original):

     

          DBMS_DATAPUMP.METADATA_FILTER (HANDLE   => V_JOB_NUM,
                                         NAME     => 'NAME_LIST',
                                         VALUE    => q'|'BATCH'|',
                                         OBJECT_PATH => 'TABLE');

    I get this error:

    - ORA-31627: API call succeeded but more information is available
    - ORA-31655: no data or metadata objects selected for job

    If I change the filter value to (using the source schema name):


                                        VALUE    => q'|'CMR2_TEST.BATCH'|',

    I get:

    - ORA-39002: invalid operation
    - ORA-39166: Object CMR2_TEST.BATCH was not found.

    If I change the filter value to (using the destination schema name):

     

                                         VALUE    => q'|'CMR2_DEV.BATCH'|',

    I get:

    - ORA-39002: invalid operation
    - ORA-39166: Object CMR2_DEV.BATCH was not found.

    I even tried adding quotes around the schema and table name:

                                         VALUE    => q'|'CMR2_TEST'.'BATCH'|',

    and I got:

    - ORA-39001: invalid argument value
    - ORA-39071: Value for NAME_LIST is badly formed.
    - ORA-00907: missing right parenthesis

     

    So anyways, if there is another notation I should try let me know.  Otherwise, I'm going to find something else to busy myself with for a couple of days and then start on a multi-step process as Richard suggested.

     

    Thanks,

    Adam

  • 4. Re: Import Specified Tables in Conjunction with Schema Remap
    Dean Gagne Expert
    Currently Being Moderated

    I think my advice was not correct. The name_list filter only takes a table name.  If you don't do a schema list filter, then the owner of the table defaults to the schema running the job.  I think you need to add a schema filter specifying the owner of the table.

     

    If you can't figure it out, I can try to see if I can come up with the right calls, but it may take me a while.

     

    Dean

  • 5. Re: Import Specified Tables in Conjunction with Schema Remap
    ThatAdamGuy Newbie
    Currently Being Moderated

    Thanks Dean, that did the trick!  I added schema filter specifying the source schema and it was working like a charm again.  I definitely appreciate you (and Richard) taking the time to respond.

     

    Take care,

    Adam

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points