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
Recent Comments