Default: There is no default
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.
Specifies that one or more views are to be exported as tables.
Syntax and Description
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
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
VIEWS_AS_TABLES parameter cannot be used with the
Tables created using the
VIEWS_AS_TABLES parameter do not contain any hidden columns that were part of the specified view.
VIEWS_AS_TABLES parameter does not support tables that have columns with a data type of
The following example exports the contents of view
scott.view1 to a dump file named
> 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.