How to export some rows of multiple tables linked between them according to one column value? I'm not sure my question is clear so let's me rephrase it. I have three tables organized like that:



I want to make a dump of these tables but I'm not interested by all the rows, I just want those who were last updated during the last month.
Is there a way with Data Pump Export to do it? I thought to the QUERY parameter but I can't use it because there is no last_updated_date in the SON and DAUGHTER tables.

For instance, if, in my MOTHER table, I have 3 rows:


id_mother = 1 ; last_updated_date = 01/01/17 ;
id_mother = 2 ; last_updated_date = 01/02/17 ;
id_mother = 3 ; last_updated_date = 01/03/17.


And, in my SON table, I have 2 rows:


id_son = 1 ; id_mother = 2 ;
id_son = 2 ; id_mother = 3.


If I want to export the rows of month of March, the process will return one row for the MOTHER table (id_mother = 3) and one row for the SON table (id_son = 2).

How can I handle it?
you can, if you use a correlated subquery. Check the manual.

QUERY=son:"where exists (
    select * 
    from mother 
    where
        mother.id_mother = ku$.id_mother and 
        mother.last_updated &gt; trunc(sysdate,'month')"


ku$represents the table you are actually exporting, so here it is the table son.

Starting with Oracle 12c you have the VIEWS_AS_TABLES parameter. Then you can export  the data of a view and store it in a table, e.g. with sqlplus do

create view new_son
as
select son.*
from son, mother
where
    mother.id_mother = son-id_mother and 
    mother.last_updated &gt; trunc(sysdate,'month')"


Now export this using

VIEWS_AS_TABLES='new_son'


If you import this it will create a table new_son.