from dba_tab_modifications a,dba_tab_partitions b where a.table_owner =b.table_owner and a.table_name=b.table_name
and a.partition_name=b.partition_name and a.table_owner not in ('SYS', 'SYSTEM', 'SYSMAN', 'DMSYS', 'OLAPSYS', 'XDB',
'EXFSYS', 'CTXSYS', 'WMSYS', 'DBSNMP', 'ORDSYS','OUTLN', 'TSMSYS', 'MDSYS')
group by a.table_owner,a.table_name,a.partition_name
having (sum(a.inserts)/decode(sum(b.num_rows),0,1,sum(b.num_rows)))>=0.1
or
(sum(a.updates)/decode(sum(b.num_rows),0,1,sum(b.num_rows)))>=0.1
or
(sum(a.deletes)/decode(sum(b.num_rows),0,1,sum(b.num_rows)))>=0.1
order by a.table_name;
begin
dbms_output.enable(1000000);
----flush the monitorring information into the dba_tab_modifications
DBMS_STATS.FLUSH_DATABASE_MONITORING_INFO;
----display the top_n_insert information-------------------
dbms_output.put_line('- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -' );
dbms_output.put_line('Over the Change_Rate 10%:');
for v_topinsert in overchangerate loop
dbms_output.put_line(v_topinsert.table_owner || '.' || v_topinsert.table_name || ' partition ' || v_topinsert.partition_name || ' once has ' || v_topinsert.num_rows || ' rows, ' ||
'till now inserted ' || v_topinsert.inserts || ' rows, updated ' || v_topinsert.updates || ' rows, deleted ' || v_topinsert.deletes ||
' rows. consider gathering statistics');
end loop;
dbms_output.put_line('- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -' );