EXPDP导出两表关联后数据

操作环境：redhat6.5 

数据库版本：11.2.0.4.20201020

https://docs.oracle.com/cd/E11882_01/server.112/e22490/dp_import.htm#SUTIL924

最近客户有需求需要到处正式生产数据进行审计，以满足上市业务的审核需求。导出的数据需要满足以下需求（这里是使用hr用户进行模拟测试）

select count(*)
from employees a
where exists (select department_id
from departments t2
where a.department_id = t2.department_id
and t2.location_id = 1800);

COUNT(*)
----------
2

#在查看官方文档的时候发现不仅仅能满足于两表关联，而且使用dblink进行关联过滤也是可以的，顿时感觉Oracle无比强大

#通过以下语句进行导出操作

#这里使用的是sys用户进程导出的，使用也是为了更好的匹配在日常运维中我们不知道用户密码的尴尬，Oracle官方手册上提供的是直接使用用户进程导出，感觉对于dba来说不太友善

#需要注意的是：正在导出的表的别名一定要使用ku$，否则过滤条件不起作用

[oracle@lhh expdp]$ expdp \'/as sysdba\' dumpfile=exp%U.dmp tables=hr.employees query='employees:" where exists (select department_id from hr.departments t2 where ku$.department_id = t2.department_id and t2.location_id = 1800)"'

Export: Release 11.2.0.4.0 - Production on Tue May 17 22:08:59 2022

Copyright (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_TABLE_01": "/******** AS SYSDBA" dumpfile=exp%U.dmp tables=hr.employees query=employees:" where exists (select department_id from hr.departments t2 where ku$.department_id = t2.department_id and t2.location_id = 1800)"
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
Processing object type TABLE_EXPORT/TABLE/INDEX/INDEX
Processing object type TABLE_EXPORT/TABLE/CONSTRAINT/CONSTRAINT
Processing object type TABLE_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
Processing object type TABLE_EXPORT/TABLE/CONSTRAINT/REF_CONSTRAINT
Processing object type TABLE_EXPORT/TABLE/TRIGGER
Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
. . exported "HR"."EMPLOYEES" 9.398 KB 2 rows
Master table "SYS"."SYS_EXPORT_TABLE_01" successfully loaded/unloaded
******************************************************************************
Dump file set for SYS.SYS_EXPORT_TABLE_01 is:
/u01/app/oracle/admin/orcl/dpdump/exp01.dmp
Job "SYS"."SYS_EXPORT_TABLE_01" successfully completed at Tue May 17 22:09:04 2022 elapsed 0 00:00:05