Skip to main content

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
Labels:

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!
February 5, 2009 at 10:59 PM
Post a Comment

Popular posts from this blog

Fixing sendmail take a long time to start

I notice that my database box[FC6+Oracle10.2] take along time to startup. Sendmail and sm-client very very slow to start[ about 5 minutes ]. There's something wrong in /etc/hosts file. 'newalises' command take long time to update and finish with error below. [root@ora10g ~]# newaliases WARNING: local host name (ora10g) is not qualified; see cf/README: WHO AM I? /etc/aliases: 76 aliases, longest 10 bytes, 765 bytes total [root@ora10g ~]# cat /etc/hosts # Do not remove the following line, or various programs # that require network functionality will fail. 127.0.0.1 localhost.localdomain localhost 192.168.1.55 ora10g [root@ora10g ~]# To fix this, custom hostname[ora10g] need to append to localhost line in /etc/hosts. [root@ora10g ~]# cat /etc/hosts # Do not remove the following line, or various programs # that require network functionality will fail. 127.0.0.1 localhost.localdomain localhost ora10g 192.168.1.55 ora10g [root@ora10g ~]# newaliases /etc/alia...
11 comments
Read more

Too many open files

Last week tomcat log file report many error about "Too many open files" when it has high traffic. Some in catalina_log say 2007-04-07 16:13:40 HttpProcessor[80][272] Starting background thread 2007-04-07 16:13:40 HttpConnector[80] accept: java.net.SocketException: Too many open files and here is from localhost_log 2007-04-07 16:13:40 StandardWrapperValve[myservlet]: Servlet.service() for servlet myservlet threw exception java.io.FileNotFoundException: /home/log/mylog_070407.log (Too many open files) This is because too many file descriptors're opened by tomcat. File descriptor can be limited in both system level and shell level. To check maximum number of fd in system type 'cat /proc/sys/fs/file-max'. In my case it is 65536(someone said it should set to 200000). Tomcat error when try to open socket number 272 so I think 65536 is ok for me for now. Anyway if u want to set it add 'fs.file-max = 200000' to /etc/sysctl.conf pnix@pnix-a7:~$ cat /proc/sys/fs/fil...
15 comments
Read more

Setup MySQL with Ofbiz

Download ofbiz weekily build and extract it somewhere you want. From your ofbiz directory, edit file entityengine.xml in framework/entity/config add new datasources below localmysql datasource part <datasource name="custommysql" helper-class="org.ofbiz.entity.datasource.GenericHelperDAO" field-type-name="mysql" check-on-start="true" add-missing-on-start="true" check-pks-on-start="false" use-foreign-keys="true" join-style="ansi-no-parenthesis" alias-view-columns="false" drop-fk-use-foreign-key-keyword="true" table-type="InnoDB" character-set="latin1" collate="latin1_general_cs"> <read-data reader-name="seed"/> <read-data reader-name="seed-initial"/> ...
8 comments
Read more