CODEWITHSUNDEEP
sqloracle-database
okjfokjrf
okjfokjrf

Reputation: 135

Go back in time in Oracle database

I want to undo some changes that I have made in an Oracle database, I would like to undo those changes at a certain time, could this be done in Oracle?

Upvotes: 0

Views: 374

Answers (1)

Littlefoot
Littlefoot

Reputation: 142753

What you asked sounds like rollback or flashback.

Though, as far as I can tell, you can't "schedule" any of these in a meaningful way as you'd have to use dbms_scheduler (or dbms_job), and that's not the same session that made changes in the database.

Anyway, just to illustrate options I mentioned:

This is to set default format so that you'd know what you're looking at:

SQL> alter session set nls_date_format = 'dd.mm.yyyy hh24:mi';

Session altered.

Right now is

SQL> select sysdate from dual;

SYSDATE
----------------
29.09.2021 12:05

"Original" table contents:

SQL> select * from dept;

    DEPTNO DNAME          LOC
---------- -------------- -------------
        10 ACCOUNTING     NEW YORK
        20 RESEARCH       DALLAS
        30 SALES          CHICAGO
        40 OPERATIONS     BOSTON

Let's update it:

SQL> update dept set dname = 'test' where deptno = 10;

1 row updated.

Right; department name is now changed:

SQL> select * from dept;

    DEPTNO DNAME          LOC
---------- -------------- -------------
        10 test           NEW YORK
        20 RESEARCH       DALLAS
        30 SALES          CHICAGO
        40 OPERATIONS     BOSTON

Using flashback, you can see table contents as it was at certain point of time (e.g. today at 10:00):

SQL> select * from dept as of timestamp to_timestamp('29.09.2021 10:00', 'dd.mm.yyyy hh24:mi');

    DEPTNO DNAME          LOC
---------- -------------- -------------
        10 ACCOUNTING     NEW YORK
        20 RESEARCH       DALLAS
        30 SALES          CHICAGO
        40 OPERATIONS     BOSTON

SQL>

Or, to revert changes I made (as I didn't commit yet):

SQL> rollback;

Rollback complete.

SQL> select * from dept;

    DEPTNO DNAME          LOC
---------- -------------- -------------
        10 ACCOUNTING     NEW YORK
        20 RESEARCH       DALLAS
        30 SALES          CHICAGO
        40 OPERATIONS     BOSTON

SQL>

What does flashback say? Nothing new, as nothing really happened:

SQL> select * from dept as of timestamp to_timestamp('29.09.2021 10:00', 'dd.mm.yyyy hh24:mi');

    DEPTNO DNAME          LOC
---------- -------------- -------------
        10 ACCOUNTING     NEW YORK
        20 RESEARCH       DALLAS
        30 SALES          CHICAGO
        40 OPERATIONS     BOSTON

What if I actually committed? rollback would be without affect, but flashback will still display "old" data:

SQL> update dept set dname = 'test' where deptno = 10;

1 row updated.

SQL> commit;

Commit complete.

SQL> select * from dept;

    DEPTNO DNAME          LOC
---------- -------------- -------------
        10 test           NEW YORK
        20 RESEARCH       DALLAS
        30 SALES          CHICAGO
        40 OPERATIONS     BOSTON

SQL> rollback;

Rollback complete.

SQL> select * from dept;

    DEPTNO DNAME          LOC
---------- -------------- -------------
        10 test           NEW YORK
        20 RESEARCH       DALLAS
        30 SALES          CHICAGO
        40 OPERATIONS     BOSTON

Right; nothing was rolled back as UPDATE was already committed. Flashback:

SQL> select * from dept as of timestamp to_timestamp('29.09.2021 10:00', 'dd.mm.yyyy hh24:mi');

    DEPTNO DNAME          LOC
---------- -------------- -------------
        10 ACCOUNTING     NEW YORK
        20 RESEARCH       DALLAS
        30 SALES          CHICAGO
        40 OPERATIONS     BOSTON

SQL>

Upvotes: 1

Related Questions