CODEWITHSUNDEEP
oracle-databaseoracle-sqldeveloperoracle12coracle19c
STORM
STORM

Reputation: 4341

Oracle 12 get changed tables (inserted, deleted oder updated records)

Is it possible in an Oracle 12/19 database to get a list of tables and its records which have changed/added/deleted within the last X month? The problem here is, that the tables do not have any kind of timestamp per record. Is this possible?

Upvotes: 0

Views: 66

Answers (1)

Chris Saxon
Chris Saxon

Reputation: 9875

You can use Flashback Query to find the changes to a table in period of time/range of SCNs:

create table t (
  c1 int
);

exec dbms_lock.sleep ( 2 );
insert into t values ( 1 );
commit;
select current_scn from v$database;

CURRENT_SCN   
   19279246

exec dbms_lock.sleep ( 2 );
update t set c1 = 2;
commit;
delete t;
exec dbms_lock.sleep ( 2 );
commit;

select t.*, versions_operation, versions_startscn, versions_endscn 
from   t
  versions between scn 19279246 and maxvalue
order  by versions_startscn nulls first;

C1    VERSIONS_OPERATION       VERSIONS_STARTSCN    VERSIONS_ENDSCN   
    1 <null>                              <null>           19279250 
    2 U                                 19279250           19279253 
    2 D                                 19279253             <null>

The default window for this is small (900s). So it's highly unlikely you'll be able to go back and view changes from weeks or even hours ago. Enable Flashback Data Archive to define how long you want to keep the history for.

Upvotes: 1

Related Questions