提供了编程的基础技术教程

zl程序教程

您现在的位置是：首页 >  数据库

当前栏目

expdp导出表关联数据的功能

数据
2023-03-20 14:59:57 时间

通过数据泵导出的时候，除了能导出表的数据，其实可以导出多张表关联的数据，这个确实之前没碰到过。

可以参考官方文档中的介绍，用到的就是expdp的query参数设置，

https://docs.oracle.com/database/121/SUTIL/GUID-CDA1477D-4710-452A-ABA5-D29A0F3E3852.htm#SUTIL860

格式是，

QUERY = [schema.][table_name:] query_clause

可能我们用到过query加上一些条件做过滤，但是[schema.][table_name]确实很少用，他的作用其实就是限定要导出的表是什么，重要的是query_clause子句中可以用ku$作为表的别名。

用个例子来说明，employees记录数是107条，

SQL> select count(*) from employees;
  COUNT(*)
----------
       107

employees和departments做关联，找出department_id是110，同时出现在departments的employees的记录，共返回2条，

SQL> select count(*) from employees t1 
     where exists (select department_id from departments t2 
                    where t1.department_id = t2.department_id and t2.department_id=110);
  COUNT(*)
----------
         2

expdp的query表示是从该表检索数据，冒号跟着的，就是检索条件，注意到ku$.department_id，指的是employees.department_id，可以看到导出的记录数是2 rows，

[oracle@modb ~]$ expdp hr/oracle@modb:1521/xepdb1 directory=exp_dir dumpfile=exp.dmp tables=employees query='employees:" where exists (select departme_id from departments t2 where ku$.department_id = t2.department_id and t2.department_id=110)"'


Export: Release 18.0.0.0.0 - Production on Thu Nov 25 16:42:42 2021
Version 18.4.0.0.0


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


Connected to: Oracle Database 18c Express Edition Release 18.0.0.0.0 - Production
Starting "HR"."SYS_EXPORT_TABLE_01":  hr/********@modb:1521/xepdb1 directory=exp_dir dumpfile=exp.dmp tables=employees query=employees:" where exists (select department_id from departments t2 where ku$.department_id = t2.department_id and t2.department_id=110)" 
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
Processing object type TABLE_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
Processing object type TABLE_EXPORT/TABLE/STATISTICS/MARKER
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/CONSTRAINT/REF_CONSTRAINT
Processing object type TABLE_EXPORT/TABLE/TRIGGER
. . exported "HR"."EMPLOYEES"                            9.679 KB       2 rows
Master table "HR"."SYS_EXPORT_TABLE_01" successfully loaded/unloaded
******************************************************************************
Dump file set for HR.SYS_EXPORT_TABLE_01 is:
  /home/oracle/exp.dmp
Job "HR"."SYS_EXPORT_TABLE_01" successfully completed at Thu Nov 25 21:00:00 2021 elapsed 0 00:00:24

如果我们不用ku$，可以看到他导出的就是employees所有的记录，107条，

[oracle@modb ~]$ expdp hr/oracle@modb:1521/xepdb1 directory=exp_dir dumpfile=exp0.dmp tables=employees query='employees:" where exists (select department_id from departments t2 where department_id = t2.department_id and t2.department_id=110)"'


Export: Release 18.0.0.0.0 - Production on Thu Nov 25 16:50:55 2021
Version 18.4.0.0.0


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


Connected to: Oracle Database 18c Express Edition Release 18.0.0.0.0 - Production
Starting "HR"."SYS_EXPORT_TABLE_01":  hr/********@modb:1521/xepdb1 directory=exp_dir dumpfile=exp0.dmp tables=employees query=employees:" where exists (select department_id from departments t2 where department_id = t2.department_id and t2.department_id=110)" 
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
Processing object type TABLE_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
Processing object type TABLE_EXPORT/TABLE/STATISTICS/MARKER
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/CONSTRAINT/REF_CONSTRAINT
Processing object type TABLE_EXPORT/TABLE/TRIGGER
. . exported "HR"."EMPLOYEES"                            17.08 KB     107 rows
Master table "HR"."SYS_EXPORT_TABLE_01" successfully loaded/unloaded
******************************************************************************
Dump file set for HR.SYS_EXPORT_TABLE_01 is:
  /home/oracle/exp0.dmp
Job "HR"."SYS_EXPORT_TABLE_01" successfully completed at Thu Nov 25 21:10:00 2021 elapsed 0 00:00:15

query参数有些限制，不能和这些参数同时使用，

(1) CONTENT=METADATA_ONLY

(2) ESTIMATE_ONLY

(3) TRANSPORT_TABLESPACES

如果讲到原理，数据泵是采用了外部表的形式导出目标表的数据。外部表使用CTAS(Create Table As Select)来创建的。query参数的值就是Select时的检索条件Where子句。如果参数query包含了其他表的引用，并在在查询中使用了这些列，就需要使用表别名来区分其他的表，数据泵中使用的别名统一就叫做ku$。

当我们需要导出一些表关联的数据时，expdp的query参数，算是一种便利的选择。

相关文章