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

SQL> DROP TABLE STANDBY CASCADE CONSTRAINTS;

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.

Advertisements

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