[20130727]ORACLE 12C使用expdp导出view数据.txt
12C新特性里面可以定义试图，然后通过view当作表一样导出数据，然后导入数据库，自己做一个测试：
1.建立测试环境:
SQL> @ver
BANNER
--------------------------------------------------------------------------------
Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 - 64bit Production
SQL> create view v_emp_dept as select emp.*,dept.dname from dept,emp where dept.deptno=emp.deptno;
--system用户执行
CREATE OR REPLACE DIRECTORY tmp_expdp AS 'D:\tmp\expdp\';
SQL> GRANT READ, WRITE ON DIRECTORY tmp_expdp TO scott;
Grant succeeded.
SQL> grant EXP_FULL_DATABASE to scott;
Grant succeeded.
SQL> grant IMP_FULL_DATABASE to scott;
Grant succeeded.
2.expdp导出视图信息以及数据：
d:\tmp>expdp scott/tiger@test01p views_as_tables=v_emp_dept directory=tmp_expdp dumpfile=emp_dept.dmp
Export: Release 12.1.0.1.0 - Production on Sun Jul 28 21:50:26 2013
Copyright (c) 1982, 2013, Oracle and/or its affiliates. All rights reserved.
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 "SCOTT"."SYS_EXPORT_TABLE_01": scott/********@test01p views_as_tables=v_emp_dept directory=tmp_expdp dumpfile=emp_dept.dmp */
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"."V_EMP_DEPT" 9.296 KB 14 rows
Master table "SCOTT"."SYS_EXPORT_TABLE_01" successfully loaded/unloaded
******************************************************************************
Dump file set for SCOTT.SYS_EXPORT_TABLE_01 is:
D:\TMP\EXPDP\EMP_DEPT.DMP
Job "SCOTT"."SYS_EXPORT_TABLE_01" successfully completed at Sun Jul 28 21:50:41 2013 elapsed 0 00:00:13
3.impdp导入:
d:\tmp>impdp scott/tiger@test01p directory=tmp_expdp dumpfile=emp_dept.dmp
Import: Release 12.1.0.1.0 - Production on Sun Jul 28 21:54:57 2013
Copyright (c) 1982, 2013, Oracle and/or its affiliates. All rights reserved.
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 "SCOTT"."SYS_IMPORT_FULL_01" successfully loaded/unloaded
Starting "SCOTT"."SYS_IMPORT_FULL_01": scott/********@test01p directory=tmp_expdp dumpfile=emp_dept.dmp */
Processing object type TABLE_EXPORT/VIEWS_AS_TABLES/TABLE
ORA-39325: TABLE_EXISTS_ACTION cannot be applied to "SCOTT"."V_EMP_DEPT".
Processing object type TABLE_EXPORT/VIEWS_AS_TABLES/TABLE_DATA
Job "SCOTT"."SYS_IMPORT_FULL_01" completed with 1 error(s) at Sun Jul 28 21:55:07 2013 elapsed 0 00:00:07
--视图存在，无法导入，改名视图，或者使用remap_table参数。选择修改视图看看。
SQL> rename V_EMP_DEPT to V_EMP_DEPT1;
Table renamed.
d:\tmp>impdp scott/tiger@test01p directory=tmp_expdp dumpfile=emp_dept.dmp
Import: Release 12.1.0.1.0 - Production on Sun Jul 28 22:00:19 2013
Copyright (c) 1982, 2013, Oracle and/or its affiliates. All rights reserved.
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 "SCOTT"."SYS_IMPORT_FULL_01" successfully loaded/unloaded
Starting "SCOTT"."SYS_IMPORT_FULL_01": scott/********@test01p directory=tmp_expdp dumpfile=emp_dept.dmp */
Processing object type TABLE_EXPORT/VIEWS_AS_TABLES/TABLE
Processing object type TABLE_EXPORT/VIEWS_AS_TABLES/TABLE_DATA
. . imported "SCOTT"."V_EMP_DEPT" 9.296 KB 14 rows
Job "SCOTT"."SYS_IMPORT_FULL_01" successfully completed at Sun Jul 28 22:00:24 2013 elapsed 0 00:00:03
SQL> column object_name format a20
SQL> select object_id,data_object_id,object_name,object_type from dba_objects where wner=user and object_name in ('V_EMP_DEPT','V_EMP_DEPT1');
OBJECT_ID DATA_OBJECT_ID OBJECT_NAME OBJECT_TYPE
---------- -------------- -------------------- -----------------------
92488 V_EMP_DEPT1 VIEW
92776 92776 V_EMP_DEPT TABLE
--可以发现导入后，建立了新表v_emp_dept.