oracle expdp数据到txt,[20130727]ORACLE 12C使用expdp导出view数据.txt

[20130727]ORACLE 12C使用expdp导出view数据.txt

12C新特性里面可以定义试图，然后通过view当作表一样导出数据，然后导入数据库，自己做一个测试：

1.建立测试环境:

SQL> @verBANNER--------------------------------------------------------------------------------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 a20SQL> 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          VIEW92776          92776 V_EMP_DEPT           TABLE

--可以发现导入后，建立了新表v_emp_dept.

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/267265/viewspace-767376/，如需转载，请注明出处，否则将追究法律责任。