Problem with the imp.exe utility of oracle 9i

Posted on 2003-03-27
Medium Priority
Last Modified: 2010-10-05
hello sir,
I have exported the table in a particular dump file using the exp.exe utility n i have used the system/manager password for it. But when i am trying to import the data in using the imp.exe utility , it only imports the tables which are in the system space, n does not import the tables which are in my table space.
the syntax that i am using is as follows :
D:\oracle\ora91\bin\IMP.EXE system/manager@mis file=d:\all.dmp indexes=y rows=y constraints=y log=d:\impall.log ignore=y full=y

Can u pls tell me why is it importing only the system tables n not my own table space.
Thanks a lot
Question by:IT-Freek

Expert Comment

ID: 8218668
Did you specify full database during export. Its possible that this was not specified and the dump file contains objects from System only.

Expert Comment

ID: 8218675
Because those are the only objects that you exported to the dump file.  What command was used in the export?
LVL 10

Accepted Solution

SDutta earned 200 total points
ID: 8219637
If you are loading from a full export, you should mention the name of schema you are importing :

IMP system/manager@mis FROMUSER=SCOTT TOUSER=SCOTT IGNORE=Y file=d:\all.dmp log=d:\impall.log

If doing a full import, your database should be completely empty. SYSTEM and SYS objects should never be loaded from an export, they are created at database creation time.

If you want to export just your schema to load at a later time, mention the owner with OWNER=zzz. Your dump file will be very much smaller too.
for example :

If you even want to specifically metion the table/s add the parameter TABLES=(xxx,yyy) to either the EXP or IMP as the case may be.
LVL 35

Expert Comment

by:Mark Geerlings
ID: 8219785
Is the operating system and disk and directory structure the same for the computer that did the export as for the one doing the import?  If the O/S is different, or if the disk drives/mount points are different, import may not be able to create the datafiles for the tablespaces.  In that case, create the tablespaces manually, then run import with "ignore=Y".

Author Comment

ID: 8230869
thanks so much sdutta
i just tried importing single table n it worked , but i will try sooon to imporrt a table with lacs of records in it.
hope it will work just as fine :)
thanks again
u really helped me a lot

Featured Post

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Background In several of the companies I have worked for, I noticed that corporate reporting is off loaded from the production database and done mainly on a clone database which needs to be kept up to date daily by various means, be it a logical…
Using SQL Scripts we can save all the SQL queries as files that we use very frequently on our database later point of time. This is one of the feature present under SQL Workshop in Oracle Application Express.
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.
This video shows how to copy an entire tablespace from one database to another database using Transportable Tablespace functionality.
Suggested Courses

621 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