Identifying Unused Objects in Database

Sometimes in the database, there exist objects that have never been used. As you can guess they are junk objects and are waisting necessary space in the database. So we should identify them and remove from the DB. Oracle offers us some useful techniques to do this.

First of all, you should turn on monitoring process on that objects that are the most candidates of junk.

For example:

Assume that index sal_indx is considered to be the object, which has never been used. For to identify it, let’s turn on monitoring on that index.

–In your schema

alter index sal_indx monitoring usage;

–In other schema

alter index schemaName.sal_indx monitoring usage;

To check if it is monitored, query the following view

select index_name
       ,table_name
       ,monitoring as mon
       ,used
       ,start_monitoring
from v$object_usage;

–The Result

INDEX_NAME | TABLE_NAME | MON | USED | START_MONITORING
-----------------------------------------------------------
SAL_INDX   | SALARY     | YES | NO   | 02/13/2011 21:48:25

Note: the result shows that this index has not been used since we tuned on monitoring, but it still doesn’t mean that this object is junk. It depends on what is the maximum period of time in which this object should be used. For example, if you know that this object should be used minimum once in a day, let it to be monitored till tomorrow, then query again v$object_usage view and you will see if this object has been used since yesterday or not and identify its necessity.

For example, someone run the following query:

select *
from salary
where id=1;

Assume that index sal_indx is created on id column of salary table.

–Check again the v$object_usage view

select index_name
       ,table_name
       ,monitoring as mon
       ,used
       ,start_monitoring
from v$object_usage;

–The Result

INDEX_NAME | TABLE_NAME | MON | USED | START_MONITORING
-----------------------------------------------------------
SAL_INDX   | SALARY     | YES | YES  | 02/13/2011 21:48:25

From here you can identify that this index is necessary and should not be removed from database.

Turning Off Monitoring

–In your schema

alter index sal_indx nomonitoring usage;

–In other schema

alter index schemaName.sal_indx nomonitoring usage;

–Check if the monitoring is disabled

select index_name
       ,table_name
       ,monitoring as mon
       ,used
       ,start_monitoring
from v$object_usage;

–The Result

INDEX_NAME | TABLE_NAME | MON | USED | START_MONITORING
-----------------------------------------------------------
SAL_INDX   | SALARY     | NO  | YES  | 02/13/2011 21:48:25