CODEWITHSUNDEEP
oracle-databaseoracle11grollback
bibhudash
bibhudash

Reputation: 101

use of ROLLBACK command in Oracle

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?

Upvotes: 9

Views: 54250

Answers (3)

Witold Kaczurba
Witold Kaczurba

Reputation: 10485

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

enter image description here

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.

enter image description here

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>:

enter image description here

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

enter image description here

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

Upvotes: 23

Guillermo Nahuel Varelli
Guillermo Nahuel Varelli

Reputation: 318

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;

Upvotes: 0

Justin Cave
Justin Cave

Reputation: 231661

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:

  1. 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.
  2. 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.

Upvotes: 16

Related Questions