7 Replies Latest reply: Oct 11, 2013 12:32 AM by 974882 RSS

    impdp using parfile...

    974882

      Hi ,

       

      I am doing import of table using parfile with query option. My import is running is running from 4 hours where as export get completed with in half hours???

      Kindly suggest...

       

      query_imp.par

      --------------------------------------

      tables=ABC
      query=ABC:"WHERE TIMESTAMP BETWEEN '09/20/2013 00:00:00' AND '10/09/2013' "
      logfile=import_ABC.log
      parallel= 3
      DIRECTORY=export_ABC
      DUMPFILE=export_ABC_%U.dmp
      table_exists_action=APPEND
      CONTENT=ALL
      JOB_NAME=import_ABC

       

      while executing the command:

      impdp SYSTEM/*****@orcl parfile=/***/***/query_imp.par

       

      O/P:

      ----------------

      Starting "SYSTEM"."IMPORT_ABC":  SYSTEM/********@ORCL tables=ABC parfile=/***/***/query_imp.par

      Processing object type TABLE_EXPORT/TABLE/TABLE

      ORA-39152: Table "XYZ"."ABC" 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

       

      Why status is 100% completed where job is running ????

       

      and STATUS is:

      --------------------

       

      Import> STATUS

      Job: IMPORT_ABC
        Operation: IMPORT
        Mode: TABLE
        State: EXECUTING
        Bytes Processed: 0
        Current Parallelism: 5
        Job Error Count: 0
        Dump File: /**/**/export_ABC_%u.dmp
        Dump File: /**/**/export_ABC_01.dmp
        Dump File: /**/**/export_ABC_02.dmp

      Worker 1 Status:
        State: WORK WAITING

      Worker 2 Status:
        State: EXECUTING
        Object Schema:XYZ
        Object Name: ABC
        Object Type: TABLE_EXPORT/TABLE/TABLE_DATA
        Completed Objects: 1
        Completed Bytes: 55,512,836,576
        Percent Done: 100
        Worker Parallelism: 1

        • 1. Re: impdp using parfile...
          DK2010

          Hi,

           

          Import is Always take more time then export. i'd prefer to take the export at Query level and then Import, it must be faster.


          check the alert log for details. may you can get some hint there


          HTH

          • 2. Re: impdp using parfile...
            Zoltan Kecskemethy

            Do you really need query parameter to import in? I guess you want to import in all data...

            Did you try to disable foreign key constraints before import?

            Do you have triggers on the target table? You may want to disable those too.

             

            Yes, there could be a locking issue here as well.

            Check for blocking sessions etc.

            • 3. Re: impdp using parfile...
              TSharma-Oracle

              How big is the export dump file and how many records it is importing?

              While importing , oracle runs simple inserts statements. Check what else going on in database? Are there any locks? Check your alert.log file to check if you are running out of space in any of your tablespace.

               

              Also, check if this note can help:

              Checklist For Slow Performance Of DataPump Export (expdp) And Import (impdp) (Doc ID 453895.1)

              • 4. Re: impdp using parfile...
                974882

                Hi ,

                 

                While checking the lock status: i am getting..throug this query:

                 

                set linesize 150;
                set head on;
                col sid_serial form a13
                col ora_user for a15;
                col object_name for a35;
                col object_type for a10;
                col lock_mode for a15;
                col last_ddl for a8;
                col status for a10;

                break on sid_serial;

                SELECT l.session_id||','||v.serial# sid_serial,
                       l.ORACLE_USERNAME ora_user,
                       o.object_name,
                       o.object_type,
                       DECODE(l.locked_mode,
                          0, 'None',
                          1, 'Null',
                          2, 'Row-S (SS)',
                          3, 'Row-X (SX)',
                          4, 'Share',
                          5, 'S/Row-X (SSX)',
                          6, 'Exclusive',
                          TO_CHAR(l.locked_mode)
                       ) lock_mode,
                       o.status,
                       to_char(o.last_ddl_time,'dd.mm.yy') last_ddl
                FROM dba_objects o, gv$locked_object l, v$session v
                WHERE o.object_id = l.object_id
                      and l.SESSION_ID=v.sid
                order by 2,3;

                ===================

                 

                SESSION_ID ORACLE_USERNAME OS_USER_NAME    OBJECT OWNER                   OBJECT_NAME                           OBJECT_TYPE                           LOCKED_MODE

                ---------- --------------- --------------- ------------------------------ ------------------------------------- ------------------------------------- -----------

                1028        SYSTEM         oracle          www                              ABC                          TABLE                                           6

                1015        NFINSERT       qa                 www                         ABC                        TABLE                                           0

                982         NFINSERT         qa                www                                  ABC                 TABLE                                           0

                482         NFINSERT          qa             www                                      ABC               TABLE                                           0

                • 5. Re: impdp using parfile...
                  Zoltan Kecskemethy

                  hm use this query instead of yours

                  SELECT oracle_username || ' (' || s.osuser || ')' AS "Username",
                         s.sid || ',' || s.serial# AS "Session ID",
                         owner || '.' || object_name AS "Object",
                         object_type AS "Object type",
                         decode(l.block, 0, 'Not Blocking', 1, 'Blocking', 2, 'Global') As "Status",
                         decode(v.locked_mode, 0, 'None', 1, 'Null', 2, 'Row-S (SS)', 3, 'Row-X (SX)', 4, 'Share', 5, 'S/Row-X (SSX)', 6, 'Exclusive', to_char(lmode)) As "Mode held"
                    FROM v$locked_object v, dba_objects d, v$lock l, v$session s
                   WHERE v.object_id = d.object_id
                     AND v.object_id = l.id1
                     AND v.session_id = s.sid
                   ORDER BY oracle_username, session_id
                  
                  • 6. Re: impdp using parfile...
                    Richard Harrison .

                    Hi,

                    As other people have already suggested - it's likely doing something 'after' the actual table load. Try looking in v$session_longops to see what it thinks is happening.

                     

                    Import will always be slower than export - primarily because an export just expots index definitions - the import has to actually create them - which can be a very long operation on big tables even if done in parallel.

                     

                    Cheers,

                    Harry

                    • 7. Re: impdp using parfile...
                      974882

                      Hi All,

                       

                       

                      Thanks for the suggestion, I observed some locks in my table level.. because of which table import goes slow...

                       

                      In 09 hours , import got completed .....