Finding INSERT statements and Object_name between 2 AWR snapshots
We have a requirement of (on a daily basis) gathering the information of all the 'INSERT' statements between 2 given AWR snapshots
The output should be like following:
Object Owner Object Name count(*)
========================================
APPS1 TABLE-A 55
APPS1 TABLE-B 32
APPS2 TABLE-Z 20
We tried using the DBA_HIST_SQL_PLAN alongwith DBA_HIST_SQLSTAT having a WHERE clause of --> where operation='INSERT'
but it did not work because DBA_HIST_SQL_PLAN is recording and storing these INSERTS as OPERATION='TABLE ACCESS' and option='BY INDEX ROWID'.
Anyways, We used the following SQL to gather the SQL ID's of INSERT information
select p.sql_id,count(*)
The output should be like following:
Object Owner Object Name count(*)
========================================
APPS1 TABLE-A 55
APPS1 TABLE-B 32
APPS2 TABLE-Z 20
We tried using the DBA_HIST_SQL_PLAN alongwith DBA_HIST_SQLSTAT having a WHERE clause of --> where operation='INSERT'
but it did not work because DBA_HIST_SQL_PLAN is recording and storing these INSERTS as OPERATION='TABLE ACCESS' and option='BY INDEX ROWID'.
Anyways, We used the following SQL to gather the SQL ID's of INSERT information
select p.sql_id,count(*)
0