Data Pump Export with VIEWS_AS_TABLES:

Now with the help of “VIEWS_AS_TABLES” export parameter we can export multiple views as tables, this smart feature of data pump is invented in Oracle 12c edition.

According to Oracle, VIEWS_AS_TABLES parameter exports data in unencrypted format & creates an unencrypted tables. If the data is sensitive then it is strongly recommended to enable encryption while unloading.

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

schema_name: Name of the user/schema where the views resides. If schema name not specified then it will take export of views of user who performing export operation.
view_name: Name of the views to be exported as tables.
table_name: This is optional & the name of the table which is use as source of the metadata for the exported views. Defaultly, data pump automatically creates a temporary table called: template table with identical view defination ( like column name, data types, etc ). This table automatically deleted after export done.

 

Note:

• In case of database is in read-only mode then default creation of temporary template table will fail. In this scenario you have to specify temporary templete table name.
• If data pump have to export multiple view with multiple template tables name specified, then template tables must all be different. In case of data pump have been found same template tables names then it will skip duplication.

 

Consider following example, in this test_view1 & test_view2 views are to be exported with same template table ( tmp_tbl ), with this scenario test_view2:tmp_tbl will be skip and error message is returned reporting the omitted view.

expdp scott/tiger directory=Demo_dir dumpfile=test_export.dmp views_as_tables=test_view1:tmp_tbl, test_view2:tmp_tbl

 

Restrictions of VIEWS_AS_TABLES:

• We can’t use VIEWS_AS_TABLES parameter with the TRANSPORTABLE=ALWAYS parameter.
• Source table should not contain any hidden columns that were part of the specified view.

Consider following example, this data pump command will export scott.tbl_view as table in test_export.dmp file.:
expdp system/manager directory=Demo_dir dumpfile=test_export.dmp views_as_tables=scott.tbl_view.tmp_tbl
Data Pump Import with VIEWS_AS_TABLES:
We can import above exported views as table in target database.

Syntax:
VIEWS_AS_TABLES = [schema_name.]view_name,….
schema_name: Name of the user/schema where the views resides. If schema name not specified then it will take export of views of user who performing export operation.
view_name: Name of the views to be imported as tables.

 

Consider following example, this data pump command will import scott.tbl_view as table in scott schema:
impdp system/manager directory=Demo_dir dumpfile=test_export.dmp views_as_tables=scott.tbl_view

************************************************************************

Note: Please don’t hesitate to revert in case of any query OR feedback.

Thanking you.

Have a easy life ahead.