Hello,
I have one question for you regarding the data modified. I am working on a project where one team is making modifications in database (insert, update and delete). The data is being modified using one editor software and the software does not permit us to add Time Stamp with each modified, inserted and deleted record.
One of the method proposed by our team is to use Oracle Flashback technology and use the query :
SELECT SCN_TO_TIMESTAMP(max(ora_rowscn)) from <TABLE NAME>;
to get time stamp and use the query to get recently modified data :
SELECT ID_OBJECT,ora_rowscn from <TABLE NAME> order by ora_rowscn desc;
To get the deleted lines the query proposed is :
SELECT ID_OBJECT FROM <TABLE NAME> AS OF TIMESTAMP TO_TIMESTAMP('2017-12-01 03:40:00 PM', 'YYYY-MM-DD HH:MI:SS PM') where ID_OBJECT not in (SELECT ID_OBJECT FROM <TABLE NAME>);My question is regarding this method.
Is this method reliable ?
Can we use Oracle Flashback technologies to capture modified data from application ?
Are there any technical constraints associated with this technology.
I present you my excuse if this is a basic question. In fact I don't have anyone who can guide in this prospect and in documentation I had not found any risk.
Thanking you in anticipation
Ayyaz
If you want to find the insert/updates/deletes to a table, you can use the version between flashback clause to find them:
create table t (
x int
);
insert into t values (1);
insert into t values (2);
commit;
exec dbms_lock.sleep(5);
update t set x = x + 1;
commit;
exec dbms_lock.sleep(5);
delete t
where x > 2;
commit;
exec dbms_lock.sleep(5);
select * from t;
X
2
select * from t as of timestamp sysdate - interval '12' second;
X
1
2
select t.*, versions_operation, versions_startscn, versions_endscn
from t versions between timestamp sysdate - interval '12' second and sysdate;
X VERSIONS_OPERATION VERSIONS_STARTSCN VERSIONS_ENDSCN
3 D 75941081
2 U 75941078
3 U 75941078 75941081
1 75941078
2 75941078
So you can see what happened using versions_operation I(nsert), U(pdate) or D(elete) and when it happened.
The issue here is the database uses undo to generate this information. So how far back you can query is limited by the undo_retention parameter. Which defaults to 15 minutes!
To be sure you can go further back than this, increase the value of this parameter. Just ensure your undo tablespace can grow large enough to support this.
Some DDL statements also break flashback query:
alter table t add unique (x);
select * from t as of timestamp sysdate - interval '2' second;
ORA-01466: unable to read data - table definition has changed
To overcome these issues, use Flashback Data Archive. This persists the change history to disk. So you're no longer bound by undo. You can also query across DDL statements.
If you want to know more about this and Flashback in general, read about it in the Database Development Guide:
https://docs.oracle.com/en/database/oracle/oracle-database/12.2/adfns/flashback.html#GUID-03D1CAAE-D940-444A-8771-B1BC636D105D