On Sep 13, 9:34 am, Gayathri <[email protected]> wrote: > Hi All, > > I just want to know if there is any utility or a better way to rollback > thousands of DML(mostly update and inserts, *with commits inbetween*) > statements issued on Production. > I am trying to write a rollback scripts and was wondering if 11g has some > simplier way of doing this. > > Thanks in advance. > G
You could flashback the table to a given SCN or timestamp; you can use DBMS_LOGMNR to extract the SQL_UNDO statements of interest and apply those; you can use flashback query to restore the data as of a given timestamp or SCN. Flashback query is described here: http://oratips-ddf.blogspot.com/2008/01/resurrecting-dead-or-gee-i-thought-that.html Use of DBMS_LOGMNR is described here: http://oratips-ddf.blogspot.com/2008/10/workin-in-mines.html Flashback table is described here, and relies on the generated undo to work, as does flashback query: http://download.oracle.com/docs/cd/E11882_01/server.112/e17118/statements_9012.htm#SQLRF01802 If the data changes are older than the undo_retention setting then your only option of the three mentioned here is DBMS_LOGMNR, which relies on the redo logs and archived copies of them presuming you have the archivelogs available. Of course you can write your own rollback scripts but that relies on your knowledge of the original values in the table. David Fitzjarrell -- You received this message because you are subscribed to the Google Groups "Oracle PL/SQL" group. To post to this group, send email to [email protected] To unsubscribe from this group, send email to [email protected] For more options, visit this group at http://groups.google.com/group/Oracle-PLSQL?hl=en
