Skip to Main Content
  • Questions
  • How to export data for specific time period using DBMS_DATAPUMP api.


Question and Answer

Chris Saxon

Thanks for the question, Amar.

Asked: March 04, 2021 - 4:48 am UTC

Last updated: March 08, 2021 - 2:27 pm UTC

Version: 19c

Viewed 100+ times

You Asked

We have a requirement where we are looking for DB dumps to be exported using DBMS_DATAPUMP api for specific time period like 01-Jan-2020 to 31-March-2020. I am aware of Flashback time and flashback scn number but it will export only for specific timestamp but not time period. Could you please help me on this.

and we said...

Search this site for DBMS_DATAPUMP.DATA_FILTER but in a nutshell, you'll use METADATA_FILTER to limit the objects, and DATA_FILTER to apply queries to those objects, eg

dbms_datapump.metadata_filter(handle => h1, name => 'SCHEMA_LIST', value => '''SCOTT''');
dbms_datapump.metadata_filter(handle => h1, name => 'NAME_LIST', value => '''EMP'',''DEPT''');
dbms_datapump.data_filter(handle => h1, table_name=>'EMP', schema_name=>'SCOTT', name => 'SUBQUERY', value => 'WHERE empno > 0');
dbms_datapump.data_filter(handle => h1, table_name=>'DEPT', schema_name=>'SCOTT', name => 'SUBQUERY', value => 'WHERE deptno = 10');

on the presumption here that you have some column(s) on your tables that will identify the date range you want


  (1 rating)


A reader, March 08, 2021 - 5:01 am UTC

Thanks for your valuable reply!
Actually I have a use case where I am exporting entire schema and I want data to be exported from 01-Jan-2020 to 01-Jan-2021 only. Could you please let me know if this is possible along with some example.
Chris Saxon
March 08, 2021 - 2:27 pm UTC

For each table you're exporting you'll need to add a filter like:

dbms_datapump.data_filter ( ... , value => 'WHERE insert_date >= date''2020-01-01'' and insert_date < date''2021-01-01''' );

This assumes all the tables have a column named insert_date storing when the row was added. Adjust to match the columns in your schema.

If there are tables without this (or a similar) column... you're going to struggle!

Bear in mind that there may also be rows inserted in earlier years that you need for referential integrity. For example, you can have orders made last year for customers first inserted many years ago. How you resolve this kinda depends on why you're trying to do this.

More to Explore


All of the database utilities are explained in the Utilities guide.