Mir Sayeed Hassan – Oracle Blog

Monitoring any table operation from your own schema in Oracle database

SQL> conn mir/mir123;
Connected.

SQL> select table_name from user_tables;

TABLE_NAME
------------
TEST1

SQL> conn sys/sys as sysdba
Connected.

SQL> alter table mir.test1 monitoring;
Table altered.

SQL> Select * from User_Tab_Modifications;

SQL> exec dbms_stats.flush_database_monitoring_info;
PL/SQL procedure successfully completed.

SQL> select * from dba_tab_modifications;

SQL> set long 999999

SQL> select TABLE_OWNER,TABLE_NAME,INSERTS,UPDATES,DELETES,to_char(TIMESTAMP,'dd-Mon-yyyy hh24:mi:ss') TIMESTAMP,TRUNCATED,DROP_SEGMENTS from DBA_TAB_MODIFICATIONS where TABLE_OWNER in ('MIR') and TABLE_NAME = 'TEST1';

TABLE_OWNER   TABLE_NAME        INSERTS   UPDATES    DELETES        TIMESTAMP                TRU     DROP_SEGMENTS
----------------------------------- --------------------- --- -----------------------------------------------------
MIR                                TEST1                        8                         4                8        31-Dec-2017 14:48:01           YES                      0

SQL> alter table mir.test1 nomonitoring;
Table altered.

======Hence the above scenario is tested by enabling monitoring for table in schema level======