Skip to Main Content

Breadcrumb

May 4th

Question and Answer

Connor McDonald

Thanks for the question, jinwei.

Asked: August 29, 2018 - 1:15 am UTC

Last updated: September 04, 2018 - 5:04 am UTC

Version: 11.2.0.4.0

Viewed 10K+ times! This question is

You Asked

I need to expdp the 5 tables data which satisfy the below query,I had read some documents on MOS and found that the doc 'Export/Import DataPump Parameter QUERY - How to Specify a Query (Doc ID 277010.1)',which show the use of QUERY parameter,but the example from the doc is for 2 tables join,so could u help me for expdp 5 tables in the below query? Thanks a lot!

query statement:

select *
  from ufzsswxt.T_PM_N_ORDER odr
  join ufzsswxt.T_PM_N_ORDER_TRADE trade on odr.ORDER_ID = trade.ORDER_ID
  join ufzsswxt.T_PM_N_ORDER_DETAIL dtl on trade.TRADE_ID = dtl.TRADE_ID
  LEFT join ufzsswxt.T_PM_N_ORDER_PAY pay ON trade.trade_id = pay.trade_id
  left join ufzsswxt.T_PM_N_ORDER_SNAPSHOT odr_snap ON dtl.DETAIL_ID = odr_snap.order_detail_id and dtl.SKU_ID = odr_snap.sku_id 
  where odr.business_status in ('TO_BE_PAY','TO_BE_SHIP', 'SHIPPING','REFUNDOK','FINISH','AFTER_SALE','CLOSE','SHIPOK') 
  and odr.CHANNEL_ID in('GDJSYH1','GDJSYH','KHD_QD','XYKJ1','XYKJ2','ZYHLWYK','10085MALL','HD_LTDH') 
  and odr.create_time >=to_date('2018-07-01 00:00:00','yyyy-mm-dd hh24:mi:ss') 
  and odr.create_time <to_date('2018-08-01 00:00:00','yyyy-mm-dd hh24:mi:ss');

and Connor said...

You can get the 5 tables in question in their entirety using the INCLUDE parameter, eg

expdp ufzsswxt schemas=ufzsswxt include=TABLE:"IN ('T_PM_N_ORDER','T_PM_N_ORDER_TRADE','T_PM_N_ORDER_DETAIL','T_PM_N_ORDER_PAY','T_PM_N_ORDER_SNAPSHOT')" directory=MYDIR dumpfile=my_data.dmp logfile=my_log.log


If you want a subset of the data, then you need to provide a query for each object concerned using a similar syntax to above. Once you get into that kind of syntax, I'll typically use a parameter file because otherwise managing all of the quotes becomes tricky on the command line, so for example:

x.par
=====
include=TABLE:"IN ('EMP','DEPT')" 
query=DEPT:"WHERE dname=\'ACCOUNTING\'"
query=EMP:"WHERE deptno in ( select deptno from DEPT where dname=\'ACCOUNTING\')"

expdp scott directory=TEMP dumpfile=my_data.dmp logfile=my_log.log parfile=c:\temp\x.par


Notice that the query has to be a WHERE clause, so you can see that you'll need to remap the query to be similar to what your join is achieving. In my example, I converted:

select *
from emp, dept
where emp.deptno = dept.deptno
and  dept.dname = 'ACCOUNTING'


to

select *
from emp
where emp.deptno in ( select dept.deptno from dept where dept.dname = 'ACCOUNTING')

select * 
from dept where dept.dname = 'ACCOUNTING'



Rating

  (2 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

Details

jinwei huang, August 29, 2018 - 6:17 am UTC

Thanks! Are there any syntax error on your remap query in the bottom of your answer?
Connor McDonald
August 29, 2018 - 6:50 am UTC

Thanks, corrected.

Details

A reader, August 29, 2018 - 6:57 am UTC

According to your suggestion I have remaped my query below ,could u pls help me check if the remap query is ok? or need to change somewhere,thanks a lot!
before query:
select *
from ufzsswxt.T_PM_N_ORDER odr
join ufzsswxt.T_PM_N_ORDER_TRADE trade on odr.ORDER_ID = trade.ORDER_ID
join ufzsswxt.T_PM_N_ORDER_DETAIL dtl on trade.TRADE_ID = dtl.TRADE_ID
LEFT join ufzsswxt.T_PM_N_ORDER_PAY pay ON trade.trade_id = pay.trade_id
left join ufzsswxt.T_PM_N_ORDER_SNAPSHOT odr_snap ON dtl.DETAIL_ID = odr_snap.order_detail_id and dtl.SKU_ID = odr_snap.sku_id
where odr.business_status in ('TO_BE_PAY','TO_BE_SHIP', 'SHIPPING','REFUNDOK','FINISH','AFTER_SALE','CLOSE','SHIPOK')
and odr.CHANNEL_ID in('GDJSYH1','GDJSYH','KHD_QD','XYKJ1','XYKJ2','ZYHLWYK','10085MALL','HD_LTDH')
and odr.create_time >=to_date('2018-07-01 00:00:00','yyyy-mm-dd hh24:mi:ss')
and odr.create_time <to_date('2018-08-01 00:00:00','yyyy-mm-dd hh24:mi:ss');

remap query:
select * from ufzsswxt.T_PM_N_ORDER odr where odr.ORDER_ID in (select trade.ORDER_ID from ufzsswxt.T_PM_N_ORDER_TRADE trade) and odr.business_status in ('TO_BE_PAY','TO_BE_SHIP', 'SHIPPING','REFUNDOK','FINISH','AFTER_SALE','CLOSE','SHIPOK')
and odr.CHANNEL_ID in('GDJSYH1','GDJSYH','KHD_QD','XYKJ1','XYKJ2','ZYHLWYK','10085MALL','HD_LTDH')
and odr.create_time >=to_date('2018-07-01 00:00:00','yyyy-mm-dd hh24:mi:ss')
and odr.create_time <to_date('2018-08-01 00:00:00','yyyy-mm-dd hh24:mi:ss');

select * from ufzsswxt.T_PM_N_ORDER_TRADE trade where trade.ORDER_ID in (select odr.ORDER_ID from ufzsswxt.T_PM_N_ORDER odr where odr.business_status in ('TO_BE_PAY','TO_BE_SHIP', 'SHIPPING','REFUNDOK','FINISH','AFTER_SALE','CLOSE','SHIPOK')
and odr.CHANNEL_ID in('GDJSYH1','GDJSYH','KHD_QD','XYKJ1','XYKJ2','ZYHLWYK','10085MALL','HD_LTDH')
and odr.create_time >=to_date('2018-07-01 00:00:00','yyyy-mm-dd hh24:mi:ss')
and odr.create_time <to_date('2018-08-01 00:00:00','yyyy-mm-dd hh24:mi:ss'));

select * from ufzsswxt.T_PM_N_ORDER_DETAIL dtl where dtl.TRADE_ID in (select trade.TRADE_ID from ufzsswxt.T_PM_N_ORDER odr,ufzsswxt.T_PM_N_ORDER_TRADE trade where odr.ORDER_ID = trade.ORDER_ID and odr.business_status in ('TO_BE_PAY','TO_BE_SHIP', 'SHIPPING','REFUNDOK','FINISH','AFTER_SALE','CLOSE','SHIPOK')
and odr.CHANNEL_ID in('GDJSYH1','GDJSYH','KHD_QD','XYKJ1','XYKJ2','ZYHLWYK','10085MALL','HD_LTDH')
and odr.create_time >=to_date('2018-07-01 00:00:00','yyyy-mm-dd hh24:mi:ss')
and odr.create_time <to_date('2018-08-01 00:00:00','yyyy-mm-dd hh24:mi:ss'));

select * from ufzsswxt.T_PM_N_ORDER_PAY pay where pay.trade_id in (select trade.TRADE_ID from ufzsswxt.T_PM_N_ORDER odr,ufzsswxt.T_PM_N_ORDER_TRADE trade,ufzsswxt.T_PM_N_ORDER_DETAIL dtl where odr.ORDER_ID = trade.ORDER_ID and trade.TRADE_ID = dtl.TRADE_ID and odr.business_status in ('TO_BE_PAY','TO_BE_SHIP', 'SHIPPING','REFUNDOK','FINISH','AFTER_SALE','CLOSE','SHIPOK')
and odr.CHANNEL_ID in('GDJSYH1','GDJSYH','KHD_QD','XYKJ1','XYKJ2','ZYHLWYK','10085MALL','HD_LTDH')
and odr.create_time >=to_date('2018-07-01 00:00:00','yyyy-mm-dd hh24:mi:ss')
and odr.create_time <to_date('2018-08-01 00:00:00','yyyy-mm-dd hh24:mi:ss'));

select * from ufzsswxt.T_PM_N_ORDER_SNAPSHOT odr_snap where (odr_snap.order_detail_id,odr_snap.SKU_ID) in (select dtl.DETAIL_ID,dtl.SKU_ID from ufzsswxt.T_PM_N_ORDER odr
join ufzsswxt.T_PM_N_ORDER_TRADE trade on odr.ORDER_ID = trade.ORDER_ID
join ufzsswxt.T_PM_N_ORDER_DETAIL dtl on trade.TRADE_ID = dtl.TRADE_ID
LEFT join ufzsswxt.T_PM_N_ORDER_PAY pay ON trade.trade_id = pay.trade_id
where odr.business_status in ('TO_BE_PAY','TO_BE_SHIP', 'SHIPPING','REFUNDOK','FINISH','AFTER_SALE','CLOSE','SHIPOK')
and odr.CHANNEL_ID in('GDJSYH1','GDJSYH','KHD_QD','XYKJ1','XYKJ2','ZYHLWYK','10085MALL','HD_LTDH')
and odr.create_time >=to_date('2018-07-01 00:00:00','yyyy-mm-dd hh24:mi:ss')
and odr.create_time <to_date('2018-08-01 00:00:00','yyyy-mm-dd hh24:mi:ss'));


Connor McDonald
September 04, 2018 - 5:04 am UTC

Um.........maybe.

We really can't tell just by looking at the SQL - you need to run before and after tests. Make sure it returns the right results, and is giving the performance boost you are after etc.

More to Explore

Utilities

All of the database utilities are explained in the Utilities guide.