关于DataPump的external_table模式

在pre10g的很长时间内，Oracle仅提供exp/imp导入导出工具，虽然这2个实用程序十分有效(现在也是如此)，但因为它们受限于client/server模式工具自身的限制，以普通用户程序的身份来运转数以TB计的数据，其才不堪大用！DataPump是10g以后主推的数据抽取/导入工具，不同于exp/imp工具，DataPump是一个服务器端的实用程序，因为运行在服务器上故而DataPump进程可以直接访问数据文件与SGA(无需借shadow进程之手)，与exp/imp工具相比使用DataPump可以获得显著的性能改善。DataPump可以通过直接路径或外部表路径这两种方法导出数据；其中直接路径避开了数据库高速缓存。当使用直接路径模式抽取数据时，DataPump从磁盘直接读取数据文件，抽取和格式化文件内容，最后将内容写出到转储文件。该种模式和SGA交互等待少，其导入导出速度直接取决于数据库所在磁盘速度和cpu;因此，直接路径极为快速。外部表路径模式将使用到数据库的高速缓存buffer cache，通过外部表路径方法导出数据时，DataPump使用普通的SELECT操作将数据块从数据文件中读入buffer cache，为了写出转储文件，数据会在缓存中被格式化。通过外部表路径导入数据时，DataPump根据转储文件的内容构造标准的插入语句，并且通过将数据块从数据文件读至缓存来执行这些语句，插入操作按照标准的样式在缓存中完成；如同任何普通DML操作一样，外部表路径也会同时产生撤销和重做。 DataPump自身会根据对象的复杂性作出使用直接路径还是外部表路径的选择；对于较复杂的对象(后文将列出)而言，为了分解复杂性而必须同SGA进行交互，此情况下Data Pump只能采用外部表模式。我们还可以通过使用access_method参数来控制其行为，当然这仅在我们确认Data Pump作出了错误选择时才有必要。

若满足右列条件EXPDP将采用direct_path即直接路径模式

表结构允许使用直接路径模式,举例而言：表上没有启用针对SELECT操作的fine-grained access control 非队列表(queue table) 表上没有BFILE和opaque类型的列，或包含有opaque列的对象类型表上没有加密列表上没有被废弃的旧类型列若表上存在LONG或LONG RAW类型列，则此列只能是最后一列使用Expdp执行导出任务时没有为相关表指定QUERY, SAMPLE, or REMAP_DATA等参数需要导出的表或分区相对较少(多达250M)，亦或者表或分区其实很大，但导出任务无法工作在并行模式(未指定parallel参数，或parallel参数设置为1)

若满足右列条件EXPDP将采用external_table即外部表模式

数据结构不满足在直接路径模式下抽取的条件，举例而言: 表上启用了针对SELECT操作的精细粒度控制队列表表上包含了BFILE或opaque类型列，或者包含有opaque列的对象类型表上存在加密列表上存在被废弃的旧类型列表上存在LONG或LONG RAW类型列，且不是最后列数据结构满足使用直接路径模式的条件，但执行导出任务时相关表上指定了QUERY, SAMPLE, or REMAP_DATA等参数数据结构满足使用直接路径模式的条件，但相关的表或分区相对较大(大于250M)，此时并行SQL可以用来加速数据抽取

若满足右列条件IMPDP将采用direct_path即直接路径模式

数据结构满足使用直接路径模式的条件，举例而言: 当导入某单一表分区时该分区表上没有建立全局索引，这一点也包括分区的对象表没有基于LOB列建立的域索引(domain index) 非cluster表表上没有BFILE列或opaque类型列表上没有嵌入了opaque类型的VARRAY列表上没有加密列没有启用补全日志(Supplemental logging)且表上没有LOB类型列若导入表已预先建立了表建构，则需满足以下条件: 表上没有激活的触发器并且若是分区表则应有索引并且表上上没有启用针对INSERT操作的精细粒度控制并且表上除去check类型约束不存在其他类型约束并且表上没有unique的索引执行导入任务时没有为相关表指定QUERY, REMAP_DATA等参数需要导入的表或分区相对较小(少于250M)，或者表或分区其实很大，但无法以并行模式导入(未指定parallel参数或指定其为1)

若满足右列条件IMPDP将采用external_table即外部表路径

当数据结构不满足在直接路径模式下导入的条件，举例而言: 当导入某单一表分区时该分区表上建有全局索引，这一点也包括分区的对象表表上有基于LOB列建立的域索引(domain index) cluster表表上有BFILE列或opaque类型列表上有嵌入了opaque类型的VARRAY列表上有加密列启用了补全日志且表上有至少一个LOB列若导入表已预先建立了表建构，且满足以下条件: 表上有激活的触发器或者是分区表且没有任何索引或者表上启用了针对INSERT操作的精细粒度控制或者表上除去check类型约束还还有其他类型约束或者表上有unique的索引执行导入任务时有为相关表指定QUERY, REMAP_DATA等参数数据结构满足使用直接路径模式的条件，但相关的表或分区相对较大(大于250M)，此时并行SQL可以用来加速数据导入

我们有必要深入了解一下外部表路径究竟是如何工作的:

<code>SQL> </code><code>select</code> <code>* </code><code>from</code> <code>v$version;</code>

<code>BANNER</code>

<code>Oracle </code><code>Database</code> <code>10g Enterprise Edition Release 10.2.0.4.0 - 64bi</code>

<code>PL/SQL Release 10.2.0.4.0 - Production</code>

<code>CORE 10.2.0.4.0 Production</code>

<code>TNS </code><code>for</code> <code>Linux: Version 10.2.0.4.0 - Production</code>

<code>NLSRTL Version 10.2.0.4.0 - Production</code>

<code>SQL> </code><code>exec</code> <code>dbms_workload_repository.create_snapshot;</code>

<code>PL/SQL </code><code>procedure</code> <code>successfully completed.</code>

<code>/* 执行expdp导出任务，TCACHE表大小为465M，这里显示指定了外部表路径 */</code>

<code>[maclean@rh2 ~]$ expdp maclean/maclean directory=dumps tables=TCACHE dumpfile=tcache1.dmp access_method=external_table</code>

<code>Export: Release 10.2.0.4.0 - 64bit Production </code><code>on</code> <code>Tuesday, 24 August, 2010 20:31:48</code>

<code>Connected </code><code>to</code><code>: Oracle </code><code>Database</code> <code>10g Enterprise Edition Release 10.2.0.4.0 - 64bit Production</code>

<code>With</code> <code>the Partitioning, OLAP, Data Mining </code><code>and</code> <code>Real</code> <code>Application Testing options</code>

<code>Starting </code><code>"MACLEAN"</code><code>.</code><code>"SYS_EXPORT_TABLE_07"</code><code>: maclean/******** directory=dumps tables=TCACHE dumpfile=tcache1.dmp access_method=external_table</code>

<code>Estimate </code><code>in</code> <code>progress using BLOCKS method...</code>

<code>Processing object type TABLE_EXPORT/</code><code>TABLE</code><code>/TABLE_DATA</code>

<code>Total estimation using BLOCKS method: 544 MB</code>

<code>Processing object type TABLE_EXPORT/</code><code>TABLE</code><code>/</code><code>TABLE</code>

<code>Processing object type TABLE_EXPORT/</code><code>TABLE</code><code>/</code><code>STATISTICS</code><code>/TABLE_STATISTICS</code>

<code>. . exported </code><code>"MACLEAN"</code><code>.</code><code>"TCACHE"</code> <code>465.6 MB 4999999 </code><code>rows</code>

<code>Master </code><code>table</code> <code>"MACLEAN"</code><code>.</code><code>"SYS_EXPORT_TABLE_07"</code> <code>successfully loaded/unloaded</code>

<code>Dump file </code><code>set</code> <code>for</code> <code>MACLEAN.SYS_EXPORT_TABLE_07 </code><code>is</code><code>:</code>

<code> </code><code>/s01/dump/tcache1.dmp</code>

<code>Job </code><code>"MACLEAN"</code><code>.</code><code>"SYS_EXPORT_TABLE_07"</code> <code>successfully completed </code><code>at</code> <code>20:32:18</code>

<code>/* 这次我们指定了并行度为4 */</code>

<code>[maclean@rh2 ~]$ expdp maclean/maclean directory=dumps tables=TCACHE dumpfile=tcache2.dmp,tcache3.dmp,tcache4.dmp,tcache5.dmp access_method=external_table parallel=4</code>

<code>Export: Release 10.2.0.4.0 - 64bit Production </code><code>on</code> <code>Tuesday, 24 August, 2010 20:32:49</code>

<code> </code><code>/s01/dump/tcache2.dmp</code>

<code> </code><code>/s01/dump/tcache3.dmp</code>

<code> </code><code>/s01/dump/tcache4.dmp</code>

<code>Job </code><code>"MACLEAN"</code><code>.</code><code>"SYS_EXPORT_TABLE_07"</code> <code>successfully completed </code><code>at</code> <code>20:33:04</code>

<code>SQL> @?/rdbms/admin/awrrpt</code>

<code>接着我们分析该时段内的awr报告!</code>

<code>从AWR报告中，我们不难找到以下语句:</code>

<code>SQL ID 3qwsywpvtdyjp:</code>

<code>CREATE</code> <code>TABLE</code> <code>"ET$088200010001"</code> <code>( </code><code>"OWNER"</code><code>, </code><code>"OBJECT_NAME"</code><code>, </code><code>"SUBOBJECT_NAME"</code><code>, </code><code>"OBJECT_ID"</code><code>, </code><code>"DATA_OBJECT_ID"</code><code>, </code><code>"OBJECT_TYPE"</code><code>, </code><code>"CREATED"</code><code>, </code><code>"LAST_DDL_TIME"</code><code>, </code><code>"TIMESTAMP"</code><code>, </code><code>"STATUS"</code><code>, </code><code>"TEMPORARY"</code><code>, </code><code>"GENERATED"</code><code>, </code><code>"SECONDARY"</code> <code>) ORGANIZATION EXTERNAL ( TYPE ORACLE_DATAPUMP </code><code>DEFAULT</code> <code>DIRECTORY DUMPS ACCESS PARAMETERS (DEBUG =0 DATAPUMP INTERNAL </code><code>TABLE</code> <code>"MACLEAN"</code><code>.</code><code>"TCACHE"</code> <code>JOB ( </code><code>"MACLEAN"</code><code>, </code><code>"SYS_EXPORT_TABLE_07"</code><code>, 1) WORKERID 1 PARALLEL 1 VERSION COMPATIBLE ENCRYPTPASSWORDISNULL ) LOCATION (</code><code>'bogus.dat'</code><code>) ) PARALLEL 1 REJECT LIMIT UNLIMITED </code><code>AS</code> <code>SELECT</code> <code>/*+ PARALLEL(KU$, 1) */ </code><code>"OWNER"</code><code>, </code><code>"OBJECT_NAME"</code><code>, </code><code>"SUBOBJECT_NAME"</code><code>, </code><code>"OBJECT_ID"</code><code>, </code><code>"DATA_OBJECT_ID"</code><code>, </code><code>"OBJECT_TYPE"</code><code>, </code><code>"CREATED"</code><code>, </code><code>"LAST_DDL_TIME"</code><code>, </code><code>"TIMESTAMP"</code><code>, </code><code>"STATUS"</code><code>, </code><code>"TEMPORARY"</code><code>, </code><code>"GENERATED"</code><code>, </code><code>"SECONDARY"</code> <code>FROM</code> <code>RELATIONAL(</code><code>"MACLEAN"</code><code>.</code><code>"TCACHE"</code> <code>) KU$</code>

<code>CREATE</code> <code>TABLE</code> <code>"ET$088000020001"</code> <code>( </code><code>"OWNER"</code><code>, </code><code>"OBJECT_NAME"</code><code>, </code><code>"SUBOBJECT_NAME"</code><code>, </code><code>"OBJECT_ID"</code><code>, </code><code>"DATA_OBJECT_ID"</code><code>, </code><code>"OBJECT_TYPE"</code><code>, </code><code>"CREATED"</code><code>, </code><code>"LAST_DDL_TIME"</code><code>, </code><code>"TIMESTAMP"</code><code>, </code><code>"STATUS"</code><code>, </code><code>"TEMPORARY"</code><code>, </code><code>"GENERATED"</code><code>, </code><code>"SECONDARY"</code> <code>) ORGANIZATION EXTERNAL ( TYPE ORACLE_DATAPUMP </code><code>DEFAULT</code> <code>DIRECTORY DUMPS ACCESS PARAMETERS (DEBUG =0 DATAPUMP INTERNAL </code><code>TABLE</code> <code>"MACLEAN"</code><code>.</code><code>"TCACHE"</code> <code>JOB ( </code><code>"MACLEAN"</code><code>, </code><code>"SYS_EXPORT_TABLE_07"</code><code>, 1) WORKERID 2 PARALLEL 2 VERSION COMPATIBLE ENCRYPTPASSWORDISNULL ) LOCATION (</code><code>'bogus.dat'</code><code>) ) PARALLEL 2 REJECT LIMIT UNLIMITED </code><code>AS</code> <code>SELECT</code> <code>/*+ PARALLEL(KU$, 2) */ </code><code>"OWNER"</code><code>, </code><code>"OBJECT_NAME"</code><code>, </code><code>"SUBOBJECT_NAME"</code><code>, </code><code>"OBJECT_ID"</code><code>, </code><code>"DATA_OBJECT_ID"</code><code>, </code><code>"OBJECT_TYPE"</code><code>, </code><code>"CREATED"</code><code>, </code><code>"LAST_DDL_TIME"</code><code>, </code><code>"TIMESTAMP"</code><code>, </code><code>"STATUS"</code><code>, </code><code>"TEMPORARY"</code><code>, </code><code>"GENERATED"</code><code>, </code><code>"SECONDARY"</code> <code>FROM</code> <code>RELATIONAL(</code><code>"MACLEAN"</code><code>.</code><code>"TCACHE"</code> <code>) KU$</code>

<code>以上就是Expdp在导出数据时使用的创建组织类型为ORACLE_DATAPUMP的外部表的SQL语句；后者语句中指定的并行度为2，而非我所指定的4，因该是Oracle考虑到所需导出表的具体大小，设置过高的并行度反而会适得其反，所以对设定值做了修正。</code>

<code>SQL> </code><code>truncate</code> <code>table</code> <code>tcache;</code>

<code>Table</code> <code>truncated.</code>

<code>SQL> </code><code>set</code> <code>pagesize 1400;</code>

<code>SQL> </code><code>set</code> <code>linesize 140;</code>

<code>/* 执行外部表路径的数据导入，并行度1 */</code>

<code>[maclean@rh2 admin]$ impdp maclean/maclean directory=dumps tables=TCACHE content=data_only dumpfile=tcache1.dmp access_method=external_table</code>

<code>/* 在以上SQL执行期间查询V$SQL动态视图的相关语句*/</code>

<code>select</code> <code>sql_text,</code>

<code> </code><code>elapsed_time,</code>

<code> </code><code>disk_reads,</code>

<code> </code><code>buffer_gets,</code>

<code> </code><code>rows_processed</code>

<code> </code><code>where</code> <code>sql_text </code><code>like</code> <code>'%TCACHE%'</code>

<code>SQL_ID CPU_TIME ELAPSED_TIME DISK_READS BUFFER_GETS ROWS_PROCESSED</code>

<code>INSERT</code> <code>/*+ SYS_DL_CURSOR */ </code><code>INTO</code> <code>RELATIONAL(</code><code>"MACLEAN"</code><code>.</code><code>"TCACHE"</code><code>) (</code><code>"OWNER"</code><code>,</code><code>"OBJECT_NAME"</code><code>,</code><code>"SUBOBJECT_NAME"</code><code>,</code><code>"OBJECT_ID"</code><code>,</code><code>"DATA_OBJECT_ID"</code><code>,</code><code>"OBJECT</code>

<code>_TYPE"</code><code>,</code><code>"CREATED"</code><code>,</code><code>"LAST_DDL_TIME"</code><code>,</code><code>"TIMESTAMP"</code><code>,</code><code>"STATUS"</code><code>,</code><code>"TEMPORARY"</code><code>,</code><code>"GENERATED"</code><code>,</code><code>"SECONDARY"</code><code>) </code><code>VALUES</code> <code>(</code><code>NULL</code><code>,</code><code>NULL</code><code>,</code><code>NULL</code><code>,</code><code>NULL</code><code>,</code><code>NULL</code><code>,</code><code>NULL</code><code>,</code><code>NULL</code><code>,</code><code>NULL</code><code>,N</code>

<code>INSERT</code> <code>/*+APPEND+*/ /*+PARALLEL(</code><code>"TCACHE"</code><code>,1)+*/ </code><code>INTO</code> <code>RELATIONAL(</code><code>"MACLEAN"</code><code>.</code><code>"TCACHE"</code> <code>) (</code><code>"OWNER"</code><code>, </code><code>"OBJECT_NAME"</code><code>, </code><code>"SUBOBJECT_NAME"</code><code>, </code><code>"OBJECT_ID"</code><code>,</code>

<code> </code><code>"DATA_OBJECT_ID"</code><code>, </code><code>"OBJECT_TYPE"</code><code>, </code><code>"CREATED"</code><code>, </code><code>"LAST_DDL_TIME"</code><code>, </code><code>"TIMESTAMP"</code><code>, </code><code>"STATUS"</code><code>, </code><code>"TEMPORARY"</code><code>, </code><code>"GENERATED"</code><code>, </code><code>"SECONDARY"</code><code>) </code><code>SELECT</code> <code>"OWNE</code>

<code>R"</code><code>, </code><code>"OBJECT_NAME"</code><code>, </code><code>"SUBOBJECT_NAME"</code><code>, </code><code>"OBJECT_ID"</code><code>, </code><code>"DATA_OBJECT_ID"</code><code>, </code><code>"OBJECT_TYPE"</code><code>, </code><code>"CREATED"</code><code>, </code><code>"LAST_DDL_TIME"</code><code>, </code><code>"TIMESTAMP"</code><code>, </code><code>"STATUS"</code><code>, </code><code>"TEMPO</code>

<code>RARY"</code><code>, </code><code>"GENERATED"</code><code>, </code><code>"SECONDARY"</code> <code>FROM</code> <code>"ET$087B00060001"</code> <code>KU$</code>

<code>/* 当导入job完成后再次查询 */</code>

<code>/* 执行完成后一句SQL立马消失了！？ Oracle好像很不情愿我们看到外部表路径使用的</code><code>INSERT</code><code>语句 :) */</code>

<code>INSERT</code> <code>/*+APPEND+*/ /*+PARALLEL(</code><code>"TCACHE"</code><code>,1)+*/</code>

<code>INTO</code> <code>RELATIONAL</code>

<code> </code><code>(</code><code>"MACLEAN"</code><code>.</code><code>"TCACHE"</code><code>)</code>

<code> </code><code>(</code><code>"OWNER"</code><code>,</code>

<code> </code><code>"OBJECT_NAME"</code><code>,</code>

<code> </code><code>"SUBOBJECT_NAME"</code><code>,</code>

<code> </code><code>"OBJECT_ID"</code><code>,</code>

<code> </code><code>"DATA_OBJECT_ID"</code><code>,</code>

<code> </code><code>"OBJECT_TYPE"</code><code>,</code>

<code> </code><code>"CREATED"</code><code>,</code>

<code> </code><code>"TIMESTAMP"</code><code>,</code>

<code> </code><code>"STATUS"</code><code>,</code>

<code> </code><code>"TEMPORARY"</code><code>,</code>

<code> </code><code>"GENERATED"</code><code>,</code>

<code> </code><code>"SECONDARY"</code><code>)</code>

<code> </code><code>SELECT</code> <code>"OWNER"</code><code>,</code>

<code> </code><code>"OBJECT_NAME"</code><code>,</code>

<code> </code><code>"SUBOBJECT_NAME"</code><code>,</code>

<code> </code><code>"OBJECT_ID"</code><code>,</code>

<code> </code><code>"DATA_OBJECT_ID"</code><code>,</code>

<code> </code><code>"OBJECT_TYPE"</code><code>,</code>

<code> </code><code>"CREATED"</code><code>,</code>

<code> </code><code>"TIMESTAMP"</code><code>,</code>

<code> </code><code>"STATUS"</code><code>,</code>

<code> </code><code>"TEMPORARY"</code><code>,</code>

<code> </code><code>"GENERATED"</code><code>,</code>

<code> </code><code>"SECONDARY"</code>

<code>/* 这里的KU$代表的就是以ORACLE_DATAPUMP转储文件形式存放在数据库外的外部表 */</code>

<code>/* 可以看到这里尝试使用了APPEND插入模式，以减少undo和redo的产生*/</code>

<code>/* 若启用并行导入呢？ */</code>

<code>[maclean@rh2 admin]$ impdp maclean/maclean directory=dumps tables=TCACHE content=data_only dumpfile=tcache2.dmp,tcache3.dmp,tcache4.dmp access_method=external_table parallel=3</code>

<code>RARY"</code><code>, </code><code>"GENERATED"</code><code>, </code><code>"SECONDARY"</code> <code>FROM</code> <code>"ET$087A00040001"</code> <code>KU$</code>

<code>/* 似乎因为沿用了APPEND插入模式，插入语句的并行度并未上升 */</code>

<code>/* 因为Oracle不想同我们分享它的小秘密，所以awr和ash报告中都不记录外部表路径插入SQL的相关信息 */</code>

<code>/* 好吧，它有权保持沉默！ */</code>

<code>That's great!</code>

本文转自maclean_007 51CTO博客，原文链接:http://blog.51cto.com/maclean/1277134

关于DataPump的external_table模式

继续阅读

报错：'mysql' 不是内部或外部命令，也不是可运行的程序或批处理文件。

Linxu常用命令技巧汇总

ERROR 1 (HY000): Can't create/write to file '/tmp/#sql_4188_1.MYI' (Errcode: 28)

艰难安装LDAP,SSL认证

《Linux命令行与Shell脚本编程大全第2版.布卢姆》pdf

MySQL的4种隔离级别？出现问题

XX系统实施过程问题总结

无组件上传图片到数据库中，最完整解决方案

【MySQL数据库】数据库索引事务1.索引2.事务

neo4j之cypher使用文档

NOSQL安全攻击

mybatis_入门程序Mybatis入门

登录plsql 报错 the account is locked --用户被锁

sqlServer根据经纬查距离

SequoiaDB巨杉数据库C++驱动概述

Oracle 批量查询传入List 返回List