关于oracle:oracledmp文件的导入导出

dmp 文件的导入导出

数据泵文件是一个高效的数据导入导出形式。

用 exp，imp 导入导出

导出

exp username/password@{ip}:{port}/schema tables="TABLEA" file="/xxx/TABLEA.dmp"

导入

imp username/password@{ip}:{port}/schema file="/xxx/TABLEA.dmp"

Import: Release 11.2.0.4.0 - Production on Tue Oct 19 10:03:39 2021

Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.


Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

Export file created by EXPORT:V11.02.00 via conventional path

Warning: the objects were exported by `XXX`, not by you

import done in ZHS16GBK character set and AL16UTF16 NCHAR character set
IMP-00031: Must specify FULL=Y or provide FROMUSER/TOUSER or TABLES arguments
IMP-00000: Import terminated unsuccessfully

报错提醒文件是由 XXX 导出的不是我。所以要改下 owner。

imp username/password@{ip}:{port}/schema file="/xxx/TABLEA.dmp" fromuser=USER1 touser=USER2

用 expdp，impdp 导入导出

-- 查问导入导出目录
select * from dba_directories;
-- 查问用户的表空间名
select default_tablespace from dba_users WHERE username = 'XH_BAS'

导入

expdp xh_uso/czty_xh_uso@192.168.150.37:1521/waep_dev TABLES="OSF_CDR" DIRECTORY="DUMP1" DUMPFILE="OSF_CDR.dmp"

导出

impdp xdra/xdra@192.168.150.233:1521/zsms DIRECTORY="DUMP1" DUMPFILE="OSF_CDR.dmp" REMAP_SCHEMA=xdrb:XH_USO REMAP_TABLESPACE=XDRB_DATA:USERS