APEX

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

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.

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)

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

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

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

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 .....