OracIe加快數(shù)據(jù)導(dǎo)入速度測試

v1.0

目錄

1.報告概述4

2.EXP測試4

2.1.Exp學(xué)習(xí)及測試4

2.1.1.Exp參數(shù)4

2.1.2.Expbuffer5

2.1.3.ExpDIRECT8

2.1.4.Exp導(dǎo)出測試時間對比9

2.2.Exp導(dǎo)出問題11

221.Exp使用直接路徑寫,導(dǎo)入空的分區(qū)會報錯11

2.2.2.Exp使用直接路徑寫，導(dǎo)入存在default值的字段會報錯14

2.2.3.exp導(dǎo)出報錯EXP-00006,0000016

2.2.4.exp導(dǎo)出報錯EXP-0011316

3.Expdp測試17

3.1.Expdp相關(guān)的信息17

3.1.1.Expdp預(yù)估導(dǎo)出大小17

3.1.2.Expdp的導(dǎo)出文件方式19

3.1.3.Impdp的導(dǎo)入文件方式20

3.2.數(shù)據(jù)泵使用的其它信息21

3.2.1.數(shù)據(jù)泵相關(guān)的進程信息查詢21

3.2.2.如何獲取每個類型的詳細(xì)時間22

3.2.3.如何清理數(shù)據(jù)泵的作業(yè)表23

3.2.4.小結(jié)27

3.3.數(shù)據(jù)泵的案例27

3.3.1.分區(qū)太多Bug2995902527

3.3.2.SYS_EXPORT_SCHEMA_01表blockchange變化很高28

3.3.3.數(shù)據(jù)泵導(dǎo)出緩慢-Sharedpool收縮導(dǎo)致28

3.3.4.Expdp遭遇ORA-07445的背后29

3.3.5.內(nèi)存太小expdp報錯UDE-31623ORA-3162329

3.3.6.Expdp由于存儲問題JOB狀態(tài)為DEFINING31

3.3.7.ImpdpORA-01628undosegmentsmax#extents(32765)32

3.3.8.小結(jié)34

3.4.expdp的并彳亍35

3.4.1.Oracle數(shù)據(jù)泵導(dǎo)出使用并行參數(shù)，單個表能否真正的并行？35

3.4.2.閱讀并行pdb文檔37

3.4.3.閱讀并行Mos文檔38

3.4.4.OracleLOB可以并行導(dǎo)出嗎38

3.4.5.OracleIndex可以并行導(dǎo)入嗎38

3.4.6.如何加快泵導(dǎo)入的速度40

3.4.7.數(shù)據(jù)泵19c導(dǎo)出并行42

3.4.8.數(shù)據(jù)泵19c導(dǎo)入并行1004644

3.4.9.數(shù)據(jù)泵19c導(dǎo)入索引增強45

3.4.10.數(shù)據(jù)泵慢分析47

3.4.11.小結(jié)48

4.Exp與Expdp的區(qū)別48

4.1.Mos官方說法48

4.2.實驗及個人總結(jié)49

5.附件51

1.報告概述

本篇文檔學(xué)習(xí)測試Oracle加快數(shù)據(jù)導(dǎo)入速度進行測試。

數(shù)據(jù)遷移Oracle內(nèi)部的邏輯數(shù)據(jù)遷移一般使用exp,expdp,imp,impdp;那么當(dāng)數(shù)據(jù)量比較小的時候都無所

謂，但是當(dāng)數(shù)據(jù)量很大的情況下，如何加快導(dǎo)入的速度呢？

Oracle數(shù)據(jù)泵的并行在11g,19c有什么不同,如何加快數(shù)據(jù)導(dǎo)入的速度？

本篇文章基于上述問題進行學(xué)習(xí)測試！

2.EXP測試

2.1.Exp學(xué)習(xí)及測試

2.1.1.Exp參數(shù)

Exp還有比較有意思的參數(shù)，可以設(shè)置報錯結(jié)束時間，給你一個機會在一定時間內(nèi)解決問題，而不是直接

停止！這個功能在數(shù)據(jù)泵是默認(rèn)使用的，例如表空間滿導(dǎo)入報錯，并不會直接終止Job會給一定的時間讓用

戶進行處理，當(dāng)然超時還是會報錯終止！

WhatistheuseoftheRESUMABLEparameterinEXPORT?(DocID152013.1)

Buffer

ALERT:Export(EXP)WithLargeBUFFERCanSilentlyProduceACorruptDumpFile(DocID223399.1)

TheexportparameterBUFFERspecifiesthesizeinbytesofthebufferusedtofetchrowsfromatable.Adata

corruptioncanoccuriftheBUFFERparameterhasbeensettoavaluesomorethan32,768rowswillbefetched.

ThisimpliesarelationtothelengthoftherowsthatarefetchedintheBUFFER.Inaddition,thestructureofthetable

andtheactualdataintherows,haveanimpactontheoccurrenceofthisbugtoo.Therefore,anexactandsafevalue

fortheBUFFERparametercannotbegiven.

version

Forexample:

->theAVG_ROW_LENGTHis100bytes

->thetablehasmorethan32,768rows

->criticalBUFFERvalue3276800orhigher(32k*100;about3.1Mb)

->datacorruption*can*occurif:BUFFER=3200000(orhigher)

-buffer的參數(shù)取決于常規(guī)路徑導(dǎo)出時，達到這個buffer閾值后寫入dump文件中，當(dāng)buffer設(shè)置為

3.2Mbytes,buffer單位bytes可能會崩潰。

FILESIZE

ORA-01403duringexportusingFILESIZEparameter(DocID282079.1)

Inwindows,themaximumfilesizelimitis2GB.

Use2000M,insteadof2GB

EXP-00002:Errorinwritingtoexportfile

EXP-00056:Oracleerror1403encountered

ORA-01403:Nodatafound

EXP-00000:Exportterminatedunsuccessfully

exp<user>/<password>file='dump_l.dmpdump_2.dmp,dump_3.dmp1

log=export.logfilesize=2000Mtables=<table_name>

舉例：

expuserid=lhr/lhrfile=/tmp/testl,test2,test3,test4,test5filesize=2Glog=test.log

舉例：遷移項目使用的參數(shù)列表

SQL>SELECTCURRENT_SCNFROMV$DATABASE；

CURRENT_SCN

26238040

[oracle@t3vi/home/oracle/exp_schema.par

userid="exp_user/exp_user"

OWNER=YZ

FILE=/home/oracle/exp_schema_yz01.dmp

LOG=/home/oracle/exp_schema_yz01.log

FLASHBACK_SCN=26238040

statistics=none

direct=N

feedback=10000

--buffer=1024000

--FLASHBACK導(dǎo)出SCN號

--statistics是否遷移統(tǒng)計信息，否

--buffer導(dǎo)出cache大小，滿足cache時寫入file

--direct是否直接路徑寫

--FEEDBACK顯示每X行(0)的進度

nohuptimeexpparfile=exp_schema.par&

2.1.2.Expbuffer

.EXP10046TraceBuffer1024000

SQL>createuserexp__useridentifiedby"exp_user";

SQL>grantdbatoexp_user;

[oracle@t3expexp_user/exp__user

Export:Release.0-ProductiononThuMay2602:48:372022

Copyright(c)1982,2011,Oracleand/oritsaffiliates.Allrightsreserved.

Connectedto:OracleDatabase11gEnterpriseEditionRelease.0-64bitProduction

WiththePartitioning,OLAP,DataMiningandRealApplicationTestingoptions

Enterarrayfetchbuffersize:4096>

--此時,對于Oracledb來說有一個新的連接，可以對這個連接開啟10046trace

sqlplus/assysdba

colsidfor999999

colserial#for99999

colusernamefora20

colprogramfora20

colsql_idfora30

coleventfora30

setlinesize140

selectsidjserial#username,program,sql_idjeventfromv$sessionwhereusername='EXP_USER';

SIDSERIAL#USERNAMEPROGRAM-SQL_IDEVENT

4323EXP_USERexp@t3(TNSVI-V3)SQL*Netmessagefromclient

SQL>SELECTS.USERNAME,

P.SPIDOS_PROCESS_ID

FROMV$SESSIONS,V$PROCESSP

WHERES.PADDR=P.ADDR

ANDS.SID=43;

USERNAMEOS_PROCESS_ID

EXPJJSER19633

V_0S_PID=19633

V_LEVEL=12

v_exec_oral0046trc=$(echo"oradebugsetospid"${V_OS_PID)";

oradebugunlimit;

oradebugevent10046tracenamecontextforever4level"${V_LEVEL}“；

oradebugtracefile_name;

exit;"|sqlplus-s/assysdba|sed-e'/A*$/d')

一執(zhí)行導(dǎo)出

[oracle@t3~]$expexp_user/exp_user

Enterarrayfetchbuffersize:4096>1024000

Exportfile:expdat.dmp>/home/oracle/exp_schema_yz_10046_01.dmp

(l)E(ntiredatabase),(2)U(sers),or(3)T(ables):(2)U>U

Exportgrants(yes/no):yes>

Exporttabledata(yes/no):yes>

Compressextents(yes/no):yes>no

ExportdoneinUS7ASCIIcharactersetandAL16UTF16NCHARcharacterset

serverusesAL32UTF8characterset(possiblecharsetconversion)

Abouttoexportspecifiedusers???

Usertobeexported:(RETURNtoquit)>YZ

Usertobeexported:(RETURNtoquit)>

Exportterminatedsuccessfullywithwarnings.

■■關(guān)閉該session的10046,由于exp導(dǎo)出完成session自動關(guān)閉，因此10046sessiontraceoff提示找不到進

程id

$echo"${v__exec_oral0046trc}"

Oraclepid:19,Unixprocesspid:19633,image:oracle@t3(TNSVI-V3)

Statementprocessed.

Statementprocessed.

/u01/app/oracle/diag/rdbms/dgtest001/dgtest001/trace/dgtest001__ora_19633.trc

.查詢exp最慢的SQL

tkprof/u01/app/oracle/diag/rdbms/dgtest001/dgtest001/trace/dgtest001_ora_19633.trc

dgtest001_ora_19633.txt

[oracle@t3~]$catdgtest001_ora_19633.txt|grep-iTOTAL|grep-vwaited|grep-vFOR|awk-F',

'(print$4}'|sort-n|tail-10

0.86

0.87

0.93

0.96

1.07

2.55

2.77

3.27

40.70

69.35

********************************************************************************

SQLID:Ifrvh4kv91y7kPlanHash:903426141

SELECT/*+NESTEDTABLE_GETREFS+*/"YZ"."TEST_BIG1".*

FROM

"YZ"."TEST_BIG1"

callcountcpuelapseddiskquerycurrentrows

Parse10.000.000200

Execute10.000.000000

Fetch4813.7440.6916370524175005544384

total4833.7540.7016370524175205544384

Missesinlibrarycacheduringparse:1

Optimizermode:ALL_ROWS

Parsinguserid:94

Numberofplanstatisticscaptured:1

Rows(1st)Rows(avg)Rows(max)RowSourceOperation

554438455443845544384TABLEACCESSFULLTEST_BIG1(cr=241750pr=163705pw=0

time=848380uscost=46664size=1202608521card=5809703)

Elapsedtimesincludewaitingonfollowingevents:

EventwaitedonTimesMax.WaitTotalWaited

SQL*Netmessagetoclient4810.000.00

directpathread12922.0934.11

SQL*Netmoredatatoclient277120.234.97

SQL*Netmessagefromclient4812.0021.56

dbfilesequentialread20.000.00

********************************************************************************

我們可以發(fā)現(xiàn)，導(dǎo)出的大表直接路徑讀totalwaited等待最多，sql解析一次，執(zhí)行一次，但是fetch達到

481次？？？select可以認(rèn)為一個大表的數(shù)據(jù)導(dǎo)出消耗40s.

.EXP10046TraceBuffer20480000

[oracle@t3~]$catdgtest001_ora_25733.txt|grep-iTOTAL|grep-vwaited|gr叩-vFOR|awk-F

'(print$4)1|sort-n|tail-10

0.86

0.87

0.90

3.04

4.03

5.92

7.92

11.58

50.45

99.59

********************************************************************************

SQLID:Ifrvh4kv91y7kPlanHash:903426141

SELECT/*+NESTEDTABLEGETREFS+*/"YZ"."TEST_BIG1".*

FROM

“YZ"."TEST_B：[G1”

callcountcpuelapseddiskquerycurrentrows

Parse10.000.000200

Execute10.000.000000

Fetch683.0150.4516370524134505544384

total703.0150.4516370524134705544384

Missesinlibrarycacheduringparse:1

Optimizermode:ALL_ROWS

Parsinguserid:94

Numberofplanstatisticscaptured:1

Rows(1st)Rows(avg)Rows(max)RowSourceOperation

554438455443845544384TABLEACCESSFULLTEST_BIG1(cr=241345pr=163705pw=0

time=1217368uscost=46664size=1202608521card=5809703)

Elapsedtimesincludewaitingonfollowingevents:

EventwaitedonTimesMax.WaitTotalWaited

SQL*Netmessagetoclient680.000.00

directpathread12920.8441.45

SQL*Netmoredatatoclient279150.205.82

SQL*Netmessagefromclient680.7116.17

dbfilesequentialread20.000.00

我們可以看到fetch獲取的次數(shù)從481次降低到了68次，但是時間反而延長了！cache加大沒有看到時間變少！

從測試結(jié)果看,buffer越大并不代表導(dǎo)出時間越少，因為從讀取寫入cache的時間會更長，常規(guī)路徑寫的

時間消耗分為2部分：1.從table讀取數(shù)據(jù)寫入cache;

2.從cache寫入磁盤file文件中；

當(dāng)cache加大時，第一個步驟的時間加大了，由于測試數(shù)據(jù)不夠多，了解即可。

2.1.3.ExpDIRECT

參數(shù)DIRECT：常規(guī)路徑導(dǎo)出與直接路徑導(dǎo)出(文檔ID155477.1)

OracleDatabase-EnterpriseEdition-Version7.3.0.0to[Release7.3.0to11.2]

1.2.ConventionalpathExport.

ConventionalpathExportusestheSQLSELECTstatementtoextractdatafromtables.Dataisread

fromdiskintothebuffercache,androwsaretransferredtotheevaluatingbuffer.Thedata,

afterpassingexpressionevaluation,istransferredtotheExportclientwhichthenwritesthe

dataintotheexportfile.

1.3.DirectpathExport.

WhenusingaDirectpathExport,thedataisreadfromdiskdirectlyintotheexportsession's

programglobalarea(PGA):therowsaretransferreddirectlytotheExportsession'sprivate

buffer.ThisalsomeansthattheSQLcommand-processinglayer(evaluationbuffer)canbe

bypassed,becausethedataisalreadyintheformatthatExportexpects.Asaresult,unnecessary

dataconversionisavoided.ThedataistransferredtotheExportclient,whichthenwritesthe

dataintotheexportfile.

1.4.TheparameterDIRECTspecifieswhetheryouusethedirectpathExport(DIRECT=Y)orthe

conventionalpathExport(DIRECT=N).

1.5.TousedirectpathExport,specifytheDIRECT=Yparameteronthecommandlineorinthe

parameterfile.ThedefaultisDIRECT=N,whichextractsthetabledatausingtheconventional

path.

1.6.Thisparameterisonlyapplicabletotheoriginalexportclient.ExportDataPump(expdp)

usesaDirectPathunloadbydefaultandswitchestoExternalTablemodeifrequired.

2.Performanceissues.

2.1.DirectpathExportcanbemuchfasterthanConventionalpathExportbecausetheSQLcommand-

processinglayerisbypassed.

2.2.YoumaybeabletoimproveperformancebyincreasingthevalueoftheRECORDLENGTHparameter

whenyouinvokeadirectpathExport.Yourexactperformancegaindependsuponthefollowing

factors:

-DB_BLOCK_SIZE

-thetypesofcolumnsinyourtable

-yourI/Olayout(thedrivereceivingtheexportfileshouldbeseparatefromthediskdrive

wherethedatabasefilesreside)

ThefollowingvaluesaregenerallyrecommendedforRECORDLENGTH:

-multiplesofthefilesystemI/Oblocksize

-multiplesofDB_BLOCK_SIZE

Forexample,invokingaDirectpathExportwithamaximumI/Obufferof64kbcanimprovethe

performanceoftheExportwithalmost50%.Thiscanbeachievedbyspecifyingtheadditional

ExportparametersDIRECTandRECORDLENGTH.E.g.:

>exp<LOGIN>/<PASSWORD>FILE=<DUMP_NAME>.dmpLOG=<LOG_NAME>>.log\

FULL=yDIRECT=yRECORDLENGTH=65535―

>imp<LOGIN>/<PASSWORD>FILE=<DUMP_NAME>.dmpLOG=<LOG_NAME>.log\

FULL=yRECORDLENGTH=65535

2.3.ADirectpathExportdoesnotinfluencethetimeittakestoImportthedata.Thatis,an

exportfilecreatedusingDirectpathExportorConventionalpathExport,willtakethesame

amountoftimetoImport.

3.4.導(dǎo)出參數(shù)QUERYo

參數(shù)QUERY僅適用于常規(guī)路徑導(dǎo)出。不能在直接路徑導(dǎo)出（DIRECT=Y）中指定。此QUERY參數(shù)允許您在執(zhí)行表模式

導(dǎo)出時從一組表中選擇行的子集。

例子：

>exp<LOGIN>/<PASSWORD>TABLES=<TABLE_NAME>QUERY=\"WHERE<COLUMN_NAME1>=\1<VALUE1>\'和

<COLUMN_NAME2>\<VALUE2>\"

3.5.導(dǎo)出參數(shù)BUFFERo

BUFFER參數(shù)僅適用于常規(guī)路徑導(dǎo)出。它對直接路徑導(dǎo)出沒有影響。此BUFFER參數(shù)指定用于獲取行的緩沖區(qū)的大小

（以字節(jié)為單位）。它確定由Export獲取的數(shù)組中的最大行數(shù)。對于直接路徑導(dǎo)出，使用RECORDLENGTH參數(shù)指定

Export用于寫入導(dǎo)出文件的緩沖區(qū)大小。

3.6.導(dǎo)出參數(shù)RECORDLENGTHo

RECORDLENGTH參數(shù)指定文件記錄的長度（以字節(jié)為單位）。您可以使用此參數(shù)指定導(dǎo)出I/O緩沖區(qū)的大小（最大值

為64kb）。更改RECORDLENGTH參數(shù)僅影響寫入磁盤之前累積的數(shù)據(jù)大小。它不影響操作系統(tǒng)文件塊的大小。如果

您未定義此參數(shù)，則它默認(rèn)為與平臺相關(guān)的BUFSIZ值（大多數(shù)情況下為1024字節(jié)）。

如果有遷移項目必須使用exp時可以考慮仔細(xì)閱讀上述mos文章，其中我們梳理重點信息：

源端db版本,可以filter大部分的異常！

1.默認(rèn)情況下exp的導(dǎo)出是先讀取到內(nèi)存中的buffer,buffer的大小由exp參數(shù)BUFFER控制,數(shù)據(jù)寫

入足夠的buffer之后，寫入導(dǎo)出的文件；

2.如果使用直接路徑寫后,exp直接讀取數(shù)據(jù)寫入磁盤中的文件；

3.直接路徑寫or常規(guī)路徑導(dǎo)出對導(dǎo)入時間無影響；

4.直接路徑寫的cache和什么有關(guān)系呢？和另一個參數(shù)RECORDLENGTH最大64kB,達到這個參數(shù)

值開始寫入磁盤數(shù)據(jù)，默認(rèn)與平臺BUFSIZE值相同1024bytes;

2.1.4.Exp導(dǎo)出測試時間對比

SQL>createuserexp_useridentifiedby"exposer";

SQL>grantdbatoexp_user;

--制造一個大表進行測試

SQL>sqlplusyz/yz

createtabletest__biglasselect*fromdba_objects;

insertintotest_biglselect*fromtest_bigl;

r

r

SQL>commit;

SQL>selectownersegment_name^segment_type>bytes/1024/1024fromdba_segmentswhereowner=*YZ

andsegment_name="TEST_BIG1";

OWNER-一SEGMENT_NAMESEGMENT_TYPEBYTES/1024/1024

YZTEST_BIG1TABLE1348

SQL>SELECTCURRENT_SCNFROMV$DATABASE;

CURRENT_SCN

26238040

--排除測試干擾，重啟測試庫執(zhí)行導(dǎo)出操作！?。?/p>

SQL>shutdownimmediate;

startup

[oracle@t3~]$vi/home/oracle/exp_schema.par

userid="exp__user/exp__user"

OWNER=YZ

FILE=/home/oracle/exp__schema__yz01.dmp

LOG=/home/oracle/exp_schema_yz01.log

FLASHBACK_SCN=26238040

statistics=none

direct=N

feedback=10000

--buffer=1024000

--FLASHBACK導(dǎo)出SCN號

--statistics是否遷移統(tǒng)計信息，否

--buffer導(dǎo)出cache大小，滿足cache時寫入file

--direct是否直接路徑寫

--FEEDBACK顯示每X行(0)的進度

nohuptimeexpparfile=exp_schema.par&

序列direbufferRECORDLENGTHDump_size(Mb)Time

Ct(bytes)

常規(guī)導(dǎo)出1N默認(rèn)0815real0m40.837s

user0m10.040s

sys0ml.556s

常規(guī)導(dǎo)出2N10240000815real0m39.497s

userOmlO.OHs

sys0m0.690s

直接路徑導(dǎo)3Y0默認(rèn)802real0m26.793s

user0m0.360s

sys0m0.830s

直接路徑導(dǎo)4Y065536802real0m24.982s

user0m0.133s

sys0m0.476s

2.2.Exp導(dǎo)出問題

2.2.1.Exp使用直接路徑寫,導(dǎo)入空的分區(qū)會報錯

參數(shù)DIRECT：常規(guī)路徑導(dǎo)出與直接路徑導(dǎo)出(文檔ID155477.1)

警告：如果存在空表分區(qū)，直接路徑導(dǎo)出(EXP)會損壞轉(zhuǎn)儲(文檔ID1604983.1)

OracleDatabase-EnterpriseEdition-Versionandlater

Youperformedadirectpathexport(table,schemaorfull)usingthetraditionalexportutility

(EXP).Iftheexportedobjectsincludeanemptytablepartition,thentheexportdumpiscorrupt

andcannotbeimported.

Onlyexpversions>=areaffected.Thetraditionalexportutilityisde-supported

beginningwiththeversion11gandisnomoremaintained.

實驗測試，創(chuàng)建一個分區(qū)表,制造一個空的分區(qū)沒有數(shù)據(jù)，沒有segments

connectyz/yz

createtablepart001

(

col001number

col002varchar2(100)

)

partitionbyrange(col001)

(

partitionp001valueslessthan(10),

partitionp002valueslessthan(100),

partitionp003valueslessthan(1000)

)；

insertintopart001values(5,'Text5');

insertintopart001values(500,'Text500');

commit;

實驗直接路徑寫，進行導(dǎo)出導(dǎo)入測試

#>expyz/yzfile=part001.dmptables=part001direct=y

Thiswillshow:

AbouttoexportspecifiedtablesviaDirectPath???

?,exportingtablePART001

,,exportingpartitionP0011rowsexported

?,exportingpartitionP0020rowsexported

,,exportingpartitionP0031rowsexported

Exportterminatedsuccessfullywithoutwarnings.

SQL>droptablepart001;

[oracle@t3~]$impyz/yzfile=part001.dmpfull=y

Import:Release.0-ProductiononWedMay2511:45:272022

Copyright(c)1982,2011,Oracleand/oritsaffiliates.Allrightsreserved.

Connectedto:OracleDatabase11gEnterpriseEditionRelease.0-64bitProduction

WiththePartitioning,OLAP,DataMiningandRealApplicationTestingoptions

ExportfilecreatedbyEXPORT:Vil.02.00viadirectpath

importdoneinUS7ASCIIcharactersetandAL16UTF16NCHARcharacterset

importserverusesAL32UTF8characterset(possiblecharsetconversion)

,importingYZ'sobjectsintoYZ

?importingYZ'sobjectsintoYZ

,,importingpartition"PART001":"P001"1rowsimported

,,importingpartitionMPART001":"P002"

IMP-00009:abnormalendofexportfile

Importterminatedsuccessfullywithwarnings.

一使用參數(shù)show可以查詢異常信息

[oracle@t3impyz/yzfile=part001.dmpfull=yshow=y

Import:Release.0-ProductiononWedMay2511:46:112022

Copyright(c)1982,2011,Oracleand/oritsaffiliates.Allrightsreserved.

Connectedto:OracleDatabase11gEnterpriseEditionRelease.0-64bitProduction

WiththePartitioning,OLAP,DataMiningandRealApplicationTestingoptions

ExportfilecreatedbyEXPORT:V11.02.00viadirectpath

importdoneinUS7ASCIIcharactersetandAL16UTF16NCHARcharacterset

importserverusesAL32UTF8characterset(possiblecharsetconversion)

,importingYZ'sobjectsintoYZ

?importingYZ'sobjectsintoYZ

"CREATETABLE“PART001"("COL001"NUMBER,"COL002"VARCHAR2(100))PCTFREE1"

"0PCTUSED40INITRANS1MAXTRANS255TABLESPACE"USERS'*PARTITIONBYRANGE"

"("COL001")(PARTITION"P001"VALUESLESSTHAN(10)PCTFREE10PCTUSED4"

"0INITRANS1MAXTRANS255STORAGE(INITIAL8388608NEXT1048576MINEXTENTS1"

"FREELISTS1FREELISTGROUPS1BUFFER_POOLDEFAULT)TABLESPACE"USERS"LOGG"

"INGNOCOMPRESS,PARTITION"P002"VALUESLESSTHAN(100)PCTFREE10PCTUSED"

"40INITRANS1MAXTRANS255TABLESPACE"USERS"LOGGINGNOCOMPRESS,PARTITIO"

"N"P003nVALUESLESSTHAN(1000)PCTFREE10PCTUSED40INITRANS1MAXTRANS"

"255STORAGE(INITIAL8388608NEXT1048576MINEXTENTS1FREELISTS1FREELIST"

"GROUPS1BUFFER_POOLDEFAULT)TABLESPACE"USERS"LOGGINGNOCOMPRESS)"

,,skippingpartition"PART001":"P001"

..skippingpartition"PART001":"P002"

IMP-00009:abnormalendofexportfile

Importterminatedsuccessfullywithwarnings.

OracleMos的解決方案：

intheexportoutput(orlogfile),thenyouobtainacorruptdump.Youcanverifythedumpwith

thecommands:

#>impuser/passwfull=y

or:

#>impuser/passwfull=yshow=y

whichwillshowyouoneofthebehaviorslistedabove.

Toworkaroundthispleaseuse:

-conventionalpathexport(expdirect=n)

Or:

-materializetheemptypartitionsbeforerunningdirectpathexports:

connect/assysdba

execdbms_space_admin.materialize__deferred_segments(schema_name=>'TEST*,table_name=>

'PART001',partition_name=>'P0021);

Or:

-DataPumpexport(expdp)

嘗試一下exec的存儲過程的調(diào)用？為什么能規(guī)避問題？

一導(dǎo)入后是不完整的導(dǎo)入，數(shù)據(jù)會丟失！

selectPARTITION__NAMEcount(*)fromuser_segmentswheresegment_name='PART001'groupby

PARTITION__NAME;

PARTITION_NAMECOUNT(*)

P0011

可以發(fā)現(xiàn)Oraclellg新特性，段延遲創(chuàng)建，如果沒有數(shù)據(jù)則不在分配segments,那么直接路徑寫的方式會

導(dǎo)入報錯！但是我們可以使用存儲過程指定對沒有數(shù)據(jù)的空的分區(qū)，指定創(chuàng)建一個segments,這樣再次使用

exp遷移不報錯！

新建測試表進行測試

--user

createtablepart002

(

col001number,

col002varchar2(100)

)

partitionbyrange(col001)

(

partitionp001values