Solved

REcovering deleted records from oracle database.

Posted on 2008-06-13
5
805 Views
Last Modified: 2008-09-27
I have mistaken deleted records from a table in a database ( Oracle) , How can i retrieve the information back into the database.
0
Comment
Question by:FranklinRaj22
5 Comments
 
LVL 47

Accepted Solution

by:
schwertner earned 168 total points
ID: 21778353
Have you committed the transaction? If not then issue 'rollback' command.
If you have commited the try to flashback the table:

SELECT * FROM my_table AS OF TIMESTAMP (sysdate - 0.3);

If you see the deleted records create a new table:

CREATE TABLE copy_my_table AS (SELECT * FROM my_table AS OF TIMESTAMP (sysdate - 0.3));

0.3 stands for fraction of the day, so it means close to 7 hours in the past.
0
 
LVL 34

Assisted Solution

by:johnsone
johnsone earned 166 total points
ID: 21778374
If that does not work and your database is in archive log mode, you should be able to use Log Miner to recover the records.
0
 
LVL 16

Assisted Solution

by:Richard Olutola
Richard Olutola earned 166 total points
ID: 21778993
If you are NOT in archivelog mode, I dread to think, you have 2 options.

1. Shutdown and restore from backup

2. Create a duplicate instance, restore to last cold backup, export the table and import it into the live instance.

If you are in ARCHIVELOG mode, then don't panic, you don't have to lose data.

R.

0

Featured Post

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

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

Suggested Solutions

Note: this article covers simple compression. Oracle introduced in version 11g release 2 a new feature called Advanced Compression which is not covered here. General principle of Oracle compression Oracle compression is a way of reducing the d…
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.
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
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…

832 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