ORACLE数据库泵使用案例汇总

案例1 排除部分表，导出其他表结构

bash-3.00$ vi expdp.par
"expdp.par" [New file] 
directory=pump
dumpfile=mdb_metadata.dmp
logfile=mdb_metadata.log
CONTENT=METADATA_ONLY
JOB_NAME=MDB_EXPDP_METADATA
exclude=TABLE:"IN(SELECT TABLE_NAME FROM DBA_TABLES WHERE (TABLE_NAME  LIKE 'EMP%' OR TABLE_NAME  LIKE 'TMP%') AND OWNER='MDB')"

paralle=5

expdp mdb/2013 parfile=expdp.par 

 

 

一个测试 ：

 

create user user1 identified by "ora#!@123";
create user user2 identified by "ora#!@123";
grant dba to user1,user2;

create table user1.t1 as select * From dba_objects;
create table user2.t1 as select * From dba_objects;
create table user1.temp as select * From dba_objects;
create table user2.temp as select * From dba_objects;

[oracle@gzrac01 ~]$ vi 123.par

directory=pump
dumpfile=123.dmp
schemas=user1,user2
exclude=table_statistics,index_statistics,table:"in(select segment_name from dba_segments where segment_Name like 'TEMP%')"
~

[oracle@gzrac01 ~]$ expdp system/Tjnmc#2018 parfile=123.par

Export: Release 19.0.0.0.0 - Production on Fri Nov 15 16:54:22 2019
Version 19.3.0.0.0

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

Connected to: Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Starting "SYSTEM"."SYS_EXPORT_SCHEMA_01":  system/******** parfile=123.par 
Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
Processing object type SCHEMA_EXPORT/USER
Processing object type SCHEMA_EXPORT/SYSTEM_GRANT
Processing object type SCHEMA_EXPORT/ROLE_GRANT
Processing object type SCHEMA_EXPORT/DEFAULT_ROLE
Processing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA
Processing object type SCHEMA_EXPORT/TABLE/TABLE
. . exported "USER2"."T1"                                11.15 MB   87927 rows
. . exported "USER1"."T1"                                11.15 MB   87926 rows
Master table "SYSTEM"."SYS_EXPORT_SCHEMA_01" successfully loaded/unloaded
******************************************************************************
Dump file set for SYSTEM.SYS_EXPORT_SCHEMA_01 is:
  /home/oracle/123.dmp
Job "SYSTEM"."SYS_EXPORT_SCHEMA_01" successfully completed at Fri Nov 15 16:55:03 2019 elapsed 0 00:00:40
 

 

案例2 只导入某个分区数据

vi expdp.par

network_link=nidb1

tables=R

include=TABLE_DATA:"IN (select  partition_name from dba_tab_partitions where table_name='R' AND partition_name='R7') "

nologfile=y

table_exists_action=truncate  ---先禁用外键

parallel=5

 

expdp system/123 dumpfile=data_pump_dir:123_%u.dmp tables=hr.R:R7,hr:c:c1 content=data_only parallel=5

案例3 query妙用

-bash-3.00$ more expdp01.par

directory=DUMP

dumpfile=r_%u.dmp

parallel=5

logfile=nmosdb.log

tables=r

query=DB.R:"WHERE IME BETWEEN TO_DATE('20150101','YYYY-MM-DD') AND TO_DATE('20150228','YYYY-MM-DD')",db.r1:"xxx"

job_name=db_tran

exclude=index,index_statistics,table_statistics

案例3 remap_table

     remap_table=tj_x.A:A1 或者remap_table=tj_x.A:bj_x.A1

 

1. 1. 导出数据

使用如下脚本导出数据

vi expdp.par dumpfile=data_pump_dir:bingfie_man%u.dmp parallel=5 job_name=bigfile_man tables=bz.CITY_60_20191011 exclude=table_statistics,index_statistics query=bz.CITY_60_20191011:"where start_time<sysdate-90" content=data_only       expdp system/oran parfile=expdp.par

1. 1. 导入数据

使用append的方式进行数据导入，不影响导出过程中已经入库的数据。

impdp system/oran dumpfile=data_pump_dir:bingfie_man%u.dmp parallel=5 tables=tj_txbz.CITY_60_20191011 remap_table=bz.CITY_60_20191011:PM_INTERCON_CITY_60 table_exists_action=append