Use datapump(impdp) to import a table : Oracle 10g

—– Procedure to import table from production system to test system ——

1. Copy the backup(dump) of  schema onto /backup_restor_only

$ gzip -d <name of the file>

2. After this gzping finished logon to SQL*PLUS and type below commands

SQL> create or replace directory import_dir as ‘/backup_restor_only’;

SQL> grant read,write on DIRECTORY import_dir to public;

3. Now before proceeding, take backup of table and create script for the table and keep it.

4. Now drop the table


5. Now go back to command prompt and type below command

impdp abc/abc123 directory=import_dir dumpfile=ex_28042010.dmp logfile=imp_standby.log tables=STANDBY

Successfuly imported

6. Creating indexes and other constraints would be done by default by IMPDP. If not created, create it from the script you have.


Leave a Reply

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

You are commenting using your 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