Data Purging and Restoration

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..


Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

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.
Hi, just wondered if you had managed to resolve your purge and restoration issue.
HarekrushnaPanigrahyAuthor Commented:
can u give some idea about  REMAP_SCHEMA options how it works.
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.


Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Oracle Database

From novice to tech pro — start learning today.