{"id":582,"date":"2013-01-14T00:46:58","date_gmt":"2013-01-13T16:46:58","guid":{"rendered":"http:\/\/www.yallonking.com\/?p=582"},"modified":"2013-01-23T00:01:22","modified_gmt":"2013-01-22T16:01:22","slug":"data-pump%e5%9f%ba%e6%9c%ac%e6%9c%af%e8%af%ad%e5%92%8c%e6%a6%82%e5%bf%b5","status":"publish","type":"post","link":"http:\/\/www.yallonking.com\/?p=582","title":{"rendered":"Data Pump\u57fa\u672c\u672f\u8bed\u548c\u6982\u5ff5"},"content":{"rendered":"


\nData Pump
\n1.\u7b80\u4ecb
\n\t\u6570\u636e\u6cf5\u53ef\u4ee5\u5feb\u901f\u628a\u6570\u636e\u548c\u6570\u636e\u5e93\u5143\u6570\u636e\u4ece\u4e00\u4e2a\u6570\u636e\u5e93\u8fc1\u79fb\u5230\u53e6\u4e00\u4e2a\u6570\u636e\u5e93\uff0c\u6570\u636e\u6cf5\u4eceoracle10.1\u7248\u672c\u5f00\u59cb\u51fa\u73b0\u3002\u7531\u547d\u4ee4\u884c\uff0cDBMS_DATAPUMP PL\/SQL package\uff0cDBMS_METADATA PL\/SQL package\u4e09\u90e8\u5206\u7ec4\u6210\u3002Data Pump\u4f7f\u7528\u63a5\u53e3expdp\u548cimpdp\u901a\u8fc7\u8c03\u7528DBMS_DATAPUMP PL\/SQL package\u6216\u8005DBMS_METADATA PL\/SQL package\u5b9e\u73b0\u6570\u636e\u7684\u62bd\u53d6\u548c\u5bfc\u5165\u3002expdp\/impdp\u548c\u4e4b\u524d\u7684exp\/imp\u6bd4\u8f83\u76f8\u4f3c\uff0c\u4f46\u662f\u533a\u522b\u5f88\u5927\uff0c\u4e8c\u8005\u4e4b\u95f4\u7684dump\u6587\u4ef6\u4e0d\u53ef\u4ea4\u53c9\u4f7f\u7528\uff0c\u5373\u5c31\u662f\uff0c\u7528expdp\u51fa\u6765\u7684dump\u6587\u4ef6\u65f6\u65e0\u6cd5\u4f7f\u7528imp\u5bfc\u5165\u5230\u6570\u636e\u5e93\u7684\u3002\u5e76\u4e14\u65b0\u7684expdp\/impdp\u6bd4\u539f\u59cb\u7684exp\/imp\u6027\u80fd\u6709\u5927\u5e45\u63d0\u5347\u3002<\/p>\n

2.expdp\/impdp\u65b0\u7279\u6027
\n\t2.1 \u53ef\u4ee5\u6307\u5b9a\u5e76\u884c\u5ea6\uff0c\u53c2\u6570\uff1aparallel\uff0810g\u4f01\u4e1a\u7248\uff09
\n\t2.2 \u53ef\u4ee5\u91cd\u542fData Pump\u7684job\uff0c\u53c2\u6570\uff1astart job
\n\t2.3 \u53ef\u4ee5\u5206\u79bb\u548c\u91cd\u65b0\u8fde\u63a5\u5230\u4e00\u4e2ajob\u800c\u4e0d\u5f71\u54cd\u5230\u8be5job\uff0c\u4fbf\u4e8edba\u5bf9job\u7684\u76d1\u63a7\uff0c\u53c2\u6570\uff1aattach
\n\t2.4 \u53ef\u4ee5\u901a\u8fc7\u7f51\u7edc\u8fdb\u884c\u64cd\u4f5c\uff0c\u53c2\u6570\uff1anetwork link
\n\t2.5 \u53ef\u4ee5\u5728\u4e00\u4e2aimpdp\u7684job\u4e2d\u5c06\u6570\u636e\u5e93\u6570\u636e\u6587\u4ef6\u91cd\u547d\u540d\uff0c\u53c2\u6570\uff1aremap datafile
\n\t2.6 \u589e\u5f3a\u652f\u6301\u91cd\u65b0\u6620\u5c04\u8868\u7a7a\u95f4\uff0c\u53c2\u6570\uff1aremap tablespace
\n\t2.7 \u53ef\u4ee5\u6839\u636e\u5bf9\u8c61\u7684\u7c7b\u578b\u652f\u6301\u8fc7\u6ee4\u5143\u6570\u636e\uff0c\u53c2\u6570\uff1ainclude\u548cexclude
\n\t2.8 \u652f\u6301\u4ea4\u4e92\u5f0f\u6a21\u5f0f\uff0c\u53ef\u4ee5\u76d1\u63a7\u548c\u4ea4\u4e92\u6b63\u5728\u8fd0\u884c\u7684job
\n\t2.9 \u53ef\u4ee5\u4e0d\u53bb\u5b9e\u9645\u6267\u884c\uff0c\u800c\u4f30\u7b97\u5bfc\u51fa\u6587\u4ef6\u7684\u5927\u5c0f\uff0c\u53c2\u6570\uff1aestimate only
\n\t2.10 \u53ef\u4ee5\u6307\u5b9a\u4e00\u4e2a\u6570\u636e\u5e93\u7684\u5bf9\u8c61\u7684\u7248\u672c\u4ece\u6e90\u5e93\u62bd\u53d6\u51fa\u6765\u6570\u636e\uff0c\u53c2\u6570\uff1aversion
\n\t2.11 \u53ef\u4ee5\u5728\u670d\u52a1\u7aef\u4e00\u6b21\u6267\u884c\u591a\u4e2aData Pump\u7a0b\u5e8f\uff08\u800c\u4ee5\u524d\u57fa\u4e8e\u5ba2\u6237\u7aef\u7684exp\/imp\u662f\u529e\u4e0d\u5230\u7684\uff09<\/p>\n

3.Data Pump\u6743\u9650\u76f8\u5173
\n\t\u53ea\u8981\u7528\u6237\u5177\u6709exp_full_database\u548cimp_full_database\u7684\u89d2\u8272\uff0c\u5176\u5c31\u53ef\u4ee5\u62e5\u6709\u4ee5\u4e0b\u6743\u9650
\n\t3.1 \u8ba9\u5176\u4ed6\u7528\u5bfc\u51fa\/\u5bfc\u5165\u81ea\u5df1\u7684\u6570\u636e\u5e93\u5bf9\u8c61
\n\t3.2 \u53ef\u4ee5\u5bfc\u51fa\/\u5bfc\u5165\u975e\u6a21\u5f0f\u5bf9\u8c61\uff0c\u5982\uff1a\u8868\u7a7a\u95f4\uff0c\u6a21\u5f0f\u5b9a\u4e49\u4fe1\u606f\uff0c\u7cfb\u7edf\u6388\u4e88\u7684\u6743\u9650\uff0c\u8d44\u6e90\u8ba1\u5212\u6216\u4ee5\u4e0a\u5168\u90e8
\n\t3.3 \u8ba9\u5176\u4ed6\u7528\u6237\u76d1\u63a7\uff0c\u63a7\u5236\uff0c\u5206\u79bbData Pump job\u7684\u6743\u9650
\n\t3.4 \u6570\u636e\u5e93\u6570\u636e\u6587\u4ef6\u7684remap\u64cd\u4f5c
\n\t3.5 \u6570\u636e\u5e93\u5176\u4ed6\u7528\u6237\u7684remap\u64cd\u4f5c<\/p>\n

4.Data Pump\u5982\u4f55\u52a0\u8f7d\u548c\u5378\u8f7d\u6570\u636e
\n\tData Pump\u67092\u4e2d\u65b9\u5f0f\u8fdb\u884c\u52a0\u8f7d\u6216\u5378\u8f7d\u6570\u636e\uff0c\u5916\u90e8\u8868\u6216\u8005\u76f4\u63a5\u8def\u5f84\u3002
\n\t\u6ce8\u610f\uff1a\u5728\u4e0d\u7981\u7528\u552f\u4e00\u6027\u7d22\u5f15\u7684\u60c5\u51b5\u4e0b\uff0cimpdp\u662f\u4e0d\u4f1a\u52a0\u8f7d\u6570\u636e\u5e93\u8868\u7684\u3002\u9664\u975e\u5728impdp\u8868\u4e4b\u524d\u5220\u9664\u7d22\u5f15\u6216\u8005\u91cd\u65b0\u662f\u7d22\u5f15\u751f\u6548\u3002
\n\t\u6570\u636e\u5e93\u7684\u76f4\u63a5\u8def\u5f84\u7684\u529f\u80fd\u662f\u4eceoracle7\u5f00\u59cb\u7684\uff0c\u800cAPI\u662f\u4eceoracle8i\u5f00\u59cb\u7684\uff0cData Pump\u5728\u4ee5\u4e0b\u65b9\u9762\u52a0\u5f3a\u4e86\u76f4\u63a5\u8def\u5f84\u52a0\u8f7d\u548c\u5378\u8f7d\u7684\u7279\u6027\u3002
\n\t4.1 \u4ee5\u81ea\u5df1\u7684\u65b9\u5f0f\u76f4\u63a5\u8def\u5f84\u5378\u8f7d\u6570\u636e
\n\t4.2 \u901a\u8fc7\u53d6\u6d88\u4e0d\u5fc5\u8981\u7684\u5185\u90e8\u8f6c\u6362\uff0c\u4f7f\u7528\u5185\u90e8\u6d41\u5f62\u5f0f\u7684\u65b9\u5f0f\u5c06\u6570\u636e\u5378\u8f7d\u81f3dump\u6587\u4ef6
\n\t4.3 \u652f\u6301\u989d\u5916\u7684\u6570\u636e\u7c7b\u578b\u548c\u4f20\u8f93\u65b9\u5f0f
\n\t\u5728\u8868\u7ed3\u6784\u5141\u8bb8\u7684\u60c5\u51b5data pump\u9ed8\u8ba4\u662f\u91c7\u7528\u76f4\u63a5\u8def\u5f84\u52a0\u8f7d\u548c\u5378\u8f7d\u6570\u636e\u7684\uff0c\u5982\u679c\u8868\u6709\u5f88\u5bf9\u6570\u636e\u7c7b\u578b\u4e3along\u7684\u5217\u65f6\uff0c\u5fc5\u987b\u4f7f\u7528\u76f4\u63a5\u8def\u5f84\u3002<\/p>\n

\t\u4ee5\u4e0b\u60c5\u51b5\u5c06\u4e0d\u4f7f\u7528\u76f4\u63a5\u8def\u5f84\u52a0\u8f7d\u6570\u636e
\n\t1. \u5728\u5206\u533a\u8868\u4e0a\u6709\u5168\u5c40\u7d22\u5f15\u3002
\n\t2. \u5728lob\u5217\u4e0a\u6709domain\u7d22\u5f15
\n\t3. \u805a\u7c07\u8868
\n\t4. \u8868\u4e0a\u6709\u5df2\u5b58\u5728\u7684\u6d3b\u52a8\u7684\u89e6\u53d1\u5668
\n\t5. Fine-grained access control is enabled in insert mode on a pre-existing table.
\n\t6. A table contains BFILE columns or columns of opaque types.
\n\t7. \u5728\u8868\u4e0a\u6709\u53c2\u7167\u7ea6\u675f\u5b58\u5728
\n\t8. A table contains VARRAY columns with an embedded opaque type.
\n\t9. \u8868\u4e0a\u6709\u52a0\u5bc6\u7684\u5217
\n\t10. \u8865\u5145\u65e5\u5fd7\u6a21\u5f0f\u5f00\u542f\uff0c\u4e14\u8868\u4e0a\u81f3\u5c11\u6709\u4e00\u4e2alob\u5217\u3002
\n\t11. The table into which data is being imported is a pre-existing table and at least one of the following conditions exists:
\n\t\tThere is an active trigger
\n\t\tThe table is partitioned
\n\t\tfgac is in insert mode
\n\t\tA referential integrity constraint exists
\n\t\tA unique index exists<\/p>\n

\t\u4ee5\u4e0b\u60c5\u51b5\u5c06\u4e0d\u4f7f\u7528\u76f4\u63a5\u8def\u5f84\u5378\u8f7d\u6570\u636e
\n\t1. Fine-grained access control for SELECT is enabled.
\n\t2. \u8868\u6b63\u5728\u67e5\u8be2
\n\t3. The table contains one or more columns of type BFILE or opaque, or an object type containing opaque columns.
\n\t4. \u8868\u4e0a\u6709\u52a0\u5bc6\u5217
\n\t5. A column of an evolved type that needs upgrading.
\n\t6. A column of type LONG or LONG RAW that is not last.<\/p>\n

5. \u5173\u4e8eData Pump Job
\n\tData Pump\u4f7f\u7528\u4e00\u4e2a\u4e3b\u8868\uff0c\u4e00\u4e2a\u4e3b\u8fdb\u7a0b\uff0c\u591a\u4e2a\u5de5\u4f5c\u8fdb\u7a0b\u6765\u8fdb\u884c\u5de5\u4f5c\u3002\u4e00\u65e6\u4e00\u4e2adata pump\u542f\u52a8\uff0c\u5219\u4f1a\u521b\u5efa\u4e00\u4e2a\u4e3b\u8fdb\u7a0b\uff0c\u4e3b\u8fdb\u7a0b\u4f1a\u63a7\u5236\u6574\u4e2ajob\uff0c\u5305\u62ec\u548c\u5ba2\u6237\u7aef\u7684\u901a\u4fe1\u548c\u521b\u5efa\u5de5\u4f5c\u8fdb\u7a0b\u6c60\uff0c\u4ee5\u53ca\u4ea7\u751f\u65e5\u5fd7\u7684\u64cd\u4f5c\u3002
\n\t\u76f8\u5173\u89c6\u56fe\u548c\u6570\u636e\u5b57\u5178\u5982\u4e0b\uff1a
\n\tDBA_DATAPUMP_JOBS,
\n\tUSER_DATAPUMP_JOBS,
\n\tDBA_DATAPUMP_SESSIONS,
\n\tV$SESSION_LONGOPS
\n\t\tThe V$SESSION_LONGOPS columns that are relevant to a Data Pump job are as follows:
\n\t\t\tUSERNAME - job owner
\n\t\t\tOPNAME - job name
\n\t\t\tTARGET_DESC - job operation
\n\t\t\tSOFAR - megabytes (MB) transferred thus far during the job
\n\t\t\tTOTALWORK - estimated number of megabytes (MB) in the job
\n\t\t\tUNITS - 'MB'
\n\t\t\tMESSAGE - a formatted status message of the form:
\n\t\t\t: : nnn out of mmm MB done'<\/p>\n

6.\u6587\u4ef6\u4f4d\u7f6e
\n\t\u9996\u5148\u9700\u8981\u6709\u4e00\u4e2adump\u76ee\u5f55\uff0c\u800c\u521b\u5efa\u76ee\u5f55\uff0c\u7528\u6237\u9700\u8981CREATE ANY DIRECTORY \u7684\u6743\u9650\uff0c\u5e76\u4e14\u6709\u5728\u8fd9\u4e2a\u76ee\u5f55\u4e0a\u7684\u8bfb\u5199\u6743\u9650\u3002
\n\tData Pump\u9996\u5148\u4f1a\u91c7\u7528\u6307\u5b9a\u7684\u76ee\u5f55\u5b58\u6570\u6570\u636e\uff0c\u5982\u679c\u4e0d\u6307\u5b9a\uff0c\u5219\u4f1a\u4f7f\u7528\u53c2\u6570DIRECTORY \u6307\u5b9a\u7684\u76ee\u5f55\uff1b\u5982\u679c\u4e5f\u6ca1\u6709\u8fd9\u4e2a\u53c2\u6570\uff0c\u90a3\u4e48\u4f1a\u4f7f\u7528\u5230\u7528\u73af\u5883\u53d8\u91cfDATA_PUMP_DIR\u8bbe\u5b9a\u7684\u76ee\u5f55\uff0c\u5982\u679c\u4ee5\u4e0a\u5747\u6ca1\u6709\u8bbe\u5b9a\uff0c\u5219\u4f1a\u4f7f\u7528\u6570\u636e\u5b57\u5178\u4e2dDATA_PUMP_DIR\u6307\u5b9a\u7684\u76ee\u5f55\uff0c\u800c\u8be5\u503c\u662f\u5728\u6570\u636e\u5e93\u521b\u5efa\u6216\u8005\u5728\u6570\u636e\u5b57\u5178\u5347\u7ea7\u4e4b\u540e\u81ea\u52a8\u521b\u5efa\u7684\u3002
\n\t\u76f8\u5173\u547d\u4ee4\uff1a
\n\tSQL> SELECT directory_name, directory_path FROM dba_directories WHERE directory_name='DATA_PUMP_DIR';
\n\tSQL> CREATE DIRECTORY DUMP_FILES1 AS '\/usr\/apps\/dumpfiles1';
\n\tSQL> GRANT READ, WRITE ON DIRECTORY dpump_dir1 TO hr;<\/p>\n

7. expdp\/impdp\u548cexp\/imp\u7684\u5bf9\u6bd4
\n\t7.1 Data Pump\u4f7f\u7528\u4e00\u7ec4dump\u7684\u6587\u4ef6\uff0c\u800c\u4e0d\u662f\u4e00\u4e2a\u6709\u5e8f\u7684dump\u6587\u4ef6\u3002
\n\t7.2 Data Pump\u8fd0\u884c\u5728\u670d\u52a1\u7aef\u800c\u4e0d\u662f\u5ba2\u6237\u7aef\uff0c\u63d0\u9ad8\u6027\u80fd\uff0c\u4e14\u9700\u8981\u4f60\u6307\u5b9a\u4e00\u4e2a\u76ee\u5f55\u3002
\n\t7.3 Data Pump\u548c\u4ee5\u524d\u7684exp\/imp\u547d\u4ee4\u57fa\u672c\u76f8\u540c
\n\t7.4 Data Pump\u53ef\u4ee5\u4f7f\u7528\u5e76\u884c\uff0c\u800c\u4e0d\u662f\u4e00\u4e2a\u5355\u72ec\u7684\u6d41\u3002
\n\t7.5 Data Pump\u53ef\u4ee5\u4ee5xml\u7684\u5f62\u5f0f\u4ee3\u66ffddl\u8bed\u8a00\u63cf\u8ff0\u6570\u636e\u5e93\u5143\u6570\u636e
\n\t7.6 Data Pump\u6709\u81ea\u5df1\u7684\u4f18\u5316\u53c2\u6570
\n\t7.7 Data Pump\u6ca1\u6709\u539f\u59cb\u7684\u7c7b\u4f3ccommit\u7684\u53c2\u6570
\n\t7.8 Data Pump\u6ca1\u6709\u539f\u59cb\u7684compress\u53c2\u6570\u53ef\u4ee5merge extent\u800c\u662f\u6839\u636e\u5b58\u50a8\u53c2\u6570\u91cd\u73b0\u5206\u914d\u3002
\n\t7.9 \u4e32\u578b\u8bbe\u5907\u5982\u78c1\u5e26\uff0c\u7ba1\u9053\u4e0d\u652f\u6301\u3002
\n\t7.10 \u4f7f\u7528\u53c2\u6570version\u8fdb\u884c\u4e0d\u540c\u7248\u672c\u7684\u6570\u636e\u8fc1\u79fb
\n\t7.11 Data Pump\u5b58\u5728\u53c2\u6570append \u548c truncate\u8fdb\u884c\u6570\u636e\u7684\u8ffd\u52a0
\n\t7.12 Data Pump\u5c06\u6d88\u8017\u66f4\u591a\u7684UNDO
\n\t7.13 Data Pump\u5c06\u4ee5\u538b\u7f29\u7684\u6570\u636e\u52a0\u8f7d\u6570\u636e\u5230\u538b\u7f29\u8868\uff0c\u800c\u4e0d\u662f\u4ee5\u975e\u538b\u7f29\u7684\u6570\u636e\u52a0\u8f7d\u7684\u538b\u7f29\u7684\u8868
\n\t7.14 \u5728\u76f4\u63a5\u8def\u5f84\u6216\u8005\u5916\u90e8\u8868\u5747\u652f\u6301\u5b57\u7b26\u96c6\u8f6c\u6362\uff0c
\n<\/coolcode><\/p>\n","protected":false},"excerpt":{"rendered":"

Data Pump 1.\u7b80\u4ecb \u6570\u636e\u6cf5\u53ef\u4ee5\u5feb\u901f\u628a\u6570\u636e\u548c\u6570\u636e\u5e93\u5143\u6570\u636e\u4ece\u4e00\u4e2a\u6570\u636e\u5e93\u8fc1\u79fb\u5230\u53e6\u4e00\u4e2a\u6570\u636e\u5e93\uff0c\u6570\u636e\u6cf5\u4eceora […]<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":[],"categories":[87],"tags":[],"_links":{"self":[{"href":"http:\/\/www.yallonking.com\/index.php?rest_route=\/wp\/v2\/posts\/582"}],"collection":[{"href":"http:\/\/www.yallonking.com\/index.php?rest_route=\/wp\/v2\/posts"}],"about":[{"href":"http:\/\/www.yallonking.com\/index.php?rest_route=\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"http:\/\/www.yallonking.com\/index.php?rest_route=\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"http:\/\/www.yallonking.com\/index.php?rest_route=%2Fwp%2Fv2%2Fcomments&post=582"}],"version-history":[{"count":10,"href":"http:\/\/www.yallonking.com\/index.php?rest_route=\/wp\/v2\/posts\/582\/revisions"}],"predecessor-version":[{"id":641,"href":"http:\/\/www.yallonking.com\/index.php?rest_route=\/wp\/v2\/posts\/582\/revisions\/641"}],"wp:attachment":[{"href":"http:\/\/www.yallonking.com\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=582"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"http:\/\/www.yallonking.com\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=582"},{"taxonomy":"post_tag","embeddable":true,"href":"http:\/\/www.yallonking.com\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=582"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}