Solved

Oracle import not completing cleanly ?

Posted on 2008-06-18
8
1,184 Views
Last Modified: 2013-12-18
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 ?

0
Comment
Question by:tvedtem
8 Comments
 
LVL 6

Expert Comment

by:chedgey
ID: 21811911
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
 
LVL 4

Author Comment

by:tvedtem
ID: 21811993
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
 
LVL 47

Expert Comment

by:schwertner
ID: 21812594
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
 
LVL 7

Expert Comment

by:Dauhee
ID: 21821393
add log=implog.log to script to determine where it stops
0
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
LVL 47

Expert Comment

by:schwertner
ID: 21821462
Normally it stops suddenly if the software is upgraded
but the catalog is not upgraded.

Another issue could be no disk space.
0
 
LVL 4

Author Comment

by:tvedtem
ID: 21890093
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
 
LVL 47

Accepted Solution

by:
schwertner earned 250 total points
ID: 21897209
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
 
LVL 4

Author Comment

by:tvedtem
ID: 21981474
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

Featured Post

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Join & Write a Comment

Why doesn't the Oracle optimizer use my index? Querying too much data Most Oracle developers know that an index is useful when you can use it to restrict your result set to a small number of the total rows in a table. So, the obvious sideā€¦
Checking the Alert Log in AWS RDS Oracle can be a pain through their user interface.  I made a script to download the Alert Log, look for errors, and email me the trace files.  In this article I'll describe what I did and share my script.
This video shows how to Export data from an Oracle database using the Datapump Export Utility.  The corresponding Datapump Import utility is also discussed and demonstrated.
This video explains what a user managed backup is and shows how to take one, providing a couple of simple example scripts.

706 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

16 Experts available now in Live!

Get 1:1 Help Now