Export Import in Oracle – Examples

Sample export script to restrict data in 9i

exp abc_def_schema FILE=live_ie_am_130508.dmp LOG=live_ie_am_130508.log TABLES=table_a query=\"where fk_ad_id IN \(SELECT id FROM advert WHERE status=\’Published\’\)\" ROWS=y INDEXES=no GRANTS=N TRIGGERS=no CONSTRAINTS=n feedback=5000

Sample Import into 91 database

imp userid=abc_uk_pf/xxxxx55xx tables=(pafdata_new) file=pafdata_new_20090420_MP.dmp log=pafnew_imp_20090420.log rows=y indexes=y grants=y constraints=y

Sample export script to exclude data in 10g

expdp MOHSIN_P dumpfile=EXPDAT_ABC_UK_PS_DB2.dmp directory=EXPDP logfile=EXPDAT_ABC_UK_PS_DB2.LOG content=ALL schemas=ABC_UK_PS exclude=table:"in (‘ADVERT_MEDIA’, ‘EVENT_LOG’)"

Sample export script using PARAMETER file

parameter file
dumpfile= EXPDAT_ABC_UK_PS_DB2.dmp
exclude=table:"in (‘ADVERT_MEDIA’,’EVENT_LOG’,’AM_CHANGE_LOG’)"

Expdp Mohsin_p parfile=parameterfile name

To move file across the network

Log into the Destination box
Move to the correct directory and ensure a file with the same name doesn’t already exist
If exists: rm dump_filenamevi

Ll –h gives list in human friendly form
Find Ip address of current node: ifconfig
The data link is usually the eth0 interface

Then on the source machine: scp zipfilename ipaddress:/pathdirectory


scp ps.dmp.gz xxx.xxx.xxx.xxx:/u01/export/ps

To unzip
Go to the destination directory where the zip has been copied to and run:
gunzip ps.dmp.gz

Import DMP into Database 10g

Check the PAR file on the destination box to ensure the settings variables are correct.

Depending on what is being imported do the following:
Disable the constraints (to allow tabled to be dropped)
Then go through the different objects and drop whatever will be replaced. Ensure that the tables which were not dumped (i.e. MEDIA) are not dropped.
Do not drop DATA LINKS

impdp Mohsin_p parfile=parameterfile name

Compatibility Versions and Network Export

Add the VERSION= (or version compatibility level) to the export for backward compatibility.

Similar to SQLPLUS, add the Tns link to export over the network. The export will be saved on the source database server NOT the server the EXPDP is being run from.

expdp mohsin_p@Tns_DB dumpfile=EXP.dmp directory=EXPORT logfile=EXPDAT.LOG content=ALL schemas=SCHEMA_NAME VERSION=


Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: