Hi,

I will explain Where and Query Clause in Export ( Expdp ) in Oracle in this post.

You can read the following Datapump export import Tutorial, if you don’t know what is the Datapump and how to export and import a table.

Oracle Data Pump – Export Import ( Expdp Impdp ) Tutorial with Examples-4

Query Clause in Export ( expdp )

If your tables are not partitioned, but you want to export only specific data, then you can use the Where Clause in QUERY option. Thus you can export only specific rows using Query and Where clause.

QUERY=SCHEMA_NAME.TABLE_NAME:'"WHERE filtered_colum =>< interval_number"'

Where Clause in Export ( expdp )

If you export only specific rows, then use the Query and Where clause in expdp command.

I will export only last 30 days Customer using Membership_time column.

QUERY=SCHEMA_NAME.TABLE_NAME:'"WHERE filtered_colum =>< interval_number"'

Last 30 days’ customer can be queried bu using membership_time>sysdate-30 filter.

Add the following query option to your export code.

query=MEHMETSALIH.CUSTOMER:'"WHERE membership_time>sysdate-30"'

Export script should be as follows, it will export only last 30 days’ customers.

expdp \"/ as sysdba\" directory=DATAPUMP dumpfile=CUSTOMER%U.dmp logfile=CUSTOMER.log parallel=64 cluster=n exclude=statistics  tables=MEHMETSALIH.CUSTOMER query=MEHMETSALIH.CUSTOMER:'"WHERE membership_time>sysdate-30"'

If your tables are partitioned and you want to export specific partition of table, you can read the following post to learn it.

How to Export ( Expdp ) Specific Table Partition in Oracle Datapump

Do you want to learn Oracle Database for Beginners, then read the following articles.

Oracle Tutorial | Oracle Database Tutorials for Beginners ( Junior Oracle DBA )