环境: Oralce + Unix

1.       进入到Oracle的安装路径： \bin;

2.       查看是否有exp.exe文件

[3.1]/oracle/product/10.2.0/db_1/bin>ls exp

3.       在Step2中的目录下，执行如下语句：

exp username/password/@IP:Port/@sid_name file=fileName tables=tableName

A:导出jf数据库的所有表到testData.dmp

exp username/password/@10.1.1.61:1521/@jf file=/home/testData.dmp full=y;

B:导出jf数据库中Sys与System用户的所有表到testData2.dmp

exp username/password/@10.1.1.61:1521/@jf file=/home/testData2.dmp owner=system,sys;(待测试owner=(system,sys)中括弧是否允许使用)

C:导出jf数据库中的Test表到testData3.dmp

exp username/password/@10.1.1.61:1521/@jf file= /home/testData3.dmp tables=Test;

D:导出jf数据库Test表中UserName字段以”A0”开头的数据到testData4.dmp

exp username/password/@10.1.1.61:1521/@jf file= /home/testData4.dmp tables=Test query=\”where UserName like ‘A0%’ \”;

2012-01-03补充范例:

exp userName/PassWord file=tableName.dmp tables=tableName query=\"where substr\(create_time,1,8\)\>\'20111101\' \"; (注意转义字符\的使用，否则有可能提示：

EXP-00056: ORACLE error 933 encountered
ORA-00933: SQL command not properly ended
Export terminated successfully with warnings.）

[3.1]/oracle/product/10.2.0/db_1/bin>exp username/password/@IP:1521/@JF file=/home/bea/testtable/test_table.dmp tables=test_table 

Export: Release 10.2.0.2.0 - Production on Fri Apr 8 08:46:06 2011

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

EXP-00004: invalid username or password

Username: 重新输入一次

Password:

Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.2.0 - 64bit Production

With the Partitioning, OLAP and Data Mining options

Export done in ZHS16GBK character set and AL16UTF16 NCHAR character set

About to export specified tables via Conventional Path ...

. . exporting table                     TEST_TABLE         35 rows exported

Export terminated successfully without warnings.

[3.1]/oracle/product/10.2.0/db_1/bin>

4.       在Step2中的目录下，执行如下语句：

5.       imp username/password/@IP:Port/@sid_name full=y indexes=no ignore=yes file=fileName tables=tableName(如果要导入的文件中含有多张表，则可以指定只导入某张表的数据)

     注：indexes=no 忽略索引，避免序列问题

6.       需要注意如果数据库中已经存在导入的表，可以先将其drop掉。执行imp后的Table的表结构与导出原表的表结构一致。

[3.1]/oracle/product/10.2.0/db_1/bin>imp username/password/@IP:1521/@JF full=y file=/home/bea/testtable/test_table.dmp;

Import: Release 10.2.0.2.0 - Production on Fri Apr 8 09:00:09 2011

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

IMP-00004: invalid username or password

Username:

Password:

Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.2.0 - 64bit Production

With the Partitioning, OLAP and Data Mining options

Export file created by EXPORT:V10.02.01 via conventional path

import done in ZHS16GBK character set and AL16UTF16 NCHAR character set

. importing JF_ISU's objects into JF_ISU

. importing JF_ISU's objects into JF_ISU

. . importing table                   "TEST_TABLE"         35 rows imported

Import terminated successfully with warnings.

[3.1]/oracle/product/10.2.0/db_1/bin>