{"id":2095,"date":"2025-01-09T16:59:07","date_gmt":"2025-01-09T08:59:07","guid":{"rendered":"http:\/\/www.821121.com\/?p=2095"},"modified":"2025-05-09T17:07:13","modified_gmt":"2025-05-09T09:07:13","slug":"oracle-data-pump%ef%bc%88%e8%bf%9b%e9%98%b6%e7%94%a8%e6%b3%95","status":"publish","type":"post","link":"http:\/\/www.821121.com\/?p=2095","title":{"rendered":"Oracle Data Pump\uff08\u8fdb\u9636\u7528\u6cd5)"},"content":{"rendered":"

\u63a5\u4e0a\u6587Oracle Data Pump\uff08\u57fa\u7840\u4f7f\u7528) http:\/\/www.821121.com\/?p=2086<\/a><\/p>\n

3.\u8fdb\u9636\u7528\u6cd5\u00a0<\/h1>\n
nohup expdp system\/Windows2012  directory=HOME dumpfile=expdp_ANJOY_%U.dmp logfile=exp_ANJOY.log schemas=ANJOY job_name=exp_ANJOY COMPRESSION=all parallel=8 cluster=n  exclude=STATISTICS &<\/code><\/pre>\n
3.1.INCLUDE<\/h3>\n
INCLUDE=TABLE_DATA:IN (select partition_name from dba_tab_partitions where table_name in (\u2018TABLE_NAME1\u2032,\u2019TABLE_NAME2\u2032\u2026) and partition_name like \u2018%_P%\u2019 and substr(partition_name,-4,4) < to_char(sysdate,\u2019\u2019) and partition_name not like \u2018%P2013%\u2019)\n<\/code><\/pre>\n
3.2.EXCLUDE<\/h3>\n
expdp scott\/tiger DIRECTORY=dump_dir DUMPFILE=halberd.dup    SCHEMAS = scott  TABLE:\\"IN ('EMP', 'DEPT')\\"\nexclude=table,index,INDEX_STATISTICS,TABLE_STATISTICS<\/code><\/pre>\n
3.3.QUERY<\/h3>\n
QUERY=emp:\\"WHERE username in ('aaa','bb')\\",taba:\\"WHERE rownum<5\\"<\/code><\/pre>\n
3.4.PARFILE<\/h3>\n
expdp \\'sys\/********* as sysdba\\' directory=sfdir  logfile=export.log parfile=parfile.txt\n\ncat parfile.txt\ndumpfile=export.dmp\ntables=( CHANNEL.TR_CHANNEL_ACCESS_LOG_BAK, scott.test1, scott.test2)\nquery=(\n xxxxx.TR_XXXX_ACCESS_LOG_BAK:"where CHANNEL_CODE='10689021144'",\n scott.test1:"where UA_SERIAL_ID in ('96','26')",\n scott.test2:"where FILESIZE=273899"\n)\n<\/code><\/pre>\n
\u6ce8\uff1awindows\u7684parfile\u8981\u7528UE\u6216\u8005notepad++\u7b49\u5de5\u5177\u7f16\u8f91\u5e76\u4fdd\u5b58\u4e3auft8\u7f16\u7801\uff0c\u4e0d\u8981\u7528windows\u81ea\u5e26\u7684\u8bb0\u4e8b\u672c\uff01\u4e0d\u8981\u7528windows\u81ea\u5e26\u7684\u8bb0\u4e8b\u672c\uff01\u4e0d\u8981\u7528windows\u81ea\u5e26\u7684\u8bb0\u4e8b\u672c\uff01<\/p>\n
3.5.\u4e0d\u751f\u6210\u6587\u4ef6\u76f4\u63a5\u5bfc\u5165<\/h3>\n
impdp \\'\\\/ as sysdba\\' parfile=test.par\/\ncat test.par\ndirectory=dump_dir\nlogfile=test.log\nschemas=test\nquery="where create_date > last_day(add_months(sysdate,-1)) and create_date <= last_day(sysdate)"\ntransform=segment_attributes:n\nnetwork_link=to_aibcrm\ntable_exists_action=append<\/code><\/pre>\n
\u6ce8\uff1a\u53ef\u4ee5\u4e0d\u751f\u6210dmp\u6587\u4ef6\uff0c\u76f4\u63a5\u5c06\u6570\u636e\u62bd\u53d6\u5230\u76ee\u6807\u6570\u636e\u3002\u5728\u8fc1\u79fb\u5927\u91cf\u6570\u636e\u800c\u6ca1\u6709\u5145\u8db3\u5b58\u50a8\u7a7a\u95f4\u65f6\uff0c\u8fd9\u662f\u4e00\u4e2a\u6551\u547d\u7a3b\u8349\u3002 \u6700\u5173\u952e\u7684\u70b9\u5c31\u662f\u5728\u76ee\u6807\u7aef\u6267\u884cimpdp\u7684\u65f6\u5019\uff0c\u4f7f\u7528network_link\uff0c\u76f4\u63a5\u4ece\u6e90\u5e93\u62bd\u53d6\u6570\u636e<\/p>\n
3.6.\u4e0d\u77e5\u9053\u5bc6\u7801<\/h3>\n
expdp \\"\/ as sysdba\\"     --\u64cd\u4f5c\u7cfb\u7edf\u8ba4\u8bc1<\/p>\n
3.7.\u53bb\u9664\u6bb5\u5c5e\u6027<\/h3>\n
deferred_segment_creation=true \u5ef6\u8fdf\u6bb5\u521b\u5efa\uff0c\u7a7a\u8868\u9ed8\u8ba4 \u4e0d\u5206\u914d\u6bb5
\nTRANSFORM=segment_attributes:n:table    --\u5bf9\u8868\u53bb\u9664\u6bb5\u5c5e\u6027<\/p>\n
3.8.lob\u5b57\u6bb5<\/h3>\n
ORA-39126: Workerunexpected fatal error in KUPW$WORKER.FETCH_XML_OBJECTS[INDEX:"SAPSR3"."LXE_OBJ_EXP_LANG~0"]
\nORA-01555: snapshottoo old: rollback segment number 11 with name "_SYSSMU11_2268899145$"too small
\nORA-06512: at"SYS.DBMS_SYS_ERROR", line 95
\nORA-06512: at"SYS.KUPW$WORKER", line 11259
\n\u89e3\u51b3\u529e\u6cd5\uff1a
\n\u589e\u5927undo_retention
\n\u6269\u5927UNDO\u5927\u5c0f
\n\u5c06lob\u5b57\u6bb5\u9ed8\u8ba4\u5c5e\u6027basicfiles\u8f6c\u6362\u4e3asecurefiles\uff0c\u5219\u5bfc\u51fa\u65f6\u95f4\u5927\u5927\u51cf\u5c11\uff0cDoc ID 728758.1<\/p>\n
ora-31693 :table data object xx.xxxx failed to load\/unload and is being skipped  due to error;<\/p>\n
ora-29913 :error in executing ODCIEXTTABLEPOPULATE callout<\/p>\n
ora-22923 :amount of data specified in streaming LOB write is 0<\/p>\n
3.9.ATTACH<\/h3>\n
nohup expdp system\/oracle directory=HOME dumpfile=expdp_ANJOY_%U.dmp logfile=exp_ANJOY.log job_name=exp_ANJOY COMPRESSION=all parallel=8 cluster=n  exclude=STATISTICS   schemas=dbis  job_name=dbis &<\/code><\/pre>\n
\"file\"
\n\"file\"
\nexpdp system\/oracle123 attach=dbis
\n\"file\"
\nExport> help<\/h2>\n
The following commands are valid while in interactive mode.
\nNote: abbreviations are allowed.<\/p>\n
ADD_FILE
\nAdd dumpfile to dumpfile set.<\/p>\n
CONTINUE_CLIENT
\nReturn to logging mode. Job will be restarted if idle.<\/p>\n
EXIT_CLIENT
\nQuit client session and leave job running.<\/p>\n
FILESIZE
\nDefault filesize (bytes) for subsequent ADD_FILE commands.<\/p>\n
HELP
\nSummarize interactive commands.<\/p>\n
KILL_JOB
\nDetach and delete job.<\/p>\n
PARALLEL
\nChange the number of active workers for current job.<\/p>\n
REUSE_DUMPFILES
\nOverwrite destination dump file if it exists [NO]. <\/p>\n
START_JOB
\nStart or resume current job.
\nValid keyword values are: SKIP_CURRENT.<\/p>\n
STATUS
\nFrequency (secs) job status is to be monitored where
\nthe default [0] will show new status when available.<\/p>\n
STOP_JOB
\nOrderly shutdown of job execution and exits the client.
\nValid keyword values are: IMMEDIATE.<\/p>\n
STOP_WORKER
\nStops a hung or stuck worker.<\/p>\n
TRACE
\nSet trace\/debug flags for the current job.<\/p>\n
3.10.existing object<\/h3>\n
\"file\"
\n\u89e3\u51b3\u529e\u6cd5\uff1a
\nselect * from dba_datapump_jobs;
\n\"file\"
\nDROP TABLE OWNER_NAME.JOB_NAME;<\/p>\n","protected":false},"excerpt":{"rendered":"
\u63a5\u4e0a\u6587Oracle Data Pump\uff08\u57fa\u7840\u4f7f\u7528)<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"closed","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[18],"tags":[],"class_list":["post-2095","post","type-post","status-publish","format-standard","hentry","category-oracle-11g"],"_links":{"self":[{"href":"http:\/\/www.821121.com\/index.php?rest_route=\/wp\/v2\/posts\/2095","targetHints":{"allow":["GET"]}}],"collection":[{"href":"http:\/\/www.821121.com\/index.php?rest_route=\/wp\/v2\/posts"}],"about":[{"href":"http:\/\/www.821121.com\/index.php?rest_route=\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"http:\/\/www.821121.com\/index.php?rest_route=\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"http:\/\/www.821121.com\/index.php?rest_route=%2Fwp%2Fv2%2Fcomments&post=2095"}],"version-history":[{"count":3,"href":"http:\/\/www.821121.com\/index.php?rest_route=\/wp\/v2\/posts\/2095\/revisions"}],"predecessor-version":[{"id":2104,"href":"http:\/\/www.821121.com\/index.php?rest_route=\/wp\/v2\/posts\/2095\/revisions\/2104"}],"wp:attachment":[{"href":"http:\/\/www.821121.com\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=2095"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"http:\/\/www.821121.com\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=2095"},{"taxonomy":"post_tag","embeddable":true,"href":"http:\/\/www.821121.com\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=2095"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}