Oracle

TimeStamp of Change To a Row In Oracle Table

Objective:

To find the recent modified date time of the object in oracle can be find out by using the following scripts. If we keep only the schema restriction, it can pull out all the tables from that owner or schema (Date and Time of Most Recent Change To a Row In Oracle Table)

Many of the technical experts trying to find out when the table lastly modified for several reasons. This below script is a handy tool for them to assist.

 

    declare
    v_count TIMESTAMP;
    strsql VARCHAR2(200);
    tblname VARCHAR2(32);
begin

    for r in (select OBJECT_NAME from all_objects where OBJECT_TYPE='TABLE' 
    and OWNER='OWNERNAME' 
    and OBJECT_NAME like 'TABLENAME%'  -- ANY OBJECT NAME CAN BE WRITTEN HERE 
    ORDER BY OBJECT_NAME) 
    loop
      begin
        tblname := R.OBJECT_NAME;
        strsql := 'select SCN_TO_TIMESTAMP(MAX(ORA_ROWSCN)) FROM OWNERNAME.' || tblname;
        execute immediate strsql into v_count;
        DBMS_OUTPUT.PUT_LINE(tblname || ',' || v_count);
        EXCEPTION
        when others then 
          null;
         end;

 

Leave a Reply

Your email address will not be published. Required fields are marked *