Oracle导出SQL语句结果集为dmp文件

Oracle导出SQL语句结果集为dmp文件

文章目录

• 前言
• 一、views_as_tables
• 二、测试日志

---

前言

在运维工作中遇到一个应用方提出的需求，需要将SQL语句查询出来的结果集导出为dmp文件。

---

提示：以下是本篇文章正文内容，下面案例可供参考

一、views_as_tables

Oracle呢，从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 allowed

3、视图查询中不支持lob和lang字段类型的导出
4、views_as_tables参数与schemas参数不能同时使用

二、测试日志

[oracle@cxl1dmp]$
[oracle@cxl1 dmp]$ expdp cxl/cxl123456@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

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/cxl123456@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

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]$