This discussion is archived
2 Replies Latest reply: May 16, 2012 1:07 AM by KuljeetPalSingh RSS

table modifications not logged

59640 Newbie
Currently Being Moderated
select table_name, monitoring from dba_tables where table_name='ABC';

------------------------------ ---

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
    KuljeetPalSingh Guru
    Currently Being Moderated
    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
    P.Forstmann Guru
    Currently Being Moderated
    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


  • Correct Answers - 10 points
  • Helpful Answers - 5 points