IMPDP Import Only the Rows Does not exist in the Target Table

advertisements

_____________________________________________________________________________________________________________________

This is a question or requirement which I got from one of my blog reader. “How to import only the rows does not present in the targeted table?”. The rows present in the table should not be deleted and import only rows which does not exist in the database target table. This can be achieved by using the combination of table_exists_action and query options.

Here I have given an example with normal import and with data pump API import.

a.   Normal import method.

I have taken the full table export using expdp utility.

SQL> select count(*) from emp;
 

  COUNT(*)
----------
        14
Deleting 6 rows from the table
SQL> delete from emp where rownum<7 o:p="">

 
6 rows deleted.

SQL> commit;

Commit complete.

SQL> select count(*) from emp;
  COUNT(*)
----------
        8

$ impdp directory=exp_dir dumpfile=emp.dmp logfile=impemp.log query=sthomas.emp:\"where EMPNO not in \(SELECT EMPNO FROM STHOMAS.EMP\)\" table_exists_action=append tables=sthomas.emp

Import: Release 11.2.0.3.0 - Production on Fri May 23 16:07:07 2014

Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.

Username: / as sysdba

Connected to: Oracle Database 11g Release 11.2.0.3.0 - 64bit Production

Master table "SYS"."SYS_IMPORT_TABLE_01" successfully loaded/unloaded
Starting "SYS"."SYS_IMPORT_TABLE_01":  /******** AS SYSDBA directory=exp_dir dumpfile=emp.dmp logfile=impemp.log query=sthomas.emp:"where EMPNO not in (SELECT EMPNO FROM STHOMAS.EMP)" table_exists_action=append tables=sthomas.emp
Processing object type TABLE_EXPORT/TABLE/TABLE

Table "STHOMAS"."EMP" 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 TABLE_EXPORT/TABLE/TABLE_DATA

. . imported "STHOMAS"."EMP"                             8.578 KB       6 out of 14 rows

Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS

Job "SYS"."SYS_IMPORT_TABLE_01" successfully completed at 16:07:15

a.   Using Datapump API method.

SQL> select count(*) from emp;

  COUNT(*)
----------
        14
SQL> delete from emp where rownum<7 o:p="">

6 rows deleted.

SQL> select count(*) from emp;

  COUNT(*)
----------
          8
SQL> commit;

Commit complete.
Script for import using DBMS_DATAPUMP

SQL> declare

empimp number;
BEGIN
empimp := DBMS_DATAPUMP.OPEN('IMPORT','TABLE',NULL,'EXAMPLE','LATEST');
DBMS_DATAPUMP.ADD_FILE(empimp,'emp.dmp','EXP_DIR');
DBMS_DATAPUMP.SET_PARAMETER(empimp,'TABLE_EXISTS_ACTION','APPEND');
dbms_datapump.add_filehandle=>empimp,filename=>'impemp.log',
directory=>'EXP_DIR',filesize=>'1024M',filetype=>DBMS_DATAPUMP.KU$_FILE_TYPE_LOG_FILE);
DBMS_DATAPUMP.DATA_FILTER(handle => empimp,Name => 'SUBQUERY',value => 'where EMPNO NOT IN (SELECT EMPNO FROM STHOMAS.EMP)', table_name => 'EMP');
DBMS_DATAPUMP.START_JOB(empimp);
END;
/
PL/SQL procedure successfully completed.

SQL> select count(*) from emp;

  COUNT(*)

----------
        14

_____________________________________________________________________________________________________________________

Website Stats