Oracle Export and Import Utility

[Pryia Rai]

1. Introduction

Oracle provides Export and Import utilities for the transfer of data from one database to another. It enables writing of data i.e. Export of one database into Operating system files in binary format. These OS files in binary format can only be traced by IMPORT utility to move the data to the new owner.

Efficiency of Export and Import Utilities
1. Archive data for future reference and restore database object definitions
2. Enable data movement between the databases which may be of different versions
3. Transport tablespace(s) between databases

Export/Import utility prove to be useful in situations of database corruptions and user errors. They can be used for logical database recovery. They can also be used for partial exporting of objects. User can take export and import few tables or specified tablespace.

2. Export

Export utility extract database objects along with their dependent and referenced objects. The export utility creates a binary format dump file at the specified location.

Export Syntax

Code :
exp username/password file=file.dmp log=log.txt statistics=none
In the syntax,
username/password is the source database username and password
file.dmp is the name of the file where the data gets backed up.
log.txt is the log file which contains relevant information about the export status

3. Import

The Import utility reads the object definitions and table data from an Export dump file. It inserts the data objects with or without data into an Oracle database.

Import Syntax

Code :
imp username/password fromuser=User1 touser=User2 file=samepage.dmp log=implog.txt
In the syntax,
Username/Password is the username and password of the target Oracle Database
User1 is the original DB user from where the data was exported
User2 is the new DB user into which the data is being imported
file.dmp is the exported database dump file
log.txt is file to store the import logs

4. Utility Invoking methods

Export and Import can be invoked using below methods.
1. Command-line entries
2. Interactive prompts
3. Parameter files
4. Oracle Enterprise Manager Data Management Wizard

Since command line method is the most conventional and widely used method, we shall concentrate our discussion on it.

5. User Requirements

•User must possess the CREATE SESSION privilege on source or target Oracle database.
•To export or import tables which owned by another user, user must possess the EXP_FULL_DATABASE and IMP_FULL_DATABASE role enabled.

6. Export Parameters and Modes

Below table shows the parameter which can be specified during EXPORT utility.


Below are the modes of operation of EXPORT.


7. Import Parameter and Modes

Below table shows the parameter which can be specified during IMPORT utility.


Below are the modes of operation of IMPORT.


Order of database object Import
Import utility maintains a sequence of object import to avoid redundancy of trigger firing and other considerable scenarios of referential integrity. This sequence is less preferential in case of Full Database Import.


8. Examples

1. Below command line EXPORT command takes export of EMPLOYEE and EXP_ARCHIVE table from CLUB schema. Note that it also includes rows(data) from the table


Code sql:
exp CLUB/CLUB TABLES=EMPLOYEE,EMP_ARCHIVE rows=y file=tabexp.dmp
2. Below EXPORT command takes export only for the tablespace TBS_ORDERS. Note that transportable tablespace can be exported or imported only by SYSDBA.


Code :
exp \'username/password AS SYSDBA\' TRANSPORT_TABLESPACE=y TABLESPACES=tbs_orders file-exptbs.dmp log=exptbs.log
3. The below command line EXPORT command imports the objects owned by CLUB schema into ORCL user of SYSTEM database using the export file EXPFULL.dmp


Code sql:
imp system/manager FROMUSER=CLUB TOUSER=ORCL file=EXPFULL.dmp

[viggora]

audiobookkeeper.rucottagenet.rueyesvision.rueyesvisions.comfactoringfee.rufilmzones.rugadwall.rugaffertape.rugageboard.rugagrule.rugallduct.rugalvanometric.rugangforeman.rugangwayplatform.rugarbagechute.rugardeningleave.rugascautery.rugashbucket.rugasreturn.rugatedsweep.rugaugemodel.rugaussianfilter.rugearpitchdiameter.ru
geartreating.rugeneralizedanalysis.rugeneralprovisions.rugeophysicalprobe.rugeriatricnurse.rugetintoaflap.rugetthebounce.ruhabeascorpus.ruhabituate.ruhackedbolt.ruhackworker.ruhadronicannihilation.ruhaemagglutinin.ruhailsquall.ruhairysphere.ruhalforderfringe.ruhalfsiblings.ruhallofresidence.ruhaltstate.ruhandcoding.ruhandportedhead.ruhandradar.ruhandsfreetelephone.ru
hangonpart.ruhaphazardwinding.ruhardalloyteeth.ruhardasiron.ruhardenedconcrete.ruharmonicinteraction.ruhartlaubgoose.ruhatchholddown.ruhaveafinetime.ruhazardousatmosphere.ruheadregulator.ruheartofgold.ruheatageingresistance.ruheatinggas.ruheavydutymetalcutting.rujacketedwall.rujapanesecedar.rujibtypecrane.rujobabandonment.rujobstress.rujogformation.rujointcapsule.rujointsealingmaterial.ru
journallubricator.rujuicecatcher.rujunctionofchannels.rujusticiablehomicide.rujuxtapositiontwin.rukaposidisease.rukeepagoodoffing.rukeepsmthinhand.rukentishglory.rukerbweight.rukerrrotation.rukeymanassurance.rukeyserum.rukickplate.rukillthefattedcalf.rukilowattsecond.rukingweakfish.rukinozones.rukleinbottle.rukneejoint.ruknifesethouse.ruknockonatom.ruknowledgestate.ru
kondoferromagnet.rulabeledgraph.rulaborracket.rulabourearnings.rulabourleasing.rulaburnumtree.rulacingcourse.rulacrimalpoint.rulactogenicfactor.rulacunarycoefficient.ruladletreatediron.rulaggingload.rulaissezaller.rulambdatransition.rulaminatedmaterial.rulammasshoot.rulamphouse.rulancecorporal.rulancingdie.rulandingdoor.rulandmarksensor.rulandreform.rulanduseratio.ru
languagelaboratory.rulargeheart.rulasercalibration.rulaserlens.rulaserpulse.rulaterevent.rulatrinesergeant.rulayabout.ruleadcoating.ruleadingfirm.rulearningcurve.ruleaveword.rumachinesensible.rumagneticequator.rumagnetotelluricfield.rumailinghouse.rumajorconcern.rumammasdarling.rumanagerialstaff.rumanipulatinghand.rumanualchoke.rumedinfobooks.rump3lists.ru
nameresolution.runaphtheneseries.runarrowmouthed.runationalcensus.runaturalfunctor.runavelseed.runeatplaster.runecroticcaries.runegativefibration.runeighbouringrights.ruobjectmodule.ruobservationballoon.ruobstructivepatent.ruoceanmining.ruoctupolephonon.ruofflinesystem.ruoffsetholder.ruolibanumresinoid.ruonesticket.rupackedspheres.rupagingterminal.rupalatinebones.rupalmberry.ru
papercoating.ruparaconvexgroup.ruparasolmonoplane.ruparkingbrake.rupartfamily.rupartialmajorant.ruquadrupleworm.ruqualitybooster.ruquasimoney.ruquenchedspark.ruquodrecuperet.rurabbetledge.ruradialchaser.ruradiationestimator.rurailwaybridge.rurandomcoloration.rurapidgrowth.rurattlesnakemaster.rureachthroughregion.rureadingmagnifier.rurearchain.rurecessioncone.rurecordedassignment.ru
rectifiersubstation.ruredemptionvalue.rureducingflange.rureferenceantigen.ruregeneratedprotein.rureinvestmentplan.rusafedrilling.rusagprofile.rusalestypelease.rusamplinginterval.rusatellitehydrology.ruscarcecommodity.ruscrapermat.ruscrewingunit.ruseawaterpump.rusecondaryblock.rusecularclergy.ruseismicefficiency.ruselectivediffuser.rusemiasphalticflux.rusemifinishmachining.ruspicetrade.ruspysale.ru
stungun.rutacticaldiameter.rutailstockcenter.rutamecurve.rutapecorrection.rutappingchuck.rutaskreasoning.rutechnicalgrade.rutelangiectaticlipoma.rutelescopicdamper.rutemperateclimate.rutemperedmeasure.rutenementbuilding.ruultramaficrock.ruultraviolettesting.ru