Thursday, January 28, 2010

Oracle XE: Exporting and Importing Dump Files

An easy way to copy a schema would be through JDeveloper Database Copy Utility under Tools>Database>Database Copy... menu. But in our case, we encounter errors in the transfer of records from the source to my target connection and many of the records were not exported successfully. This is the reason why we resorted to exporting and importing dump files.

Yesterday, I was given a dump file which was generated from one of my colleague's machine. Since I am new to this import and export thing and I was not involved in the export process when the file was generated, I face the challenge on how to successfully import the dump file to my Oracle XE.

I do my research on the internet but I was overwhelmed with too much information (information overload), and in applying the crowded information that I have gathered, I encountered a bunch of errors some of which are listed below:
Error 1:
ORA-39001: invalid argument value
ORA-39000: bad dump file specification
ORA-39143: dump file "my_sample_dumpfile.dmp" may be an original export dump file
Reason: I used impdp instead of imp command.

Error 2:
IMP-00002:failed to open expdat.dmp for read
Import File:EXPDAT.DMP>
Reason: I did not specify a full file path.

Error 3:
IMP-00013: only a DBA can import a file exported by another DBA
Reason: I did not specify '... as sysdba'

But finally, I was able to make it. How? Please see the steps below:
1) I created my target user using the Oracle XE browser base UI (Go To Database Home Page)
2) I run the following command in windows command prompt:
imp 'targetUser/password as sysdba' file=C:/my_sample_dumpfile.dmp fromuser=userWhoExported touser=targetUser
what to expect as result would be something like this:
importing table ...
importing table ...
About to enable constraints...
Import terminated successfully without warnings.

If you want to know the parameters of the imp command, type:
imp help=y

Now that I realized how easy it is to import a dump file, I became excited on how to do the exporting, and indeed, it was even easier. To generate a dump file, open your command prompt and enter something like the following:
exp username/password file=C:/exported_file.dmp owner=yourschema
yourschema is the user/schema that you wanted to export which can be the same with username value.


No comments:

Post a Comment