Where and Query Clause in Export ( Expdp ) in Oracle

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 )

 13,915 views last month,  7 views today

About Mehmet Salih Deveci

I am Founder of SysDBASoft IT and IT Tutorial and Certified Expert about Oracle & SQL Server database, Goldengate, Exadata Machine, Oracle Database Appliance administrator with 10+years experience. I have OCA, OCP, OCE RAC Expert Certificates I have worked 100+ Banking, Insurance, Finance, Telco and etc. clients as a Consultant, Insource or Outsource. I have done 200+ Operations in this clients such as Exadata Installation & PoC & Migration & Upgrade, Oracle & SQL Server Database Upgrade, Oracle RAC Installation, SQL Server AlwaysOn Installation, Database Migration, Disaster Recovery, Backup Restore, Performance Tuning, Periodic Healthchecks. I have done 2000+ Table replication with Goldengate or SQL Server Replication tool for DWH Databases in many clients. If you need Oracle DBA, SQL Server DBA, APPS DBA,  Exadata, Goldengate, EBS Consultancy and Training you can send my email adress [email protected]. -                                                                                                                                                                                                                                                 - Oracle DBA, SQL Server DBA, APPS DBA,  Exadata, Goldengate, EBS ve linux Danışmanlık ve Eğitim için  [email protected] a mail atabilirsiniz.

Leave a Reply

Do NOT follow this link or you will be banned from the site!