當(dāng)前位置：首頁(yè) > 编程资源 > 编程问答 >内容正文

编程问答

Oracle-数据泵expdp/impdp实操

發(fā)布時(shí)間：2025/3/21 编程问答 43 豆豆

生活随笔收集整理的這篇文章主要介紹了 Oracle-数据泵expdp/impdp实操小編覺(jué)得挺不錯(cuò)的,現(xiàn)在分享給大家,幫大家做個(gè)參考.

概述
- 數(shù)據(jù)泵的作用：
- 數(shù)據(jù)泵的特點(diǎn)與傳統(tǒng)導(dǎo)出導(dǎo)入的區(qū)別
需求描述
方案分析
操作步驟
- expdp
  - 1.創(chuàng)建邏輯目錄
  - 2.查看目錄
  - 3.給cc用戶賦予在指定目錄的操作權(quán)限
  - 4. 導(dǎo)出數(shù)據(jù)
- scp dmp 到目標(biāo)主機(jī)
- impdp
  - 刪除用戶
  - 創(chuàng)建用戶
  - 賦予權(quán)限
  - 使用Oracle用戶導(dǎo)入
    - 相同的schema
    - 不同的schema
    - REMAP_SCHEMA、REMAP_TABLESPACE、REMAP_DATAFILE
    - 不同路徑下的dump文件同時(shí)導(dǎo)入
    - 查看腳本
    - schema解釋
注意事項(xiàng)
導(dǎo)出導(dǎo)入全庫(kù)
- 全庫(kù)導(dǎo)出
  - 單個(gè)實(shí)例
  - 多個(gè)實(shí)例
- 全庫(kù)導(dǎo)入
單表以及單表按條件導(dǎo)出導(dǎo)入

概述

數(shù)據(jù)泵的作用：

1.實(shí)現(xiàn)邏輯備份和邏輯恢復(fù)
2.在數(shù)據(jù)庫(kù)用戶之間移動(dòng)對(duì)象
3.在數(shù)據(jù)庫(kù)之間移動(dòng)對(duì)象
4.實(shí)現(xiàn)表空間轉(zhuǎn)移

數(shù)據(jù)泵的特點(diǎn)與傳統(tǒng)導(dǎo)出導(dǎo)入的區(qū)別

1.EXP和IMP是客戶段工具程序， EXPDP和IMPDP是服務(wù)端的工具程序
2.EXP和IMP效率比較低. EXPDP和IMPDP效率高
3.數(shù)據(jù)泵功能強(qiáng)大并行、過(guò)濾、轉(zhuǎn)換、壓縮、加密、交互等等
4.數(shù)據(jù)泵不支持9i以前版本， EXP/IMP短期內(nèi)還是比較適用
5.同exp/imp數(shù)據(jù)泵導(dǎo)出包括導(dǎo)出表,導(dǎo)出方案,導(dǎo)出表空間,導(dǎo)出數(shù)據(jù)庫(kù)4種方式.

Oracle官方指導(dǎo)文檔

需求描述

198測(cè)試環(huán)境的cc用戶下的全量數(shù)據(jù)，需要同步到到準(zhǔn)生產(chǎn)環(huán)境197一份

方案分析

因數(shù)據(jù)庫(kù)版本為11.2（大于Oracle10g）初步確定使用數(shù)據(jù)泵的方式從198導(dǎo)出后導(dǎo)入197數(shù)據(jù)庫(kù)

操作步驟

expdp

SQL 窗口下執(zhí)行以下操作

1.創(chuàng)建邏輯目錄

創(chuàng)建邏輯目錄

SQL> create directory dir_dp as '/oracle/oracle11';

2.查看目錄

同時(shí)查看操作系統(tǒng)是否存在，如果不存在，mkdir -p 新建目錄否則出錯(cuò)

SELECT privilege, directory_name, DIRECTORY_PATHFROM user_tab_privs t, all_directories dWHERE t.table_name(+) = d.directory_nameORDER BY 2, 1;

或者可以查看

select * from dba_directories ;

DATA_PUMP_DIR就是直接可以用的DUMP目錄，導(dǎo)出文件和導(dǎo)入文件就放在上面的路徑下，如果沒(méi)有，則需要手動(dòng)創(chuàng)建并賦給讀/寫(xiě)權(quán)限.

如果沒(méi)有查詢到的話，刷新一下

SQL> host ls /oracle/oracle11

3.給cc用戶賦予在指定目錄的操作權(quán)限

以dba用戶等管理員賦予

SQL> Grant read,write on directory dir_dp to cc;

4. 導(dǎo)出數(shù)據(jù)

使用主機(jī)的Oracle用戶

按用戶導(dǎo)/導(dǎo)出指定schema：

oracle@entel2:[/oracle]$expdp cc/password@//10.45.7.198:1521/cc schemas=cc directory=dir_dp dumpfile =expdp_test1.dmp logfile=expdp_test1.log;...........Dump file set for CC.SYS_EXPORT_SCHEMA_01 is:/oracle/oracle11/expdp_test1.dmp Job "CC"."SYS_EXPORT_SCHEMA_01" successfully completed at Sat Aug 20 12:02:13 2016 elapsed 0 00:01:33

參數(shù)解析：

schemas：數(shù)據(jù)庫(kù)用戶別名而非實(shí)例名
cc/password@//10.45.7.198:1521/cc port后面指定的是實(shí)例名，最前面的是用戶名和密碼

scp dmp 到目標(biāo)主機(jī)

[root@entel2 ~]# scp expdp_198.dmp oracle@10.45.7.197:/oracle/oracle11

impdp

刪除用戶

SQL> DROP USER cc CASCADE;

創(chuàng)建用戶

SQL>CREATE USER cc IDENTIFIED BY password DEFAULT TABLESPACE TAB_CC;

賦予權(quán)限

SQL>GRANT CONNECT ,RESOURCE TO cc ; SQL>GRANT ALL PRIVILEGES TO cc ; SQL>GRANT DBA TO cc ;

使用Oracle用戶導(dǎo)入

相同的schema

oracle@entel1:[/oracle]$impdp cc/password@//10.45.7.197:1521/cc schemas=cc directory=dir_dp_198 dumpfile =expdp_198.dmp logfile=expdp_198.log;

不同的schema

oracle@entel1:[/oracle]$impdp entelcc_st4/password@//10.45.7.197:1521/cc schemas=cc remap_schema=cc:entelcc_st4 directory=dir_dp_198 dumpfile =expdp_198.dmp logfile=expdp_198.log;

說(shuō)明：

目標(biāo)主機(jī)上 directory也需要建立，并賦予讀寫(xiě)權(quán)限,如果不指定，則使用缺省目錄 缺省Directory為DATA_PUMP_DIR

--查看缺省目錄 select * from dba_directories where directory_name='DATA_PUMP_DIR';

DATA_PUMP_DIR這個(gè)目錄，是系統(tǒng)缺省目錄，如不單獨(dú)指定目錄，dmp文件會(huì)在這里，但默認(rèn)這個(gè)目錄其他用戶是沒(méi)有權(quán)限的。

dmp文件需要存放到對(duì)應(yīng)的directory目錄下，否者導(dǎo)入報(bào)錯(cuò)
如果目標(biāo)庫(kù)的schema和源庫(kù)的schema一致，按以上腳本即可，不一致的情況需要指定，語(yǔ)法如下：schemas=old_schema remap_schema=old_schema:new_schema

否者報(bào)錯(cuò)：

ORA-39002: invalid operation ORA-39166: Object ENTELCC_ST4 was not found.

REMAP_SCHEMA、REMAP_TABLESPACE、REMAP_DATAFILE

除了REMAP_SCHEMA，還有REMAP_TABLESPACE、REMAP_DATAFILE 等

1、REMAP_SCHEMA
該選項(xiàng)用于將源方案的所有對(duì)象裝載到目標(biāo)方案中：REMAP_SCHEMA=source_schema:target_schema
2、REMAP_TABLESPACE
將源表空間的所有對(duì)象導(dǎo)入到目標(biāo)表空間中：REMAP_TABLESPACE=source_tablespace:target:tablespace
3、REMAP_DATAFILE 該選項(xiàng)用于將源數(shù)據(jù)文件名轉(zhuǎn)變?yōu)槟繕?biāo)數(shù)據(jù)文件名,在不同平臺(tái)之間搬移表空間時(shí)可能需要該選項(xiàng).
REMAP_DATAFIEL=source_datafie:target_datafile

不同路徑下的dump文件同時(shí)導(dǎo)入

需要?jiǎng)?chuàng)建多個(gè)directory

指定 dumpfile=dir1:1.dmp,dir2:2.dm

查看腳本

查看各個(gè)SCHEMA占用空間的大小

select owner,round(sum(bytes/1024/1024),2)||' MB' as schema_size from dba_segments group by owner order by SCHEMA_SIZE desc;

查詢己經(jīng)導(dǎo)入的對(duì)象個(gè)數(shù)

select owner,count(*) from dba_objects where owner like 'ENTELCC_ST4%' group by owner order by owner;

這里不同的schema，之前沒(méi)有注意到，導(dǎo)致一直報(bào)錯(cuò)，看到了yfleng2002的博文Oracle schema 級(jí)別的數(shù)據(jù)遷移，贊一個(gè)。

schema解釋

A schema is a collection of database objects (used by a user.).
Schema objects are the logical structures that directly refer to the database’s data.
A user is a name defined in the database that can connect to and access objects.
Schemas and users help database administrators manage database security.

一個(gè)用戶一般對(duì)應(yīng)一個(gè)schema,該用戶的schema名等于用戶名，并作為該用戶缺省schema。

Oracle數(shù)據(jù)庫(kù)中不能新創(chuàng)建一個(gè)schema，要想創(chuàng)建一個(gè)schema，只能通過(guò)創(chuàng)建一個(gè)用戶的方法解決(Oracle中雖然有create schema語(yǔ)句，但是它并不是用來(lái)創(chuàng)建一個(gè)schema的)，在創(chuàng)建一個(gè)用戶的同時(shí)為這個(gè)用戶創(chuàng)建一個(gè)與用戶名同名的schem并作為該用戶的缺省shcema。

即schema的個(gè)數(shù)同user的個(gè)數(shù)相同，而且schema名字同user名字一一對(duì)應(yīng)并且相同，所有我們可以稱(chēng)schema為user的別名，雖然這樣說(shuō)并不準(zhǔn)確，但是更容易理解一些。

一個(gè)用戶有一個(gè)缺省的schema，其schema名就等于用戶名，當(dāng)然一個(gè)用戶還可以使用其他的schema。如果我們?cè)L問(wèn)一個(gè)表時(shí)，沒(méi)有指明該表屬于哪一個(gè)schema中的，系統(tǒng)就會(huì)自動(dòng)給我們?cè)诒砩霞由先笔〉膕heman名。比如我們?cè)谠L問(wèn)數(shù)據(jù)庫(kù)時(shí)，訪問(wèn)scott用戶下的emp表，通過(guò)select * from emp; 其實(shí)，這sql語(yǔ)句的完整寫(xiě)法為select * from scott.emp。在數(shù)據(jù)庫(kù)中一個(gè)對(duì)象的完整名稱(chēng)為schema.object，而不屬u(mài)ser.object。類(lèi)似如果我們?cè)趧?chuàng)建對(duì)象時(shí)不指定該對(duì)象的schema，在該對(duì)象的schema為用戶的缺省schema。這就像一個(gè)用戶有一個(gè)缺省的表空間，但是該用戶還可以使用其他的表空間，如果我們?cè)趧?chuàng)建對(duì)象時(shí)不指定表空間，則對(duì)象存儲(chǔ)在缺省表空間中，要想讓對(duì)象存儲(chǔ)在其他表空間中，我們需要在創(chuàng)建對(duì)象時(shí)指定該對(duì)象的表空間。

以上schema解釋參考o(jì)racle中schema指的是什么？，感謝分享

注意事項(xiàng)

EXPDP和IMPDP是服務(wù)端的工具程序，只能在ORACLE服務(wù)端使用，不能在客戶端使用

低版本是不能導(dǎo)入高版本dmp，需要在高版本的EXPDP導(dǎo)出時(shí)指定版本號(hào)導(dǎo)出。低版本IMPDP無(wú)需指定版本。
例如：11.2.0.4導(dǎo)入到10.2.0.5

expdp username/password directory=dump_dir dumpfile=test.dmp version=10.2.0.5.0

導(dǎo)出導(dǎo)入全庫(kù)

全庫(kù)導(dǎo)出

單個(gè)實(shí)例

oracle@entel2:[/oracle]$expdp \'/ as sysdba\' directory=dir_dp full=y dumpfile=fulldb.dmp parallel=2;【開(kāi)始執(zhí)行........上述命令也可以增加logfile等自行決定】 Export: Release 11.2.0.4.0 - Production on Mon Oct 24 18:47:01 2016Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production With the Partitioning, OLAP, Data Mining and Real Application Testing options Starting "SYS"."SYS_EXPORT_FULL_01": "/******** AS SYSDBA" directory=dir_dp full=y dumpfile=fulldb.dmp parallel=2 Estimate in progress using BLOCKS method... Processing object type DATABASE_EXPORT/SCHEMA/TABLE/TABLE_DATA Total estimation using BLOCKS method: 2.294 GB Processing object type DATABASE_EXPORT/TABLESPACE . . exported "ZMC"."NM_ALARM_EVENT" 317.5 MB 467108 rows Processing object type DATABASE_EXPORT/PROFILE Processing object type DATABASE_EXPORT/SYS_USER/USER Processing object type DATABASE_EXPORT/SCHEMA/USER Processing object type DATABASE_EXPORT/ROLE Processing object type DATABASE_EXPORT/GRANT/SYSTEM_GRANT/PROC_SYSTEM_GRANT Processing object type DATABASE_EXPORT/SCHEMA/GRANT/SYSTEM_GRANT Processing object type DATABASE_EXPORT/SCHEMA/ROLE_GRANT Processing object type DATABASE_EXPORT/SCHEMA/DEFAULT_ROLE Processing object type DATABASE_EXPORT/SCHEMA/TABLESPACE_QUOTA ..............

PARALLEL 更改當(dāng)前作業(yè)的活動(dòng) worker 的數(shù)目。PARALLEL=<worker 的數(shù)目>。指定執(zhí)行導(dǎo)出操作的并行進(jìn)程個(gè)數(shù),默認(rèn)值為1

FULL 導(dǎo)出整個(gè)數(shù)據(jù)庫(kù) 默認(rèn)為N。

多個(gè)實(shí)例

如果存在多個(gè)實(shí)例，需要先export ORACLE_SID

tbprocsdb1:[/oracle$]export ORACLE_SID=testbed tbprocsdb1:[/oracle$]expdp \'/ as sysdba\' directory=TMP_DIR full=y dumpfile=fulldb20180512.dmp logfile=full20180512.log parallel=4;Export: Release 11.2.0.4.0 - Production on Sat May 12 14:06:38 2018Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production With the Partitioning, OLAP, Data Mining and Real Application Testing options Starting "SYS"."SYS_EXPORT_FULL_01": "/******** AS SYSDBA" directory=TMP_DIR full=y dumpfile=fulldb20180512.dmp logfile=full20180512.log parallel=4 Estimate in progress using BLOCKS method... Processing object type DATABASE_EXPORT/SCHEMA/TABLE/TABLE_DATA

全庫(kù)導(dǎo)入

$ impdp \'/ as sysdba\' directory=dir_dp full=y dumpfile=fulldb.dmp parallel=2 table_exists_action=replace;

更多詳情

單表以及單表按條件導(dǎo)出導(dǎo)入

artisandb:[/oracle$] expdp xxx/xxxx@PR_CC directory=CC_DUMP include=table:\" =\'TMP_BATCH203\' \" dumpfile =TMP_BATCH203.dmp logfile=TMP_BATCH203.log;Export: Release 11.2.0.4.0 - Production on Thu May 31 14:31:41 2018Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production With the Partitioning, OLAP, Data Mining and Real Application Testing options Starting "CRM"."SYS_EXPORT_SCHEMA_02": crm/********@PR_CC directory=CC_DUMP include=table:" ='TMP_BATCH203' " dumpfile=TMP_BATCH203.dmp logfile=TMP_BATCH203.log Estimate in progress using BLOCKS method... Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA Total estimation using BLOCKS method: 64 KB Processing object type SCHEMA_EXPORT/TABLE/TABLE Processing object type SCHEMA_EXPORT/TABLE/COMMENT . . exported "CRM"."TMP_BATCH203" 9.75 KB 50 rows Master table "CRM"."SYS_EXPORT_SCHEMA_02" successfully loaded/unloaded ****************************************************************************** Dump file set for CRM.SYS_EXPORT_SCHEMA_02 is:/ccexp/cc/TMP_BATCH203.dmp Job "CRM"."SYS_EXPORT_SCHEMA_02" successfully completed at Thu May 31 14:31:49 2018 elapsed 0 00:00:08artisandb:[/oracle$]

或者

artisandb:[/oracle$] expdp xxx/xxxxx@PR_CC directory=CC_DUMP Tables=TMP_BATCH203 dumpfile =TMP_BATCH204.dmp logfile=TMP_BATCH204.log;Export: Release 11.2.0.4.0 - Production on Thu May 31 14:36:25 2018Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production With the Partitioning, OLAP, Data Mining and Real Application Testing options Starting "CRM"."SYS_EXPORT_TABLE_01": crm/********@PR_CC directory=CC_DUMP Tables=TMP_BATCH203 dumpfile=TMP_BATCH204.dmp logfile=TMP_BATCH204.log Estimate in progress using BLOCKS method... Processing object type TABLE_EXPORT/TABLE/TABLE_DATA Total estimation using BLOCKS method: 64 KB Processing object type TABLE_EXPORT/TABLE/TABLE Processing object type TABLE_EXPORT/TABLE/COMMENT . . exported "CRM"."TMP_BATCH203" 9.75 KB 50 rows Master table "CRM"."SYS_EXPORT_TABLE_01" successfully loaded/unloaded ****************************************************************************** Dump file set for CRM.SYS_EXPORT_TABLE_01 is:/ccexp/cc/TMP_BATCH204.dmp Job "CRM"."SYS_EXPORT_TABLE_01" successfully completed at Thu May 31 14:36:28 2018 elapsed 0 00:00:02artisandb:[/oracle$]

按照條件

artisandb:[/ccexp/cc$] expdp xxx/xxxxx@PR_CC directory=CC_DUMP Tables=TMP_BATCH203 dumpfile =TMP_BATCH206.dmp logfile=TMP_BATCH206.log query=TMP_BATCH203:'"where acc_nbr_id=170686"';Export: Release 11.2.0.4.0 - Production on Thu May 31 14:40:16 2018Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production With the Partitioning, OLAP, Data Mining and Real Application Testing options Starting "CRM"."SYS_EXPORT_TABLE_01": crm/********@PR_CC directory=CC_DUMP Tables=TMP_BATCH203 dumpfile=TMP_BATCH206.dmp logfile=TMP_BATCH206.log query=TMP_BATCH203:"where acc_nbr_id=170686" Estimate in progress using BLOCKS method... Processing object type TABLE_EXPORT/TABLE/TABLE_DATA Total estimation using BLOCKS method: 64 KB Processing object type TABLE_EXPORT/TABLE/TABLE Processing object type TABLE_EXPORT/TABLE/COMMENT . . exported "CRM"."TMP_BATCH203" 7.601 KB 1 rows Master table "CRM"."SYS_EXPORT_TABLE_01" successfully loaded/unloaded ****************************************************************************** Dump file set for CRM.SYS_EXPORT_TABLE_01 is:/ccexp/cc/TMP_BATCH206.dmp Job "CRM"."SYS_EXPORT_TABLE_01" successfully completed at Thu May 31 14:40:27 2018 elapsed 0 00:00:10artisandb:[/ccexp/cc$]

然后drop掉TMP_BATCH203

artisandb:[/ccexp/cc$] impdp xxx/xxxxx@PR_CC directory=CC_DUMP dumpfile =TMP_BATCH206.dmp logfile=TMP_BATCH_1.log ;Import: Release 11.2.0.4.0 - Production on Thu May 31 14:44:26 2018Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production With the Partitioning, OLAP, Data Mining and Real Application Testing options Master table "CRM"."SYS_IMPORT_FULL_01" successfully loaded/unloaded Starting "CRM"."SYS_IMPORT_FULL_01": crm/********@PR_CC directory=CC_DUMP dumpfile=TMP_BATCH206.dmp logfile=TMP_BATCH_1.log Processing object type TABLE_EXPORT/TABLE/TABLE Processing object type TABLE_EXPORT/TABLE/TABLE_DATA . . imported "CRM"."TMP_BATCH203" 7.601 KB 1 rows Processing object type TABLE_EXPORT/TABLE/COMMENT Job "CRM"."SYS_IMPORT_FULL_01" successfully completed at Thu May 31 14:44:27 2018 elapsed 0 00:00:01artisandb:[/ccexp/cc$]

查看數(shù)據(jù)：

exclude和include更多用法

總結(jié)

以上是生活随笔為你收集整理的Oracle-数据泵expdp/impdp实操的全部?jī)?nèi)容，希望文章能夠幫你解決所遇到的問(wèn)題。

如果覺(jué)得生活随笔網(wǎng)站內(nèi)容還不錯(cuò)，歡迎將生活随笔推薦給好友。

上一篇：修改centos等linux的hostn
下一篇： Oracle自动备份脚本（Linux）