Data Purging and Restoration

Posted on 2008-01-31
Medium Priority
Last Modified: 2013-12-18
Hi  Experts,
                   I have an application which schedules the purging activity. and purges the set of tables at run time(tables to be purged gets at run time).
It creates an external table using oracle datapump. after deleting the records drops the external table  and the same name is used for diff tables. Now i have the *.dmp  which contains the purged data. now i want to restore the data from those dumps.?  mean while the table structure has been changed ... some col s added and some droped...

plz suggest an purge and  restoration approach..


Question by:HarekrushnaPanigrahy
  • 3
LVL 16

Expert Comment

ID: 20786241
Hi Harekrushna,

Not sure from your description exactly what you are doing, a fuller explanation would be useful.

As far as using oracle datadump to load from a dmp file into a table with a different definition this can't be done.  To get around this you will need to import the data from the dmp file into a schema where the table doesn't already exists.  You will need to use the REMAP_SCHEMA option to import the table to a schema that is different to the one from which it was originally exported.

You will then need to write a specific insert select statement to insert the required rows into the destination table (the one that was original exported, but which now has different columns).  The imported table can then be dropped.
LVL 16

Expert Comment

ID: 20865851
Hi, just wondered if you had managed to resolve your purge and restoration issue.

Author Comment

ID: 21003342
can u give some idea about  REMAP_SCHEMA options how it works.
LVL 16

Accepted Solution

Milleniumaire earned 1000 total points
ID: 21033189
Hi, sorry for the delay in getting back to you.

The REMAP_SCHEMA option of Oracle data pump allows you to import data into a different schema to the one from which it was exported.

For example, assume data pump is used to export the table EMP from the SCOTT schema to the file mine.dmp:

expdp scott/tiger TABLES=EMP DIRECTORY=datapump DUMPFILE=mine.dmp LOGFILE=full.log

If you then import from the mine.dmp file, it will re-create or re-populate the EMP table in the SCOTT schema:

impdp scott/tiger DIRECTORY=datapump DUMPFILE=mine.dmp LOGFILE=full.log

If you prefer this table to be imported into another Oracle account, you would use the REMAP_SCHEMA option as follows:

impdp scott/tiger REMAP_SCHEMA=scott:fred DIRECTORY=datapump DUMPFILE=mine.dmp LOGFILE=full.log

This would result in a new EMP table being created in fred's Oracle account even though scott was used as the account to run the import.


Featured Post

2018 Annual Membership Survey

Here at Experts Exchange, we strive to give members the best experience. Help us improve the site by taking this survey today! (Bonus: Be entered to win a great tech prize for participating!)

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Using SQL Scripts we can save all the SQL queries as files that we use very frequently on our database later point of time. This is one of the feature present under SQL Workshop in Oracle Application Express.
From implementing a password expiration date, to datatype conversions and file export options, these are some useful settings I've found in Jasper Server.
This video shows how to copy a database user from one database to another user DBMS_METADATA.  It also shows how to copy a user's permissions and discusses password hash differences between Oracle 10g and 11g.
Video by: Steve
Using examples as well as descriptions, step through each of the common simple join types, explaining differences in syntax, differences in expected outputs and showing how the queries run along with the actual outputs based upon a simple set of dem…
Suggested Courses

593 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question