This feature extends functionality of Data Pump 12C. It allows to export views as tables.

expdp .. VIEWS_AS_TABLES=[schema_name.]view_name ...

Features

• new parameter for expdp VIEWS_AS_TABLES
• it exports view as table definition plus data
• it exports dependent objects like constraints(on the view) and grants(on the view)
• very useful to export subset of a data from table(s) via complex view
• during import as first step table is created then data is imported
• it can be exported in PL/SQL using DBMS_DATAPUMP.METADATA_FILTER
• doesn’t support views that reference object type or functions
• unloads data as unencrypted format

Examples

Export of view emp_view”as table and table departments

expdp hr/hr DIRECTORY=dpdump_dir DUMPFILE=hr.dmp
VIEWS_AS_TABLES=emp_view TABLES=departments

Export of two views as tables emp_view and oe.orders_v

expdp hr/hr DIRECTORY=dpdump_dir DUMPFILE=hr.dmp
VIEWS_AS_TABLES=emp_view,oe.orders_v

Import of exported view as table emp_view and rename the table as emp_new_table

impdp hr/hr DIRECTORY=dpdump_dir DUMPFILE=hr.dmp
VIEWS_AS_TABLES=emp_view REMAP_TABLE=emp_view:emp_new_table

Import through data base link db_link via view emp_v and loading it directly to existing table employees

impdp hr/hr VIEWS_AS_TABLE=emp_v NETWORK_LINK=db_link
REMAP_TABLE=emp_v:employees TABLE_EXISTS_ACTION=append

Have a fun 🙂

Tomasz