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

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
DBA Tips Data Pump Reference

0 comments:

Post a Comment

 

dba topics. Copyright 2011-16 All Rights Reserved | Site Map | Contact | Disclaimer | Google