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?
Answer
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