How to Check When was the Table Insert, Update, Delete Done on Oracle Tables or Partitions

advertisements

_____________________________________________________________________________________________________________________

This is done with the help of the DBMS_STATS. FLUSH_DATABASE_MONITORING_INFO package. This package flushes in-memory monitoring information for all the tables to the dictionary.

This procedure is useful when you need up-to-date information in *_TAB_MODIFICATIONS, *_TAB_STATISTICS and *_IND_STATISTICS 

SQL Query

SET PAGES 1000 LINES 222
COL TABLE_OWNER FOR A15
COL TABLE_NAME FOR A15
COL PARTITION_NAME FOR A15
COL SUBPARTITION_NAME FOR A15
alter session set nls_date_format='DD-MM-YYYY HH24:MI:SS';

select * 
from 	dba_tab_modifications
where 	table_name='ROLE';

advertisements

 

Demo 
Execute the package.

exec DBMS_STATS.FLUSH_DATABASE_MONITORING_INFO;

PL/SQL procedure successfully completed.

SQL> insert into role values (103, 'IT Manager');

1 row created.

SQL> UPDATE ROLE SET DESCRIPTION='IT EXECUTIVE' WHERE ROLE_ID=103;

1 row updated.

SQL> commit;

Commit complete.

SQL> delete from role where role_id=103; 

1 row deleted.

SQL> commit;

Commit complete.

SQL> alter session set nls_date_format='DD-MM-YYYY HH24:MI:SS';

Session altered.

SQL> SELECT TABLE_OWNER , TABLE_NAME, INSERTS, UPDATES, DELETES, TIMESTAMP FROM DBA_TAB_MODIFICATIONS WHERE TABLE_NAME ='ROLE';

TABLE_OWNER	TABLE_NAME	   INSERTS    UPDATES	 DELETES TIMESTAMP
--------------- --------------- ---------- ---------- ---------- -------------------
TEST		ROLE			 1	    1	       1 17-10-2019 06:22:28

Example on Table Partition

SQL> CREATE TABLE "PART_TAB"
   (    "ID" number(10) NOT NULL ENABLE
   ) PARTITION BY RANGE(ID)
(PARTITION max_value values less than (maxvalue));  

Table created.

SQL> insert into part_tab values (100);

1 row created.

SQL> commit; 

Commit complete.

SQL> SELECT TABLE_OWNER , TABLE_NAME, PARTITION_NAME, SUBPARTITION_NAME,TIMESTAMP 
FROM dba_tab_modifications
WHERE table_name='PART_TAB';
 
TABLE_OWNER	TABLE_NAME	PARTITION_NAME	SUBPARTITION_NA TIMESTAMP
--------------- --------------- --------------- --------------- -------------------
TEST		PART_TAB					17-10-2019 06:52:29
TEST		PART_TAB	MAX_VALUE			17-10-2019 06:52:29

_____________________________________________________________________________________________________________________

Website Stats