1

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.

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

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

  • 5
    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
  • 1
    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 '14 at 11:49
1

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.

0

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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy