oracle exp 带sql语句,Oracle PLSQL Exp/Imp

环境: Oralce +

Unix

1.

进入到Oracle的安装路径：

\bin;

2.

查看是否有exp.exe文件

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

exp

3.

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

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

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>