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

_____________________________________________________________________________________________________________________

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

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';

Example
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

_____________________________________________________________________________________________________________________

0 comments:

Post a Comment

 

acehints.com Copyright 2011-19 All Rights Reserved | Site Map | Contact | Disclaimer | Google