Skip to main content

export,import data in oracle with exp/imp command

One of database box hang today. It doesn't let me ssh to see what happen, so I just shut it down and try to reboot again. But during boot process, I got "Kernel panic: VFS: Unable to mount root fs on...". err.. look like the disk is gone.
Anyway, after few time of cold boot it give me a chance to backup my data:). I don't need all data in database[too many outdate data], so I decide to move some importance table to another database. Oracle has exp and imp utility to do this job. To export table test123 to file test123.dmp type follwing command.
[oracle@Oracle-1 oracle]$ exp username/password TABLES=test123 FILE=test123.dmp

Export: Release 8.1.7.0.1 - Production on Mon Apr 7 13:22:01 2008

(c) Copyright 2000 Oracle Corporation.  All rights reserved.


Connected to: Oracle8i Enterprise Edition Release 8.1.7.0.1 - Production
With the Partitioning option
JServer Release 8.1.7.0.1 - Production
Export done in US7ASCII character set and US7ASCII NCHAR character set

About to export specified tables via Conventional Path ...
. . exporting table                        TEST123          1 rows exported
Export terminated successfully without warnings.
[oracle@Oracle-1 oracle]$

then ftp dump file[test123.dmp] to new database[make sure that using binary on transfer]. To import dump file into new database see below.
[oracle@ora10g ~]$ imp username/password FILE=test123.dmp fromuser=username touser=username

Import: Release 10.1.0.3.0 - Production on Mon Apr 7 13:29:40 2008

Copyright (c) 1982, 2004, Oracle.  All rights reserved.


Connected to: Oracle Database 10g Enterprise Edition Release 10.1.0.3.0 - Production
With the Partitioning, OLAP and Data Mining options

Export file created by EXPORT:V08.01.07 via conventional path
import done in WE8ISO8859P1 character set and AL16UTF16 NCHAR character set
export client uses US7ASCII character set (possible charset conversion)
export server uses US7ASCII NCHAR character set (possible ncharset conversion)
. . importing table                      "TEST123"          1 rows imported
Import terminated successfully without warnings.
[oracle@ora10g ~]$


note: when move data DOWN a version(s). you may need to export using that lower versions EXP tool and IMP using that lower versions tool.[more detail]
Labels:

Comments

Harish Goud said…
hie its very useful to me. when i check in my user scott thr is ob=nly one table emp. i exp emp table again i imp same table then in scott user total 2 tables i have to see but i find out only one.plz execute with user names
October 25, 2011 at 5:45 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