Solved

Oracle import not completing cleanly ?

Posted on 2008-06-18
8
1,195 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
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 48

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
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 7

Expert Comment

by:Dauhee
ID: 21821393
add log=implog.log to script to determine where it stops
0
 
LVL 48

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 48

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

Salesforce Has Never Been Easier

Improve and reinforce salesforce training & adoption using WalkMe's digital adoption platform. Start saving on costly employee training by creating fast intuitive Walk-Thrus for Salesforce. Claim your Free Account Now

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Truncate is a DDL Command where as Delete is a DML Command. Both will delete data from table, but what is the difference between these below statements truncate table <table_name> ?? delete from <table_name> ?? The first command cannot be …
Have you ever had to make fundamental changes to a table in Oracle, but haven't been able to get any downtime?  I'm talking things like: * Dropping columns * Shrinking allocated space * Removing chained blocks and restoring the PCTFREE * Re-or…
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
Via a live example, show how to take different types of Oracle backups using RMAN.

717 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