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';

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
    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
    http://docs.oracle.com/cd/B19306_01/server.102/b14237/statviews_2097.htm
  • 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;
    
    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

Legend

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