Rollback command not working - Oracle

Question

I dropped a table and tried to rollback, but to no use. Will it ever work like this or am I playing wrong here?

As from most of the comments I am clear that DDL statements cannot be undone by rollback but only by FLASHBACK.

I tried undoing DELETE FROM STUDENT;

It still it can't be undone:

My order of execution was

INSERT,
DELETE FROM ,
ROLLBACK.

http://stackoverflow.com/questions/4711447/oracle-ddl-and-transaction-rollback — NPE, Jan 20 '12 at 13:22
@Sathya I dont have enough reputation to answer my own question . So i updated here to let my commenters know that the question is resolved otherwise they would have to wait 8 hours and some of them surely has burned their time by then solving the same question . You must have considered this fact before voting down and editing my answer . For me i did the right thing and guess what !! now ,i have to write my answer again from the scratch . I guess i should thank you ! — HalfWebDev, Jan 20 '12 at 17:11
@kushal 1. Section above is meant for questions, not answers. 2. downvote was for poor formatting and a crappy question with no details at the start 3. You can leave a comment saying you got it fixed/will update in 8 hours or whatever time 4. Your "answer" is not lost, always available from the previous revisions list. http://i.imgur.com/RxLCQ.png http://stackoverflow.com/posts/8942034/revisions — Sathyajith Bhat, Jan 20 '12 at 17:20
@Sathya... You really must read the comment flow below . Initially,i was not even aware that if we can rollback a dropped table ! And thanks to the commentors for making me clear . And for that what details could i have provided initilally MR . sathya ? God bless your intelligent mind Mr . moderator . — HalfWebDev, Jan 20 '12 at 17:39

score 13 · Answer 1 · answered Jan 20 '12 at 13:19

13

I don't believe rollback will undo schema changes.

answered Jan 20 '12 at 13:19

Mike Park

10,845
2
34
50

3

Correct. DDL statements cannot be rolled back in Oracle – Jan 20 '12 at 13:21
@climbage Can it undo the deleted rows from table ? – HalfWebDev Jan 20 '12 at 13:22
Could you provide us with the statements which you use and the exact order in which you executed them? You can rollback a data manipulation (like deletion of rows) as long as you don't execute a commit or perform structural changes (like dropping tables) in the database. Rollback only works back in time until it hits one of such events, no further. – Kosi2801 Jan 20 '12 at 13:27
@Kosi2801 . i have updated my question and i haven't committted anything. – HalfWebDev Jan 20 '12 at 13:37
Does your database client have autocommit enabled? Check that it doesn't automatically performs a commit after each successful statement! – Kosi2801 Jan 20 '12 at 13:44
@Kushal: Not only can you not roll back DDL, you can not roll back DML that happens prior to DDL. DDL does an implicit commit, performs the action, and another implicit commit, all within one statement. – Shannon Severance Jan 20 '12 at 23:27

score 5 · Accepted Answer · answered Jan 20 '12 at 14:40

ROLLBACK without a savepoint qualifier will roll back the entire current transaction.

For DDL statements, there is no current transaction to rollback. The DDL statement implicitly generates a COMMIT before the statement starts and after it completes. So if you issue a ROLLBACK following a DROP, no work has been done in the current transaction so there is nothing to roll back.

For DML statements, you'll roll back the entire current transaction. If you do

INSERT
DELETE
ROLLBACK

your transaction begins when you execute the INSERT operation. So when you issue the ROLLBACK, you are rolling back both the INSERT and the DELETE so you're back to having no data in the table (assuming you started with no data). If you COMMIT after the INSERT then the next transaction would begin with the DELETE and your ROLLBACK will only roll back the DELETE operation. Alternately, you can declare a savepoint after the INSERT and roll back to the savepoint

SQL> create table foo( col1 number );

Table created.

SQL> insert into foo values( 1 );

1 row created.

SQL> savepoint after_insert;

Savepoint created.

SQL> delete from foo;

1 row deleted.

SQL> rollback to savepoint after_insert;

Rollback complete.

SQL> select * from foo;

      COL1
----------
         1

Hey man , i have one more problem regarding dropped tables and i posted it too but till now haven't found a solution . Can you please see it . here is the link [link](http://stackoverflow.com/questions/8937093/dropped-table-not-showing-up-in-recycle-bin) — HalfWebDev, Jan 20 '12 at 14:50

score 4 · Answer 3 · answered Jan 20 '12 at 13:22

4

Rollback does not undo schema changes, but to undo drop table operations you can check:

http://docs.oracle.com/cd/B19306_01/backup.102/b14192/flashptr004.htm

answered Jan 20 '12 at 13:22

xpapad

4,376
1
24
25

NPE · Answer 4 · 2012-01-20T13:43:46.217

3

From the documentation:

Oracle Database implicitly commits the current transaction before and after every DDL statement.

This means that you cannot ROLLBACK a DDL statement (that is, a schema change).

edited Jan 20 '12 at 13:43

answered Jan 20 '12 at 13:22

NPE

486,780
108
951
1,012

I'm not sure if this answer is helpful for the specific question as it requires more knowledge on the concepts of structure and transactions, which are obviously not yet clear to the questioner. – Kosi2801 Jan 20 '12 at 13:30
@Kosi2801@ajx yeah ! it wasn't an appropriate answer but info is helpul . – HalfWebDev Jan 20 '12 at 13:40

score 2 · Answer 5 · answered Jan 20 '12 at 13:22

2

Rollback will never undo Data Definition commands such as drop table alter table etc.

answered Jan 20 '12 at 13:22

spudgun

91
1
2

score 0 · Answer 6 · answered Jan 20 '12 at 13:21

Dropping a table changes the structure of the database (using DDL statements like CREATE, DROP, ...).

COMMIT and ROLLBACK only work on the data which is exchanged with the database using DML statements (like INSERT, UPDATE, ...).

So, no it will never work like this.

score 0 · Answer 7 · answered Jan 21 '12 at 06:53

0

To rollback ddl changes you need to use Flashback.

answered Jan 21 '12 at 06:53

steve

5,870
1
21
22

vijikumar · Answer 8 · 2012-01-24T04:33:32.773

Rollback:

Discard all pending changes by using the ROLLBACK statement. Following a ROLLBACK statement:

Data changes are undone.
The previous state of the data is restored.
The locks on the affected rows are released.

Example

While attempting to remove a record from the TEST table, you can accidentally empty the table. You can correct the mistake, reissue the proper statement, and make the data change permanent.

DELETE FROM test;
25,000 rows deleted.

ROLLBACK;
Rollback complete.

DELETE FROM test
WHERE id = 100;

1 row deleted.

SELECT *
FROM test
WHERE id = 100;
No rows selected.

COMMIT;
Commit complete

After giving commit we can't rollback.

`After giving commit we can rollback.` ==> You mean we *cannot* rollback, rather it's pointless because the transaction has already been closed. — Sathyajith Bhat, Jan 23 '12 at 17:41

Rollback command not working - Oracle

8 Answers8