Skip to Main Content

Breadcrumb

Question and Answer

Connor McDonald

Thanks for the question, vishnudas.

Asked: May 22, 2018 - 7:12 am UTC

Last updated: May 22, 2018 - 7:29 am UTC

Version: 11.2.0.1.0

Viewed 10K+ times! This question is

You Asked

HI,THERE
I have a situation here,
in one of my table, i hvae loc_id column

my requirement is that i want all loc_id that have changed to new loc_id


eg:


LOC_ID CUST_NAME
---------- ----------------------------------------
17 A B PHARMA


location 17 is Mumbai


now the loc_id has changed to 21

LOC_ID CUST_NAME
---------- ----------------------------------------
21 A B PHARMA

locaion 21 is Bangalore


i want this changed records(old loc_id and new loc_id)..
please help me solve this situation...




and Connor said...

You *might* be able to get the old data using a flashback query, eg

SQL> SELECT deptno, dname,
  2       VERSIONS_STARTTIME
  3      ,VERSIONS_XID
  4      ,VERSIONS_OPERATION
  5  FROM dept
  6  VERSIONS BETWEEN
  7     TIMESTAMP SYSTIMESTAMP - INTERVAL '20:00' MINUTE TO SECOND
  8     AND SYSTIMESTAMP
  9  WHERE deptno = 10;

    DEPTNO DNAME          VERSIONS_STARTTIME       VERSIONS_XID     V
---------- -------------- ------------------------ ---------------- -
        10 UNKNOWN        03-SEP-08 11.53.45 PM    0200100060040000 U
        10 MONEY GRABBERS 03-SEP-08 11.53.36 PM    0600050065040000 U
        10 FINANCE        03-SEP-08 11.53.24 PM    09000D001D050000 U
        10 BEAN COUNTERS  03-SEP-08 11.53.12 PM    01001A00EA030000 U
        10 ACCOUNTING



The above data is a *single* row in the table that has been changed 5 times.

But this will depend on how much undo information you have available to you (a DBA setting). If the query does not work, then you'd need to look at auditing or flashback data archive to assist you *in future* (ie, it won't get you back the data you've already updated)

Is this answer out of date? If it is, please let us know via a Comment

More to Explore

Backup/Recovery

Check out the complete guide to all of the Backup & Recovery techniques in the Oracle Database.