How To Export Oracle Tables Specifying Conditions?

The Query parameter of exp/expdp utility allows to export the subset of a table based on any condition. The value for the parameter should be specified as a string with a WHERE clause.

Consider that we have to export data from employee table where salary > 5000.

exp scott/tiger TABLES=employee QUERY=\”WHERE sal\>5000\”

Multiple tables and conditions can also be specified

exp scott/tiger TABLES=employee,designation QUERY=\”WHERE job=\’CLERK\’ and sal\>5000\”

>>  In this case , the data will be exported from employee and designation table based on the condition  job=CLERK and sal>5000

There is a little syntax difference in case of expdp

QUERY = schema.table_name:query_clause

exp scott/tiger QUERY=employee:'”WHERE salary > 5000″‘

An enhancement with this syntax is that we can export from multiple tables based on multiple conditions, which was not possible in exp.

Query= table1:condition1, table2:condition2

However, a few restrictions are there for this parameter

For Exp

  • The parameter QUERY cannot be specified for full, user, or tablespace mode exports.
  • The parameter QUERY must be applicable to all specified tables.
  • The parameter QUERY cannot be specified in a direct path export (DIRECT=y)
  • The parameter QUERY cannot be specified for tables with inner nested tables.
  • You cannot determine from the contents of the export file whether the data is the result of a QUERY export.

For Expdp

  • The QUERY parameter cannot be used in conjunction with the following parameters   CONTENT=METADATA_ONLY , ESTIMATE_ONLY, TRANSPORT_TABLESPACES

Article by : Anju