DBA_TAB_MODIFICATIONS can miss multi-table inserts July 5, 2010
Posted by mwidlake in internals.Tags: data dictionary, SQL
4 comments
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
Martin Widlake's Yet Another Oracle Blog 