2 Replies Latest reply: May 16, 2012 3:07 AM by kuljeet singh - RSS

    table modifications not logged

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

      ------------------------------ ---
      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?


        • 1. Re: table modifications not logged
          kuljeet singh -
          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
          • 2. Re: table modifications not logged
            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;
            Oracle Database 11g Express Edition Release - Production
            PL/SQL Release - Production
            CORE      Production
            TNS for 32-bit Windows: Version - Production
            NLSRTL Version - 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