[20171212]EXPDP如何匯出兩表關聯後的資料
[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
--//參考文件:
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.
來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/267265/viewspace-2148604/，如需轉載，請註明出處，否則將追究法律責任。
相關文章
- expdp匯出表的部分資料
- 使用EXPDP/IMPDP匯入匯出表中資料/後設資料測試
- Oracle expdp匯出多表或表中的部分資料Oracle
- expdp遠端匯出資料
- 【資料泵】EXPDP匯出表結構(真實案例)
- Oracle資料泵匯出匯入（expdp/impdp）Oracle
- expdp 使用QUERY 匯出部分資料。
- 使用expdp匯出分割槽表中的部分分割槽資料
- 資料庫泵（expdp/impdp）匯入匯出流程資料庫
- 使用資料泵工具expdp工具匯出資料
- 如何簡單實現關聯型別資料的Excel匯出？型別Excel
- Oracle expdp資料泵遠端匯出Oracle
- 【impdp】IMPDP中的TRANSFORM引數--【資料泵】EXPDP匯出表結構(真實案例)後傳ORM
- expdp impdp 資料庫匯入匯出命令詳解資料庫
- Oracle使用資料泵expdp,impdp進行資料匯出匯入Oracle
- [oracle] expdp 匯出分割槽表的分割槽Oracle
- oracle按照表條件expdp匯出資料Oracle
- expdp中使用include或者exclude匯出資料
- 在鎖表情況下expdp匯出資料
- Progress資料表的匯入匯出
- Oracle 12c expdp和impdp匯出匯入表Oracle
- mysqldump匯入匯出表資料MySql
- 資料泵匯出匯入表
- Oracle expdp/impdp匯出匯入命令及資料庫備份Oracle資料庫
- 通過EXPDP/IMPDP匯出匯入遠端資料倒本地
- Oracle expdp/impdp匯入匯出備份資料庫指令碼Oracle資料庫指令碼
- DB2資料庫匯出表結構與匯入、匯出表資料DB2資料庫
- Oracle 12.1.0.2 expdp匯出分割槽表資料遇到BUG慢的原因和解決方法Oracle
- MySQL表資料匯入與匯出MySql
- 如何將傳統關聯式資料庫的資料匯入Hadoop？資料庫Hadoop
- 使用expdp匯出資料，報ORA-01691表空間不足錯誤
- 細緻入微：如何使用資料泵匯出表的部分列資料
- expdp 匯出指令碼指令碼
- 資料泵 EXPDP 匯出工具的使用
- 利用Dbutils實現往兩個有關聯的表新增資料
- 【EXPDP/IMPDP】使用 EXPDP/IMPDP工具“模糊”匯出和匯入
- Mysql匯出表結構、表資料MySql
- ORACLE關於如何是exp匯出還是expdp並檢視資訊Oracle