jump to navigation

DBA_TAB_MODIFICATIONS can miss multi-table inserts July 5, 2010

Posted by mwidlake in internals.
Tags: ,
trackback

Last week I blogged a piece all about DBA_TAB_MODIFICATIONS and how it captures all normal DML against table. However, a word of warning. This may not be true for multi-table inserts. It appears that inserts via multi-table inserts are not recorded in DBA_TAB_MODIFICATIONS for 10.2.0.3 and a couple of 9.2 versions of oracle.

{My thanks got to my comrade-in-arms Basavaraj Patil for this. He not only noticed the issue but tracked it down to multi-table inserts, came and told me about it and then said I could put it in my blog. This is his work really}.

I will not go into multi-table inserts right now but it is a very nice feature that was introduced with oracle 9 which allows you to insert a row into several tables at a time or into one of different tables. In fact, I think it is a relatively unknown feature. Check out this oracle-base article on new 9i features that mentions it or go pull up the Oracle 10 SQL reference manual.

There is a bug with Oracle up to 10.2.0.3 and 9.2.0.6 and 9.2.0.7.
It is fixed in 9.2.0.8, 10.2.0.4 and 11.1.0.6
Bug number 4572043 it you have metalink.
It impacts direct load merge statements as well as multi-table inserts, but I have not tested and verified the direct load merge statement.

Below is a demonstration of the issue:

TDB>drop table obj_nonsys purge;

Table dropped.

TDB>drop table obj_sys purge;

Table dropped.

TDB>create table obj_nonsys
  2  as select * from all_objects where rownum<1
  3  /

Table created.

TDB>create table obj_sys
  2  as select * from all_objects where rownum<1
  3  /

Table created.

TDB>insert into obj_sys
  2  select * from dba_objects
  3  where owner in ('SYS','SYSTEM')
  4  and rownum <= 200
  5  /

200 rows created.

TDB>insert into obj_nonsys
  2  select * from dba_objects
  3  where owner not in ('SYS','SYSTEM')
  4  and rownum <= 150
  5  /

150 rows created.

TDB>commit;

Commit complete.
TDB>-- flush the changes down to the DBA_TAB_MODIFICATIONS table.
TDB>exec DBMS_STATS.FLUSH_DATABASE_MONITORING_INFO

PL/SQL procedure successfully completed.

TDB>--
TDB>select table_name,inserts,updates,deletes,truncated,timestamp
  2  from sys.dba_tab_modifications
  3  where table_owner=user and table_name in ('OBJ_SYS','OBJ_NONSYS');

TABLE_NAME      INSERTS    UPDATES    DELETES trnc TIMESTAMP                    
------------ ---------- ---------- ---------- ---- -----------------            
OBJ_NONSYS          150          0          0 NO   05-JUL-2010 20:59            
OBJ_SYS             200          0          0 NO   05-JUL-2010 20:59            

2 rows selected.

TDB>-- As can be seen above, the inserts are correctly captured
TDB>-- And the below counts confirm this
TDB>select count(*) from obj_sys;

  COUNT(*)                                                                      
----------                                                                      
       200                                                                      

1 row selected.

TDB>select count(*) from obj_nonsys;

  COUNT(*)                                                                      
----------                                                                      
       150                                                                      

1 row selected.

TDB>-- Now the core of it. Multi-table inserts
TDB>-- NB this is not the best example of a multi-table insert but it will do.
TDB>insert when (owner='SYS' or owner ='SYSTEM')
  2  	      then into obj_sys values ( OWNER,OBJECT_NAME,SUBOBJECT_NAME,OBJECT_ID,DATA_OBJECT_ID
  3  				       ,OBJECT_TYPE,CREATED,LAST_DDL_TIME,TIMESTAMP,STATUS
  4  				       ,TEMPORARY,GENERATED,SECONDARY)
  5  	    when (owner !='SYS' and owner !='SYSTEM')
  6  	      then into obj_nonsys values ( OWNER,OBJECT_NAME,SUBOBJECT_NAME,OBJECT_ID,DATA_OBJECT_ID
  7  				 ,OBJECT_TYPE,CREATED,LAST_DDL_TIME,TIMESTAMP,STATUS
  8  				 ,TEMPORARY,GENERATED,SECONDARY)
  9  	    select  OWNER,OBJECT_NAME,SUBOBJECT_NAME,OBJECT_ID,DATA_OBJECT_ID
 10  				 ,OBJECT_TYPE,CREATED,LAST_DDL_TIME,TIMESTAMP,STATUS
 11  				 ,TEMPORARY,GENERATED,SECONDARY
 12  	    from dba_objects
 13  	    where object_type='TABLE'
 14  	    and rownum <= 1000
 15  /

1000 rows created.

TDB>commit;

Commit complete.

TDB>exec DBMS_STATS.FLUSH_DATABASE_MONITORING_INFO

PL/SQL procedure successfully completed.

TDB>-- And what do we see in DTM?
TDB>select table_name,inserts,updates,deletes,truncated,timestamp
  2  from sys.dba_tab_modifications
  3  where table_owner=user and table_name in ('OBJ_SYS','OBJ_NONSYS');

TABLE_NAME      INSERTS    UPDATES    DELETES trnc TIMESTAMP                    
------------ ---------- ---------- ---------- ---- -----------------            
OBJ_NONSYS          150          0          0 NO   05-JUL-2010 20:59            
OBJ_SYS             200          0          0 NO   05-JUL-2010 20:59            

2 rows selected.

TDB>-- Argh! No change to the number of inserts! They have been missed
TDB>--
TDB>-- Let me veryify what is in the tables...
TDB>select count(*) from obj_sys;

  COUNT(*)                                                                      
----------                                                                      
      1025                                                                      

1 row selected.

TDB>select count(*) from obj_nonsys;

  COUNT(*)                                                                      
----------                                                                      
       325                                                                      

1 row selected.

TDB>-- And I will do similar inserts to what the multi-table insert would do just to make sure
TDB>-- there is nothing odd going on.
TDB>insert into obj_sys
  2  select * from dba_objects
  3  where object_type='TABLE'
  4  and owner in ('SYS','SYSTEM')
  5  and rownum <= 600
  6  /

600 rows created.

TDB>insert into obj_nonsys
  2  select * from dba_objects
  3  where object_type='TABLE'
  4  and owner not in ('SYS','SYSTEM')
  5  and rownum <= 400
  6  /

400 rows created.

TDB>commit;

Commit complete.

TDB>exec DBMS_STATS.FLUSH_DATABASE_MONITORING_INFO

PL/SQL procedure successfully completed.

TDB>--
TDB>select table_name,inserts,updates,deletes,truncated,timestamp
  2  from sys.dba_tab_modifications
  3  where table_owner=user and table_name in ('OBJ_SYS','OBJ_NONSYS');

TABLE_NAME      INSERTS    UPDATES    DELETES trnc TIMESTAMP                    
------------ ---------- ---------- ---------- ---- -----------------            
OBJ_NONSYS          550          0          0 NO   05-JUL-2010 20:59            
OBJ_SYS             800          0          0 NO   05-JUL-2010 20:59            

2 rows selected.

TDB>select count(*) from obj_sys;

  COUNT(*)                                                                      
----------                                                                      
      1625                                                                      

1 row selected.

TDB>select count(*) from obj_nonsys;

  COUNT(*)                                                                      
----------                                                                      
       725                                                                      

1 row selected.

TDB>
TDB>-- Note, the counts have gone as well of course and now are adrift from DTM
About these ads

Comments»

1. dba_tab_modifications « Oracle Scratchpad - July 6, 2010

[...] the link to Martin Widlake’s blog (see right) very often, he’s done a couple of recent posts on dba_tab_modifications that are worth reading. Leave a [...]

2. Rajabaskar Thangaraj - July 7, 2010

Good One..

Thanks
Rajabaskar Thangaraj

3. Yuri - July 7, 2010

“It is fixed in 8.2.0.8…” might be “It is fixed in 9.2.0.8″

mwidlake - July 7, 2010

Thanks for that, you are right of course. I have corrected the text.


Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Follow

Get every new post delivered to your Inbox.

Join 166 other followers

%d bloggers like this: