[20171212]EXPDP如何导出两表关联后的数据.txt
--//重复测试:
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.
