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