10 Replies Latest reply on Apr 17, 2014 2:45 PM by Solomon Yakobson

    Importing a single table data using dbms_datapump in oracle 10g

    Sudhir

      Hello ,

       

      I am having 2 database db1 and db2.

      we have data user ,which contains all the tables.

      I need to import data from table data.RENTAL_INV in db1 to the same table data.RENTAL_INV in db2 using a dblink.

       

      i created a user clonedev who can do the importing.

       

      I am using dbms_datapump to do the importing but my exp is failing with the below error.

       

      errors in the log file:

       

      [kususu1@seattle01 tmp]$ DATAPUMPIMP170420141309.log

      -bash: DATAPUMPIMP170420141309.log: command not found

      [kususu1@seattle01 tmp]$ cat DATAPUMPIMP170420141309.log

      Starting "CLONEDEV"."SYS_IMPORT_TABLE_05":

      Estimate in progress using BLOCKS method...

      Processing object type TABLE_EXPORT/TABLE/TABLE_DATA

      Total estimation using BLOCKS method: 0 KB

      ORA-39166: Object IN ('RENTAL_INV') was not found.

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

      Job "CLONEDEV"."SYS_IMPORT_TABLE_05" completed with 2 error(s) at 13:09:55

       

       

      code:

      create or replace procedure prc_import_rental_inv is

       

       

        l_dp_handle NUMBER;

        v_job_state varchar2(4000);

        l_last_job_state VARCHAR2(30) := 'UNDEFINED';

        l_job_state VARCHAR2(30) := 'UNDEFINED';

        l_sts  KU$_STATUS;

        v_logs ku$_LogEntry;

        v_row  PLS_INTEGER;

        v_string   varchar2(2000);

        l_short_dt varchar2(20);

       

       

       

       

      begin

       

       

          dbms_output.put_line('Import rental_inv table started.');

       

       

          SELECT TO_CHAR (SYSDATE, 'DDMMYYYYHH24MI')

          INTO L_SHORT_DT

          FROM DUAL;

       

       

        --Creating a named data pump job to do the schema import.

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

                                            job_mode    => 'TABLE',

                                            remote_link => 'PSUP_DATA',

                                            version     => 'LATEST');

          dbms_output.put_line('prc_import_rental_inv: File handle opened.');

       

       

        -- Specify a log file for the job (using the handle just returned)

          -- and a directory object, which must already be defined and accessible

          -- to the user running this procedure.

        -- used already created a DMPDIR directory in /tmp.

       

       

          DBMS_DATAPUMP.add_file(handle    => l_dp_handle,

                                 filename  => 'DATAPUMPIMP'||L_SHORT_DT||'.log',

                                 directory => 'DMPDIR',

                                 filetype  => DBMS_DATAPUMP.KU$_FILE_TYPE_LOG_FILE);

          dbms_output.put_line('prc_import_rental_inv: added the log file.');

       

       

          dbms_output.put_line('prc_import_rental_inv: Include only table.');

       

       

            -- A metadata filter is used to specify the schema that will be exported.

          DBMS_DATAPUMP.METADATA_FILTER(handle      => l_dp_handle,

                                        name        => 'SCHEMA_LIST',

                                        value       => 'IN ('''||'IRIS_DATA'||''')');

                                      

              --Set the parameter to ignore if the db objects are already imported.

          dbms_datapump.set_parameter ( handle   => l_dp_handle,

                                        name     => 'TABLE_EXISTS_ACTION',

                                        value    => 'TRUNCATE' );

                                      

          --Add the tables you wish to import.

          v_string  := 'IN ('''||'RENTAL_INV'||''')';

       

       

          --Metadat filter to remap source and target schemas.

          dbms_datapump.metadata_remap ( handle     => l_dp_handle,

                                         name       => 'REMAP_SCHEMA',

                                         old_value  => 'IRIS_DATA',

                                         value      => 'CLONEDEV');

       

       

        -- A metadata filter is used to specify the table that will be exported.

          DBMS_DATAPUMP.METADATA_FILTER(handle      => l_dp_handle,

                                        name        => 'NAME_LIST',

                                        value       => v_string,

                                        object_type => 'TABLE');

          dbms_output.put_line('prc_import_rental_inv: Added the filter to pull only selected objects from the user.');

       

       

          dbms_datapump.data_filter(handle     => l_dp_handle,

                                    name       => 'INCLUDE_ROWS',

                                    value      => 1);

       

       

       

       

          dbms_output.put_line('prc_import_rental_inv: Added the data filter to get only the structure.');

       

       

          dbms_output.put_line('prc_import_rental_inv: starting job.');

       

       

          -- Start the job. An exception will be generated if something is not set up properly.

          DBMS_DATAPUMP.start_job(handle   => l_dp_handle);

       

       

          DBMS_DATAPUMP.WAIT_FOR_JOB(handle     => l_dp_handle,

                                     job_state  => v_job_state);

       

       

          dbms_output.put_line('prc_import_rental_inv: completed job.');

       

       

      exception

      WHEN OTHERS THEN

          dbms_datapump.get_status(handle     => NULL,

                                   mask       => 8,

                                   timeout    => 0,

                                   job_state  => v_job_state,

                                   status     => l_sts);

          v_logs := l_sts.error;

          dbms_output.put_line('prc_import_rental_inv: Exception Raised.');

          v_row := v_logs.FIRST;

          LOOP

            EXIT WHEN v_row IS NULL;

            dbms_output.put_line('logLineNumber=' || v_logs(v_row).logLineNumber);

            dbms_output.put_line('ErrorNumber=' || v_logs(v_row).errorNumber);

            dbms_output.put_line('LogText=' || v_logs(v_row).LogText);

            v_row := v_logs.NEXT(v_row);

          END LOOP;

       

       

      end prc_import_rental_inv;

       

       

      please advice.

        • 1. Re: Importing a single table data using dbms_datapump in oracle 10g
          Solomon Yakobson

          I don't see where you define dump file(s). All I see is single DBMS_DATAPUMP.add_file to add log file.

           

          SY.

          • 2. Re: Importing a single table data using dbms_datapump in oracle 10g
            Solomon Yakobson

            Never mind, I missed you are using remote_link.

             

            SY.

            • 3. Re: Importing a single table data using dbms_datapump in oracle 10g
              Sudhir

              Hello Solomon,

               

              Thanks for the reply.

               

              But i need to directly import using the dblink.

               

              I don't have any export dump files to import.

               

              Please advice.

              • 5. Re: Importing a single table data using dbms_datapump in oracle 10g
                Sudhir

                currently i am using this method only to pull the data.but it's taking 2 hr and 25 min to pull the complete table.

                 

                So want to try using the data pump method , so that it will be faster. right ?

                 

                Regards

                • 6. Re: Importing a single table data using dbms_datapump in oracle 10g
                  Solomon Yakobson

                  Based on ORA-39166: Object IN ('RENTAL_INV') was not found. User who is running data pump import has no access to table IRIS_DATA.RENTAL_INV@PSUP_DATA.

                   

                  SY.

                  • 7. Re: Importing a single table data using dbms_datapump in oracle 10g
                    Sudhir

                    Hello Solomon,

                     

                    the user has all the below given privilges

                     

                    GRANT SELECT ANY TABLE TO CLONEDEV;

                    GRANT DELETE ANY TABLE TO CLONEDEV;

                    GRANT INSERT ANY TABLE TO CLONEDEV;

                    GRANT CREATE TABLE TO CLONEDEV;

                    GRANT UPDATE ANY TABLE TO CLONEDEV;

                    GRANT DROP ANY TABLE TO CLONEDEV;

                    GRANT ALTER ANY TABLE TO CLONEDEV;

                    GRANT ALTER ANY TRIGGER TO CLONEDEV;

                    GRANT ALTER ANY INDEX TO CLONEDEV;

                    GRANT CREATE ANY SEQUENCE TO CLONEDEV;

                    GRANT DROP ANY SEQUENCE TO CLONEDEV;

                    GRANT SELECT ANY SEQUENCE TO CLONEDEV;

                    GRANT EXECUTE ON DBMS_STATS TO CLONEDEV;

                    GRANT IMP_FULL_DATABASE TO CLONEDEV;

                     

                    and he is able to execute the below query as well.

                     

                    select * from IRIS_DATA.RENTAL_INV@PSUP_DATA;

                     

                    Regards

                    • 8. Re: Importing a single table data using dbms_datapump in oracle 10g
                      Solomon Yakobson

                      Got it!. You are using NAME_LIST metadata filter while supplying expression. That's why error says "IN ('RENTAL_INV')" not found. Either change it to NAME_EXPR or change value to 'RENTAL_INV'. Same applies to schema name.

                       

                      SY.

                      • 9. Re: Importing a single table data using dbms_datapump in oracle 10g
                        Sudhir

                        Hello Solomon,

                         

                        I modified my code as below

                         

                        code:

                         

                        create or replace procedure prc_import_rental_inv is

                         

                         

                        l_dp_handle NUMBER;

                          v_job_state varchar2(4000);

                          l_last_job_state VARCHAR2(30) := 'UNDEFINED';

                          l_job_state VARCHAR2(30) := 'UNDEFINED';

                          l_sts  KU$_STATUS;

                          v_logs ku$_LogEntry;

                          v_row  PLS_INTEGER;

                          v_string   varchar2(2000);

                          l_short_dt varchar2(20);

                         

                         

                         

                         

                        begin

                         

                         

                            dbms_output.put_line('Import iriscode table started.');

                         

                         

                           SELECT TO_CHAR (SYSDATE, 'DDMMYYYYHH24MI')

                            INTO L_SHORT_DT

                            FROM DUAL;

                         

                         

                          --Creating a named data pump job to do the schema import.

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

                                                              job_mode    => 'SCHEMA',

                                                              remote_link => 'PSUP_DATA',

                                                              version     => 'LATEST');

                            dbms_output.put_line('prc_import_rental_inv: File handle opened.');

                         

                         

                          -- Specify a log file for the job (using the handle just returned)

                            -- and a directory object, which must already be defined and accessible

                            -- to the user running this procedure.

                          -- used already created a DMPDIR directory in /tmp.

                         

                         

                            DBMS_DATAPUMP.add_file(handle    => l_dp_handle,

                                                   filename  => 'DATAPUMPIMP'||L_SHORT_DT||'.log',

                                                   directory => 'DMPDIR',

                                                   filetype  => DBMS_DATAPUMP.KU$_FILE_TYPE_LOG_FILE);

                            dbms_output.put_line('prc_import_rental_inv: added the log file.');

                         

                         

                            DBMS_DATAPUMP.METADATA_FILTER(handle      => l_dp_handle,

                                                         name        => 'INCLUDE_PATH_EXPR',

                                                         value       => 'IN (''TABLE'')');

                            dbms_output.put_line('prc_import_rental_inv: Include only table.');

                         

                         

                              -- A metadata filter is used to specify the schema that will be exported.

                            DBMS_DATAPUMP.METADATA_FILTER(handle      => l_dp_handle,

                                                          name        => 'SCHEMA_EXPR',

                                                          value       => 'IN (''IRIS_DATA'')');

                         

                         

                            --Add the tables you wish to import.

                            v_string  := 'IN ('||'RENTAL_INV'||')';

                         

                         

                            --Metadat filter to remap source and target schemas.

                            dbms_datapump.metadata_remap ( handle     => l_dp_handle,

                                                           name       => 'REMAP_SCHEMA',

                                                           old_value  => 'IRIS_DATA',

                                                           value      => 'IRIS_DATA');

                         

                         

                          -- A metadata filter is used to specify the table that will be exported.

                            DBMS_DATAPUMP.METADATA_FILTER(handle      => l_dp_handle,

                                                          name        => 'NAME_LIST',

                                                          value       => 'RENTAL_INV',

                                                          object_type => 'TABLE');

                            dbms_output.put_line('prc_import_rental_inv: Added the filter to pull only selected objects from the user.');

                         

                         

                            dbms_datapump.data_filter(handle     => l_dp_handle,

                                                      name       => 'INCLUDE_ROWS',

                                                      value      => 1);

                                                         

                            --Set the parameter to ignore if the db objects are already imported.

                            dbms_datapump.set_parameter ( handle   => l_dp_handle,

                                                          name     => 'TABLE_EXISTS_ACTION',

                                                          value    => 'APPEND' );

                         

                         

                            dbms_output.put_line('prc_import_rental_inv: Added the data filter to get only the structure.');

                         

                         

                            dbms_output.put_line('prc_import_rental_inv: starting job.');

                         

                         

                            -- Start the job. An exception will be generated if something is not set up properly.

                            DBMS_DATAPUMP.start_job(handle   => l_dp_handle);

                         

                         

                            DBMS_DATAPUMP.WAIT_FOR_JOB(handle     => l_dp_handle,

                                                       job_state  => v_job_state);

                         

                         

                            dbms_output.put_line('prc_import_rental_inv: completed job.');

                         

                         

                        exception

                        WHEN OTHERS THEN

                            dbms_datapump.get_status(handle     => NULL,

                                                     mask       => 8,

                                                     timeout    => 0,

                                                     job_state  => v_job_state,

                                                     status     => l_sts);

                            v_logs := l_sts.error;

                            dbms_output.put_line('prc_import_rental_inv: Exception Raised.');

                            v_row := v_logs.FIRST;

                            LOOP

                              EXIT WHEN v_row IS NULL;

                              dbms_output.put_line('logLineNumber=' || v_logs(v_row).logLineNumber);

                              dbms_output.put_line('ErrorNumber=' || v_logs(v_row).errorNumber);

                              dbms_output.put_line('LogText=' || v_logs(v_row).LogText);

                              v_row := v_logs.NEXT(v_row);

                            END LOOP;

                         

                         

                        end prc_import_rental_inv;

                         

                        but still not able to load the data.

                         

                        log file:

                         

                        [kususu1@seattle01 tmp]$ cat DATAPUMPIMP170420141522.log

                        FLASHBACK automatically enabled to preserve database integrity.

                        Starting "CLONEDEV"."SYS_IMPORT_SCHEMA_19":

                        Estimate in progress using BLOCKS method...

                        Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA

                        Total estimation using BLOCKS method: 0 KB

                        Processing object type SCHEMA_EXPORT/TABLE/TABLE

                        ORA-39152: Table "IRIS_DATA"."RENTAL_INV" exists. Data will be appended to existing table but all dependent metadata will be skipped due to table_exists_action of append

                        Processing object type SCHEMA_EXPORT/TABLE/GRANT/OWNER_GRANT/OBJECT_GRANT

                        Processing object type SCHEMA_EXPORT/TABLE/INDEX/INDEX

                        Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/CONSTRAINT

                        Processing object type SCHEMA_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS

                        Processing object type SCHEMA_EXPORT/TABLE/COMMENT

                        Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/REF_CONSTRAINT

                        Processing object type SCHEMA_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS

                        Job "CLONEDEV"."SYS_IMPORT_SCHEMA_19" completed with 1 error(s) at 15:22:57

                        [kususu1@seattle01 tmp]$

                         

                        Please advice.

                        • 10. Re: Importing a single table data using dbms_datapump in oracle 10g
                          Solomon Yakobson

                          Advice what? Your import succeeded with standard APPEND action. Meaning things like indexes & constraints that exist on source table will not be created on target table since it already exists. Add EXCLUDE=INDEX,CONSTRAINT if you don't want to see the error.

                           

                          SY.