Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I have exported an existing oracle schema from another machine and then imported it in my local machine. Import was successful, but some tables which are present in the export dump file are not imported.

Here are the export and import commands i have used.

Export Command:
---------------
exp sreeni/sreeni@agentrics1:1524/ezmodc full=n file=SreeniTrunkDump.dmp log=SreeniTrunkDump_Export.log 

Import Command:
---------------
imp badri/badri@localhost:1521/xe file=SreeniTrunkDump.dmp log=BadriSchemaImport_Import.log full=y   

The Oracle we are using is 10g EE.

What could be going wrong ? Can you please suggest a solution to this issue.

share|improve this question

migrated from stackoverflow.com Apr 9 '13 at 15:26

This question came from our site for professional and enthusiast programmers.

4  
Showing the export and import logs might be helpful, or at least edited versions excluding the tables that did transfer; that will at least confirm the tables were exported and if so might indicate why they didn't import. You might also want to consider using data pump rather than the old exp/imp utilities. –  Alex Poole Apr 9 '13 at 10:48
    
How do you know that there are some tables in the export dump file that are not imported? –  miracle173 Aug 7 '13 at 20:24
    
It seems that exp is not exporting all the tables, as those with no allocated extent will not be included. I found this helpful hint in this article. –  user39072 May 27 at 11:49

2 Answers 2

Use following statement to see what is exactly in export file:

imp sreeni/sreeni@agentrics1:1524/ezmodc file=SreeniTrunkDump.dmp indexfile=SreeniTrunkDump.sql full=y

This statment will not try to import data, just dumps DDL to indexfile. Check the generated file to see if table is in export.

Alternatively if you are on unix

strings SreeniTrunkDump.dmp|grep <missingtable>

If table is in export file something goes wrong during import. Missing tablespace for example. Check the logfile of import statement (BadriSchemaImport_Import.log) for that.

share|improve this answer

whatch out for triggers, it happens the IMP try to create the trigger before creating the table it refer to, so it fails (because the table desn't exists yet) and it will not even create the table.

dont't how if it is your case, but i was mine, and i ended up exporting the ddl for the all tables, and then creating all tables before launching the IMP.


you may also try to import only the missing table and see what is going wrong

imp badri/badri@localhost:1521/xe file=SreeniTrunkDump.dmp tables=TABLE_NAME
share|improve this answer
    
additional info about "Oracle Import not re-creating tables" can be found here >> docs.oracle.com/cd/B10501_01/server.920/a96652/ch02.htm#1005111 –  kkk Oct 11 '13 at 11:23

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.