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

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!

Popular posts from this blog

Converting dat to mpeg file, to burn vcd on ubuntu

Creating vcd [from dat file] on WinXP, I use "Nero Burning Rom". It can burn dat file to vcd directly. On ubuntu, AFAIK, No burning cd software to burn dat to vcd. I use k3b but it can't burn dat file. It support only mpeg-1 and mpeg-2 format. Dat file need to convert to mpeg first. Thist post will show how I install and use vcdgear to convert dat file to mpeg file. pnix@pnix-a7n:~/progdl$ wget http://www.vcdgear.com/files/vcdgear176-040415_linux.tar.gz --04:21:14-- http://www.vcdgear.com/files/vcdgear176-040415_linux.tar.gz => `vcdgear176-040415_linux.tar.gz' Resolving www.vcdgear.com... 66.98.192.104 Connecting to www.vcdgear.com|66.98.192.104|:80... connected. HTTP request sent, awaiting response... 200 OK Length: 442,937 (433K) [application/x-tar] 100%[====================================>] 442,937 105.23K/s ETA 00:00 04:21:20 (87.02 KB/s) - `vcdgear176-040415_linux.tar.gz' saved [442937/442937] pnix@pnix-a7n:~/progdl$ tar xzf vcdgear

using Class.getResource() load resource file in Eclipse

There are many ways to load resource file in java app. What sun recommended is using Class.getResource(" resource_name ") or Class.getResourceAsStream(" resource_name ") then you will get URL and InputStream respectively. If resource_name is specified without "/", it will be prepend with Class package. So resource file must be in same place[folder structure] as the Class. What I love to do is call getResource() with "/" and put resource file at the root of package. This way i can have separate resource folder. below is in Eclipse, 1. From Package explorer right click src folder->click import 2. In import dialog, Choose General->File System ->next 3. from directory:->Browse to your resource folder. 4. to folder:-> I add "resource" as a folder name under src folder. then click "Finish". In the code, load resource with this.getClass().getResource("/resource/buttons1.png") or this.getClass().getResourceAs

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