ITPub博客

首页 > Linux操作系统 > Linux操作系统 > [20130727]ORACLE 12C使用expdp导出view数据.txt

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

原创 Linux操作系统 作者:lfree 时间:2013-07-29 15:37:36 0 删除 编辑
[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.

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

请登录后发表评论 登录
全部评论
熟悉oracle相关技术,擅长sql优化,rman备份与恢复,熟悉linux shell编程。

注册时间:2008-01-03

  • 博文量
    2455
  • 访问量
    6258514