Migrate DB data using expdp/impdp

最新推荐文章于 2024-01-09 06:15:00 发布

crystalxie

最新推荐文章于 2024-01-09 06:15:00 发布

阅读量91

点赞数

文章标签： Oracle

本文详细介绍了如何在不拥有源数据库读写目录权限的情况下，通过网络链接功能expdp/impdp来迁移数据库数据。包括直接从源数据库到目标数据库的数据迁移方法，以及从源数据库导出数据到数据泵文件集，再导入到目标数据库的方法。同时，针对数据迁移过程中可能出现的错误，如‘LXP-00225’，提供了额外导入约束的解决策略。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

If you want to migrate the source db data to a target db while you don’t have privileges of Read/Write Directory on the source db, then how to export the source db data and migrate them to the target db?

Here we can use network_link feature of expdp/impdp. There are two ways to implement the migration.

Note that we must have privileges to read/write a directory and privilege of create database link on target database.

data migration directly from source database to target database.

Example:

impdp rs_aeowner/rsaeowner@xiech2 directory=RS_DATA_PUMP_DIR schemas=rs_aeowner network_link=RS_PP logfile=parallel_import_pp100430.log parallel=5

This example migrate data under rs_aeowner of the database rs_pp linked to network alias xiech2 corresponding database, the log file is created under directory RS_DATA_PUMP_DIR.

data migration from source database to data pump file set and then from data pump file set to source database.

Example:

expdp rs_aeowner/rsaeowner@xiech2 directory=RS_DATA_PUMP_DIR network_link=RS_PP schemas=rs_aeowner query=T_FRM_MIF_LOG:\" where rownum<1 \",T_FRM_MIF_REFNUM:\" where rownum<1 \",LTS_TRIGGER_EXECUTION_MSG:\" where rownum<1 \",AL_ACTIONLOG:\" where rownum<1 \", AL_ACTIONLOG_DETAIL:\" where rownum<1 \", AL_ACTIONLOG_SUM:\" where rownum<1 \", RCM_SAS_MDATA:\" where rownum<1 \" dumpfile=parallel_export_pp100430%u.dmp logfile=parallel_export_pp100430.log parallel=5

this command export the data under rs_aeowner of network link rs_pp correspoinding database as pump file set to directory RS_DATA_PUMP_DIR under network alias xiech2 corresponding database, the data rows of table T_FRM_MIF_LOG, T_FRM_MIF_REFNUM, LTS_TRIGGER_EXECUTION_MSG, AL_ACTIONLOG, AL_ACTIONLOG_DETAIL, AL_ACTIONLOG_SUM, RCM_SAS_MDATA are excluded.

impdp rs_aeowner/rsaeowner@xiech2 directory=RS_DATA_PUMP_DIR schemas=rs_aeowner dumpfile=parallel_export_pp100430%u.dmp logfile=parallel_import_pp100430.log parallel=5

this command import the pump file set to network alias xiech2 corresponding database. During the import, some errors such ‘LXP-00225’ might happened which causes constraints missed, it’s a bug of oracle expdp/impdp. To import constraints to the target database, need import constraints additionally, the following command is used to import .

impdp rs_aeowner/rsaeowner@xiech2 directory=RS_DATA_PUMP_DIR schemas=rs_aeowner include=CONSTRAINT dumpfile=parallel_export_pp100430%u.dmp logfile=parallel_import_constraint_pp100430.log