Re: dba_tab_mod view

From: Mladen Gogala <mgogala_at_yahoo.com>
Date: Tue, 12 May 2015 21:33:24 -0400
Message-ID: <5552A9E4.9070501_at_yahoo.com>

On 05/11/2015 07:12 PM, Ram Raman wrote:
> List,
> After seeing lots of global services enqueue deadlock errors in our
> rac, I was looking up the trace files. I was able to see the sql; the
> trace files are huge (in GBs) . The sql was simple and was updating
> one table only repeatedly. I wanted to know how many updates had
> happened in the table, but the dba_tab_mod view is empty for that
> table. The table was last analyzed last night. Does this mean that the
> updates did not happen at all due the deadlock errors?
>
> To my knowledge, the DTM view is emptied out after a stats collection.
> Is there anything else that would cause the no rows selected for a
> table from that view. v11.2
> Ram.
>

Did you turn on monitoring for the table? Things work something like this:

    SQL> create table emp1 as select * from emp;

    Table created.

    _*SQL> alter table emp1 monitoring;
    *_
    Table altered.

    SQL> insert into emp1 select * from emp;

    14 rows created.

    SQL> /     14 rows created.

    SQL> commit;

    Commit complete.

Then, connect as a DBA user and do the following:

First, flush the monitoring info and rinse well:

    _*SQL> exec dbms_stats.FLUSH_DATABASE_MONITORING_INFO;*_

    _*PL/SQL procedure successfully completed.*_

After that, you can enjoy the view:

    SQL> select table_owner,table_name,inserts from dba_tab_modifications

       2 where table_owner='SCOTT';

    TABLE_OWNER TABLE_NAME                     INSERTS
    ------------------------------ ------------------------------ ----------
    SCOTT                          EMP1                        28

That's about it.

-- 
Mladen Gogala
Oracle DBA
http://mgogala.freehostia.com


--
http://www.freelists.org/webpage/oracle-l

Received on Wed May 13 2015 - 03:33:24 CEST