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.
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].
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.
Flashback Query as create table before_delete as select * from Table as of TIMESTAMP XX;
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';
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.