VIEWS_AS_TABLES

Default: There is no default

Caution:

The VIEWS_AS_TABLES parameter unloads view data in unencrypted format and creates an unencrypted table. If you are unloading sensitive data, then Oracle strongly recommends that you enable encryption on the export operation and that you ensure the table is created in an encrypted tablespace. You can use the REMAP_TABLESPACE parameter to move the table to such a tablespace.

Purpose

Specifies that one or more views are to be exported as tables.

Syntax and Description

VIEWS_AS_TABLES=[schema_name.]view_name[:table_name], ...

Data Pump exports a table with the same columns as the view and with row data fetched from the view. Data Pump also exports objects dependent on the view, such as grants and constraints. Dependent objects that do not apply to tables (for example, grants of the UNDER object privilege) are not exported.The VIEWS_AS_TABLES parameter can be used by itself or along with the TABLES parameter. If either is used, Data Pump performs a table-mode export.

The syntax elements are defined as follows:

schema_name--The name of the schema in which the view resides. If a schema name is not supplied, it defaults to the user performing the export.

view_name--The name of the view to be exported as a table. The view must exist and it must be a relational view with only scalar, non-LOB columns. If you specify an invalid or non-existent view, the view is skipped and an error message is returned.

table_name--The name of a table to serve as the source of the metadata for the exported view. By default Data Pump automatically creates a temporary "template table" with the same columns and data types as the view, but no rows. If the database is read-only, then this default creation of a template table will fail. In such a case, you can specify a table name. The table must be in the same schema as the view. It must be a non-partitioned relational table with heap organization. It cannot be a nested table.

If the export job contains multiple views with explicitly specified template tables, the template tables must all be different. For example, in the following job (in which two views use the same template table) one of the views is skipped:

expdp scott/tiger directory=dpump_dir dumpfile=a.dmp views_as_tables=v1:emp,v2:emp

An error message is returned reporting the omitted object.

Template tables are automatically dropped after the export operation is completed. While they exist, you can perform the following query to view their names (which all begin with KU$VAT):

SQL> SELECT * FROM user_tab_comments WHERE table_name LIKE 'KU$VAT%';
TABLE_NAME                     TABLE_TYPE
------------------------------ -----------
COMMENTS
-----------------------------------------------------
KU$VAT_63629                   TABLE
Data Pump metadata template table for view SCOTT.EMPV

Restrictions

  • The VIEWS_AS_TABLES parameter cannot be used with the TRANSPORTABLE=ALWAYS parameter.

  • Tables created using the VIEWS_AS_TABLES parameter do not contain any hidden columns that were part of the specified view.

  • The VIEWS_AS_TABLES parameter does not support tables that have columns with a data type of LONG.

Example

The following example exports the contents of view scott.view1 to a dump file named scott1.dmp.

> expdp scott/tiger views_as_tables=view1 directory=data_pump_dir dumpfile=scott1.dmp

The dump file will contain a table named view1 with rows fetched from the view.