OraFAQ Forum: SQL & PL/SQL » how to fetch deleted row

Home » SQL & PL/SQL » SQL & PL/SQL » how to fetch deleted row (oracle 11g)

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

how to fetch deleted row [message #646204]

Tue, 22 December 2015 01:28

rahuljadhav177@gmail.com
Messages: 7
Registered: December 2015

Junior Member

Hello sir

i m using oracle 11g i have deleted some row by mistake and commit transaction.
now i want to fetch this row in a regarding table.i have tried using flashback funtion.

SELECT * FROM employees
AS OF TIMESTAMP
TO_TIMESTAMP('2004-04-04 09:30:00', 'YYYY-MM-DD HH:MI:SS')
WHERE last_name = 'Chung';

But it has some time and date limitation. i know ony date but i doesn't remember actual time of deleted row...
can i fetch this row through query is any way please suggest me.

Thank You

Report message to a moderator

Re: how to fetch deleted row [message #646205 is a reply to message #646204]

Tue, 22 December 2015 01:37

John Watson
Messages: 8931
Registered: January 2010
Location: Global Village

Senior Member

Are you really trying o run a flashback query that goes back eleven years? You would need an undo tablespace the size of Jupiter for that to succeed. You may find that using the Log Miner facility is more effective - if you have all the archive logfiles for that day, and the structure of the table has not been changed since then. Analyzing a whole day of redo may be difficult and slow, but it should give you what you need.

Report message to a moderator

Re: how to fetch deleted row [message #646208 is a reply to message #646204]

Tue, 22 December 2015 02:08

Michel Cadot
Messages: 68645
Registered: March 2007
Location: Nanterre, France, http://...

Senior Member
Account Moderator

And before posting a new question, you should envisage to feedback and thank people in your yesterday topic.

Report message to a moderator

Re: how to fetch deleted row [message #646228 is a reply to message #646208]

Tue, 22 December 2015 08:42

Bill B
Messages: 1971
Registered: December 2004

Senior Member

You don't use the date it was inserted, you use the date it was deleted. And if it really is from 2004, what took you so long?

Report message to a moderator

Re: how to fetch deleted row [message #646299 is a reply to message #646228]

Thu, 24 December 2015 04:48

rahuljadhav177@gmail.com
Messages: 7
Registered: December 2015

Junior Member

Thank you sir

but i know deleted row date but i dont know exact delete time how to implement in this flashback feature .
i have no idea about this please guid me ..

SELECT * FROM emp
AS OF TIMESTAMP
TO_TIMESTAMP('2004-12-24 ', 'YYYY-MM-DD HH:MI:SS')
WHERE empid=5;

Report message to a moderator

Re: how to fetch deleted row [message #646301 is a reply to message #646299]

Thu, 24 December 2015 04:53

Michel Cadot
Messages: 68645
Registered: March 2007
Location: Nanterre, France, http://...

Senior Member
Account Moderator

Can't you read what has been told?

You have no chance to flashback 11 years.

Report message to a moderator

Re: how to fetch deleted row [message #646303 is a reply to message #646301]

Thu, 24 December 2015 04:56

rahuljadhav177@gmail.com
Messages: 7
Registered: December 2015

Junior Member

i have deleted this row at date '2015-1-2'. how to get back in a table this row guid me.

SELECT * FROM emp
AS OF TIMESTAMP
TO_TIMESTAMP('2015-1-2 ', 'YYYY-MM-DD HH:MI:SS')
WHERE empid=5;

Report message to a moderator

Re: how to fetch deleted row [message #646304 is a reply to message #646303]

Thu, 24 December 2015 04:58

Michel Cadot
Messages: 68645
Registered: March 2007
Location: Nanterre, France, http://...

Senior Member
Account Moderator

You have no change to flashback one year.

Report message to a moderator

Re: how to fetch deleted row [message #646305 is a reply to message #646304]

Thu, 24 December 2015 05:01

rahuljadhav177@gmail.com
Messages: 7
Registered: December 2015

Junior Member

is any other way can we fetch back this row in a table through query......

Report message to a moderator

Re: how to fetch deleted row [message #646306 is a reply to message #646305]

Thu, 24 December 2015 05:01

Michel Cadot
Messages: 68645
Registered: March 2007
Location: Nanterre, France, http://...

Senior Member
Account Moderator

Backup.

Report message to a moderator

Re: how to fetch deleted row [message #646307 is a reply to message #646306]

Thu, 24 December 2015 05:04

rahuljadhav177@gmail.com
Messages: 7
Registered: December 2015

Junior Member

i know this way but every time restore backup is not possible for single row ...
is oracle providing feature regarding that problem......

Report message to a moderator

Re: how to fetch deleted row [message #646308 is a reply to message #646307]

Thu, 24 December 2015 05:05

John Watson
Messages: 8931
Registered: January 2010
Location: Global Village

Senior Member

rahuljadhav177@gmail.com wrote on Thu, 24 December 2015 11:04

i know this way but every time restore backup is not possible for single row ...
is oracle providing feature regarding that problem......

Yes: the Flashback Data Archive (which is free in all editions from 12.x, chargeable in 11.x) is designed for exactly this purpose. But it didn't exist 11 years ago, so you can't use it.

Report message to a moderator

Re: how to fetch deleted row [message #646309 is a reply to message #646307]

Thu, 24 December 2015 05:11

Michel Cadot
Messages: 68645
Registered: March 2007
Location: Nanterre, France, http://...

Senior Member
Account Moderator

Quote:

i know this way but every time restore backup is not possible for single row ...

Why if this is a so important row that you are searching for a solution since 2 days and does not want to accept the reality?

As John says, for the future you can use FDA but you can't put the whole database inside FDA feature during 11 years, it would be like having inline all backups since then. You can just use it for some time in some specific tables.

Report message to a moderator

Re: how to fetch deleted row [message #646310 is a reply to message #646308]

Thu, 24 December 2015 05:17

rahuljadhav177@gmail.com
Messages: 7
Registered: December 2015

Junior Member

no sir something mistake happening i dont want data of last 11 year back.
i want data of date which is less than current date...
which may be yesterday or privios date.

Report message to a moderator

Re: how to fetch deleted row [message #646312 is a reply to message #646310]

Thu, 24 December 2015 05:22

Michel Cadot
Messages: 68645
Registered: March 2007
Location: Nanterre, France, http://...

Senior Member
Account Moderator

So first it was 11 years ago, then 1 year, now 2 days or less.
But you have to accept that

if your flashback query does not return what you want then you have to use backup.

Note that in the end we don't know what you want.
Once you will know for sure, then ask you may get an accurate answer.
For the moment you just waste everyone's time.

Report message to a moderator

Re: how to fetch deleted row [message #646313 is a reply to message #646310]

Thu, 24 December 2015 05:24

cookiemonster
Messages: 13920
Registered: September 2008
Location: Rainy Manchester

Senior Member

Stuff from yesterday will be in flashback. Older stuff will or won't be in flashback depending on the size of the flashback area.
Anything that is no longer in flashback can only be got from backups.

Report message to a moderator

Previous Topic:	Generate GUID other than sys_GUID() (merged 3)
Next Topic:	Start DB session using sh script with out the password

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Thu Apr 25 18:52:30 CDT 2024