Re: updated rows

From: Thomas Kyte <tkyte_at_oracle.com>
Date: 26 Jan 2005 10:08:55 -0800
Message-ID: <116762935.00000941.045@drn.newsguy.com>

In article <4zPJd.2501$Ny6.4498_at_mencken.net.nih.gov>, hastenthunder says...
>
>
>"hastenthunder" <hastenthunder_at_hotmail.com> wrote in message
>news:ByPJd.2500$Ny6.4328_at_mencken.net.nih.gov...
>> Hello,
>>
>> Is there a easy way for a DBA to get all the rows in a particular table
>that
>> have been updated since a specified timestamp?
>>
>> Thanks
>>
>>
>>
>
>By the way I'm running Oracle 10g, thanks!
>
>

there are a variety of possible answers -- if the table has a "column maintained by a trigger", then sure -- "where last-modified >= sysdate-1" for example.

If not, and the needs are "from the recent past", you might be able to use flashback query, this is built on the query talked about in this article:

Comparing the Contents of Two Tables
http://www.oracle.com/technology/oramag/oracle/05-jan/o15asktom.html

scott_at_ORA9IR2> create or replace procedure compare_it( p_tname in varchar2, p_asof in date, p_cur in out sys_refcursor )   2 as
  3 l_cols long;
  4 begin
5 for x in ( select column_name from user_tab_columns where table_name = p_tname )

  6      loop
  7          l_cols := l_cols || x.column_name || ',';
  8      end loop;
  9
 10      open p_cur for '
 11      select ' || l_cols ||
 12             'count(src1) cnt1, count(src2) cnt2
 13        from (select a.*, 1 src1, to_number(null) src2
 14                from ' || p_tname || ' a
 15               union all
 16              select b.*, to_number(null) src1, 1 src2
 17                from ' || p_tname || ' as of timestamp :x b)
 18       group by ' || rtrim(l_cols,',') || '
 19      having count(src1) <> count(src2)' using p_asof;

 20 end;
 21 /  

Procedure created.  

scott_at_ORA9IR2> variable x refcursor
scott_at_ORA9IR2> exec compare_it( 'EMP', sysdate-1, :x );  

PL/SQL procedure successfully completed.  

scott_at_ORA9IR2> print x  

     EMPNO ENAME JOB MGR HIREDATE SAL COMM ---------- ---------- --------- ---------- --------- ---------- ----------

    DEPTNO CNT1 CNT2
---------- ---------- ----------

      7369 SMITH      CLERK           7902 17-DEC-80        800
        20          0          1
 
      7369 Smith      CLERK           7902 17-DEC-80        800
        20          1          0
 
      7499 ALLEN      SALESMAN        7698 20-FEB-81       1600        300
        30          0          1
 
      7499 Allen      SALESMAN        7698 20-FEB-81       1600        300
        30   

that shows there are two rows in there that have been "changed" between right now and a day ago.

Logminer would be another technique that could be used for longer durations of time.

-- 
Thomas Kyte
Oracle Public Sector
http://asktom.oracle.com/
opinions are my own and may not reflect those of Oracle Corporation

Received on Wed Jan 26 2005 - 12:08:55 CST