Home » SQL & PL/SQL » SQL & PL/SQL » How to recover deleted records? (merged)
How to recover deleted records? (merged) [message #215235] Sat, 20 January 2007 01:11 Go to next message
amit_vass2000
Messages: 52
Registered: December 2006
Location: Delhi
Member
hello sir,
I want to know that if some data has got deleted by the user and committed. then how can the data is recovered.


[Mod edit: deleted Poll]

[Updated on: Sat, 20 January 2007 02:05] by Moderator

Report message to a moderator

Re: data recovery [message #215238 is a reply to message #215235] Sat, 20 January 2007 01:22 Go to previous messageGo to next message
ebrian
Messages: 2794
Registered: April 2006
Senior Member
The docs are perfect for that (Backup and Recovery Basics).

BTW...not sure why this topic is labeled as a poll ?
Re: data recovery [message #215239 is a reply to message #215238] Sat, 20 January 2007 01:29 Go to previous messageGo to next message
amit_vass2000
Messages: 52
Registered: December 2006
Location: Delhi
Member
thanks for help.
i was clicking on create topic but by mistake i clicked on create poll.
Re: data recovery [message #215242 is a reply to message #215235] Sat, 20 January 2007 01:57 Go to previous messageGo to next message
amit_vass2000
Messages: 52
Registered: December 2006
Location: Delhi
Member
if someone have issued a command

delete from emp where hiredate between '1-jul-92' and '31-jul-92'

20 rows deleted

commit;

NOW IS THERE ANY COMMAND BY WHICH THIS DATA COULD BE RECOVERED.

THANX IN ADVANCE
how lost data recovered [message #215250 is a reply to message #215235] Sat, 20 January 2007 02:53 Go to previous messageGo to next message
amit_vass2000
Messages: 52
Registered: December 2006
Location: Delhi
Member
if someone have issued a query

delete from emp where hiredate between '1-jul-92' and '31-jul-92'

20 rows deleted

commit;

NOW IS THERE ANY COMMAND BY WHICH THIS DATA COULD BE RECOVERED.

THANX IN ADVANCE
Re: how lost data recovered [message #215256 is a reply to message #215250] Sat, 20 January 2007 03:33 Go to previous messageGo to next message
amit_vass2000
Messages: 52
Registered: December 2006
Location: Delhi
Member
plz someone among seniors, give me reply, its very urgent.
Re: how lost data recovered [message #215258 is a reply to message #215256] Sat, 20 January 2007 04:01 Go to previous messageGo to next message
Mahesh Rajendran
Messages: 10672
Registered: March 2002
Location: oracleDocoVille
Senior Member
Account Moderator
Do not crosspost. It helps nothing.
EBrian has already answered your question in the other thread.
Check the docs. It is called as Tablespace point-in-time recovery.
>>its very urgent.
If it is so urgent,
You should call Oracle support. This is just a forum.
Re: how lost data recovered [message #215284 is a reply to message #215256] Sat, 20 January 2007 08:51 Go to previous messageGo to next message
Mohammad Taj
Messages: 2412
Registered: September 2006
Location: Dubai, UAE
Senior Member

Hi,
you are not mention your oracle Version. if 10g. then you can use 'FLASHBACK' option.
Flashback Query 

you must choose an undo retention interval that is long enough to enable users to construct a snapshot of the database for the oldest version of the database that they are interested in.


SQL> conn sys as sysdba
Enter password:
Connected.
SQL> alter system set undo_retention = 3600;
System altered.
SQL> conn hr/hr
Connected.
SQL> alter session set nls_date_format = 'hh24:mi:ss';
Session altered.
SQL> select sysdate from dual;
SYSDATE
--------
19:04:49
SQL> create table hrtest ( no number, name varchar2(20));
Table created.
SQL> begin
2 insert into hrtest values ( 1, 'aaa');
3 insert into hrtest values ( 2, 'bbb');
4 insert into hrtest values ( 3, 'ccc');
5 end;
6 /
PL/SQL procedure successfully completed.
SQL> commit;
Commit complete.
SQL> select sysdate from dual;
SYSDATE
--------
19:06:23
SQL> delete from hrtest where name = 'ccc';
1 row deleted.
SQL> commit;
Commit complete.
SQL> select * from hrtest;
NO NAME
---------- --------------------
1 aaa
2 bbb

SQL> edWrote file afiedt.buf
1 select * from hrtest AS OF TIMESTAMP 2 to_timestamp ('2006-11-16 19:06:23','YYYY-MM-DD HH24:MI:SS')
3* where name = 'ccc'
4 /
NO NAME
---------- --------------------
3 ccc
SQL> insert into hrtest 2 ( select * from hrtest AS OF TIMESTAMP 3 to_timestamp ('2006-11-16 19:06:23','YYYY-MM-DD HH24:MI:SS')
4 where name = 'ccc');
1 row created.
SQL> select * from hrtest;
NO NAME
---------- --------------------
1 aaa
2 bbb
3 ccc


http://dbataj.blogspot.com/2006/11/flashback-oracle10gr1-101020.html

regards
Taj

[Updated on: Sat, 20 January 2007 08:55]

Report message to a moderator

Re: how lost data recovered [message #215413 is a reply to message #215284] Mon, 22 January 2007 03:42 Go to previous messageGo to next message
hari.choprala
Messages: 65
Registered: November 2006
Location: Mumbai
Member

Hi Taj

Please tell me is this procedure is applicable for 9i also?
Because iam using oracle 9i.

thanks & rgds

Hari Krishna
Re: How to recover deleted records? (merged) [message #215414 is a reply to message #215235] Mon, 22 January 2007 03:47 Go to previous messageGo to next message
tahpush
Messages: 961
Registered: August 2006
Location: Stockholm/Sweden
Senior Member

It's available in 9i as well, thou it has alot of improvments in 10g.

http://www.oracle-base.com/articles/9i/FlashbackQuery.php
Re: How to recover deleted records? (merged) [message #215418 is a reply to message #215414] Mon, 22 January 2007 03:52 Go to previous message
hari.choprala
Messages: 65
Registered: November 2006
Location: Mumbai
Member

hi Tahpush,


Thanks

Rgds
Hari Krishna
Previous Topic: how 2 store images in oracle 9i
Next Topic: Maximum constant values in IN operator in SQL query
Goto Forum:
  


Current Time: Sat Dec 03 04:15:38 CST 2016

Total time taken to generate the page: 0.10689 seconds