1 Reply Latest reply on Apr 29, 2014 7:56 AM by Richard Harrison .

    DBMS_DATAPUMP

    Magneto

      Hi

      I have exported few tables from one schema EMP and I'm trying to load those tables in another schema named SALARY using DBMS_DATAPUMP utility

      I'm getting the following error when I try to import the dump.

       

      Master table "SALARY"."IMP_IMPORT" successfully loaded/unloaded

      ORA-31655: no data or metadata objects selected for job

      Starting "SALARY"."IMP_IMPORT": 

      Job "SALARY"."IMP_IMPORT" successfully completed at 14:48:07

       

      I have kept schema_remap also still it fails with the same error

       

      I have this follwing block to do the task , correct me what I'mmissing

       

      DECLARE

      l_dp_handle  NUMBER;

        p_logname    VARCHAR2(200);

        l_job_status VARCHAR2(200);

        l_dir_not_exists EXCEPTION;

        l_dp_not_success EXCEPTION;

        l_from_Schema   VARCHAR2(30):='EMP';

        l_to_Schema     VARCHAR2(30):=sys_context('userenv','CURRENT_SCHEMA');

        l_sts  KU$_STATUS;

        v_logs ku$_LogEntry;

        v_row  PLS_INTEGER;

      BEGIN

       

      l_dp_handle := dbms_datapump.open(operation   => 'IMPORT',

                                          job_mode    => 'TABLE',

                                          remote_link => NULL,

                                          job_name    => 'IMP_IMPORT',

                                          version     => 'LATEST');

        dbms_datapump.metadata_filter(handle => l_dp_handle,

                                      NAME   => 'EXCLUDE_PATH_EXPR',

                                      VALUE  => 'IN (''TRIGGER'')');

      dbms_datapump.add_file(handle    => l_dp_handle,

                                 filename  => p_filename,

                                 directory => p_arc_dir);

        dbms_datapump.add_file(handle    => l_dp_handle,

                               filename  => p_logname,

                               directory => p_arc_dir,

                               filetype  => dbms_datapump.ku$_file_type_log_file);

        dbms_datapump.set_parameter(handle => l_dp_handle,

                                    NAME   => 'TABLE_EXISTS_ACTION',

                                    VALUE  => 'TRUNCATE');

      dbms_datapump.metadata_remap(handle    => l_dp_handle,

                                     NAME      => 'REMAP_SCHEMA',

                                     old_value => l_from_Schema,

                                     VALUE     => l_to_Schema);

        dbms_datapump.start_job(l_dp_handle);

        dbms_datapump.wait_for_job(l_dp_handle, l_job_status);

        dbms_output.put_line('completed datapump import job=>' || systimestamp ||

                             ' l_job_status=>' || l_job_status);

        dbms_datapump.detach(l_dp_handle);

      END;