编者按
云和恩墨大讲堂社群(本文底部有入群方式)里有人提出一个需求:一张表数据量很大,如何只导出其中一部分列?云和恩墨CTO、Oracle ACE总监、ACOUG核心专家杨廷琨老师使用了数据泵的方式,细致入微地解释了过程并给出具体的代码实现。数据和云(ID:OraNews)独家发布,以飨读者。
以下为正文
无论是老版本exp还是数据泵expdp,Oracle都提供了QUERY的功能,这使得查询表中部分记录的功能可以实现,但是QUERY只能过滤行,而不能过滤列,Oracle数据泵会读取表中全部列的。
在12c中,Oracle为数据泵提供了VIEW功能,使得导出的时候可以根据视图的定义来导出表中的数据:
SQL>select banner from v$version; BANNER-------------------------------------------------------------------------------- OracleDatabase 18c Enterprise Edition Release 18.0.0.0.0 - Production SQL>CREATE TABLE T_TABLES AS SELECT * FROM ALL_TABLES;
(左右滑动查看代码部分,下同)
表已创建。
SQL>create view v_tables as select owner, table_name, tablespace_name from t_tableswhere owner not in ('SYS');
视图已创建。
利用数据泵的VIEWS_AS_TABLES参数可以直接导出视图对应的表数据:
C:\Users\yangt>expdpc##u1 directory=d_output dumpfile=t_tab_view views_as_tables=v_tables
Export:Release 18.0.0.0.0 - Production on 星期二 2月 26 13:37:312019
Version18.3.0.0.0
Copyright(c) 1982, 2018, Oracle and/or its affiliates. All rights reserved.
口令:
连接到: Oracle Database 18c Enterprise Edition Release 18.0.0.0.0 -Production
警告: 连接到容器数据库的根或种子时通常不需要 Oracle Data Pump 操作。
启动 "C##U1"."SYS_EXPORT_TABLE_01": c##u1/******** directory=d_outputdumpfile=t_tab_view views_as_tables=v_tables 处理对象类型 TABLE_EXPORT/VIEWS_AS_TABLES/TABLE_DATA 处理对象类型 TABLE_EXPORT/VIEWS_AS_TABLES/TABLE . . 导出了"C##U1"."V_TABLES" 8.289 KB 71 行 已成功加载/卸载了主表"C##U1"."SYS_EXPORT_TABLE_01" ****************************************************************************** C##U1.SYS_EXPORT_TABLE_01的转储文件集为: D:\TEMP\T_TAB_VIEW.DMP 作业 "C##U1"."SYS_EXPORT_TABLE_01" 已于 星期二 2月 26 13:37:40 2019 elapsed 0 00:00:06 成功完成
虽然是通过视图导出的,但是数据泵把它当作一个表来处理,因此这个dump文件是可以直接导入到数据库中变成表的:
SQL>drop view v_tables;
视图已删除。
完成导入操作:
C:\Users\yangt>impdpc##u1 directory=d_output dumpfile=t_tab_view full=y Import:Release 18.0.0.0.0 - Production on 星期二 2月 26 14:33:022019 Version18.3.0.0.0 Copyright(c) 1982, 2018, Oracle and/or its affiliates. All rights reserved. 口令: 连接到: Oracle Database 18c Enterprise Edition Release 18.0.0.0.0 -Production
警告: 连接到容器数据库的根或种子时通常不需要 Oracle Data Pump 操作。
已成功加载/卸载了主表"C##U1"."SYS_IMPORT_FULL_01" 启动 "C##U1"."SYS_IMPORT_FULL_01": c##u1/******** directory=d_outputdumpfile=t_tab_view full=y 处理对象类型 TABLE_EXPORT/VIEWS_AS_TABLES/TABLE处理对象类型 TABLE_EXPORT/VIEWS_AS_TABLES/TABLE_DATA . . 导入了"C##U1"."V_TABLES" 8.289 KB 71 行 作业 "C##U1"."SYS_IMPORT_FULL_01" 已于 星期二 2月 26 14:33:07 2019 elapsed 0 00:00:02 成功完成
查询表数据:
SQL>select count(*) from v_tables; COUNT(*) ---------- 71 SQL>select table_name, tablespace_name from user_tables where table_name ='V_TABLES'; TABLE_NAME TABLESPACE_NAME------------------------------------------------------------ V_TABLES USERS
这个功能是12c的特新特,如果是12c之前的版本,可以考虑使用ORACLE_DATAPUMP类型的外部表来实现:
SQL>create table t_external_tables (owner, table_name, tablespace_name)organization external 2 (type oracle_datapump 3 default directory d_output 4 location ('external_table.dp')) 5 asselect owner, table_name, tablespace_name from t_tables where owner not in('SYS');
表已创建。
通过将外部表导出的 external_table.dp 文件拷贝到目标环境对应的目录下,根据上面的定义重建外部表,注意目标环境创建过程由于已经拷贝了导出的数据,因此语法截至到 location 语句就结束了,不要再包含 as select 部分。
SQL>create table t_target_ext_tab (owner varchar2(30), table_name varchar2(128),tablespace_name varchar2(30)) 2 organization external 3 (type oracle_datapump 4 default directory d_output 5 location ('external_table.dp'));
表已创建。
SQL>select count(*) from t_target_ext_tab; COUNT(*)---------- 71 SQL>col table_name for a30SQL>select * from t_target_ext_tab where owner = 'SYSTEM'; OWNER TABLE_NAME TABLESPACE_NAME ------------------------------------------------------------ ------------------------------ SYSTEM OL$SYSTEM OL$HINTS SYSTEM OL$NODES SYSTEM HELP SYSTEM
所以对于11g和10g的版本,也可以不通过新建表,然后拷贝数据的方式来实现数据的导出和转移。但是如果是9i及以前的版本,那么要不然通过建表然后exp导出的方式,要不然自己写程序实现数据的导出吧。
免责声明:本站发布的内容(图片、视频和文字)以原创、转载和分享为主,文章观点不代表本网站立场,如果涉及侵权请联系站长邮箱:is@yisu.com进行举报,并提供相关证据,一经查实,将立刻删除涉嫌侵权内容。