關于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