Wednesday, July 31, 2013

Finding out Last DML Activity on a Table

create a function for the DML logging 
 
create or replace function scn_to_timestamp_safe(p integer) return timestamp is
  e_too_old_scn exception;
  pragma exception_init(e_too_old_scn,-8181);
begin
  return
     case
       when p is not null then scn_to_timestamp(p)
       else null
     end;
exception
  when e_too_old_scn then
    return null;
end;
/
 
 now querying the last DML (insert,update,delete) for table and this cant say if 
there was any select query run or not.
 
 select 
  t.owner||'.'||t.table_name
,extractvalue( dbms_xmlgen.getXMLtype(q'[select nvl(scn_to_timestamp_safe
(max(ora_rowscn)),timestamp'0001-01-01 00:00:00') t 
from "]'||t.owner||'"."'||t.table_name||'"')
               ,'/ROWSET/ROW/T'
              ) last_dml
from
  all_tables t
where
    t.IOT_TYPE is null
and t.TEMPORARY='N'
and t.NESTED='NO';
 
 
add owner if you want tables in particular schema at the end of sql.
 
 >>>     AND OWNER IN ('SCOTT'); 
 


 

No comments:

Post a Comment