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

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

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

Mount ISO, CUE/BIN, NRG, IMG, MDF files in ubuntu

I known that to mount iso file pnix@pnix-a7n:~$ sudo mount -o loop file.iso mountpoint or pnix@pnix-a7n:~$ sudo mount -o loop -t iso9660 file.iso mountpoint and for cue/bin file ,I convert it to iso first use bchunk pnix@pnix-a7n:~$ bchunk file.bin file.cue file.iso but how about the others. After some search, I collect tips to handle many types of image file in linux. For nrg [ nero image ], img [ clone cd] and mdf [ alcohol 120% ] files, We need nrg2iso, ccd2iso and mdf2iso to convert those image files to iso image. Luckily, All are in Feisty repos. pnix@pnix-a7n:~$ sudo aptitude install nrg2iso ccd2iso mdf2iso Reading package lists... Done Building dependency tree Reading state information... Done Reading extended state information Initializing package states... Done Building tag database... Done The following NEW packages will be installed: ccd2iso mdf2iso nrg2iso 0 packages upgraded, 3 newly installed, 0 to remove and 0 not upgraded. Need to get 19.1kB of archiv...
15 comments
Read more