Why does DBA_TAB_MODIFICATIONS sometimes have no values [ID

Why does when sometimes have no values [ID 762738.1] Why does DBA_TAB_MODIFICATIONS sometimes have no values [ID 762738.1] When Modified 18-MAR-2009 Type HOWTO Status MODERATED In this Document Goal Solution References when -------------------------------------------------------------------------------- this document is being delivered to you via Oracle Support's Rapid Visibility (RaV) process, and therefore has not been subject to an independent technical review. applies to: Oracle Server-Enterprise Edition-Version: 10.2.0.3Information in this document applies to any platform. oracle Server Enterprise Edition-Version: 10.2.0.3 GoalThe goal is to explain why the view has does sometimes have no values even when the parameter STATISTICS_LEVEL is set to TYPICAL and the specific schema has been analyzed successful using the package quota. in addition all the tables in that schema shows MONITORING = YES in the view dba_tables. solutionThe updates to the table * _ tab_modifications are related to the volumne of updates for a table. there is a need of approximatly 10% of datavolumn changes. just only on single update of the row for example might not lead to fill the * _ tab_modifications. see example below: STEP1: *** create a table crc. gs, analyze it and then fill test_gs.gs with 100 rows and perform some DML tables create user crc identified by crcdefault tablespace users temporary tablespace temp; grant connect, resource to crc; connect crc/crc alter session set nls_language = american; alter session set nls_date_format = 'dd-MM-YY HH24: MI: ss'; create table crc. gs (I number); begin percent (ownname => 'crc ', estimate_percent => dbms_stats.auto_sample_size, method_opt =>' FOR ALL COLUMNS SIZE auto', degree => 1, granularity => 'all', cascade => true, options => 'gather '); end;/begin for I in 1 .. 100 loop insert into CRC. gs values (I); end loop; commit; end;/delete from CRC. gs where I between 40 and 60; commit; update CRC. gs set I = I + 1000 where I between 80 and 100; commit; STEP2: *** select and use the procedure DBMS_STATS.FLUSH_DATABASE_MONITORING_INFO release Note: the procedure DBMS_STATS.FLUSH_DATABASE_MONITORING_INFO flushes in-memory monitoring information for all tables in the dictionary. corresponding entries in the * _ TAB_MODIFICATIONS, * _ TAB_STATISTICS and * _ IND_STATISTICS views are updated immediately, without waiting for the Oracle database to flush them periodically (per default every 3 hours ). this procedure is useful when you need up-to-date information in those views. SQL> select TABLE_OWNER, TABLE_NAME, INSERTS, UPDATES, DELETES, TIMESTAMP from 2 sys. dba_tab_modifications where TABLE_OWNER = 'crc '; no rows selected SQL> execute DBMS_STATS.FLUSH_DATABASE_MONITORING_INFO; PL/SQL procedure successfully completed. SQL> col table_name format a5SQL> col table_owner format a10SQL> select TABLE_OWNER, TABLE_NAME, INSERTS, UPDATES, DELETES, TIMESTAMP from 2 sys. dba_tab_modifications where TABLE_OWNER = 'crc '; TABLE_OWNER table inserts updates deletes timestamp ------------- ----- ---------- ------------ large crc gs 100 21 21 18-03-09 15:34:37 ==> Because of the 'high' volumne of DML (100 inserts, 21 updates and 21 deletes) we have an entry in the table sys. dba_tab_modifications for the table 'gs '. step 3: *** analyze again the table GS which leads to an empty sys. DBA_TAB_MODIFICATIONS for table 'gs 'begin SQL> begin 2 tables (3 ownname => 'crc', 4 estimate_percent => dbms_stats.auto_sample_size, 5 method_opt => 'FOR ALL COLUMNS SIZE auto ', 6 degree => 1, 7 granularity => 'all', 8 cascade => true, 9 options => 'gather '10); 11 end; 12/PL/SQL procedure successfully completed. SQL> select TABLE_OWNER, TABLE_NAME, INSERTS, UPDATES, DELETES, TIMESTAMP from 2 sys. dba_tab_modifications where TABLE_OWNER = 'crc '; no rows selected => which is normal due to the analyze command STEP4: *** now perform only 1 update on the table, flush the monitoring information out and then check the entry in DBA_TAB_MODIFICATIONS --------------------------------------------------------- SQL> update crc. gs set I = I + 100 where I = 30; 1 row updated. SQL> commit; Commit complete. SQL> execute DBMS_STATS.FLUSH_DATABASE_MONITORING_INFO; PL/SQL procedure successfully completed. SQL> select TABLE_OWNER, TABLE_NAME, INSERTS, UPDATES, DELETES, TIMESTAMP from 2 sys. dba_tab_modifications where TABLE_OWNER = 'crc '; no rows selected Note: please be aware that this is only an example. other values/dml changes may show different results. referencesNOTE: 456535.1-DB Monitoring Automatic