鲁达 概述
写这篇的初衷是因为之前都不怎么去记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)/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)/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)/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=ex directory=DPDATA;
2、导出表
expdp scott/tiger@orcl tables=emp,dept dumpfile=ex directory=DPDATA;
3、按查询条件导
expdp scott/tiger@orcl directory=dump_dir dumpfile=ex tables=emp query='where deptno=20';
4、按表空间导
expdp system/manager@orcl directory=DPDATA dumpfile= tablespaces=temp,example;
5、导整个数据库
expdp system/manager@orcl directory=DPDATA dumpfile= 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,SCOTT7、并行导出scott用户下所有的内容
expdp scott/tiger@orcl directory=DPDATA schemas=scott dumpfile=scott_all%U.dmp logfile= parallel=28、expdp脚本导出
expdp \'/ as sysdba\' parfile= # vi 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=ex schemas=scott;
2、导入表（从scott用户中把表dept和emp导入到system用户中）
impdp system/manager@orcl directory=dump_dir dumpfile=extables=scott.dept,scott.emp remap_schema=scott:system;
3、导入表空间
impdp system/manager@orcl directory=dump_dir dumpfile= tablespaces=example;
4、导入数据库
impdb system/manager@orcl directory=dump_dir dumpfile= full=y;
5、追加数据
impdp system/manager@orcl directory=dump_dir dumpfile=ex 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方面内容，感兴趣的朋友可以关注下！