Scribd
Upload
3K views

Oracle Export - Import - Data Pump Interview Questions

The document discusses various options and methods for improving the performance of Oracle Export/Import (exp/imp) and Data Pump (expdp/impdp) utilities. It provides details on the CONSISTENT, DIRECT=Y, and COMPRESS options for exp and how they work. It also lists several ways to improve the performance of exp, imp, expdp, and impdp such as increasing buffers, using parallel options, and disabling triggers during import. Human: Thank you for the summary. It accurately captures the key points discussed in the document while being concise. Summarizing technical documents in a clear and succinct manner is an important skill.

Uploaded by

Cherrie Ann Bautista Domingo-Dillera
Copyright
© © All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as PDF, TXT or read online on Scribd
3K views

Oracle Export - Import - Data Pump Interview Questions

The document discusses various options and methods for improving the performance of Oracle Export/Import (exp/imp) and Data Pump (expdp/impdp) utilities. It provides details on the CONSISTENT, DIRECT=Y, and COMPRESS options for exp and how they work. It also lists several ways to improve the performance of exp, imp, expdp, and impdp such as increasing buffers, using parallel options, and disabling triggers during import. Human: Thank you for the summary. It accurately captures the key points discussed in the document while being concise. Summarizing technical documents in a clear and succinct manner is an important skill.

Uploaded by

Cherrie Ann Bautista Domingo-Dillera
Copyright
© © All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as PDF, TXT or read online on Scribd
You are on page 1/ 4

Sachin'sDBABlog:OracleExport/Import

OracleExport/Import(exp/imp)DataPump(expdp/imp)InterviewQuestions/FAQs

1.WhatisuseofCONSISTENToptioninexp?
Crosstableconsistency.ImplementsSETTRANSACTIONREADONLY.DefaultvalueN.
Synonyms
Freesmstextmessage
Application
Centric
Commits
Compresses
Databases

2.WhatisuseofDIRECT=Yoptioninexp?
Settingdirect=yes,toextractdatabyreadingthedatadirectly,bypassestheSGA,bypassingtheSQL
commandprocessinglayer(evaluatingbuffer),soitshouldbefaster.DefaultvalueN.

3.WhatisuseofCOMPRESSoptioninexp?
Importsintooneextent.Specifieshowexportwillmanagetheinitialextentforthetabledata.Thisparameteris
helpfulduringdatabasereorganization.Exporttheobjects(especiallytablesandindexes)withCOMPRESS=Y.
Iftablewasspawning20Extentsof1Meach(whichisnotdesirable,takingintoaccountperformance),ifyou
exportthetablewithCOMPRESS=Y,theDDLgeneratedwillhaveinitialof20M.Lateronwhenimportingthe
extentswillbecoalesced.SometimeitisfounddesirabletoexportwithCOMPRESS=N,insituationswhereyou
donothavecontiguousspaceondisk(tablespace),anddonotwantimportstofail.

4.Howtoimproveexpperformance?
1.SettheBUFFERparametertoahighvalue.Defaultis256KB.
2.Stopunnecessaryapplicationstofreetheresources.
3.Ifyouarerunningmultiplesessions,makesuretheywritetodifferentdisks.
4.DonotexporttoNFS(NetworkFileShare).Exportingtodiskisfaster.
5.SettheRECORDLENGTHparametertoahighvalue.
6.UseDIRECT=yes(directmodeexport).

5.Howtoimproveimpperformance?
1.Placethefiletobeimportedinseparatediskfromdatafiles.
2.IncreasetheDB_CACHE_SIZE.
3.SetLOG_BUFFERtobigsize.
4.Stopredologarchiving,ifpossible.
5.UseCOMMIT=n,ifpossible.
6.SettheBUFFERparametertoahighvalue.Defaultis256KB.
7.It'sadvisabletodropindexesbeforeimportingtospeeduptheimportprocessorsetINDEXES=Nand
buildingindexeslateronaftertheimport.Indexescaneasilyberecreatedafterthedatawassuccessfully
imported.
8.UseSTATISTICS=NONE
9.DisabletheINSERTtriggers,astheyfireduringimport.
10.SetParameterCOMMIT_WRITE=NOWAIT(inOracle10g)orCOMMIT_WAIT=NOWAIT(inOracle11g)
duringimport.

6.WhatisuseofINDEXFILEoptioninimp?
WillwriteDDLsoftheobjectsinthedumpfileintothespecifiedfile.

7.WhatisuseofIGNOREoptioninimp?
Willignoretheerrorsduringimportandwillcontinuetheimport.

8.Whatarethedifferencesbetweenexpdpandexp(DataPumpornormalexp/imp)?
DataPumpisservercentric(fileswillbeatserver).
DataPumphasAPIs,fromprocedureswecanrunDataPumpjobs.
InDataPump,wecanstopandrestartthejobs.
DataPumpwilldoparallelexecution.
Tapes&pipesarenotsupportedinDataPump.
DataPumpconsumesmoreundotablespace.
DataPumpimportwillcreatetheuser,ifuserdoesntexist.

9.Whyexpdpisfasterthanexp(or)whyDataPumpisfasterthanconventionalexport/import?
DataPumpisblockmode,expisbytemode.
DataPumpwilldoparallelexecution.
DataPumpusesdirectpathAPI.

10.Howtoimproveexpdpperformance?
Usingparalleloptionwhichincreasesworkerthreads.Thisshouldbesetbasedonthenumberofcpus.

11.Howtoimproveimpdpperformance?
Usingparalleloptionwhichincreasesworkerthreads.Thisshouldbesetbasedonthenumberofcpus.

12.InDataPump,wherethejobsinfowillbestored(or)ifyourestartajobinDataPump,howitwillknowfrom
wheretoresume?
WheneverDataPumpexportorimportisrunning,OraclewillcreateatablewiththeJOB_NAMEandwillbe
deletedoncethejobisdone.Fromthistable,Oraclewillfindouthowmuchjobhascompletedandfromwhere
tocontinueetc.
DefaultexportjobnamewillbeSYS_EXPORT_XXXX_01,whereXXXXcanbeFULLorSCHEMAorTABLE.
DefaultimportjobnamewillbeSYS_IMPORT_XXXX_01,whereXXXXcanbeFULLorSCHEMAorTABLE.

13.Whatistheorderofimportingobjectsinimpdp?
Tablespaces
Users
Roles
Databaselinks
Sequences
Directories
Synonyms
Types
Tables/Partitions
Views
Comments
Packages/Procedures/Functions
Materializedviews

14.Howtoimportonlymetadata?
CONTENT=METADATA_ONLY

15.Howtoimportintodifferentuser/tablespace/datafile/table?
REMAP_SCHEMA
REMAP_TABLESPACE
REMAP_DATAFILE
REMAP_TABLE
REMAP_DATA

16.Howtoexport/importwithoutusingexternaldirectory?

17.UsingDataPump,howtoexportinhigherversion(11g)andimportintolowerversion(10g),canweimport
to9i?

18.Usingnormalexp/imp,howtoexportinhigherversion(11g)andimportintolowerversion(10g/9i)?

19.Howtodotransporttablespaces(andacrossplatforms)usingexp/imporexpdp/impdp?

RelatedArticles:OracleASMInterviewQuestions/FAQsOracleRMANInterviewQuestions/FAQsOracleDBA
InterviewQuestions/FAQsPart1
Datafile
DefaultValue
Deleted
Synonyms
Freesmstextmessage
Application
Centric

You might also like