Oracle 12c Data Pump: EXPDP IMPDP VIEWS_AS_TABLES Option

Oracle 12c release 1 onwards we can export the view(s) into export dumps and while importing it can be imported as table with the structure of the view with base table data.
Syntax: VIEWS_AS_TABLES=[schema_name.] view_name [:table_name],
Example


==> Creating a view for testing
SQL> create view emp_dept as select empno, ename, 
dept.deptno , dname 
from   emp, dept  where  emp.deptno=dept.deptno;
View created.
SQL> select * from emp_dept;
 
     EMPNO ENAME          DEPTNO DNAME
---------- ---------- ---------- --------------
      7369 SMITH              20 RESEARCH
      7499 ALLEN              30 SALES
      7521 WARD               30 SALES
…..
      …….
14 rows selected.
 
==> Exporting the views with VIEWS_AS_TABLES option
> expdp directory=DBA_DATAPUMP dumpfile=empview.dmp logfile=empview.log VIEWS_AS_TABLES=emp_dept
 
Export: Release 12.1.0.2.0 - Production on Thu Jun 18 01:50:08 2015
 
Copyright (c) 1982, 2014, Oracle and/or its affiliates.  All rights reserved.
 
Username: scott
Password:
 
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 "SCOTT"."SYS_EXPORT_TABLE_01":  scott/******** directory=DBA_DATAPUMP dumpfile=empview.dmp 
logfile=empview.log VIEWS_AS_TABLES=emp_dept
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_DEPT"                        6.757 KB      14 rows
Master table "SCOTT"."SYS_EXPORT_TABLE_01" successfully loaded/unloaded
******************************************************************************
Dump file set for SCOTT.SYS_EXPORT_TABLE_01 is:
  /exp/bkup/empview.dmp
Job "SCOTT"."SYS_EXPORT_TABLE_01" successfully completed at Thu Jun 18 01:50:37 2015 elapsed 0 00:00:15
 
==> Dropping the view and importing the dump to the user schema. It will create a table (not view) with view data.
 
SQL> drop view emp_dept;
 
View dropped.
 
impdp directory=DBA_DATAPUMP dumpfile=empview.dmp logfile=imp_empview.log VIEWS_AS_TABLES=emp_dept
Import: Release 12.1.0.2.0 - Production on Thu Jun 18 01:56:43 2015
 
Copyright (c) 1982, 2014, Oracle and/or its affiliates.  All rights reserved.
;;;
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 "SCOTT"."SYS_IMPORT_FULL_01" successfully loaded/unloaded
Starting "SCOTT"."SYS_IMPORT_FULL_01":  scott/******** directory=DBA_DATAPUMP dumpfile=empview.dmp 
logfile=imp_empview.log
Processing object type TABLE_EXPORT/VIEWS_AS_TABLES/TABLE
Processing object type TABLE_EXPORT/VIEWS_AS_TABLES/TABLE_DATA
. . imported "SCOTT"."EMP_DEPT"                        6.757 KB      14 rows
Job "SCOTT"."SYS_IMPORT_FULL_01" successfully completed at Thu Jun 18 01:56:56 2015 elapsed 0 00:00:06
 
 
OBJECT_NAME                    OBJECT_TYPE
------------------------------ -----------------------
EMP_DEPT                       TABLE
SQL> desc EMP_DEPT
 Name                                      Null?    Type
 ----------------------------------------- -------- -------------------
 EMPNO                                              NUMBER(4)
 ENAME                                              VARCHAR2(10)
 DEPTNO                                             NUMBER(2)
 DNAME                                              VARCHAR2(14)
SQL> select count(*) from EMP_DEPT;
  COUNT(*)
----------
        14
==>While importing the name of the view can be changed using remap_table option.
SQL> create view emp_dept as select empno, ename, dept.deptno , dname
  from   emp, dept
  where  emp.deptno=dept.deptno;
View created.
impdp directory=DBA_DATAPUMP dumpfile=empview.dmp logfile=imp_empview.log VIEWS_AS_TABLES=emp_dept 
remap_table=EMP_DEPT:emp_dept_new
 
Import: Release 12.1.0.2.0 - Production on Thu Jun 18 02:17:07 2015
 
Copyright (c) 1982, 2014, Oracle and/or its affiliates.  All rights reserved.
 
Username: scott
Password:
 
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 "SCOTT"."SYS_IMPORT_TABLE_01" successfully loaded/unloaded
Starting "SCOTT"."SYS_IMPORT_TABLE_01":  scott/******** directory=DBA_DATAPUMP dumpfile=empview.dmp 
logfile=imp_empview.log VIEWS_AS_TABLES=emp_dept remap_table=EMP_DEPT:emp_dept_new
Processing object type TABLE_EXPORT/VIEWS_AS_TABLES/TABLE
Processing object type TABLE_EXPORT/VIEWS_AS_TABLES/TABLE_DATA
. . imported "SCOTT"."EMP_DEPT_NEW"                    6.757 KB      14 rows

Job "SCOTT"."SYS_IMPORT_TABLE_01" successfully completed at Thu Jun 18 02:17:26 2015 elapsed 0 00:00:06
DBA Tips Data Pump Reference

0 comments:

Post a Comment

 

dba topics. Copyright 2011-16 All Rights Reserved | Site Map | Contact | Disclaimer | Google