TwelveC

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