Subscriber Count

    471

Subscribe to Posts by Email

Pages

EXPDP Import View as Table

Starting 12c, we can export a view and import it as table instead of view using data pump.
SQL> create view emp_dept as select empno, ename, dept.deptno , dname from   emp, dept  where  emp.deptno=dept.deptno;
View created.

expdp directory=DBA_DATAPUMP dumpfile=test.dmp logfile=test.log VIEWS_AS_TABLES=emp_dept_vw

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_VW"                        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:
/u01/app/oracle/product/12.1.0.1/test.dmp
Job "SCOTT"."SYS_EXPORT_TABLE_01" successfully completed

Import the dump file.

impdp directory=DBA_DATAPUMP dumpfile=test.dmp logfile=test.log VIEWS_AS_TABLES=emp_dept_vw
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

SQL> select object_name,object_type from dba_objects where object_name='EMP_DEPT_VW';

OBJECT_NAME                    OBJECT_TYPE
------------------------------ -----------------------
EMP_DEPT_VW                    TABLE

-Thanks

Suresh

Leave a Reply

You can use these HTML tags

<a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong>