前面使用了expdp来导数据,但9i的库没有expdp,只有用exp/imp来导入导出
下面是使用exp/imp迁移不同schema下面的表的步骤和注意项,和在其中遇到的一些问题
--查看源库需要迁移的表,确认目录和目标库有剩余空间
select owner,segment_name,segment_type,bytes/1024/1024 mb from dba_segments where segment_name in ('MTL_SYSTEM_ITEMS_B','PO_VENDORS','AP_BANK_ACCOUNT_USES_ALL','PO_VENDOR_SITES_ALL')
SQL> /
OWNER SEGMENT_NAME SEGMENT_TYPE MB
------------------------------ --------------------------------------------------------------------------------- ------------------ ----------
INV MTL_SYSTEM_ITEMS_B TABLE 1666
PO PO_VENDORS TABLE 13
PO PO_VENDOR_SITES_ALL TABLE 15
AP AP_BANK_ACCOUNT_USES_ALL TABLE 3
--需要打通目标库到源库的11501端口,也就是源库的监听端口,一般是1521
orayntst@misdbTS:/u01/ynaaa>tnsping old
TNS Ping Utility for IBM/AIX RISC System/6000: Version 9.2.0.6.0 - Production on 04-MAY-2018 15:16:54
Copyright (c) 1997 Oracle Corporation. All rights reserved.
Used parameter files:
/u01/yntst/yntstdb/9.2.0/network/admin/YNTST_misdbTS/sqlnet_ifile.ora
Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = 10.111.111.13)(PORT = 11501)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = YNPRD)))
OK (90 msec)
--exp命令(需要\转义括号) exp可以直接把dmp文件导出到远程服务器
exp system/gGkA-RY\!5YS7@old file=/u01/dmpdir/mis1_exp.dmp log=/u01/dmpdir/mis1_exp.log tables=\(INV.MTL_SYSTEM_ITEMS_B,PO.PO_VENDORS,PO.PO_VENDOR_SITES_ALL,AP.AP_BANK_ACCOUNT_USES_ALL\)
Export: Release 9.2.0.6.0 - Production on Fri May 4 15:18:16 2018
Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.
Connected to: Oracle9i Enterprise Edition Release 9.2.0.6.0 - 64bit Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.6.0 - Production
Export done in ZHS16GBK character set and AL16UTF16 NCHAR character set
About to export specified tables via Conventional Path ...
Current user changed to INV
. . exporting table MTL_SYSTEM_ITEMS_B 2297223 rows exported
Current user changed to PO
. . exporting table PO_VENDORS 31287 rows exported
. . exporting table PO_VENDOR_SITES_ALL 42648 rows exported
Current user changed to AP
. . exporting table AP_BANK_ACCOUNT_USES_ALL 19672 rows exported
Export terminated successfully without warnings.
--目标库备份
--目标库还有数据,先对目标库旧数据备份,然后truncate表
truncate的时需要反复确认数据库是否正确
在imp时遇到问题
imp system/ynyd2018@YNTST file=/u01/dmpdir/mis1_exp.dmplog=/u01/dmpdir/mis1_imp.log ignore=y commit=y tables=\(INV.MTL_SYSTEM_ITEMS_B,PO.PO_VENDORS,PO.PO_VENDOR_SITES_ALL,AP.AP_BANK_ACCOUNT_USES_ALL\)
Import: Release 9.2.0.6.0 - Production on Fri May 4 16:38:59 2018
Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.
Connected to: Oracle9i Enterprise Edition Release 9.2.0.6.0 - 64bit Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.6.0 - Production
Export file created by EXPORT:V09.02.00 via conventional path
import done in ZHS16GBK character set and AL16UTF16 NCHAR character set
IMP-00029: cannot qualify table name by owner (INV.MTL_SYSTEM_ITEMS_B), use FROMUSER parameter
IMP-00000: Import terminated unsuccessfully
查找了mos上关于IMP-00029的问题
OERR: IMP 29 "cannot qualify table name by owner (%s), use FROMUSERparameter (文档ID 21662.1)
文档表示,需要imp命令中的PO.这样的字样改成 fromuser的形式。
--比较奇怪的是exp都可以写成schema.table的形式,imp却不可以,如果遇到多个用户下的多个表,没有schema.table还是很麻烦的(当前环境9i)
--因为我这里有3个用户,所以需要导3次
imp system/ynyd2018@YNTST file=/u01/dmpdir/mis1_exp.dmplog=/u01/dmpdir/mis1_imp.log ignore=y tables=MTL_SYSTEM_ITEMS_B fromuser=INV touser=INV
imp system/ynyd2018@YNTST file=/u01/dmpdir/mis1_exp.dmplog=/u01/dmpdir/mis1_imp.log ignore=y tables=\(PO_VENDORS,PO_VENDOR_SITES_ALL\)
fromuser=PO touser=PO
imp system/ynyd2018@YNTST file=/u01/dmpdir/mis1_exp.dmplog=/u01/dmpdir/mis1_imp.log ignore=y tables=AP_BANK_ACCOUNT_USES_ALL
fromuser=AP touser=AP