Default: There is no default
This description of VIEWS_AS_TABLES is applicable during network imports, meaning that you supply a value for the Data Pump Import NETWORK_LINK parameter. If you are performing an import that is not a network import, then see "VIEWS_AS_TABLES (Non-Network Import)".
Purpose
Specifies that one or more views are to be imported as tables.
Syntax and Description
VIEWS_AS_TABLES=[schema_name.]view_name[:table_name], ...
Data Pump imports a table with the same columns as the view and with row data fetched from the view. Data Pump also imports 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 imported.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 import.
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 import.
view_name--The name of the view to be imported 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 imported 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 import 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:
impdp hr DIRECTORY=dpump_dir NETWORK_LINK=dblink1 VIEWS_AS_TABLES=v1:employees,v2:employees
An error message is returned reporting the omitted object.
Template tables are automatically dropped after the import 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 HR.EMPLOYEESV
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 performs a network import to import the contents of the view hr.v1 from a read-only database. The hr schema on the source database must contain a template table with the same geometry as the view view1 (call this table view1_tab). The VIEWS_AS_TABLES parameter lists the view name and the table name separated by a colon:
> impdp hr VIEWS_AS_TABLES=view1:view1_tab NETWORK_LINK=dblink1
The view is imported as a table named view1 with rows fetched from the view. The metadata for the table is copied from the template table view1_tab.