Toad World® Forums

[ASK] how to rollback the database after the commit?

Message from: Oka Ardhivia

good night

Please help the master oracle , I want to ask.
how to reverse the data that has been committed to the Toad .

please help , is there a solution ?

thx


Historical Messages

Author: Putu Oka Ardhivia
Date: Thu Oct 27 09:40:14 PDT 2011
good night

Please help the master oracle , I want to ask.
how to reverse the data that has been committed to the Toad .

please help , is there a solution ?

thx

__


Once the commit has been submitted, to oracle there is no way to unwind this.

If you haven’t committed (red down arrow) then use the blue up arrow to
rollback.

Chris

Evening,

Please help the master oracle, I want to ask.
how to reverse the data that has been committed to the Toad.
Toad is nothing more than a tool to allow you to develope database
applications, and maintain them, and the databases underneath.

To this end, it’s not possible to commit data to Toad itself, however,
you can commit data changes to a database connection that you have open
in Toad - this is what I think you mean. (I understand that English may
not be your first language.)

If we assume that you have made a few changes and then clicked on the
COMMIT button (or executed the COMMIT command) then you need to manually
undo your changes yourself and then COMMIT again.

Once you have committed some changes, that’s it, they are permanent.

However, depending on the version of the database, you may be able to
ask your DBA to flashback the database to a specific point in time, or,
assuming you are using hot backups, restore the database to a point
prior to your changes being made. However, these options will also roll
back changes made by other people on the database - which may not be
well received!

If the database is set up with flashback enabled for tables, you could
flashback the table(s) to before your updates, open a cursor to the
flash backed table, then close the flashback session.

Then you can read the data from the cursor (still as it was before you
made changes) and rebuild either the existing table(s) or build a new
one and use that to correct your changes.

Good luck


Cheers,
Norm. [TeamT]

PS. To flash back a table to an earlier SCN or timestamp, you must have
either the FLASHBACK object privilege on the table or the FLASHBACK ANY
TABLE system privilege.

In addition, you must have the SELECT, INSERT, DELETE, and ALTER object
privileges on the table.

Row movement must be enabled for all tables in the Flashback list.

To flash back a table to before a DROP TABLE operation, you need only
the privileges necessary to drop the table.

Here’s an example of flashing back a table to how it was 2 hours ago:

FLASHBACK TABLE
TO TIMESTAMP (SYSTIMESTAMP - INTERVAL ‘120’ minute);

Of course, it all depends on whether or not you have enough undo hanging
around to be able to do this.

The example is taken from the Oracle 11g docs.


Cheers,
Norm. [TeamT]