导入表的部分数据到测试环境
开发人员发现在生产库有些数据有问题,需要测试是否功能有问题,但由于生产环境不能直接进行测试,只好把数据导入到测试环境来测试。
下面描述通过exp 的query参数来完成这个任务。
在生产环境上编辑如下的exp.par 参数文件:
tables=sum50a.zbindb
file=pdm.dmp
log=pdm.log
query=" where bid ='tbpcCAkh3cpdmsum50a--g5q'"
buffer=32680000
注意query的写法。
生成dmp文件
exp \'/ as sysdba\' parfile=exp.par
下面是执行的过程:
Export: Release 9.2.0.8.0 - Production on Fri Jan 16 09:28:16 2009
Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.
Connected to: Oracle9i Enterprise Edition Release 9.2.0.8.0 - 64bit Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.8.0 - Production
Export done in US7ASCII character set and AL16UTF16 NCHAR character set
server uses ZHS16CGB231280 character set (possible charset conversion)
About to export specified tables via Conventional Path ...
Current user changed to SUM50A
. . exporting table ZBINDB 1 rows exported
EXP-00091: Exporting questionable statistics.
EXP-00091: Exporting questionable statistics.
Export terminated successfully with warnings.
ftp到测试环境,导入到测试环境。
编辑imp.par 参数文件:
fromuser=sum50a
touser=sum50a
file=/orabak/pdm.dmp
ignore=y
注意:一定要加上ignore 参数,否则imp会报错:表已经存在,不能再次导入。
加入ignore 参数可以跳过该错误。
不加ignore 参数时的错误:
Export file created by EXPORT:V09.02.00 via conventional path
import done in UTF8 character set and AL16UTF16 NCHAR character set
import server uses ZHS16CGB231280 character set (possible charset conversion)
export client uses US7ASCII character set (possible charset conversion)
. importing SUM50A's objects into SUM50A
IMP-00015: following statement failed because the object already exists:
"CREATE TABLE "ZBINDB" ("OBID" VARCHAR2(24), "ZBINDATA" LONG, "ZLEN" NUMBER("
"*,0)) PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 STORAGE(INITIAL 65536 "
"FREELISTS 1 FREELIST GROUPS 1) LOGGING NOCOMPRESS"