Solved

Data Pump by Date Range

Posted on 2008-06-25
4
1,175 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
  • 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 47

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 47

Accepted Solution

by:
schwertner earned 125 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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Join & Write a Comment

Article by: Swadhin
From the Oracle SQL Reference (http://download.oracle.com/docs/cd/B19306_01/server.102/b14200/queries006.htm) we are told that a join is a query that combines rows from two or more tables, views, or materialized views. This article provides a glimps…
This article started out as an Experts-Exchange question, which then grew into a quick tip to go along with an IOUG presentation for the Collaborate confernce and then later grew again into a full blown article with expanded functionality and legacy…
This video shows syntax for various backup options while discussing how the different basic backup types work.  It explains how to take full backups, incremental level 0 backups, incremental level 1 backups in both differential and cumulative mode a…
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function

707 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

Need Help in Real-Time?

Connect with top rated Experts

10 Experts available now in Live!

Get 1:1 Help Now