Oracle导出SQL语句结果集为dmp文件 |
您所在的位置：网站首页 › plsql工具导出dmp文件 › Oracle导出SQL语句结果集为dmp文件 |
|
文章目录
前言一、views_as_tables二、测试日志
前言
在运维工作中遇到一个应用方提出的需求，需要将SQL语句查询出来的结果集导出为dmp文件。 提示：以下是本篇文章正文内容，下面案例可供参考 一、views_as_tablesOracle呢，从12C版本以后可通过views_as_tables的参数来实现我们的expdp导出视图的结果集，方法呢也很简单，也有一定的格式要求如下： 1、导出多个视图格式：views_as_tables=v_cxl,v_wnn （注意：不加括号）否则会出现：-bash: syntax error near unexpected token `(’ 提示 2、视图中不可以包括0长度的列，像null和’'等列 ORA-39126: Worker unexpected fatal error in KUPW$WORKER.CONFIGURE_METADATA_UNLOAD [] ORA-01723: zero-length columns are not allowed3、视图查询中不支持lob和lang字段类型的导出 4、views_as_tables参数与schemas参数不能同时使用 二、测试日志 [oracle@cxl1dmp]$ [oracle@cxl1 dmp]$ expdp cxl/[email protected]:1521/cxldb views_as_tables=view_mc_mzzd,view_mc_mzmx,view_lsjd_mzzd,view_lsjd_mzmx directory=DMP dumpfile=view_mz1020.dmp logfile=view_mz1020.log cluster=N Export: Release 12.2.0.1.0 - Production on Wed Oct 20 20:33:02 2021 Copyright (c) 1982, 2017, Oracle and/or its affiliates. All rights reserved. Connected to: Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production Starting "CXL"."SYS_EXPORT_TABLE_03": cxl/********@192.168.221.138:1521/cxldb views_as_tables=view_mc_mzzd,view_mc_mzmx,view_lsjd_mzzd,view_lsjd_mzmx directory=DMP dumpfile=view_mz1020.dmp logfile=view_mz1020.log cluster=N Processing object type TABLE_EXPORT/VIEWS_AS_TABLES/TABLE_DATA Processing object type TABLE_EXPORT/VIEWS_AS_TABLES/TABLE . . exported "CXL"."VIEW_LSJD_MZMX" 939.2 MB 3796658 rows . . exported "CXL"."VIEW_LSJD_MZZD" 114.9 MB 453903 rows . . exported "CXL"."VIEW_MC_MZMX" 230.0 MB 920005 rows . . exported "CXL"."VIEW_MC_MZZD" 38.28 MB 144835 rows Master table "CXL"."SYS_EXPORT_TABLE_03" successfully loaded/unloaded ****************************************************************************** Dump file set for CXL.SYS_EXPORT_TABLE_03 is: /backup/dmp/view_mz1020.dmp Job "CXL"."SYS_EXPORT_TABLE_03" successfully completed at Wed Oct 20 20:36:51 2021 elapsed 0 00:03:48 [oracle@cxl1 dmp]$ expdp cxl/[email protected]:1521/cxldb views_as_tables=view_mc_zyzd,view_mc_zymx,view_lsjd_zyzd,view_lsjd_zymx directory=DMP dumpfile=view_zy1020.dmp logfile=view_zy1021.log cluster=N Export: Release 12.2.0.1.0 - Production on Wed Oct 20 20:37:08 2021 Copyright (c) 1982, 2017, Oracle and/or its affiliates. All rights reserved. Connected to: Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production Starting "CXL"."SYS_EXPORT_TABLE_03": cxl/********@192.168.221.138:1521/cxldb views_as_tables=view_mc_zyzd,view_mc_zymx,view_lsjd_zyzd,view_lsjd_zymx directory=DMP dumpfile=view_zy1020.dmp logfile=view_zy1021.log cluster=N Processing object type TABLE_EXPORT/VIEWS_AS_TABLES/TABLE_DATA Processing object type TABLE_EXPORT/VIEWS_AS_TABLES/TABLE . . exported "CXL"."VIEW_LSJD_ZYMX" 0 KB 0 rows . . exported "CXL"."VIEW_LSJD_ZYZD" 0 KB 0 rows . . exported "CXL"."VIEW_MC_ZYMX" 0 KB 0 rows . . exported "CXL"."VIEW_MC_ZYZD" 0 KB 0 rows Master table "CXL"."SYS_EXPORT_TABLE_03" successfully loaded/unloaded ****************************************************************************** Dump file set for CXL.SYS_EXPORT_TABLE_03 is: /backup/dmp/view_zy1020.dmp Job "CXL"."SYS_EXPORT_TABLE_03" successfully completed at Wed Oct 20 20:37:23 2021 elapsed 0 00:00:14 [oracle@cxl1 dmp]$ [oracle@cxl1 dmp]$ ls -lt|head total 272954268 -rw-r--r-- 1 oracle asmadmin 1234 Oct 20 20:37 view_zy1021.log -rw-r----- 1 oracle asmadmin 548864 Oct 20 20:37 view_zy1020.dmp -rw-r--r-- 1 oracle asmadmin 1234 Oct 20 20:36 view_mz1020.log -rw-r----- 1 oracle asmadmin 1387311104 Oct 20 20:36 view_mz1020.dmp [oracle@cxl1 dmp]$ |
今日新闻 |
推荐新闻 |
| CopyRight 2018-2019 办公设备维修网 版权所有 豫ICP备15022753号-3 |