VIEWS_AS_TABLES parameter has been introduced in datapump of 12c. With this, we can export a view from a source database and import as a table in a target database.
Create a view:
SQL> create view dba_view( emp_number) as select emp from dbatool;SQL> create view dba_view( emp_number) as select emp from dbatool; View created. SQL> select owner,object_name,object_type from dba_objects where object_name='DBA_VIEW'; OWNER OBJECT_NAM OBJECT_TYPE------------------ ---------- -----------------------SYS DBA_VIEW VIEW
Take export of this view as a table using views_as_tables parameter:
[oracle@localhost ~]$ expdp dumpfile=view.dmp logfile=view.log directory=DUMP views_as_tables=DBA_VIEW Export: Release 12.1.0.2.0 - Production on Sat Aug 22 07:07:54 2015 Copyright (c) 1982, 2014, Oracle and/or its affiliates. All rights reserved. Username: sys/oracle@ORCL as sysdba Connected to: Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options Starting "SYS"."SYS_EXPORT_TABLE_01": sys/********@ORCL AS SYSDBA dumpfile=view.dmp logfile=view.log directory=DUMP views_as_tables=DBA_VIEW Estimate in progress using BLOCKS method... Processing object type TABLE_EXPORT/VIEWS_AS_TABLES/TABLE_DATA Total estimation using BLOCKS method: 64 KB Processing object type TABLE_EXPORT/VIEWS_AS_TABLES/TABLE . . exported "SYS"."DBA_VIEW" 5.117 KB 8 rows Master table "SYS"."SYS_EXPORT_TABLE_01" successfully loaded/unloaded ****************************************************************************** Dump file set for SYS.SYS_EXPORT_TABLE_01 is: /home/oracle/DUMP/view.dmp Job "SYS"."SYS_EXPORT_TABLE_01" successfully completed at Sat Aug 22 07:08:17 2015 elapsed 0 00:00:11
Now drop the view and import the same from the export dump which we have taken.
SQL> drop view dba_view; View dropped. SQL> exit Disconnected from Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options [oracle@localhost ~]$ expdp dumpfile=view.dmp logfile=view.log directory=DUMP view_as_tables=D^C [oracle@localhost ~]$ [oracle@localhost ~]$ impdp dumpfile=view.dmp logfile=view.log directory=DUMP tables=DBA_VIEW Import: Release 12.1.0.2.0 - Production on Sat Aug 22 07:09:09 2015 Copyright (c) 1982, 2014, Oracle and/or its affiliates. All rights reserved. Username: sys/oracle@ORCL as sysdba Connected to: Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options Master table "SYS"."SYS_IMPORT_TABLE_01" successfully loaded/unloaded Starting "SYS"."SYS_IMPORT_TABLE_01": sys/********@ORCL AS SYSDBA dumpfile=view.dmp logfile=view.log directory=DUMP tables=DBA_VIEW Processing object type TABLE_EXPORT/VIEWS_AS_TABLES/TABLE Processing object type TABLE_EXPORT/VIEWS_AS_TABLES/TABLE_DATA . . imported "SYS"."DBA_VIEW" 5.117 KB 8 rows Job "SYS"."SYS_IMPORT_TABLE_01" successfully completed at Sat Aug 22 07:10:21 2015 elapsed 0 00:00:04
Now check the object_type:
[oracle@localhost ~]$ sqlplus sys/oracle@orcl as sysdba[oracle@localhost ~]$ sqlplus sys/oracle@orcl as sysdba SQL*Plus: Release 12.1.0.2.0 Production on Sat Aug 22 07:10:25 2015 Copyright (c) 1982, 2014, Oracle. All rights reserved. Connected to:Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit ProductionWith the Partitioning, OLAP, Advanced Analytics and Real Application Testing options SQL> set pagesize 200 SQL> set lines 200SQL> set long 999SQL> col owner for a9SQL> col object_name for a10SQL> select owner,object_name,object_type from dba_objects where object_name='DBA_VIEW'; OWNER OBJECT_NAM OBJECT_TYPE--------- ---------- -----------------------
We can see DBA_VIEW which we exported as the view has imported as a table.
