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
QUERYparameter cannot be used in conjunction with the following parametersCONTENT=METADATA_ONLY , ESTIMATE_ONLY, TRANSPORT_TABLESPACES
Article by : Anju