Wednesday, November 2, 2011

Datapump Utility to Import tables into another schema

Using Datapump utility to import tables

Scenario:
Previous night export dumps
I have them stored as------> somename.dmp.Z ( compressed by using "compress" unix command)
$ uncompress somename.dmp.Z
$ somename.dmp             <-------------------- uncompressed dumpfile

user A is importing tables into user B ( user B owns those tables)
User A should have appropriate priviliges to import,  read/write on datapump directory & unlimited on tablespace quota

Truncate tables owned by B,
SQL> Truncate table B.table1

At this point we have table structure of table1 in schema B but no data
sql> select count(*) from B.table1
            0

As user A run the impdp
Using vi editor create a file say--> imp.sh ( can use it to run in background, as newbie's often execute it on command prompt only to realize they are stuck in front of computer)

$ vi imp.sh
impdp "userA/psswd" directory=DBA_DP_DIR dumpfile=latestexportdumpbackup.dmp logfile=somename.log tables=B.table1,B.table2 CONTENT=DATA_ONLY


                                 OR
****** if you have multiple dumpfiles to choose from ************

impdp "userA/psswd" directory=DBA_DP_DIR dumpfile=latest_expdump_backup_1.dmp, latest_expdump_2.dmp,.... ,latest_expdump_n logfile=somename.log tables=B.table1,B.table2,...,B,tablen CONTENT=DATA_ONLY

$ nohup ./imp.sh &
you can tail  the import process
$ tail -f nohup.out

Well this is an effort to get started, hoping to refine as we move ahead. Suggestions are welcome!

No comments:

Post a Comment