0

I'm not sure my question is clear so let's me rephrase it. I have three tables organized like that:

logical_model

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?

  • So, if there's no last updated date on SON or DAUGHTER, how do you plan on telling if they've been updated? As in one of the answers, are you assuming that the MOTHER last_updated_date is changed if SON or DAUGHTER was updated? Do you have copies of those tables from last month, so you can output anything that's different? Do you have some sort of Oracle auditing feature turned on, that tracks changes? If you've got a way to do it with direct queries, there should be a way to do it with your export tool, but it's not clear there's a way to get it at all, from this. – RDFozz Jun 1 '17 at 15:56
  • @RDFozz Yes, when SON or DAUGHTER is updated, the last_updated_date is also updated. There is no copy of my tables nor auditing feature. – Pierre Jun 2 '17 at 7:11
  • @Pierre so a row of the son table should be exported if the last_updated filed of the corresponding mother row is larger than trunc(sysdate,'month')? Please edit your post and clarify how the rows of the son table (and the daughter table) schould be selected? – miracle173 Jun 2 '17 at 8:02
  • @miracle173 Done. – Pierre Jun 2 '17 at 8:49
0

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 > 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 > trunc(sysdate,'month')"

Now export this using

VIEWS_AS_TABLES='new_son'

If you import this it will create a table new_son.

  • at the moment I don't know if zhe line breaks in the QUERY parameter are alowed. But I will keep them here for readability. – miracle173 Jun 1 '17 at 19:49
  • I guess you mean create view new_son instead of create table new_son? – Pierre Jun 2 '17 at 7:14
  • @Pierre yes, you are right – miracle173 Jun 2 '17 at 7:57
0

You can run a query for specific data to export by using Data Pump.

Docs on general filtering during export here: https://docs.oracle.com/database/121/SUTIL/GUID-1F85C939-E439-4436-BFEF-6DC7E167C912.htm#SUTIL834.

The key parameter is QUERY, which you can find docs for here: https://docs.oracle.com/database/121/SUTIL/GUID-CDA1477D-4710-452A-ABA5-D29A0F3E3852.htm#SUTIL860

The provided example is:

QUERY=employees:"WHERE department_id > 10 AND salary > 10000"

  • Okay but the where clause depends of a column present in another table. In the example you give, department_id and salary are columns of the employees table. – Pierre Jun 2 '17 at 7:16
-2

If every rows in tables 'SON' and 'DAUGHTER' having mother_id value is captured, you can export using query parameter where mother_id is greater than requested date.

SELECT * INTO OUTFILE '/path/file.csv' FIELDS TERMINATED BY ',' 
OPTIONALLY ENCLOSED BY '"' LINES TERMINATED BY '\n' FROM MOTHER 
WHERE last_updated BETWEEN DATE_FORMAT( CURRENT_DATE - INTERVAL 1 MONTH, '%Y-%m-01') 
AND DATE_FORMAT( CURRENT_DATE - INTERVAL 1 MONTH, '%Y-%m-31'))

SELECT A.* INTO OUTFILE '/path/file.csv' FIELDS TERMINATED BY ',' 
OPTIONALLY ENCLOSED BY '"' LINES TERMINATED BY '\n' 
FROM SON A
INNER JOIN mother B ON A.mother_id=B.mother_id WHERE B.last_updated 
BETWEEN DATE_FORMAT( CURRENT_DATE - INTERVAL 1 MONTH, '%Y-%m-01') 
AND DATE_FORMAT( CURRENT_DATE - INTERVAL 1 MONTH, '%Y-%m-31')

and same for DAUGHTER table, you will get dump of these tables.

  • Ok for this method but I have to do one request per tables. My tables have a lot of rows and this method requires to take a lot of time. Is there a possibility to perform the dump in one request? – Pierre Jun 1 '17 at 11:21
  • Also, according to this post : stackoverflow.com/questions/16951760/… , SELECT ... INTO OUTFILE don't work on Oracle. – Pierre Jun 1 '17 at 12:09
  • It works in case of MySQL, – akumar Jun 1 '17 at 12:35
  • The question is tagged "oracle-11g-r2" – Phil Sumner Jun 2 '17 at 12:19

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.