How to back up a table and import data from dump file

There are two approaches to back up a table:

1: Back up a table under the exsiting schema by creating a new table with tablename_bck nameing convention.

   Simply run the following query under the current schema :

   Creat table tablename_bck as select * from <tablename>;

2: Export the table to a dump file.

    Open a command line tool .Use Oracle utility tool expdp

expdp sample/samplepwd@db10g tables=EMP,DEPT directory=TEST_DIR dumpfile=EMP_DEPT.dmp logfile=expdpEMP_DEPT.log

3: Import the dump file into database.

impdp sample/samplepwd@db10g tables=EMP,DEPT directory=TEST_DIR dumpfile=EMP_DEPT.dmp logfile=impdpEMP_DEPT.log

More thoughts:

    I would also like to note about the old utility tool exp and imp.

    The syntax is slightly different. Without creating a reusable directory, in exp and imp, you have to specify explicitly of the directory where your dump file is saved to.

exp sample/samplepwd@db10g tables=EMP,DEPT file="C:\EMP_DEPT.dmp" log="C:\impdpEMP_DEPT.log"
imp sample/samplepwd@db10g tables=EMP,DEPT file="C:\EMP_DEPT.dmp" log="C:\impdpEMP_DEPT.log"

  Error: ORA-39143: dump file “M:\SAMPLE.dmp” may be an original export dump file


     The above problem happened whenever you try to use the Import Data Pump client (impdp) to import a dumpfile that was created with the original Export client (exp).

Posted in ETL

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