sql query to fetch all tables in a schema that was updated on sysdate

Question

need to write a sql query to fetch all tables in a schema that was updated on sysdate.

select  distinct(table_name)
from All_Tab_Columns 
where owner = 'DBO' 
      and last_analyzed = sysdate;

It doesn't seem to work properly.

Answer 1

You need to apply TRUNC function on last_analyzed and sysdate and then it will work

select  distinct(table_name)
from All_Tab_Columns 
where owner = 'DBO' 
      and trunc(last_analyzed) = trunc(sysdate);

Answer 2

As mentioned in answers to the question I linked to, you can use the ORA_ROWSCN pseudo-column to get an idea of when the table was last updated. This will example all tables in your schema and list those which were modified on the specified date, according to the ORA_ROWSCN. This may take a while to run, of course.

set serveroutput on
declare
  last_update varchar2(10);
  bad_scn exception;
  no_scn exception;
  pragma exception_init(bad_scn, -8181);
  pragma exception_init(no_scn, -1405);
begin
  for r in (select table_name from all_tables where owner = 'DBO') loop
    begin
      execute immediate 'select to_char(scn_to_timestamp(max(ora_rowscn)), '
          || '''YYYY-MM-DD'') from DBO.' || r.table_name
        into last_update;
      if last_update = '2014-02-21' then
        dbms_output.put_line(r.table_name || ' last updated on ' || last_update);
      end if;
    exception
      when bad_scn then
        dbms_output.put_line(r.table_name || ' - bad scn');
      when no_scn then
        dbms_output.put_line(r.table_name || ' - no scn');
    end;
  end loop;
end;
/

The exception handlers are covering views (which are listed but have no SCN), and where there is an invalid SCN for some reason; you may want to ignore those rather than displaying them.

If you are only looking for today, not a specific date, then this might be faster:

declare
  start_scn number;
  changed_rows number;
  changed_tables number := 0;
begin
  start_scn := timestamp_to_scn(trunc(systimestamp));
  for r in (select table_name from all_tables where owner = 'BDO'
      order by table_name) loop
    execute immediate 'select count(*) from ('
        || 'select ora_rowscn from BDO.' || r.table_name
        || ') where ora_rowscn >= :1 and rownum < 2'
      into changed_rows
      using start_scn;
    if changed_rows > 0 then
      dbms_output.put_line(r.table_name || ' updated');
      changed_tables := changed_tables + 1;
    end if;
  end loop;
  dbms_output.put_line(changed_tables || ' tables updated today');
end;
/

You could do the same thing for any date really but you'd need to find the earliest and latest SCN for that day (which is more complicated for the current date). Also note that this may only work within your flashback window - if you go back to far you won't be able to translate an SCN to a timestamp anyway.

Answer 3

There is no easy way to do that. You have to operate table by table. Then execute this query on each table:

select max(SCN_TO_TIMESTAMP(ORA_ROWSCN)) from <table_name>;

ORA_ROWSCN is Oracle virtual pseudo-column, it is stored on block level. It contains "a sequence number" of the last transaction, which modified the database block.

The function SCN_TO_TIMESTAMP converts it into human readable date datatype.

Answer 4

You can use DMV (works on HEAPs as well - i.e. tables with no indexes) - you can expand to join on schemas

SELECT 
    OBJECT_NAME(OBJECT_ID) AS TableName, last_user_update AS UpdateDateTime
FROM 
    sys.dm_db_index_usage_stats
WHERE 
    database_id = DB_ID('PUT_DB_NAME')
    AND last_user_update = 'EnterDateTimeHereToFilterOn'