Oracle: Exporting data using a WHERE clause

Here are the details from the Oracle documentation Utilities Guide: This parameter allows you to select a subset of rows from a set of tables when doing a table mode export. The value of the query parameter is a string that contains a WHERE clause for a SQL SELECT statement that will be applied to all tables (or table partitions) listed in the TABLE parameter. For example, if user SCOTT wants to export only those employees whose job title is SALESMAN and whose salary is greater than 1600, he could do the following (note that this example is UNIX-based):

exp scott/tiger tables=emp query=\"where job=\'SALESMAN\' and sal\<1600\"

Note: Because the value of the QUERY parameter contains blanks, most operating systems require that the entire strings where job=\'SALESMAN\' and sal\<1600 be placed in double quotation marks or marked as a literal by some method. Operating system reserved characters also need to be preceded by an escape character. See your operating system-specific documentation for information about special and reserved characters on your system.

When executing this command, Export builds a SQL SELECT statement similar to this:

SELECT * FROM EMP where job='SALESMAN' and sal <1600;

The QUERY is applied to all tables (or table partitions) listed in the TABLE parameter. For example, the following statement will unload rows in both EMP and BONUS that match the query:

exp scott/tiger tables=emp,bonus query=\"where job=\'SALESMAN\' and
sal\<1600\"

Again, the SQL statements that Export executes are similar to these:

SELECT * FROM EMP where where job='SALESMAN' and sal <1600;
SELECT * FROM BONUS where where job='SALESMAN' and sal <1600;

If a table is missing the columns specified in the QUERY clause, an error message will be produced and no rows will be exported for the offending table.

Restrictions

• The parameter QUERY cannot be specified for full, user, or transportable 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 will not be able to determine from the contents of the export file whether the data is the result of a QUERY export.