6 Replies Latest reply: Dec 13, 2013 6:27 AM by Srini Chavali-Oracle RSS

    Error while using REMAP_TABLE and WHERE clause  together in IMPDP

    Ratnesh Sharma

      I am trying to move some records from a very large table to another small table.

      I am facing trouble while using REMAP_TABLE and WHERE clause together in IMPDP.

      Problem is data filter is not getting applied and all records are getting imported.

      here is how I have simulated this. please advice.

       

       

      CREATE TABLE TSHARRHB.TMP1

      (

        A  NUMBER,

        B  NUMBER

      );

       

       

      begin

      Insert into TSHARRHB.TMP1

         (A, B)

      Values

         (1, 1);

      Insert into TSHARRHB.TMP1

         (A, B)

      Values

         (2, 2);

      COMMIT;

      end;

       

       

       

       

      expdp system/password TABLES=tsharrhb.TMP1 DIRECTORY=GRDP_EXP_DIR DUMPFILE=TMP1.dmp REUSE_DUMPFILES=YES LOGFILE=EXP.log PARALLEL=8

       

       

      impdp system/password DIRECTORY=GRDP_EXP_DIR DUMPFILE=TMP1.dmp LOGFILE=imp.log PARALLEL=8 QUERY='TSHARRHB.TMP1:"WHERE TMP1.A = 2"'  REMAP_TABLE=TSHARRHB.TMP1:TMP3 CONTENT=DATA_ONLY

       

       

      Import: Release 11.2.0.1.0 - Production on Fri Dec 13 05:13:30 2013

       

       

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

       

       

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

      With the Partitioning, Automatic Storage Management, OLAP, Data Mining

      and Real Application Testing options

      Master table "SYSTEM"."SYS_IMPORT_FULL_01" successfully loaded/unloaded

      Starting "SYSTEM"."SYS_IMPORT_FULL_01":  system/********@GRD6.RBSG DIRECTORY=GRDP_EXP_DIR DUMPFILE=TMP1.dmp LOGFILE=SSD_93_TABLES_FULL_EXP.log PARALLEL=8 QUERY=TSHARRHB.TMP1:"WHERE TMP1.A = 2" REMAP_TABLE=TSHARRHB.TMP1:TMP3 CONTENT=DATA_ONLY

      Processing object type TABLE_EXPORT/TABLE/TABLE_DATA

      . . imported "TSHARRHB"."TMP3"                           5.421 KB       2 rows

      Job "SYSTEM"."SYS_IMPORT_FULL_01" successfully completed at 05:13:33

       

       

      here I am expecting only 1 record to get imported but both the records are getting imported. please advice.