Home > oracle, tutorial > Monitoring DML statements in Oracle

Monitoring DML statements in Oracle

During the last months we are upgrading the databases to oracle 10gR2.

I want to know if an application is being used in order to avoid migrating it and thus saving resources (and working time).

We have a lot of applications (more than 400) and sometimes is difficult to know if an application is being used (please, dont ask me why, I neither understand it!).

Our applications are mainly on JBoss, so we are using connection pools to access the database. So I cannot monitor access of users to the database, because they are always connected.

I decided to monitor the DML modifications of all the tables of an application. Maybe it’s not the best approah, but it’s easy. We gather schema stats weekly, so we can consider that if we don’t have DML operations during a couple of weeks, we can reach the conclussion that the application is not being used.

To monitor the DML statements, first we have to enable monitoring on that particular schema (connected as the user we want to monitor):

SQL> select 'alter table ' || table_name || ' monitoring;' from user_tables;
SQL> exec dbms_stats.gather_schema_stats(ownname=>'<owner>', cascade=>true,estimate_percent=>100,method_opt=>'for all columns size repeat');

Now I’m able to monitor DML statements:

SQL> select * from user_tab_modifications;

The information is not reflected immediately. To populate the table, connect as system to flush the information gathered. Then you can query the dba_tab_modifications or user_tab_modifications:

SQL> conn system@<database>
SQL> exec dbms_stats.flush_database_monitoring_info;
SQL> select * from dba_tab_modifications;
SQL> conn owner@<database>
SQL> select * from user_tab_modifications;
Categories: oracle, tutorial Tags: , ,
  1. No comments yet.
  1. No trackbacks yet.

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 )

Google+ photo

You are commenting using your Google+ 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 )


Connecting to %s

%d bloggers like this: