Oracle

How to check last modified table in Oracle

As a DBA, application team sometimes might ask you to provide details of last modi ed table in oracle. The table modi cation can be insert, update or delete. Below queries get details of last or latest modi ed table in oracle database. Run the queries depending upon the database version.

Last modified table in oracle 10g and Above

set linesize 500;
select TABLE_OWNER, TABLE_NAME, INSERTS, UPDATES, DELETES, to_char(TIMESTAMP,'YYYY-MON-DD HH24:MI:SS') from all_tab_modifications
where table_owner<>'SYS' and
EXTRACT(YEAR FROM TO_DATE(TIMESTAMP, 'DD-MON-RR')) > 2010 order by 6;

In 9i, table monitoring has to be enabled manually or else the all_tab_modifcations wont keep record of changes. 10g onwards, oracle by default records the modifications

READ ALSO
How to check database backups via sqlplus

Last modified table in oracle for 9i db

sql">col object for a20;
col object_name for a20;
SELECT OWNER, OBJECT_NAME, OBJECT_TYPE,
to_char(LAST_DDL_TIME,'YYYY-MON-DD HH24:MI:SS')
from dba_objects where LAST_DDL_TIME=(select max(LAST_DDL_TIME)
from dba_objects WHERE object_type='TABLE');

 

 

 

 

 

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.