Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

Data Pump by Date Range

Posted on 2008-06-25
4
Medium Priority
?
1,425 Views
Last Modified: 2008-06-29
Hello,

I have a schema (SCOTT) where there are two related tables: SCOTT.Documents (parent) and SCOTT.DocumentPages (child).  The SCOTT.Documents table has a "Created" date column.  There are PK fields that ensures there cannot be duplicates etc.

SCOTT.Documents
--------------------------------------
doc_id   NUMBER    PK
description   VARCHAR2
created   DATE

SCOTT.DocumentPages
--------------------------------------
page_id   NUMBER   PK
doc_id   NUMBER   FK
document   BLOB

1. How can I use the Data Pump to export all the data in both these tables WHERE just SCOTT.Documents (and corresponding SCOTT.DocumentPages) greater than 01-01-2008 are exported AND all other schema data in full?

2. The destination database\schema only has data less than 01-01-2008 for the two tables listed.  How can I import this data into the destination database ensuring existing older Document\DocumentPages data and it's Indexes and Constraints (PK, FK etc.)  are not negatively affected?

Note: All the other schema outside these two tables should be replaced in full on the destination database\schema.

Parameter file (.par) examples would be ideal.

Thanks,

Michael4606
0
Comment
Question by:michael4606
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
4 Comments
 
LVL 20

Expert Comment

by:gatorvip
ID: 21869005
I don't think/know if you can do it directly with expdp but you can create two tables with only the data you want, export those two tables, then import into your new location (also in two "temporary" tables) then finally merge them back with your other data.
0
 
LVL 48

Expert Comment

by:schwertner
ID: 21872781
There is a tricky way to do this and I use it for special purposes.
The trick is that Data Pump has API called DBMS_DATA_PUMP.
You can create a PL/SQL procedure that:
1. Imports (or Package that exports/imports) both tables in their original
form.
2. After the Import is done deletes the unwanted records.

The only problem you will have is to synchronize the end of the Import
with the beginning of the delete action. This is so because using the package you only
initiate the Pump job. It runs in the background and have to know when it stops.
0
 

Author Comment

by:michael4606
ID: 21883088
The legacy export\import tools had a QUERY parameter that could be used to filter data using a WHERE clause.  I guess in essence I am asking for the modern Data Pump method of doing this.

Thanks again,

Michael4606
0
 
LVL 48

Accepted Solution

by:
schwertner earned 375 total points
ID: 21883224
You can also use filter in the Data Pump like in the clasic Export.
But here you face relationship between two tables.
This is complex WHERE clause and i am not sure that you can express this
condition in the EXCLUDE clause.
0

Featured Post

What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Working with Network Access Control Lists in Oracle 11g (part 1) Part 2: http://www.e-e.com/A_9074.html So, you upgraded to a shiny new 11g database and all of a sudden every program that used UTL_MAIL, UTL_SMTP, UTL_TCP, UTL_HTTP or any oth…
I remember the day when someone asked me to create a user for an application developement. The user should be able to create views and materialized views and, so, I used the following syntax: (CODE) This way, I guessed, I would ensure that use…
This video explains at a high level with the mandatory Oracle Memory processes are as well as touching on some of the more common optional ones.
This video shows how to Export data from an Oracle database using the Original Export Utility.  The corresponding Import utility, which works the same way is referenced, but not demonstrated.

705 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question