Shulou(Shulou.com)06/01 Report--

Learn the role of DBA_TAB_MODIFICATIONS views by testing

DBA_TAB_MODIFICATIONS describes modifications to all tables in the database that have been modified since the last time statistics were gathered on the tables. Its columns are the same as those in "ALL_TAB_MODIFICATIONS" .DBA _ TAB_MODIFICATIONS describes the DML operation of all tables in the database after the statistics have been collected. Note:This view is populated only for tables with the MONITORING attribute. It is intended for statistics collection over a long period of time. For performance reasons, the Oracle Database does not populate this view immediately when the actual modifications occur. Run the FLUSH_DATABASE_MONITORING_INFO procedure in the DBMS_STATS PL/SQL package to populate this view with the latest information. The ANALYZE_ANY system privilege is required to run this procedure.SQL > desc dba_tab_modifications; Name Null? Type-TABLE_OWNER VARCHAR2 128TABLE_NAME VARCHAR2 (128) PARTITION_NAME VARCHAR2 (128) SUBPARTITION_NAME VARCHAR2 (128) INSERTS NUMBER # # insert UPDATES NUMBER # # Update DELETES NUMBER # # Delete TIMESTAMP DATE TRUNCATED VARCHAR2 (3) # # truncate DROP_SEGMENTS NUMBERSQL > SQL > select * from v$version where rownum=1 BANNER---- CON_ID-Oracle Database 12c Enterprise Edition Release 12.1.0.2.0-64bit Production 0SQL > create table T1 as select * from dba_objects Table created.SQL > select table_owner,table_name,inserts,UPDATES,deletes,timestamp,truncated,drop_segments from sys.dba_tab_modifications where table_name='T1';no rows selectedSQL > update T1 set object_id=1 where object_id=30;1 row updated.SQL > select table_owner,table_name,inserts,UPDATES,deletes,timestamp,truncated,drop_segments from sys.dba_tab_modifications where table_name='T1';no rows selectedSQL > exec dbms_stats.flush_database_monitoring_info PL/SQL procedure successfully completed.SQL > select table_owner,table_name,inserts,UPDATES,deletes,timestamp,truncated,drop_segments from sys.dba_tab_modifications where table_name='T1' TABLE_OWNER----TABLE_NAME-- -INSERTS UPDATES DELETES TIMESTAMP TRU DROP_SEGMENTS -SYST1 01 0 19-FEB-2018 06:59:33 NO 0SQL > exec dbms_stats.flush_database_monitoring_info PL/SQL procedure successfully completed.SQL > select table_owner,table_name,inserts,UPDATES,deletes,timestamp,truncated,drop_segments from sys.dba_tab_modifications where table_name='T1' TABLE_OWNER----TABLE_NAME-- -INSERTS UPDATES DELETES TIMESTAMP TRU DROP_SEGMENTS -SYST1 01 0 19-FEB-2018 06:59:33 NO 0SQL > exec dbms_stats.gather_table_stats ('SYS' 'T1') PL/SQL procedure successfully completed.SQL > select table_owner,table_name,inserts,UPDATES,deletes,timestamp,truncated,drop_segments from sys.dba_tab_modifications where table_name='T1';no rows selectedSQL >

Summary:

1. If the dml operation is not submitted, it will be recorded in the view.

2. Considering the performance problem, we need to manually flush to record it in the view.

3. After collecting the statistical information, the relevant table records in the view are empty, as described in the official document.

Test 2:

SQL > alter system set "_ dml_monitoring_enabled" = false scope=memory;System altered.SQL > delete from T1 * 90974 rows deleted.SQL > select table_owner,table_name,inserts,UPDATES,deletes,timestamp,truncated,drop_segments from sys.dba_tab_modifications where table_name='T1';no rows selectedSQL > exec dbms_stats.flush_database_monitoring_info;PL/SQL procedure successfully completed.SQL > select table_owner,table_name,inserts,UPDATES,deletes,timestamp,truncated,drop_segments from sys.dba_tab_modifications where table_name='T1';no rows selectedSQL >

Summary:

After the monitor is turned off, no matter what we do, this view does not record the actions related to dml.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.