advertisements
_____________________________________________________________________________________________________________________
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
_____________________________________________________________________________________________________________________
0 comments:
Post a Comment