I am wondering if it is possible using any version of Oracle to EXPORT from a view or EXPORT from a table using...
a WHERE clause. If not EXPORT, then are there any other utilities available to perform this task?
Since views are not "real" tables, there is no data associated with them... so you can't export a view. When you export a view, you're simply exporting the definition of the view (in other words, the SELECT statement). But, starting with 8i, there is a QUERY parameter you can use to export a table conditionally 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\"
When executing this command, Export builds a SQL SELECT statement similar to this:
SELECT * FROM EMP where job='SALESMAN' and sal <1600;
exp scott/tiger tables=emp,bonus query=\"where job=\'SALESMAN\' and sal\<1600\"
SELECT * FROM EMP where where job='SALESMAN' and sal <1600; SELECT * FROM BONUS where where job='SALESMAN' and sal <1600;
- 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.
For More Information
- Dozens more answers to tough Oracle questions from Karen Morton are available.
- The Best Oracle Web Links: tips, tutorials, scripts, and more.
- Have an Oracle or SQL tip to offer your fellow DBAs and developers? The best tips submitted will receive a cool prize. Submit your tip today!
- Ask your technical Oracle and SQL questions -- or help out your peers by answering them -- in our live discussion forums.
- Ask the Experts yourself: Our SQL, database design, Oracle, SQL Server, DB2, metadata, object-oriented and data warehousing gurus are waiting to answer your toughest questions.