概述

写这篇的初衷是因为之前都不怎么去记expdp的一些参数，用到的时候却总发现一些命令给忘了，然后网上查却怕不小心踏坑，所以花了点时间总结了下，以后就直接看这篇来找expdp方面的内容就行了。

一、基础准备

1、创建导出目录

create directory dpdata as '/home/oracle/dump'; 
select * from dba_directories where directory_name='DPDATA'; 
grant read,write on directory dpdata to hwb; 

2、确保监听没问题

服务器监听没问题，可以通过sqlplus xx/xx来登录数据库

3、检查导出前数据情况

SELECT SUM(s.BYTES)/1024/1024 "sizes(MB)" from dba_segments s where s.owner='hwb';

select count(*) from dba_tables t where t.owner='hwb';

4、确定需要导出的用户在哪些表空间，及其表初始化时占用的表空间大小

SELECT D.owner,D.tablespace_name, SUM(D.initial_extent)/1024/1024 initial_extent
FROM DBA_SEGMENTS D
WHERE D.owner IN
(SELECT a.username
FROM DBA_USERS A
WHERE A.account_status = 'OPEN'
AND A.username NOT IN
('SYS', 'SYSTEM', 'MGMT_VIEW', 'SYSMAN', 'DBSNMP'))
GROUP BY D.tablespace_name,D.owner
ORDER BY initial_extent desc;

SELECT SUM(D.initial_extent)/1024/1024 initial_extent FROM DBA_SEGMENTS D WHERE D.owner IN
(SELECT a.username FROM DBA_USERS A WHERE A.account_status = 'OPEN' AND A.username NOT IN ('SYS', 'SYSTEM', 'MGMT_VIEW', 'SYSMAN', 'DBSNMP'));

由此可以知道，创建这些元数据大约需要6.8G的空间，如果涉及到数据的话，还需要判断数据占用空间，不然执行导入的时候会因为表空间不足而不能导入

5、确定需要导出的用户中有哪些无效的对象、及总共需要导出的对象数量

SELECT d.OWNER,d.status,count(1) FROM dba_objects d WHERE d.OWNER in (SELECT a.username FROM DBA_USERS A WHERE A.account_status = 'OPEN' AND A.username NOT IN ('SYS', 'SYSTEM', 'MGMT_VIEW', 'SYSMAN', 'DBSNMP')) group by d.OWNER, d.status;

SELECT d.OWNER,d.OBJECT_NAME,d.OBJECT_TYPE,d.status FROM dba_objects d WHERE d.status = 'INVALID' and d.owner in (SELECT a.username FROM DBA_USERS A WHERE A.account_status = 'OPEN' AND A.username NOT IN ('SYS', 'SYSTEM', 'MGMT_VIEW', 'SYSMAN', 'DBSNMP'));

二、expdp命令

1、导出用户

expdp scott/tiger@orcl schemas=scott dumpfile=expdp.dmp directory=DPDATA;

2、导出表

expdp scott/tiger@orcl tables=emp,dept dumpfile=expdp.dmp directory=DPDATA;

3、按查询条件导

expdp scott/tiger@orcl directory=dump_dir dumpfile=expdp.dmp tables=emp query='where deptno=20';

4、按表空间导

expdp system/manager@orcl directory=DPDATA dumpfile=tablespace.dmp tablespaces=temp,example;

5、导整个数据库

expdp system/manager@orcl directory=DPDATA dumpfile=full.dmp full=y;

6、利用content=metadata_only导出元数据

当设置CONTENT为ALL 时,将导出对象定义及其所有数据.为DATA_ONLY时,只导出对象数据,为METADATA_ONLY时,只导出对象定义

expdp hwb/hwb directory=DPDATA dumpfile=META.dmp logfile=META.log content=metadata_only schemas=TEST,SCOTT

7、并行导出scott用户下所有的内容

expdp scott/tiger@orcl directory=DPDATA schemas=scott dumpfile=scott_all%U.dmp logfile=scott_all.log parallel=2

8、expdp脚本导出

expdp \'/ as sysdba\' parfile=parfile.ora
# vi parfile.ora
directory=xxxx
dumpfile=xxxx%U.dmp
filesize=20G
logfile=xxxx.log
tables=xxxx.xxxx
#schemas=xxxx
#tablespaces=xxxx
#full=y
content=ALL | DATA_ONLY | METADATA_ONLY
#EXCLUDE=object_type[:name_clause] [,….]
#estimate={BLOCKS | STATISTICS}
#ESTIMATE_ONLY=y
parallel=3
EXCLUDE=table:"IN('REPORT_DATA','FORMATDOC_DATA','CUSTOMER_FSRECORD','REPORT_RECORD')"
Exclude specific object types.
EXCLUDE=table:"IN('REPORT_DATA','FORMATDOC_DATA','CUSTOMER_FSRECORD','REPORT_RECORD')"

三、impdp命令

1、导入用户（从用户scott导入到用户scott）

impdp scott/tiger@orcl directory=dump_dir dumpfile=expdp.dmp schemas=scott;

2、导入表（从scott用户中把表dept和emp导入到system用户中）

impdp system/manager@orcl directory=dump_dir dumpfile=expdp.dmptables=scott.dept,scott.emp remap_schema=scott:system;

3、导入表空间

impdp system/manager@orcl directory=dump_dir dumpfile=tablespace.dmp tablespaces=example;

4、导入数据库

impdb system/manager@orcl directory=dump_dir dumpfile=full.dmp full=y;

5、追加数据

impdp system/manager@orcl directory=dump_dir dumpfile=expdp.dmp schemas=systemtable_exists_action

四、测试验证

1、删除错误用户下的包、存过和函数

执行如下的脚本删除相应的错误对象：

BEGIN
FOR CUR IN (SELECT 'DROP ' || D.OBJECT_TYPE || ' ' || D.OBJECT_NAME SQLT FROM dba_objects d WHERE d.OWNER = 'NWPP_TEST'
AND d.CREATED >=to_date('2019-04-15 00:00:00','YYYY-MM-DD HH24:MI:SS')
AND D.OBJECT_TYPE IN ('FUNCTION', 'PROCEDURE', 'PACKAGE')) LOOP
EXECUTE IMMEDIATE CUR.SQLT;
END LOOP;
END;

2、数据校验

执行如下脚本和源库作比较，查看数据是否完整。

SELECT d.OWNER,d.status,count(1) FROM dba_objects d WHERE d.OWNER in (SELECT a.username FROM DBA_USERS A WHERE A.account_status = 'OPEN' AND A.username NOT IN ('SYS', 'SYSTEM', 'MGMT_VIEW', 'SYSMAN', 'DBSNMP')) group by d.OWNER, d.status;

SELECT d.OWNER,d.OBJECT_NAME,d.OBJECT_TYPE,d.status FROM dba_objects d WHERE d.status = 'INVALID' and d.owner in (SELECT a.username FROM DBA_USERS A WHERE A.account_status = 'OPEN' AND A.username NOT IN ('SYS', 'SYSTEM', 'MGMT_VIEW', 'SYSMAN', 'DBSNMP'));

这里主要做记录用，所以会放命令比较多，部分有待验证，大家看下有什么可以补充的地方，欢迎在下面留言探讨。后面会分享更多DBA方面内容，感兴趣的朋友可以关注下！