4

I did a DB update without using a where clause and commit without taking any backup. All the rows in the table are updated. Is there any way to rollback the changes?

The DB is a Oracle SQL one. Please help.

Niketh Kovin
  • 63
  • 1
  • 2
  • 10
  • You cannot rollback what has already been commited – Richa Dec 05 '14 at 06:02
  • try using flashback https://docs.oracle.com/cd/B12037_01/server.101/b10759/statements_9012.htm – Exhausted Dec 05 '14 at 06:03
  • Why do you have both `sql-server` and `oracle` tags together. My answer is specific to Oracle database. – Lalit Kumar B Dec 05 '14 at 07:31
  • 1
    My bad. I had updated nearly 2K rows and was desperate and added all these tags to my qyestion. By the way, I used the flashback by TIMESTAMP and retrieved the data. Thanks a lot. – Niketh Kovin Dec 05 '14 at 09:29

1 Answers1

22

You could do it using Flashback feature.

1.Flashback by SCN

SELECT column_list
FROM table_name
AS OF SCN scn_number;

2.Flashback by TIMESTAMP

SELECT column_list
FROM table_name
AS OF TIMESTAMP TO_TIMESTAMP('the timestamp value');

To get current_scn and systimestamp, query :

SELECT current_scn, SYSTIMESTAMP
FROM v$database;

Let's see an example :

To flashback the table to the old scn, use FLASHBACK TABLE..TO SCN clause.

SQL> DROP TABLE string_ex PURGE;

Table dropped.

SQL> CREATE TABLE string_ex (sl_ps_code VARCHAR2(20) );

Table created.

SQL> INSERT INTO string_ex (sl_ps_code) VALUES ('AR14ASM0002');

1 row created.

SQL> INSERT INTO string_ex (sl_ps_code) VALUES ('AR14SFT0018');

1 row created.

SQL> INSERT INTO string_ex (sl_ps_code) VALUES ('AR14SFT0019');

1 row created.

SQL> INSERT INTO string_ex (sl_ps_code) VALUES ('AR14SFT0062');

1 row created.

SQL> COMMIT;

Commit complete.

SQL> SELECT current_scn, SYSTIMESTAMP FROM v$database;

         CURRENT_SCN SYSTIMESTAMP
-------------------- --------------------------------------------
      13818123201277 29-OCT-14 03.02.17.419000 PM +05:30

SQL> SELECT current_scn, SYSTIMESTAMP FROM v$database;

         CURRENT_SCN SYSTIMESTAMP
-------------------- --------------------------------------------
      13818123201280 29-OCT-14 03.02.22.785000 PM +05:30

SQL> SELECT current_scn, SYSTIMESTAMP FROM v$database;

         CURRENT_SCN SYSTIMESTAMP
-------------------- --------------------------------------------
      13818123201282 29-OCT-14 03.02.26.781000 PM +05:30

SQL> SELECT * FROM string_ex;

SL_PS_CODE
---------------
AR14ASM0002
AR14SFT0018
AR14SFT0019
AR14SFT0062

SQL>

I have four rows in the table.

SQL> ALTER TABLE string_ex ENABLE ROW MOVEMENT;

Table altered.

SQL>

Row movement is required.

SQL> DELETE FROM string_ex WHERE ROWNUM =1;

1 row deleted.

SQL>
SQL> COMMIT;

Commit complete.

SQL>
SQL> SELECT * FROM string_ex;

SL_PS_CODE
---------------
AR14SFT0018
AR14SFT0019
AR14SFT0062

I deleted a row now and committed the changes.

SQL> FLASHBACK TABLE string_ex TO SCN 13818123201277;

Flashback complete.

Flashback is complete

SQL> SELECT * FROM string_ex;

SL_PS_CODE
---------------
AR14ASM0002
AR14SFT0018
AR14SFT0019
AR14SFT0062

SQL>

I now have my table to old state and the row is back

Lalit Kumar B
  • 47,486
  • 13
  • 97
  • 124