Home » SQL & PL/SQL » SQL & PL/SQL » previous data (oracle 11gr2)
previous data [message #654933] Wed, 17 August 2016 03:48 Go to next message
guddu_12
Messages: 219
Registered: April 2012
Location: UK
Senior Member
Hello All,

I have updated a table and data got commited as well, My manager is willing to see the previous data before update.

Table has just 1 row, is there any way to get the previous record set

Thanks
Re: previous data [message #654935 is a reply to message #654933] Wed, 17 August 2016 03:57 Go to previous messageGo to next message
John Watson
Messages: 8558
Registered: January 2010
Location: Global Village
Senior Member
For example,
orclz>
orclz> select * from emp where ename='KING';

     EMPNO ENAME      JOB              MGR HIREDATE                   SAL       COMM     DEPTNO
---------- ---------- --------- ---------- ------------------- ---------- ---------- ----------
      7839 KING       PRESIDENT            1981-11-17:00:00:00       5000                    10

orclz> update emp set sal=1000  where ename='KING';

1 row updated.

orclz> commit;

Commit complete.

orclz> select * from emp as of timestamp sysdate-5/1440 where ename='KING';

     EMPNO ENAME      JOB              MGR HIREDATE                   SAL       COMM     DEPTNO
---------- ---------- --------- ---------- ------------------- ---------- ---------- ----------
      7839 KING       PRESIDENT            1981-11-17:00:00:00       5000                    10

orclz>
Re: previous data [message #654943 is a reply to message #654935] Wed, 17 August 2016 04:51 Go to previous messageGo to next message
guddu_12
Messages: 219
Registered: April 2012
Location: UK
Senior Member
Thank you
Re: previous data [message #654961 is a reply to message #654943] Wed, 17 August 2016 12:58 Go to previous messageGo to next message
Bill B
Messages: 1971
Registered: December 2004
Senior Member
Guddu_12,
please be aware that John's example will only work if your have flashback setup in your database.
Re: previous data [message #654962 is a reply to message #654961] Wed, 17 August 2016 12:59 Go to previous messageGo to next message
John Watson
Messages: 8558
Registered: January 2010
Location: Global Village
Senior Member
No, Bill - there is no configuration needed for flashback query. In fact, I don't think it possible to disable it.
Re: previous data [message #654963 is a reply to message #654961] Wed, 17 August 2016 13:02 Go to previous messageGo to next message
Bill B
Messages: 1971
Registered: December 2004
Senior Member
see https://docs.oracle.com/cd/B19306_01/backup.102/b14192/rpfbdb003.htm

You have to do setup for flashback to be enabled. It might be default in later versions but it still can be turned on or off.
Re: previous data [message #654964 is a reply to message #654963] Wed, 17 August 2016 13:04 Go to previous messageGo to next message
John Watson
Messages: 8558
Registered: January 2010
Location: Global Village
Senior Member
That is Flashback Database. Flashback query needs nothing more than the undo segments.
Re: previous data [message #654965 is a reply to message #654963] Wed, 17 August 2016 14:04 Go to previous message
Michel Cadot
Messages: 67879
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Have a look at these short posts:
http://www.dba-village.com/village/dvp_forum.OpenThread?ThreadIdA=40315#144697
http://www.dba-village.com/village/dvp_forum.OpenThread?ThreadIdA=69044#202521

Previous Topic: Renaming a table
Next Topic: How to get a value that has never been updated
Goto Forum:
  


Current Time: Sat Jun 12 21:32:31 CDT 2021