Tag: views_as_tables

VIEWS_AS_TABLES – A Hidden Data Pump Gem

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;
  1. Next, you export that view as a table:
$ expdp ... views_as_tables=sales
  1. In the target database, you import:
$ impdp ...
  1. 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.