Oracle import not completing cleanly ?

I'm running an ORACLE import on WIndows (XP) using the following command:
imp username/passwd file=file.dmp tables=* ignore=y feedback=10000

The data imports without a problem, however it seems to hang forever at the end.

e.g. I see the following output...
. importing CRM_LIVE's objects into CRM_LIVE
. . importing table                      "ADDRESS"     253693 rows imported
. . importing table                    "TELEPHONE"     280072 rows imported
. . importing table                    "USER_DATA"       1727 rows imported

without ever seeing a 'Import completed successfully' message.

If I run taskmgr I can see 'IMP.EXE' still running (though not consuming any CPU).

I can simply 'CTRL-C' to get out of this, however I'm trying to kick off this process via a Java program (which waits for it to finish before continuing)..  the 'hanging' behaviour prevents this working as it appears as though the process is still running.

How can I
- double check that 'imp' really has finished its work
- force it to terminate cleanly ?

LVL 4
tvedtemAsked:
Who is Participating?
 
schwertnerCommented:
On laptops often the loopback adapter is not assigned as first adapter
if the laptop is not networked.
there are many kind of laptops - expensive, cheap, reliable,
unreliable.

Do you have other Oracle products installed on the laptop?
If yes - see what version of Import are you using.
Is it from the Home of the server or from other installation.

One year ago i spent hours before I understood that in fact i am
ising Import from the Forms/Reports server installed on my laptop.
0
 
chedgeyCommented:
Has it reached the end of the lis tof tables? If so it sounds as if it is some kind of locking issue - are there other sessions using this schema in the database? Check for locks in the database and clear them or, for a quick solution, shutdown and re-start the database then run again.

Regards

chedgey
0
 
tvedtemAuthor Commented:
It does get to the last table (and imports the data OK).

There are no locks/blocks on the DB
(whilst the import is running, i do see a lock on each table in turn, there are none once the data has been imported.. however IMP.EXE is still present... and there is no 'success' message)

This happens every time I run it (including following a reboot of the machine, or a restart of oracle).. i'm testing this locally on a laptop so there are no other users ..


I'm using the following to check locks..
------------
set lines 132 pages 24
col object format a35
select session_id,object_type,owner||'.'||object_name object,username,osuser
from dba_objects obj,v$session ses,v$locked_object lobj
where lobj.object_id = obj.object_id
  and lobj.session_id = ses.sid
------------

And the following for blocks
------------
set feedback off
set lines 132 pages 24
col username format a10
col object format a30
col "Mode held" format a14
col "Mode requested" format a14
col "Idle" format 999999
col type format a4
select l.sid,s.username,s.last_call_et "Idle",l.type,id1,id2,
       decode(lmode,0, 'None',
                    1, 'Null (NULL)',
                    2, 'Row-S (SS)',
                    3, 'Row-X (SX)',
                    4, 'Share (S)',
                    5, 'S/Row-X (SSX)',
                    6, 'Exclusive (X)') "Mode held",
       decode(request,0, 'None',
                      1, 'Null (NULL)',
                      2, 'Row-S (SS)',
                      3, 'Row-X (SX)',
                      4, 'Share (S)',
                      5, 'S/Row-X (SSX)',
                      6, 'Exclusive (X)') "Mode requested",
       ctime,
       decode(block,0,'',1,'Blocking') "Block?",program
from v$lock l,v$session s
where id1 in (select id1 from v$lock where request <> 0)
and l.sid = s.sid
order by id1,id2
/
select session_id,object_type,owner||'.'||object_name object,username,osuser,locked_mode
from dba_objects obj,v$session ses,v$locked_object lobj
where lobj.object_id = obj.object_id
  and lobj.session_id = ses.sid
  and lobj.session_id in (
    select l.sid
    from v$lock l,v$session s
    where id1 in (select id1 from v$lock where request <> 0)
    and l.sid = s.sid )
/
------------
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

 
schwertnerCommented:
What is the version of Oracle?
If it is 10g R1 then it is too bugy.
If it is upgraded 10g R2 have you upgraded the catalog after
upgrading of the software.
Will recommend to upgrade to 10.2.0.4
0
 
DauheeCommented:
add log=implog.log to script to determine where it stops
0
 
schwertnerCommented:
Normally it stops suddenly if the software is upgraded
but the catalog is not upgraded.

Another issue could be no disk space.
0
 
tvedtemAuthor Commented:
It's oracle 10.2.0.1
The process i'm writing is destined for a real server (working on a laptop at the moment) - so think my plan is going to be to deploy to the test environement (also windows) and see if I still have the problem of a hanging imp.exe - live is linux so hoping it will be ok there.
0
 
tvedtemAuthor Commented:
Spot on.
I have oracle designer installed, which appears to have an older version of 'imp' installed, and its first in my path.  Getting rid of that solved the problem.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.