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