2 Replies Latest reply: May 16, 2012 3:08 AM by P.Forstmann RSS

    table modifications not logged

    59640
      select table_name, monitoring from dba_tables where table_name='ABC';

      TABLE_NAME MON
      ------------------------------ ---
      ABC YES


      There is no record in dba_tab_modifications for this table, however for the other tables there are records. There are many inserts on this table.

      Is there something I m missing? the table contains a clob column. this might be the cause?

      Regards,

      Peter
        • 1. Re: table modifications not logged
          415289
          These views are populated only for tables with the MONITORING attribute. They are intended for statistics collection over a long period of time. For performance reasons, the Oracle Database does not populate these views immediately when the actual modifications occur. Run the FLUSH_DATABASE_MONITORING_INFO procedure in the DIMS_STATS PL/SQL package to populate these views with the latest information. The ANALYZE_ANY system privilege is required to run this procedure.

          from below doc
          http://docs.oracle.com/cd/B19306_01/server.102/b14237/statviews_2097.htm
          • 2. Re: table modifications not logged
            P.Forstmann
            What is your 4 digits Oracle version ? You need to wait some time so that table statistics are flushed to dictionary or you can trigger this flush with DBMS_STATS.FLUSH_DATABASE_MONITORING_INFO:
            TEST@XE > select * from v$version;
            
            BANNER
            --------------------------------------------------------------------------------
            Oracle Database 11g Express Edition Release 11.2.0.2.0 - Production
            PL/SQL Release 11.2.0.2.0 - Production
            CORE    11.2.0.2.0      Production
            TNS for 32-bit Windows: Version 11.2.0.2.0 - Production
            NLSRTL Version 11.2.0.2.0 - Production
            
            TEST@XE > drop table t purge;
            
            Table dropped.
            
            TEST@XE > create table t(x int, y clob);
            
            Table created.
            
            TEST@XE > alter table t monitoring;
            
            Table altered.
            
            TEST@XE > insert into t values(1,'OK');
            
            1 row created.
            
            TEST@XE > commit;
            
            Commit complete.
            
            TEST@XE > exec dbms_stats.flush_database_monitoring_info;
            
            PL/SQL procedure successfully completed.
            
            TEST@XE > select table_name, inserts
              2  from user_tab_modifications
              3  where table_name='T';
            
            TABLE_NAME                        INSERTS
            ------------------------------ ----------
            T                                       1