12c: Exporting Database Views as Tables

Starting Oracle Database 12c, you can export view to be imported as a table. There is no need to individually export each table, Data Pump will dump a table with the same columns as the view and with row data fetched from the view.
It also exports objects dependent on the view, such as grants and constraints. To use this view,we need to use parameter VIEWS_AS_TABLES.

Let’s see this feature in action using a example.

We have created a view on emp,dept table to show employee details along with manager name

create view emp_view as select emp.EMPNO, emp.ENAME , emp.JOB,mgr.ename MGRNAME, emp.HIREDATE,emp.SAL ,emp.COMM,dept.DNAME DEPTNAME FROM
EMP emp,DEPT dept,EMP mgr
where emp.deptno=dept.deptno 
and mgr.empno(+)=emp.mgr order by 1;

Create a datapump directory to store datapump dumpfile

create directory dpdir as '/home/oracle/datapump';

Now we take datapump export and specify view name in views_as_tables

$ expdp system views_as_tables=scott.emp_view directory=dpdir dumpfile=emp_view.dmp logfile=emp_view_exp.log

Export: Release 12.1.0.1.0 - Production on Mon Jul 22 12:05:26 2013

Copyright (c) 1982, 2013, Oracle and/or its affiliates.  All rights reserved.
Password: 

Connected to: Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options
Starting "SYSTEM"."SYS_EXPORT_TABLE_01":  system/******** views_as_tables=scott.emp_view directory=dpdir dumpfile=emp_view.dmp logfile=emp_view_exp.log 
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 "SCOTT"."EMP_VIEW"                          8.781 KB      14 rows
Master table "SYSTEM"."SYS_EXPORT_TABLE_01" successfully loaded/unloaded
******************************************************************************
Dump file set for SYSTEM.SYS_EXPORT_TABLE_01 is:
  /home/oracle/datapump/emp_view.dmp
Job "SYSTEM"."SYS_EXPORT_TABLE_01" successfully completed at Mon Jul 22 12:05:52 2013 elapsed 0 00:00:20

We can see that this has exported 14 rows. To see if it actually works, we will import it but in a different schema (remap_schema does the trick here)

$impdp system remap_schema=scott:amitbans directory=dpdir dumpfile=emp_view.dmp logfile=emp_view_imp.log

Import: Release 12.1.0.1.0 - Production on Mon Jul 22 12:36:33 2013

Copyright (c) 1982, 2013, Oracle and/or its affiliates.  All rights reserved.
Password: 

Connected to: Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options
Master table "SYSTEM"."SYS_IMPORT_FULL_01" successfully loaded/unloaded
Starting "SYSTEM"."SYS_IMPORT_FULL_01":  system/******** remap_schema=scott:amitbans directory=dpdir dumpfile=emp_view.dmp logfile=emp_view_imp.log 
Processing object type TABLE_EXPORT/VIEWS_AS_TABLES/TABLE
Processing object type TABLE_EXPORT/VIEWS_AS_TABLES/TABLE_DATA
. . imported "AMITBANS"."EMP_VIEW"                       8.781 KB      14 rows
Job "SYSTEM"."SYS_IMPORT_FULL_01" successfully completed at Mon Jul 22 12:36:48 2013 elapsed 0 00:00:09

Let’s verify the data

SQL> show user
USER is "AMITBANS"
SQL> select * from emp_view;

     EMPNO ENAME      JOB	MGRNAME    HIREDATE	    SAL       COMM DEPTNAME
---------- ---------- --------- ---------- --------- ---------- ---------- --------------
      7369 SMITH      CLERK	FORD	   17-DEC-80	    800 	   RESEARCH
      7499 ALLEN      SALESMAN	BLAKE	   20-FEB-81	   1600        300 SALES
      7521 WARD       SALESMAN	BLAKE	   22-FEB-81	   1250        500 SALES
      7566 JONES      MANAGER	KING	   02-APR-81	   2975 	   RESEARCH
      7654 MARTIN     SALESMAN	BLAKE	   28-SEP-81	   1250       1400 SALES
      7698 BLAKE      MANAGER	KING	   01-MAY-81	   2850 	   SALES
      7782 CLARK      MANAGER	KING	   09-JUN-81	   2450 	   ACCOUNTING
      7788 SCOTT      ANALYST	JONES	   19-APR-87	   3000 	   RESEARCH
      7839 KING       PRESIDENT 	   17-NOV-81	   5000 	   ACCOUNTING
      7844 TURNER     SALESMAN	BLAKE	   08-SEP-81	   1500 	 0 SALES
      7876 ADAMS      CLERK	SCOTT	   23-MAY-87	   1100 	   RESEARCH
      7900 JAMES      CLERK	BLAKE	   03-DEC-81	    950 	   SALES
      7902 FORD       ANALYST	JONES	   03-DEC-81	   3000 	   RESEARCH
      7934 MILLER     CLERK	CLARK	   23-JAN-82	   1300 	   ACCOUNTING

14 rows selected.

We can see from dictionary that this is now imported as a table and not view

SQL> select * from tab;

TNAME			       TABTYPE	CLUSTERID
------------------------------ ------- ----------
EMP_VIEW		       TABLE

There are few restrictions for using this feature

-The view must exist and it must be a relational view with only scalar, non-LOB columns.
-VIEWS_AS_TABLES parameter cannot be used with the TRANSPORTABLE=ALWAYS parameter.

Reference

http://docs.oracle.com/cd/E16655_01/server.121/e17639/dp_export.htm#BEHDIADG 

Tags: 12cdatapumpexpdpimpdpNew Features