Find out New or Updated Rows from an Oracle Table without having Date Column

advertisements

_____________________________________________________________________________________________________________________

It is a common question to the DBA from the developer that is there any way to find out the rows inserted or modified recently without having the date column in the table. If you have date column inside the table it is easy job to find out the data. If you have flashback feature enabled in your database here is the way to find out. This will work out up to certain extend.

See an example below
I have a table called DEPT and there are 4 records. I am going to add one more row now

SQL> insert into dept (select * from dept where deptno=10)

1 row created.

SQL > Commit;

SQL> select * from dept;
 
    DEPTNO DNAME          LOC
---------- -------------- -------------
        10 ACCOUNTING     NEW YORK
        10 ACCOUNTING     NEW YORK
        20 RESEARCH       DALLAS
        30 SALES          CHICAGO
        40 OPERATIONS     BOSTON
Query to find out rows inserted in last 10 mins.
 
select * from dept where rowid in (
SELECT rowid
FROM dept
MINUS
SELECT rowid
FROM dept
AS OF TIMESTAMP (SYSTIMESTAMP - INTERVAL '10' MINUTE));
 
    DEPTNO DNAME          LOC
---------- -------------- -------------
        10 ACCOUNTING     NEW YORK
 
Query to find out rows inserted in last 1 Hour.
 
select * from dept where rowid in (
SELECT rowid
FROM dept
MINUS
SELECT rowid
FROM dept
AS OF TIMESTAMP (SYSTIMESTAMP - INTERVAL '1' HOUR));
 
Query to find out rows for particular day.
select * from dept where rowid in (
SELECT rowid
FROM dept
MINUS
SELECT rowid
FROM dept
AS OF TIMESTAMP TO_TIMESTAMP('2014-07-14', 'YYYY-MM-DD')); 
 
    DEPTNO DNAME          LOC
---------- -------------- -------------
          10 ACCOUNTING     NEW YORK

_____________________________________________________________________________________________________________________

Website Stats