Geek Answers Handbook

How to undo changes to my database after commit?

I did a database update without using the where clause and did it without any backup. All rows in the table are updated. Is there any way to undo the changes?

The database is Oracle SQL. Please, help.

+4
source share
1 answer

You can do this using Flashback .

1.Flashback from 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, the query:

SELECT current_scn, SYSTIMESTAMP
FROM v$database;

Let's look at an example:

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

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 a table .

SQL> ALTER TABLE string_ex ENABLE ROW MOVEMENT;

Table altered.

SQL>

A line move 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

.

SQL> FLASHBACK TABLE string_ex TO SCN 13818123201277;

Flashback complete.

Flashback

SQL> SELECT * FROM string_ex;

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

SQL>

,

+19

More articles:


All Articles