Home » SQL & PL/SQL » SQL & PL/SQL » How To recall old data
How To recall old data [message #247571] Tue, 26 June 2007 07:24 Go to next message
kamran.it
Messages: 265
Registered: September 2005
Location: Karachi
Senior Member
hi expert

I need your help actual I have made a big mistake that we have a table fields

orderno
ppno

by mistake of mine 'ppno' replaced with 1234 in all records

now please let me know how to recall my actual old data.


Re: How To recall old data [message #247572 is a reply to message #247571] Tue, 26 June 2007 07:28 Go to previous messageGo to next message
pablolee
Messages: 2834
Registered: May 2007
Location: Scotland
Senior Member
rollback
Re: How To recall old data [message #247573 is a reply to message #247571] Tue, 26 June 2007 07:29 Go to previous messageGo to next message
sanka_yanka
Messages: 184
Registered: October 2005
Location: Kolkata
Senior Member

What database version are you using?(You need to mention.)
If you are using 10g then it is posible using flashback query.
Quote:

Note:
Do not shutdown the machine after deletion done and also do not shutdown the oracle instance.


Thanks & Regards
Sanka.
Re: How To recall old data [message #247576 is a reply to message #247573] Tue, 26 June 2007 07:32 Go to previous messageGo to next message
kamran.it
Messages: 265
Registered: September 2005
Location: Karachi
Senior Member
I m using oracle 9.2 database and I have used commit also.
Re: How To recall old data [message #247579 is a reply to message #247576] Tue, 26 June 2007 07:38 Go to previous messageGo to next message
Maaher
Messages: 7062
Registered: December 2001
Senior Member
If flashback is enabled you can use it in 9iR2:
SELECT * FROM <yourtable>
   AS OF TIMESTAMP (SYSTIMESTAMP - INTERVAL '1' DAY/HOUR/MINUTE)
   WHERE last_name = 'Chung';


Interval defines how far you want to go back. Beware that, if it is enabled, you probably can't get very far back. Flashback consumes quite some space.

MHE

[Updated on: Tue, 26 June 2007 07:39]

Report message to a moderator

Re: How To recall old data [message #247591 is a reply to message #247579] Tue, 26 June 2007 08:05 Go to previous messageGo to next message
Michel Cadot
Messages: 64116
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
you probably can't get very far back

More or less what is in "undo_retention" parameter (in seconds).

Regards
Michel
Re: How To recall old data [message #247595 is a reply to message #247579] Tue, 26 June 2007 08:22 Go to previous messageGo to next message
kamran.it
Messages: 265
Registered: September 2005
Location: Karachi
Senior Member
I M USING THIS BUT GIVE ME ERROR

SELECT * FROM MERCH_ORDER
AS OF TIMESTAMP (SYSTIMESTAMP - INTERVAL '1' 1/2/30)
WHERE PPNO <> 4034

eRROR

SQL> /
AS OF TIMESTAMP (SYSTIMESTAMP - INTERVAL '1' 1/2/30)
*
ERROR at line 2:
ORA-30089: missing or invalid <datetime field>
Re: How To recall old data [message #247608 is a reply to message #247595] Tue, 26 June 2007 08:58 Go to previous messageGo to next message
dmitry.nikiforov
Messages: 723
Registered: March 2005
Senior Member
INTERVAL DAY TO SECOND format:

http://download-uk.oracle.com/docs/cd/B10501_01/server.920/a96540/expressions9a.htm#1033525



SQL> SELECT ename FROM emp AS OF TIMESTAMP  (SYSTIMESTAMP - INTERVAL '1' DAY)
  2  /

ENAME
----------
SMITH
ALLEN
WARD
JONES
MARTIN
BLAKE
CLARK
SCOTT
KING
TURNER
ADAMS
JAMES
FORD
MILLER

14 rows selected.

SQL> SELECT ename FROM emp AS OF TIMESTAMP  (SYSTIMESTAMP - INTERVAL '1' HOUR)
  2  /

ENAME
----------
SMITH
ALLEN
WARD
JONES
MARTIN
BLAKE
CLARK
SCOTT
KING
TURNER
ADAMS
JAMES
FORD
MILLER

14 rows selected.


Rgds.
Re: How To recall old data [message #255893 is a reply to message #247608] Thu, 02 August 2007 05:11 Go to previous messageGo to next message
sibgat
Messages: 45
Registered: November 2006
Location: Chennai
Member


-- Data retrieving before 5 minutes

select * from emp as of timestamp(sysdate - 5/1440)

Rgds
Sibghat
Re: How To recall old data [message #255899 is a reply to message #255893] Thu, 02 August 2007 05:25 Go to previous messageGo to next message
Michel Cadot
Messages: 64116
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
And what is the plus of your post?
This has already been said in a more proper way.

Regards
Michel
Re: How To recall old data [message #260711 is a reply to message #247608] Mon, 20 August 2007 14:48 Go to previous messageGo to next message
charansundaram
Messages: 8
Registered: August 2007
Location: INDIA
Junior Member

When you use oracle 9i. Try with flashback query option.
Re: How To recall old data [message #260717 is a reply to message #260711] Mon, 20 August 2007 15:02 Go to previous messageGo to next message
Michel Cadot
Messages: 64116
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
I don't think flashback will work 2 months later. Cool

Regards
Michel
Re: How To recall old data [message #260720 is a reply to message #260717] Mon, 20 August 2007 15:07 Go to previous messageGo to next message
charansundaram
Messages: 8
Registered: August 2007
Location: INDIA
Junior Member
i will work based on Undo retention time.
Re: How To recall old data [message #260722 is a reply to message #260720] Mon, 20 August 2007 15:11 Go to previous messageGo to next message
Michel Cadot
Messages: 64116
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
You have an undo that can retain 2 months!
Wow! I'm impressed... or your database doesn't work. Cool

Regards
Michel
Re: How To recall old data [message #260731 is a reply to message #260722] Mon, 20 August 2007 15:30 Go to previous messageGo to next message
charansundaram
Messages: 8
Registered: August 2007
Location: INDIA
Junior Member
No place i told two months. i am wondering about your understanding power.
Re: How To recall old data [message #260807 is a reply to message #260711] Tue, 21 August 2007 00:30 Go to previous messageGo to next message
flyboy
Messages: 1832
Registered: November 2006
Senior Member
kamran.it wrote on Tue, 26 June 2007 14:24
now please let me know how to recall my actual old data.
charansundaram wrote on Mon, 20 August 2007 21:48
When you use oracle 9i. Try with flashback query option.

Quiz questions for charansundaram:
What is the time difference between kamran.it posted his question and your answer?
Do you think it would be useful for him if he had shorter undo retention time than this time span?
In addition, did you not find its demonstration call in the previous posts?
Re: How To recall old data [message #260840 is a reply to message #260731] Tue, 21 August 2007 01:53 Go to previous message
Maaher
Messages: 7062
Registered: December 2001
Senior Member
charansundaram wrote on Mon, 20 August 2007 22:30
No place i told two months. i am wondering about your understanding power.

The opening post is from Tue, 26 June 2007. That's roughly two months back. I hope that the OP has solved his problem by now.

Why don't we let this thread go back to sleep?

MHE
Previous Topic: WHICH ONE IS FASTER?
Next Topic: DIANA Nodes. PLS-00123: program too large.
Goto Forum:
  


Current Time: Tue Dec 06 02:35:35 CST 2016

Total time taken to generate the page: 0.14996 seconds