How to find if Oracle Procedure/Table/Index is used?

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

Advertisements

Tagged: , , , , ,

8 thoughts on “How to find if Oracle Procedure/Table/Index is used?

  1. Yusuf June 17, 2011 at 4:02 am Reply

    Thanks , excellent info.. Any idea how to do this in a standard edition ?

    Yoosuf

    • swervedba June 18, 2011 at 12:02 pm Reply

      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

  2. TimPhillsen December 1, 2011 at 2:30 pm Reply

    Cool post as for me. Thnx a lot for posting that information.

    Tim Phillsen

  3. Tommie Leonti December 9, 2011 at 12:00 am Reply

    I am continually looking online for posts that can benefit me. Thanks!

  4. Lakshman September 6, 2012 at 11:12 am Reply

    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.

    • Shan Nawaz September 12, 2012 at 4:31 pm Reply

      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

  5. MagixITSystemRequirements September 20, 2012 at 6:44 am Reply

    Nice post, good work, keep it up! Great blog!

  6. tech blog, technology news September 22, 2012 at 7:47 am Reply

    tech blog, technology news…

    […]How to find if Oracle Procedure/Table/Index is used? « swerveDBA[…]…

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 )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: