VIEWS_AS_TABLES is a neat feature in Oracle Data Pump that allows you to export the contents of a view and import them as tables.

The idea is to export a view as if it were a table. The dump file contains a table definition:

• With the same name as the view
• With the same columns as the view
• With the same data as the view

Show Me

1. In the source database, you create a view, or you can use an existing view:

SQL> create view sales as select * from all_objects;

2. Next, you export that view as a table:

$ expdp ... views_as_tables=sales

3. In the target database, you import:

$ impdp ...

4. The view is now a table:

SQL> select object_type from all_objects where object_name='SALES';

OBJECT_TYPE
-----------
TABLE

When To Use It

• Faster import of data over a database link when using the QUERY parameter. Normally, the predicate in the QUERY parameter is evaluated on the target database, so during a Data Pump import over a database link, all rows are retrieved from the source database. Then, the QUERY parameter is applied to filter the rows. This is inefficient if you select a smaller portion of a larger table. By using VIEWS_AS_TABLES the filtering happens on the source database and might speed up the import dramatically.

• Customized data export. Another case I worked on involved a system where the user must be able to extract certain data in a format of their choosing. The user could define a view, export it, and import it into their local database for further processing. The view could:

  • Include various columns and the user can decide the ordering and column names.
  • Join tables to create a more complete data set.
  • Translate columns with domain values to text (like 1 being NEW, 2 being IN PROGRESS and so forth).
  • De-normalize data to make it more human-readable.
  • Format dates and numbers according to the user’s NLS settings.

• Transform tables are part of a migration. I’ve also seen some customers perform powerful transformations to data while the data was migrated. There are a lot of transformations already in Data Pump, but in these cases, the customer had more advanced requirements.

The Details

• You can use VIEWS_AS_TABLES in all modes: full, tablespace, schema, and table.
• The table has the same name as the view. But you can also use the REMAP_TABLE option in Data Pump to give it a new name.
• During export, Data Pump:
  1. Creates an empty table with the same structure as the view (select * from <view> where rownum < 1).
  2. Exports the table metadata
  3. Unloads data from the view
  4. Drops the interim table
• Data Pump also exports dependent objects, like grants, that are dependent on the view. On import, Data Pump adds those grants to the table.

Conclusion

A powerful feature that might come in handy one day to transform your data or boost the performance of network link imports.

Leave a comment and let me know how you used the feature.