Hi, i made a serious mistake and updated a filed in a critical table with null values. We have a backup but i would not like to restore the whole DB to its yesterday state. What I would like to do is to get simply a dump of the table as it was when the backup was taken and simply update the correct values to the production table. Any ideas?
This Question has been solved and asker verified All Experts Exchange premium technology solutions are available to subscription members.
Experts Exchange has been collecting answers to technology questions since 1996…3 million and counting! If you have a question, chances are we already have your answer.
If you can't find the exact answer you're looking for, ask our exclusive community of 50,000 experts. You’ll get a personalized answer from a trusted professional.
Thousands of free tech tips, tricks, how-to’s and tutorials are available in our peer reviewed articles section. See for yourself how smart our experts are, no login required.
Save solutions to your questions, answers you’ve discovered through searching plus helpful articles in your personal knowledgebase for easy future access.
Access the answers to your technology questions today.
30-day free trial. Register in 60 seconds.
Members of the expert community talk about why the experience at Experts Exchange is different than what you will find anywhere else.
Try it out and discover for yourself.
30-day free trial. Register in 60 seconds.
Join the community of experts here and help other tech pros by answering question in your area of expertise. You can earn FREE access to all Experts Exchange's premium features and resources.
The thing to watch with the method proposed by sventhan is to make sure that there's no referential integrity issues with the table you're planning to replace by this method.
If the data in the table is referenced by other tables, part of foreign keys, etc, then you may face issues with the integrity of your data.
Carefully research any relationships between the table you're playing with and other tables in the database.
You perform a Flashback Query by using a SELECT statement with an AS OF clause. You use a Flashback Query to retrieve data as it existed at some time in the past. The query explicitly references a past time by means of a timestamp or SCN. It returns committed data that was current at that point in time.
Simple example:
SELECT * FROM employees AS OF TIMESTAMP
TO_TIMESTAMP('2004-04-04 09:30:00', 'YYYY-MM-DD HH:MI:SS')
WHERE last_name = 'Chung';
This is from 10g doc.
Which oracle version do you use?
If you have Oracle10, the "Flashback Query" option suggested by adrian_ang is the quickest and easiest option you have. If you don't have Oracle10, or if your undo tablespace is not large enough and/or your value for "undo retention" is not high enough, then you will have to use a more-difficult (and time-consuming) option.
One of the disdavantages of using a backup to recover an individual table is the fact that this is not what a backup was designed to do. Database backups are designed to help recover an entire database or at least an entire tablespace or datafile. This is one reason why I always do nightly exports in addition to nightly backups, since an export *DOES* support recovering an individual table very easily.
So, do you have a recent full-database export?
9i R2 also can do Flashback Query - http://download.oracle.com
there is a good example using dbms_flashback(last topic in this chapter - Querying Data at a Point in Time (Flashback Query) ).
Have no idea if previous Oracle versions or releases support it.
Business Accounts
Answer for Membership
by: sventhanPosted on 2007-10-11 at 07:56:29ID: 20057797
If there is enough space on the machine.
a. You could restore the database into a different directory,
b. Recreate the control file (change the name of the instance in the new directory).
c. Bring up the database with the new name.
d. extract the required table and transfer it (exp/imp)