5/9/08

Using Datapump on oracle 10g to export/import

This will be update version of this post if you are using oracle 10g or 11g.

Last month I post about export/import data in oracle using exp,imp utility. Actually oracle has something newer and better performance if you're using 10g or 11g.

Assume that we want to export one table from one oracle db and import it into another oracle db. So create the folder /home/oracle/dbbackup on two oracle db server to store <.dmp> file[that will be created when exporting and importing].

Next, we need to tell oracle to know this folder, create directory on both db by run command below in sqlplus
SQL> create directory dmpdir as '/home/oracle/dbbackup';

Directory created.

SQL>

Now on source database export table with command below.
[oracle@k8n ~]$ expdp username/password DIRECTORY=dmpdir DUMPFILE=filename.dmp TABLES=tablename

Export: Release 10.1.0.3.0 - Production on Friday, 09 May, 2008 10:54
...
[oracle@k8n ~]$

after finish you will have filename.dmp in /home/oracle/dbbackup, ftp it to the target box make sure you are using binary mode.
Then on target database import the table like this.
[oracle@dbp4 ~]$ impdp username/password DIRECTORY=dmpdir DUMPFILE==filename.dmp TABLES=tablename

Import: Release 10.2.0.1.0 - Production on Friday, 09 May, 2008 11:02:27
...
[oracle@dbp4 ~]$

That's it. Note that up there exporting from 10.1.0.3.0 and importing into 10.2.0.1.0 is ok but down version import[e.g. expdp with higher version and impdp with lower version]is not, you will get error like this
ORA-39001: invalid argument value
ORA-39000: bad dump file specification
ORA-31619: invalid dump file "/home/oracle/dbbackup/filename.dmp"

To fix this error, expdp command must append with 'version=10.target.version'.

more detail about expdp/impdp tool
expdp help=yes
impdp help=yes

1 comments:

gux said...

"That's it. Note that up there exporting from 10.1.0.3.0 and importing into 10.2.0.1.0 is ok but down version import[e.g. expdp with higher version and impdp with lower version]is not, you will get error like this"

Only because of this you deserve to enter in my feeds... that was actually the problem I was facing at work, trying to import in a 10.1.0.3 oracle an schema exported from 10.2...

Thanks a lot!