CODEWITHSUNDEEP
sqloracle-databaseplsql
Jayanth Raghava
Jayanth Raghava

Reputation: 33

How to check previous value after update in oracle

I updated a column in the oracle table on Jan 1 2022 and committed, how to check which value is present before the update in that particular column.

Is there any possibility to view which value is there in that column on Dec 31, 2021.

Thanks

select salary from emp where id = 1111;

Upvotes: 1

Views: 1640

Answers (1)

MT0
MT0

Reputation: 167972

In general, you cannot. Once you update a table then the previous value is gone.

However:

  • If you have a history/logging table that you populate whenever you update the main table (typically via a trigger or a procedure) then you can retrieve the historic value from that table; however, you would have had to create the history table and the procedure/trigger beforehand.

  • If you have flashback enabled on the database/tablespace/table and the flashback retention period is long enough then you can use:

    SELECT salary
FROM   emp AS OF TIMESTAMP TIMESTAMP '2021-12-31 23:59:59.999999999'
WHERE  id = 1111

    If you did not enable flashback before the change or the flashback retention period is shorter than required then that will not work.

  • Assuming that you backup your database (please say you backup your database), you can restore the database to the most recent back-up from before the date in question and then you can query the table from that back-up date.

Upvotes: 2

Related Questions