CODEWITHSUNDEEP
oracle-databaseoracle11g
name_masked
name_masked

Reputation: 9794

Rollback database changes using implicit savepoints? - Oracle

Can I rollback changes to the database using implicit savepoints? I ended up making changes(INSERTs) through my Java code that I would want to revert back.

Upvotes: 0

Views: 5407

Answers (2)

John Doyle
John Doyle

Reputation: 7793

Have a look at Using Oracle Flashback Technology. Assuming your database is already set up for it then you can flashback to a time before the inserts. Though be careful if other users are also updating this table. Also, it's ony recommended if you've made a mistake and will manually flash it back, I wouldn't build this in to any code to automatically do so.

Upvotes: 3

Zack Macomber
Zack Macomber

Reputation: 6905

You can use a SAVEPOINT as outlined at How to COMMIT, ROLLBACK Oracle Transactions.

Here's the SAVEPOINT snippet from it...

SAVEPOINT

Specify a point in a transaction to which later you can roll back.

Example

insert into emp (empno,ename,sal) values (109,’Sami’,3000);
savepoint a;
insert into dept values (10,’Sales’,’Hyd’);
savepoint b;
insert into salgrade values (‘III’,9000,12000);

Now if you give

rollback to a;

Then  row from salgrade table and dept will be roll backed. Now you can commit the row inserted into emp table or rollback the transaction.

If you give

rollback to b;

Then row inserted into salgrade table will be roll backed. Now you can commit the row inserted into dept table and emp table or rollback to savepoint a or completely roll backed the transaction.

If you give

rollback;

Then the whole transactions is roll backed.

If you give

commit;

Then the whole transaction is committed and all savepoints are removed.

Upvotes: 3

Related Questions