You are given the task of cleaning up your database like drop unused tables, indexes and procedures.
If you are running Oracle10g and above, consider yourself lucky; otherwise there is no easy way to get this done.
Even this approach has some pitfalls but should be good enough for most requirements. The approach is limited by the policies you set for AWR repository.
Query DBA_HIST_SQLTEXT where COMMAND_TYPE=47 will list all PL/SQL executions. Also you can get description of numbers in the “COMMAND_TYPE” column by querying AUDIT_ACTIONS table.
Query DBA_HIST_SQL_PLAN for OBJECT_NAME to get tables and indexes that are being used
Note: Oracle 10g/11g requires additional licensing to be purchased for using AWR.
Please note that all the methods describe are not 100% reliable depending on whether the data is captured in DBA_HIST or whether your optimizer is picking the correct index or Oracle using indexes for internal purpose like indexes on foreign key to maintain relational integrity constraints. So test , test and validate every approach as every application is different
thanks
Tagged: How to check whether oracle stored procedure is being used, How to check whether Oracle table is being used, How to check whether stored procedure is being used, How to check whether table or index is being used, procedure, unsed procedure
TwelveC 
Thanks , excellent info.. Any idea how to do this in a standard edition ?
Yoosuf
Oracle’s licensing strategy as of now does not allow buying management packs for standard edition. In such cases you can mimic DBA_HIST_SQLTEXT and DBA_HIST_SQL_PLAN by querying V$SQL and V$SQL_PLAN and dumping the data into a table at 5 minute interval or much lesser if possible. Use SQL_ID as filter so that you don’t end up with too many duplicate values. With this approach you are creating your own history tables manually.
Thanks
Cool post as for me. Thnx a lot for posting that information.
Tim Phillsen
I am continually looking online for posts that can benefit me. Thanks!
Thanks thats good info… I have another question, can we know how many times that object (index) being used by application in perticular time frame.
There are couple of ways you can do it but none of them are 100% reliable depending on whether the data is captured in DBA_HIST or got flushed from shared pool before you checked or your optimizer behavior like optimizer picking the wrong index or Oracle using indexes for internal purpose indexes on foreign key to maintain relational integrity constraints. So test and validate every approach as every application is different
1. Index Monitoring : Tried and Trusted method.
2. V$SQL_PLAN: Flush and check timestamp for more accurate reading.
3. DBA_HIST_SQL_PLAN: Contain execution plans for every execution plan change, use timestamp to find the latest plan.
Thanks
Shan
Nice post, good work, keep it up! Great blog!
tech blog, technology news…
[…]How to find if Oracle Procedure/Table/Index is used? « swerveDBA[…]…