Home » SQL & PL/SQL » SQL & PL/SQL » deleted records (Oracle 9i)
deleted records [message #481893] Mon, 08 November 2010 02:36 Go to next message
ramesh55.sse
Messages: 262
Registered: December 2008
Location: Hyderabad
Senior Member
Hi Experts,

I have deleted all the records from the table.And I have commited.

Now I want to get all the records back.

How can we do that please help me.

As per my knowledge by using flash back queries we can do that.But I don't know hoe to use it.

Please help me .

Thanks in advance.
Re: deleted records [message #481898 is a reply to message #481893] Mon, 08 November 2010 02:53 Go to previous messageGo to next message
ThomasG
Messages: 3211
Registered: April 2005
Location: Heilbronn, Germany
Senior Member
SELECT * FROM your_table
   AS OF TIMESTAMP (to_timestamp('08-NOV-10 10:00:00','DD-MON-YY HH24: MI: SS'))


And have a look here.

[Updated on: Mon, 08 November 2010 02:55]

Report message to a moderator

Re: deleted records [message #481899 is a reply to message #481893] Mon, 08 November 2010 03:02 Go to previous messageGo to next message
Michel Cadot
Messages: 68257
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Database SQL Reference

Regards
Michel
Re: deleted records [message #481928 is a reply to message #481898] Mon, 08 November 2010 08:10 Go to previous messageGo to next message
ramesh55.sse
Messages: 262
Registered: December 2008
Location: Hyderabad
Senior Member
Thanks for your reply.

But I didn't remember the exact time.

In that case How can I get the records.

Please help me.
Re: deleted records [message #481929 is a reply to message #481928] Mon, 08 November 2010 08:16 Go to previous messageGo to next message
John Watson
Messages: 8738
Registered: January 2010
Location: Global Village
Senior Member
Run the query for a few times - you must know about when you did it - and save the results. And be quick! You may already be too late....

If you are too late (because the undo has expired) then you could use the log miner (assuming that you are in archive log mode?)
Re: deleted records [message #481930 is a reply to message #481928] Mon, 08 November 2010 08:17 Go to previous messageGo to next message
cookiemonster
Messages: 13894
Registered: September 2008
Location: Rainy Manchester
Senior Member
You know roughly when you deleted the data don't you?
All you need is to use any time when the records existed.
Re: deleted records [message #481931 is a reply to message #481930] Mon, 08 November 2010 08:23 Go to previous messageGo to next message
ramesh55.sse
Messages: 262
Registered: December 2008
Location: Hyderabad
Senior Member
Let us I have deleted the records by 10:00 A.M ,

But I run the flashback by 08:00 P.M.
Then what are the modification done on that table will be role backed or not.

Re: deleted records [message #481933 is a reply to message #481931] Mon, 08 November 2010 08:28 Go to previous messageGo to next message
cookiemonster
Messages: 13894
Registered: September 2008
Location: Rainy Manchester
Senior Member
When you query a table as of a specific point in time you get the data as it was at that point in time.
If the data was changed a lot and you want to get it as it was immediately before it got deleted then you are just going to have to try lots of different times and use the latest one for which the data sill exists.

[Updated on: Tue, 09 November 2010 03:53]

Report message to a moderator

Re: deleted records [message #481934 is a reply to message #481933] Mon, 08 November 2010 08:38 Go to previous messageGo to next message
ramesh55.sse
Messages: 262
Registered: December 2008
Location: Hyderabad
Senior Member
I deleted records by 10:00 A.M ,but I want to get those records by 08:00 P.m is it possible to do it by flashback queries,
If it is not possible .please provide me the alternative method.

Thanks
Re: deleted records [message #481936 is a reply to message #481934] Mon, 08 November 2010 08:41 Go to previous messageGo to next message
cookiemonster
Messages: 13894
Registered: September 2008
Location: Rainy Manchester
Senior Member
ramesh55.sse wrote on Mon, 08 November 2010 14:38
I deleted records by 10:00 A.M ,but I want to get those records by 08:00 P.m is it possible to do it by flashback queries

If the data is still in the undo yes. If you're lucky it might still be there. If you waste hours asking us variations on the same question I can pretty much guarantee it won't be.
Thomas gave you the query - try it!

ramesh55.sse wrote on Mon, 08 November 2010 14:38

If it is not possible .please provide me the alternative method.

John already did - logminer
Re: deleted records [message #481937 is a reply to message #481931] Mon, 08 November 2010 08:41 Go to previous messageGo to next message
John Watson
Messages: 8738
Registered: January 2010
Location: Global Village
Senior Member
This is beginning to sound like a homework question. In which case you must review your understanding of the process:
A committed transaction can NEVER be rolled back. Through use of a flashback query you might be able to construct another transaction which will reverse the effect of the first transaction. But it might not work - can you think of reasons why not? There are several....
Re: deleted records [message #481943 is a reply to message #481937] Mon, 08 November 2010 09:05 Go to previous messageGo to next message
ramesh55.sse
Messages: 262
Registered: December 2008
Location: Hyderabad
Senior Member
You are telling commited transaction is not roll backed.
But uncomited transaction we can rollback it directly.
But what is the advantage of Flash back.
Re: deleted records [message #481946 is a reply to message #481943] Mon, 08 November 2010 09:19 Go to previous messageGo to next message
cookiemonster
Messages: 13894
Registered: September 2008
Location: Rainy Manchester
Senior Member
To find out what state data was in at a particular point in time.
What's so difficult to understand about that?
Re: deleted records [message #482026 is a reply to message #481946] Tue, 09 November 2010 03:01 Go to previous messageGo to next message
ramesh55.sse
Messages: 262
Registered: December 2008
Location: Hyderabad
Senior Member
Please help me to how to find the state of data was in at a particular point in time.

Thanks
Re: deleted records [message #482032 is a reply to message #482026] Tue, 09 November 2010 03:24 Go to previous messageGo to next message
flyboy
Messages: 1901
Registered: November 2006
Senior Member
ramesh55.sse wrote on Tue, 09 November 2010 10:01
Please help me to how to find the state of data was in at a particular point in time.

And we are back in the beginning. The only option in Oracle 9.2 (you stated 9i as your Oracle version) is the flashback query, which was posted in the very first reply by ThomasG.

If this is the real problem, you are probably out of luck as the undo data is stored only for limited time period, which depends on some configuration and number and amount of transactions after that time. So, it still might be available.

If this is just some interview/exam preparation, what about reading the documentation from the links which were provided here?
Re: deleted records [message #482035 is a reply to message #482032] Tue, 09 November 2010 03:36 Go to previous messageGo to next message
Littlefoot
Messages: 21736
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
There are some more options - importing a table from the (last) export file, or restoring it from the backup. There are certain disadvantages here (such as: you don't backup your database so - there's nothing to restore from; or, backup is rather old so you can't restore all records you deleted).
Re: deleted records [message #482039 is a reply to message #482035] Tue, 09 November 2010 04:18 Go to previous message
ThomasG
Messages: 3211
Registered: April 2005
Location: Heilbronn, Germany
Senior Member
Another option would be to look into a possibly existing secondary backup.

Previous Topic: SQL Reporting using Cursor
Next Topic: Very Urgent Please help me
Goto Forum:
  


Current Time: Tue Aug 09 05:37:02 CDT 2022