利用expdp导出用户视图
环境信息
|
1 2 |
操作系统：CentOS release 6.8 Oracle ： 11.2.0.4 |
在Oracle数据库中，利用expdp导出用户的某类型的对象，如只导出用户的视图view。例如在研发环境中，系统开发到一定程度，需要对开发环境的数据库进行覆盖，但是开发环境有一些生产环境没有的视图，这时候，为了避免重新手工创建的麻烦，就可以利用这个expdp来导出和impdp导入。导出用户某类对象时，用expdp的include参数来指定导出数据。本次演示导出多个视图的。
1、创建一个expdp导出参数文件，这个文件在expdp的parfile参数使用到。创建独立的参数文件好处有2个。
1）在Linux下可以避免转义字符问题，如括号；
2）在Linux下，避免shell的命令字符长度限制；
|
1 2 |
[oracle@dbhost1 ~]$ vi expdp_apptst1_view.par include=view:"in('V_FLIGHT_CREW_LIST_APP2','V_FLIGHT_INFO_DELAY_APP2','V_FLIGHT_INFO_HIS_APP2','V_FLIGHT_INFO_INNER_APP2','V_FLIGHT_INFO_APP2')" |
2、执行导出视图
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 |
[oracle@dbhost1 ~]$ expdp system/oracle SCHEMAS=apptst1 directory=DUMP dumpfile=expdp_apptst1_view_`date +"%Y_%m_%d"`.dmp logfile=expdp_apptst1_view_`date +"%Y_%m_%d"`.log parfile=expdp_apptst1_view.par Export: Release 11.2.0.3.0 - Production on Fri Jun 8 16:52:19 2018 Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved. Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production With the Partitioning, OLAP, Data Mining and Real Application Testing options Starting "SYSTEM"."SYS_EXPORT_SCHEMA_01": system/******** SCHEMAS=apptst1 directory=DUMP dumpfile=expdp_apptst1_view_2018_06_08.dmp logfile=expdp_apptst1_view_2018_06_08.log parfile=expdp_apptst1_view.par Estimate in progress using BLOCKS method... Total estimation using BLOCKS method: 0 KB Processing object type SCHEMA_EXPORT/VIEW/VIEW Master table "SYSTEM"."SYS_EXPORT_SCHEMA_01" successfully loaded/unloaded ****************************************************************************** Dump file set for SYSTEM.SYS_EXPORT_SCHEMA_01 is: /u01/dump_orcldb/expdp_apptst1_view_2018_06_08.dmp Job "SYSTEM"."SYS_EXPORT_SCHEMA_01" successfully completed at 16:52:27 |
看到“successfully completed”表示成功导出。
3、利用impdp可以导出的视图创建语句，这个方法还可以用于查看创建表的原始SQL。
|
1 2 3 4 5 6 7 8 9 10 11 12 |
[oracle@dbhost1 ~]$ impdp system/oracle directory=DUMP dumpfile=expdp_apptst1_view_2018_06_08.dmp NOLOGFILE=y SQLFILE=pss2.0_apptst1_view.sql Import: Release 11.2.0.3.0 - Production on Fri Jun 8 17:00:28 2018 Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved. Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production With the Partitioning, OLAP, Data Mining and Real Application Testing options Master table "SYSTEM"."SYS_SQL_FILE_FULL_01" successfully loaded/unloaded Starting "SYSTEM"."SYS_SQL_FILE_FULL_01": system/******** directory=DUMP dumpfile=expdp_apptst1_view_2018_06_08.dmp NOLOGFILE=y SQLFILE=pss2.0_apptst1_view.sql Processing object type SCHEMA_EXPORT/VIEW/VIEW Job "SYSTEM"."SYS_SQL_FILE_FULL_01" successfully completed at 17:00:30 |
同样，看到“successfully completed”表示成功执行impdp
4、查看impdp的SQLFILE参数指定的导出文件内容，可以看到创建视图的SQL。
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 |
[oracle@dbhost1 ~]$ cd /u01/dump_orcldb [oracle@dbhost1 dump_orcldb]$ ls -lrt 总用量 8017976 -rw-r--r--. 1 oracle oinstall 58838 6月 8 16:36 impdp_apptst1_sche_2018_06_08.log -rw-r--r--. 1 oracle oinstall 1012 6月 8 16:52 expdp_apptst1_view_2018_06_08.log -rw-r-----. 1 oracle oinstall 155648 6月 8 16:52 expdp_apptst1_view_2018_06_08.dmp -rw-r--r--. 1 oracle oinstall 7181 6月 8 17:00 pss2.0_apptst1_view.sql [oracle@dbhost1 dump_orcldb]$ more pss2.0_apptst1_view.sql -- CONNECT SYSTEM ALTER SESSION SET EVENTS '10150 TRACE NAME CONTEXT FOREVER, LEVEL 1'; ALTER SESSION SET EVENTS '10904 TRACE NAME CONTEXT FOREVER, LEVEL 1'; ALTER SESSION SET EVENTS '25475 TRACE NAME CONTEXT FOREVER, LEVEL 1'; ALTER SESSION SET EVENTS '10407 TRACE NAME CONTEXT FOREVER, LEVEL 1'; ALTER SESSION SET EVENTS '10851 TRACE NAME CONTEXT FOREVER, LEVEL 1'; ALTER SESSION SET EVENTS '22830 TRACE NAME CONTEXT FOREVER, LEVEL 192 '; -- new object type path: SCHEMA_EXPORT/VIEW/VIEW -- CONNECT JXFOCTST1 CREATE FORCE VIEW "JXFOCTST1"."V_FLIGHT_CREW_LIST_APP2" ("FLIGHT_DATE", "CREW_LINK_LINE", "P_CODE", "C_NAME", "RANK_NO", "AC_TYPE", "CREW_RANDOM") AS SELECT T3005.FLIGHT_DATE, T3005.CREW_LINK_LINE, T3005.P_CODE, T3017.C_NAME, T3005.RANK_NO, T3001.AC_TYPE, T3001. RANDOM FROM T3001, T3005, T3017 WHERE T3001.FLIGHT_DATE = T3005.FLIGHT_DATE AND T3001.CREW_LINK_LINE = T3005.CREW_LINK_LINE AND T3017.P_CODE = T3005.P_CODE; ………………省略其他输出信息 |
转载请注明：猫头鹰工作室 » Oracle数据库利用expdp导出用户视图view
