use of ROLLBACK command in Oracle

Question

I created table

SQL>CREATE TABLE Student
(
StudID         NUMBER(6),
StudName       VARCHAR2(25),
JoinDate       DATE
);   
Table created.

SQL>INSERT INTO Student
VALUES (123,'JOHN',SYSDATE);
1 row created.

SQL>DELETE Student;
1 row deleted.

How can I get back the row ? If I use

SQL>ROLLBACK;
Rollback complete.

But after that

SQL>SELECT * FROM Student;
no rows selected.

Why is this coming?

score 20 · Answer 1 · answered Aug 22 '18 at 20:54

This might be a bit confusing if you are not doing rollbacks on a regular basis. I put here mistakes that you are likely to make if not using SAVEPOINTS / ROLLBACK on a regular basis.

1. This is your case. We rollback all transaction to the beginning, ie. to the last (implicit) commit

2. Here we have SAVEPOINT but it does not matter, as it is not mentioned in ROLLBACK statment. So we are rolling back to the last commit, as before.

3. This is tricky. The rollback still goes to the last commit, as the syntax was not correct (but no error reported by the 11g database). Correct syntax is ROLLBACK TO <SAVEPOINT-NAME>:

4. Finally we have correct SYNTAX: ROLLBACK TO <SAVEPOINT-NAME>

Some readings: https://docs.oracle.com/cd/B19306_01/server.102/b14200/statements_9021.htm

score 16 · Answer 2 · edited May 20 '13 at 06:58

16

ROLLBACK tells Oracle to roll back the entire transaction. In your case, both the INSERT and the DELETE are part of the same transaction so the ROLLBACK reverses both operations. That returns the database to the state it was in immediately following the CREATE TABLE statement.

Some alternatives:

If you were to issue a COMMIT after the INSERT then the DELETE statement would be in a separate transaction and the ROLLBACK would reverse only the effect of the DELETE statement.
You could also create a savepoint after running the INSERT statement and then rollback to that savepoint after the DELETE rather than rolling back the entire transaction.

edited May 20 '13 at 06:58

Jeffrey Kemp

59,135
14
106
158

answered May 19 '13 at 07:58

Justin Cave

227,342
24
367
384

Instead of SQL> DELETE Student; I used SQL> DELETE FROM Student – bibhudash May 19 '13 at 11:46
1 row deleted. Then used ROLLBACK; and then SELECT * FROM Student; and it works. – bibhudash May 19 '13 at 11:48
1

@bibhudash - What is not working? Everything you have posted appears to be working as it is supposed to. If that is not the behavior you want, you'll need to change your code. That's why I gave you a couple of different options for how to change your code. – Justin Cave May 20 '13 at 04:13

score 0 · Answer 3 · answered Aug 04 '15 at 20:21

Try

    CREATE TABLE your_table
     AS SELECT * FROM employees;

     ALTER TABLE your_table
     ENABLE ROW MOVEMENT;

     UPDATE your_table
      SET CON_TYPE = 'N';

    FLASHBACK TABLE your_table
       TO TIMESTAMP (SYSTIMESTAMP - INTERVAL '1' minute);
           /*if you want to restore the table*/
       FLASHBACK TABLE print_media TO BEFORE DROP;

    /*Restore the table with other name*/
       FLASHBACK TABLE your_table TO BEFORE DROP RENAME TO your_table;

use of ROLLBACK command in Oracle

3 Answers3