Skip to Main Content

Breadcrumb

May 4th

Question and Answer

Connor McDonald

Thanks for the question, Ketan.

Asked: October 25, 2016 - 7:38 pm UTC

Last updated: October 26, 2016 - 2:34 am UTC

Version: 11.2.0.4

Viewed 10K+ times! This question is

You Asked

In a classic case of replication
(forget the tool of replication here )

Oracle - 2 - Oracle

create table abc (anum number, aname varchar2(30) , adate date default sysdate ) ;

insert into abc values(1,'1test',sysdate);
insert into abc values(2,'2test',sysdate);
insert into abc values(3,'3test',sysdate);
insert into abc values(4,'4test',sysdate);
insert into abc values(5,'5test',sysdate);

at the target end - another process kicks in and deletes anum 1 ( we don't know that )

Now the source does an update which is success on the source - replication fails on the target as row does not exist.

There is no way to know if the target row ( original insert ) was deleted or possibly the row never got inserted.
I'd like to know when was the original row inserted - basically to get the timestamp of the insert so I can mine the logs.

Any help would be appreciated.

Thanks
Ketan

and Connor said...

Flashback can let you see versions of the row (limited by available undo)

SQL> create table t ( x int, y date );

Table created.

SQL>
SQL> insert into t values (1,sysdate);

1 row created.

SQL> insert into t values (2,sysdate-1);

1 row created.

SQL> insert into t values (3,sysdate-2);

1 row created.

SQL> insert into t values (4,sysdate-3);

1 row created.

SQL>
SQL> commit;

Commit complete.

SQL>
SQL> delete from t where x = 2;

1 row deleted.

SQL> commit;

Commit complete.

SQL> select t.* ,
  2         versions_starttime,
  3         versions_xid,
  4         versions_operation
  5  from t
  6  versions between timestamp systimestamp - interval '01:00' minute to second  and systimestamp;

         X Y         VERSIONS_STARTTIME                                                          VERSIONS_XID     V
---------- --------- --------------------------------------------------------------------------- ---------------- -
         2 25-OCT-16 26-OCT-16 10.33.02 AM                                                       0E001A0039DF0000 D
         4 23-OCT-16 26-OCT-16 10.33.02 AM                                                       110002001CD80000 I
         3 24-OCT-16 26-OCT-16 10.33.02 AM                                                       110002001CD80000 I
         2 25-OCT-16 26-OCT-16 10.33.02 AM                                                       110002001CD80000 I
         1 26-OCT-16 26-OCT-16 10.33.02 AM                                                       110002001CD80000 I

5 rows selected.

SQL>
SQL>



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