michael4606
asked on
Data Pump by Date Range
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
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
gatorvip
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.
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.
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.
ASKER
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
Thanks again,
Michael4606
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.