Prior to release 12c, Oracle expdp sometime has to go
through complex gyrations when exporting view information,
and Oracle has overcome this limitation by introducing the
view_as_tables argument to expdp.
Invoked as "view_as_tables=scott.empview" the
view_as_tables transforms a view at the source database into
a table at the destination database, exporting all of the
the columns of the view as-if they were table columns.
In a sense, the view_as_tables is an ad-hoc materialized
view generator because the result set has been transformed
from a view to a table.
In a case where only a small portion of a complex
view is exported with view_as_tables, all unnecessary
details that maintain the complex view are omitted.
Note: You cannot use expdp with the
view_as_tables if you are choosing a LOB datatype column
from the view.
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):
select
*
from
user_tab_comments
where
table_name like 'KU$VAT%'
For example, here we export a view called scott.myview
using views_as_tables, naming it newtab1:
$ expdp xff/xifenfei
views_as_tables=scott.myview:newtab1 directory=data_pump_dir
dumpfile=xifenfei.dmp
Export: Release 12.1.0.0.2 -
Beta on Sun Dec 16 07:56:48 2015
Copyright (c) 1982, 2015,
Oracle and/or its
affiliates. All rights reserved.
Connected to: Oracle Database
12c Enterprise Edition Release 12.1.0.0.2 - 64bit Beta
With the Partitioning, OLAP,
Data Mining and Real Application Testing options
Starting
"XFF"."SYS_EXPORT_TABLE_01":xff/********
views_as_tables=v_xifenfei directory=data_pump_dir
dumpfile=xifenfei.dmp
Estimate
in
progress using BLOCKS method...
Processing object
type
TABLE_EXPORT/VIEWS_AS_TABLES/TABLE_DATA
Total estimation using BLOCKS
method: 16 KB
Processing object
type
TABLE_EXPORT/VIEWS_AS_TABLES/TABLE
. . exported
"XFF"."V_XIFENFEI"
7.390 KB 3 rows
Master table
"XFF"."SYS_EXPORT_TABLE_01"
successfully loaded/unloaded
The view_as_tables allows you to specify a vie w as if it
was a table and then make it into a table when using impdp:
$ impdp xff/xifenfei
tables=v_xifenfei directory=data_pump_dir
dumpfile=xifenfei.dmp
Import: Release 12.1.0.0.2 -
Beta on Sun Dec 16 07:59:05 2015
Copyright (c) 1982, 2015,
Oracle and/or its
affiliates. All rights reserved.
Connected to: Oracle Database
12c Enterprise Edition Release 12.1.0.0.2 - 64bit Beta
With the Partitioning, OLAP,
Data Mining and Real Application Testing options
Master table
"XFF"."SYS_IMPORT_TABLE_01"
successfully loaded/unloaded
Starting
"XFF"."SYS_IMPORT_TABLE_01":
xff/******** tables=v_xifenfei directory=data_pump_dir
dumpfile=xifenfei.dmp
Processing object
type
TABLE_EXPORT/VIEWS_AS_TABLES/TABLE
ORA-39325: TABLE_EXISTS_ACTION
cannot be applied to "XFF"."V_XIFENFEI".
Processing
object
type
TABLE_EXPORT/VIEWS_AS_TABLES/TABLE_DATA
Job
"XFF"."SYS_IMPORT_TABLE_01"
completed with 1 error(s) at
Sun Dec 16 07:59:13 2015 elapsed 0 00:00:05
