[20171212]EXPDP如何导出两表关联后的数据

[20171212]EXPDP如何导出两表关联后的数据.txt

https://blogs.oracle.com/database4cn/expdp%e5%a6%82%e4%bd%95%e5%af%bc%e5%87%ba%e4%b8%a4%e8%a1%a8%e5%85%b3%e8%81%94%e5%90%8e%e7%9a%84%e6%95%b0%e6%8d%ae

--//重复测试:
1.环境:

SCOTT@book> @ &r/ver1
PORT_STRING                    VERSION        BANNER
------------------------------ -------------- --------------------------------------------------------------------------------
x86_64/Linux 2.4.xx            11.2.0.4.0     Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production

SCOTT@book> select * from emp order by 1;
     EMPNO ENAME      JOB              MGR HIREDATE                   SAL       COMM     DEPTNO
---------- ---------- --------- ---------- ------------------- ---------- ---------- ----------
      7369 SMITH      CLERK           7902 1980-12-17 00:00:00        800                    20
      7499 ALLEN      SALESMAN        7698 1981-02-20 00:00:00       1600        300         30
      7521 WARD       SALESMAN        7698 1981-02-22 00:00:00       1250        500         30
      7566 JONES      MANAGER         7839 1981-04-02 00:00:00       2975                    20
      7654 MARTIN     SALESMAN        7698 1981-09-28 00:00:00       1250       1400         30
      7698 BLAKE      MANAGER         7839 1981-05-01 00:00:00       2850                    30
      7782 CLARK      MANAGER         7839 1981-06-09 00:00:00       2450                    10
      7788 SCOTT      ANALYST         7566 1987-04-19 00:00:00       3000                    20
      7839 KING       PRESIDENT            1981-11-17 00:00:00       5000                    10
      7844 TURNER     SALESMAN        7698 1981-09-08 00:00:00       1500          0         30
      7876 ADAMS      CLERK           7788 1987-05-23 00:00:00       1100                    20
      7900 JAMES      CLERK           7698 1981-12-03 00:00:00        950                    30
      7902 FORD       ANALYST         7566 1981-12-03 00:00:00       3000                    20
      7934 MILLER     CLERK           7782 1982-01-23 00:00:00       1300                    10
14 rows selected.

SCOTT@book> create table test01 (name varchar2(30),empno number(8));
Table created.

insert into test01 values ('test1',7788);
insert into test01 values ('test2',7900);
insert into test01 values ('test3',8999);
commit;

SCOTT@book> select * from emp t1 where exists (select EMPNO from test01 t2 where t2.empno=t1.empno);
     EMPNO ENAME      JOB              MGR HIREDATE                   SAL       COMM     DEPTNO
---------- ---------- --------- ---------- ------------------- ---------- ---------- ----------
      7788 SCOTT      ANALYST         7566 1987-04-19 00:00:00       3000                    20
      7900 JAMES      CLERK           7698 1981-12-03 00:00:00        950                    30

--//要导出这2条记录.

$ expdp scott/book  dumpfile=emp.dp tables=emp query='emp:" where exists (select EMPNO from test01 where ku$.EMPNO = test01.EMPNO)"'
Export: Release 11.2.0.4.0 - Production on Tue Dec 12 16:17:28 2017
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 "SCOTT"."SYS_EXPORT_TABLE_01":  scott/a**** dumpfile=emp.dp tables=emp query=emp:" where exists (select EMPNO from test01 where ku$.EMPNO = test01.EMPNO)"
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/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/STATISTICS/TABLE_STATISTICS
. . exported "SCOTT"."EMP"                               8.070 KB       2 rows
Master table "SCOTT"."SYS_EXPORT_TABLE_01" successfully loaded/unloaded
******************************************************************************
Dump file set for SCOTT.SYS_EXPORT_TABLE_01 is:
  /u01/app/oracle/admin/book/dpdump/emp.dp
Job "SCOTT"."SYS_EXPORT_TABLE_01" successfully completed at Tue Dec 12 16:17:38 2017 elapsed 0 00:00:09

--//需要使用ku$作为表的别名，因为empno字段2个表都有,存在冲突.否则表的所有记录都会被导出。
--//如果写成如下:
$ expdp scott/book  dumpfile=emp1.dp tables=emp query='emp:" where exists (select EMPNO from test01 where EMPNO = test01.EMPNO)"'
Export: Release 11.2.0.4.0 - Production on Tue Dec 12 16:19:36 2017
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 "SCOTT"."SYS_EXPORT_TABLE_01":  scott/a**** dumpfile=emp1.dp tables=emp query=emp:" where exists (select EMPNO from test01 where EMPNO = test01.EMPNO)"
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/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/STATISTICS/TABLE_STATISTICS
. . exported "SCOTT"."EMP"                               8.562 KB      14 rows
--//这里14条
Master table "SCOTT"."SYS_EXPORT_TABLE_01" successfully loaded/unloaded
******************************************************************************
Dump file set for SCOTT.SYS_EXPORT_TABLE_01 is:
  /u01/app/oracle/admin/book/dpdump/emp1.dp
Job "SCOTT"."SYS_EXPORT_TABLE_01" successfully completed at Tue Dec 12 16:19:46 2017 elapsed 0 00:00:09

$ expdp scott/book  dumpfile=emp2.dp tables=emp query='emp:" where exists (select EMPNO from test01 where emp.EMPNO = test01.EMPNO)"'
Export: Release 11.2.0.4.0 - Production on Tue Dec 12 16:20:37 2017
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 "SCOTT"."SYS_EXPORT_TABLE_01":  scott/a*** dumpfile=emp2.dp tables=emp query=emp:" where exists (select EMPNO from test01 where emp.EMPNO = test01.EMPNO)"
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/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/STATISTICS/TABLE_STATISTICS
ORA-31693: Table data object "SCOTT"."EMP" failed to load/unload and is being skipped due to error:
ORA-00904: "EMP"."EMPNO": invalid identifier
--//无法识别"EMP"."EMPNO".要使用别名ku$.
Master table "SCOTT"."SYS_EXPORT_TABLE_01" successfully loaded/unloaded
******************************************************************************
Dump file set for SCOTT.SYS_EXPORT_TABLE_01 is:
  /u01/app/oracle/admin/book/dpdump/emp2.dp
Job "SCOTT"."SYS_EXPORT_TABLE_01" completed with 1 error(s) at Tue Dec 12 16:20:46 2017 elapsed 0 00:00:08

--//字段改名看看呢?
SCOTT@book> alter table test01 rename column empno to eno;
Table altered.

$ expdp scott/book  dumpfile=emp3.dp tables=emp query='emp:" where exists (select ENO from test01 where empno = test01.ENO)"'
Export: Release 11.2.0.4.0 - Production on Tue Dec 12 16:24:43 2017
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 "SCOTT"."SYS_EXPORT_TABLE_01":  scott/a**** dumpfile=emp3.dp tables=emp query=emp:" where exists (select ENO from test01 where empno = test01.ENO)"
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/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/STATISTICS/TABLE_STATISTICS
. . exported "SCOTT"."EMP"                               8.070 KB       2 rows
--//导出2条.简单的修改字段名,规避重名就可以正确导出.当然要可以改字段名才行.
Master table "SCOTT"."SYS_EXPORT_TABLE_01" successfully loaded/unloaded
******************************************************************************
Dump file set for SCOTT.SYS_EXPORT_TABLE_01 is:
  /u01/app/oracle/admin/book/dpdump/emp3.dp
Job "SCOTT"."SYS_EXPORT_TABLE_01" successfully completed at Tue Dec 12 16:24:53 2017 elapsed 0 00:00:09

--//参考文档:

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

Restrictions

The QUERY parameter cannot be used with the following parameters:

    CONTENT=METADATA_ONLY

    ESTIMATE_ONLY

    TRANSPORT_TABLESPACES

When the QUERY parameter is specified for a table, Data Pump uses external tables to unload the target table. External
tables uses a SQL CREATE TABLE AS SELECT statement. The value of the QUERY parameter is the WHERE clause in the SELECT
portion of the CREATE TABLE statement. If the QUERY parameter includes references to another table with columns whose
names match the table being unloaded, and if those columns are used in the query, then you will need to use a table
alias to distinguish between columns in the table being unloaded and columns in the SELECT statement with the same name.
The table alias used by Data Pump for the table being unloaded is KU$.
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
For example, suppose you want to export a subset of the sh.sales table based on the credit limit for a customer in the
sh.customers table. In the following example, KU$ is used to qualify the cust_id field in the QUERY parameter for
unloading sh.sales. As a result, Data Pump exports only rows for customers whose credit limit is greater than $10,000.

QUERY='sales:"WHERE EXISTS (SELECT cust_id FROM customers c
   WHERE cust_credit_limit > 10000 AND ku$.cust_id = c.cust_id)"'

If, as in the following query, KU$ is not used for a table alias, then the result will be that all rows are unloaded:

QUERY='sales:"WHERE EXISTS (SELECT cust_id FROM customers c
   WHERE cust_credit_limit > 10000 AND cust_id = c.cust_id)"'

The maximum length allowed for a QUERY string is 4000 bytes including quotation marks, which means that the actual
maximum length allowed is 3998 bytes.

时间： 2024-10-28 12:22:12

[20171212]EXPDP如何导出两表关联后的数据的相关文章

mpp-sql两表关联，两边字段类型不一致的问题。

问题描述 sql两表关联,两边字段类型不一致的问题. 两个表用id字段关联,主表大概4000w条数据,副表大概1.6亿条,id字段在主表为bigint类型,在副表为varchar类型,环境为db2的mpp,之前不知道存在类型不一致的情况,关联了不止十小时无果,改正类型不一致问题后,十多分钟出结果.两边的id也都是数字类型,我想知道为什么性能前后差别这么大,而且sql作为面向查询的语言,为什么需要我显式的转换数据类型? 解决方案 类型不一致怎么去关联 解决方案二: int和varchar的存储方式

mysql 数据导出修改表结构后，再导入，新添加的字段插入默认值

问题描述 mysql 数据导出修改表结构后,再导入,新添加的字段插入默认值 要实现的例子: 表A (a0,a1) 拥有数据100条 现在要将表结构修改为,A(a0,a1,a2),但是不想重新输入数据 所以,想先从旧的表中到处数据,然后导入新表,字段a2取默认值或固定值 求解决方案!! 解决方案 在各位大神的提示下,经过测试,alter table A add a2 数据类型 default 值 可以更改表结构 因此呢,我打算先把原表更改结构,导出数据,然后再将原表恢复原样 这样很安全了,不会影响

Hibernate 两表关联， 列名不同， 如何映射？

问题描述 表ord:idname表ord_lineidord_id(映射到ord中的id字段)现在希望在Order中保持一个order line的集合,如何写ord的xml映射文件啊?order.xml<set name="ordLines" inverse = "true" cascade = "all"> <key> <column name="id" /> </key> &

oracle 两表递归关联查询

问题描述 oracle 两表递归关联查询 哪位大神帮忙指点下,现表A有字段a,b,c 表B有字段d 然后用表A和表B关联,先使用表B的字段d和表A的字段c关联如果关联不上,再用表B的字段d和表A的字段b关联,如果关联不上在用表B的d字段和表A的字段a进行关联,其中表A的字段c从属字段b,字段b从属字段a 解决方案 Oracle创建两表关联查询的视图查询oracle约束所关联的表查询oracle约束所关联的表 解决方案二: 有点像地市层级.但数据库不会这样设计把. 直接 用or将3种关联 联系起来

两表(多表)关联update的写法 .

原文:两表(多表)关联update的写法 . 关于两表关联的update,可以把SQL写成了在SQL Server下面的特有形式,但是这种语法在Oracle下面是行不通的    update customers a    set    city_name=(select b.city_name from tmp_cust_city b where b.customer_id=a.customer_id)   where  exists (select 1                   fr

Oracle 11G R2 用exp无法导出空表解决方法

Oracle 11G在用EXPORT导出时空表不能导出 11G R2中有个新特性当表无数据时不分配segment以节省空间 解决方法 一. insert一行再rollback就产生segment了. 该方法是在在空表中插入数据再删除则产生segment.导出时则可导出 空表. 二. 设置deferred_segment_creation 参数 该参数值默认是TRUE当改为FALSE时无论是空表还是非空表都分配 segment.修改SQL语句 alter system se

MySQL中两表UNION查询实例介绍

一,union查询用法 union查询比较简单,就好像把两张表合并了,字段也合成一块 假如是 select * from table1, table2 的话,两个的相同的字段不会合并但 select a1 from table1 union select a1 from table2 这样的话就可以把两个表的a1合成一个 上面不知道你看明白了,没有我们接着看实例 一使用SELECT子句进行多表查询 SELECT 字段名 FROM 表1,表2 - WHERE 表1.字段 = 表2.字段 AND 其

LINQ TO SQL数据实体应该这样设计（解决多表关联问题）

前几天看了老赵的Translate方式解决多表关联后产生实体类型问题,但多数据量时还是不太妥当,所以最后还是用老的方法,建立一个实体类来解决这个问题 首先我让实体类和LINQ表类型名称一样,只是加了个后缀用来区分,并让它去继承LINQ表对象,这样它将有表对象的所有非私有的属性和方法. 如图: IEntity是个接口,只要继承它,就必须实现它的一个PrimaryKey这个属性,它用来统一表对象的主键. 本文转自博客园张占岭(仓储大叔)的博客,原文链接:LINQ TO SQL数据实体应该这样设计(解

Oracle中利用数据泵导出查询结果（一） 数据泵的QUERY功能

在ITPUB上看到有人提出这个问题,能否利用数据泵导出一个查询结果.事实上数据泵还真的具有这个功能. 建立一个简单的测试环境: SQL> CREATE TABLE T1 2  (ID NUMBER, NAME VARCHAR2(30)); Table created. SQL> INSERT INTO T1 2  SELECT ROWNUM, TNAME 3  FROM TAB; 66 rows created. SQL> CREATE TABLE T2 2  (ID NUMBER, N