开发者社区> lfreeali> 正文

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

简介: [20130727]ORACLE 12C使用expdp导出view数据.txt12C新特性里面可以定义试图,然后通过view当作表一样导出数据,然后导入数据库,自己做一个测试:1.
+关注继续查看
[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.

版权声明:本文内容由阿里云实名注册用户自发贡献,版权归原作者所有,阿里云开发者社区不拥有其著作权,亦不承担相应法律责任。具体规则请查看《阿里云开发者社区用户服务协议》和《阿里云开发者社区知识产权保护指引》。如果您发现本社区中有涉嫌抄袭的内容,填写侵权投诉表单进行举报,一经查实,本社区将立刻删除涉嫌侵权内容。

相关文章
MobaXterm 设置在使用export DISPLAY=xx.xx.xx.xx:0.0后调用图形化界面不弹出提示方法
MobaXterm 设置在使用export DISPLAY=xx.xx.xx.xx:0.0后调用图形化界面不弹出提示方法
15 0
Excel中添加并使用宏实现批量更新数据
一、状况描述    当我们需要后台更新大量数据的时候,可以使用该功能。二、解決方案    (1)新建一个Excel文件,并另存为启用宏的Excel工作簿,扩展名为.xlsm。    (2)在Excel中添加按钮。
1152 0
oracle 数据回滚,恢复误删的数据,闪回表功能的使用
oracle 数据回滚,恢复误删的数据,闪回表功能的使用
32 0
ExpandableListView的使用
ExpandableListView的使用 效果图 布局 初始化 ExpandableListView expandableListView = (ExpandableListView) findViewById(R.
743 0
Oracle 11g R2版本使用exp命令导出表不全的解决方案
建议使用 expdp和 impdp 替代  exp和imp 命令!
649 0
+关注
lfreeali
熟悉oracle数据库性能优化,rman备份,数据库恢复技术.
2122
文章
0
问答
文章排行榜
最热
最新
相关电子书
更多
OceanBase 入门到实战教程
立即下载
阿里云图数据库GDB,加速开启“图智”未来.ppt
立即下载
实时数仓Hologres技术实战一本通2.0版(下)
立即下载