how to retrieve the deleted rows in a table

[saideepak89]

hi..... i am searching for the query for retrieving the deleted data from the table.From the web i got "flashback" method to get the dropped table.Any one has any idea pls,help me

thanks in advance

[debasisdas]

Which version of oracle database you are using ?

[hfx642]

1. "Rollback;", of course... You have to do that BEFORE the "Commit;".

2. Do you have any auditing tables and triggers?

3. Turn on "Auditing".

4. Don't allow users to delete records.

5. Only "flag" records as deleted.

There are several solutions to PREVENT records from being deleted.

[saideepak89]

currently 11G but , there are any chances after the commit,or we need any additional softwares.Sorry,this question was "how to retrieve the deleted rows in a table after commit". actually mine is a general question not depending on version ,is it depends on version.

[ruhi]

You can use FLASHBACK TABLE to retrieve your rows. But before that you must enable row movement by following query

ALTER TABLE hr.employees ENABLE ROW MOVEMENT;

And there after firing the below query for the particular timestamp when your rows were existing at that time.

FLASHBACK TABLE hr.employees TO TIMESTAMP
TO_TIMESTAMP('17-10-2011 12:19:00','DD-MM-YYYY HH24:MI:SS');

[debasisdas]

FLASHBACK is used to retrive the dropped tables.

Use the following to retrieve the deleted and committed rows

select * from table_name as of timestamp to_timestamp(sysdate-(360/1440))

The above sample SQL will fetch the status of data in the table 6 hours earlier.

[ruhi]

But you can Restore the table content to a specific point in the
past with Flashback Table.

[debasisdas]

Agree.

The question was "how to retrieve the deleted rows in a table after commit" not to retrieve rows from a dropped table.

[ruhi]

This "Flashback table" is applicable/used for existing table only not for already dropped table.

[nguyentruyen]

enable row movement -> when you want to get records deleted, you do inserts all records into table template, that you get from query "select * from your_table_name as of timestamp to_tablestamp(sysdate);" And then you compare all records in table tamplate with all records in table and you have all records lost [Records deleted].

[yadgiri_559]

how to retrieve deleted hole table data from oracle database. i used delete from emp; its deleted data but by mistakely i shutdown my system . so its autometecaly committed ..so give me correct sql query for retrieve the data.

[Maclean_1]

There are some option:

**Flashback query **

Flashback Query as create table before_delete as select * from Table as of TIMESTAMP XX;

**Logminer **

Logminer if Oracle supplement log is enabled , you can get undo sql for your delete statement

-- switch again logfile to get a minimal redo activity alter system switch logfile;

-- mine the last written archived log exec dbms_logmnr.add_logfile('archivelog/redologfile', options => dbms_logmnr.new); exec dbms_logmnr.start_logmnr(options => dbms_logmnr.dict_from_online_catalog); select operation, sql_redo from v$logmnr_contents where seg_name = 'EMP';

**PRM-DUL **

Oracle PRM-DUL will be last option. Even deleted row piece in Oracle block is always just marked row flag with deleted mask, the row piece still can be read via scan Oracle data block . PRM-DUL can scan the whole table , find out every record/row piece marked deleted and write out to flat file.